Dual purpose command button

[Renee]

Hello all,
I am using a command button on a form to open another form.
The first form (frm_Orders) is bound to tbl_Orders. The 2nd
(frm_OrderErrors) is bound to tbl_OrderErrors. tbl_Orders is related in a 1
to 1 relationship to tbl_OrderErrors using RecordID.
The relationship is set to enforce referential integrity, with cascading
updates & deletes.

On the OnClick event of the command button I want to do one of two things:
If there is already an error record for this order:
Open the frm_OrderErrors where the RecordID matches the RecordID of
frm_Orders
Otherwise, start a new record with the RecordID of frm_OrderErrors set to
match the RecordID of frm_Orders.

I have tried various combinations of:
-----------------------------------
On the command Button
-----------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_OrderErrors"

stLinkCriteria = "[RecordID]=" & Me![RecordID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.RecordID
-----------------------------------
On the Form Load Event of frm_OrderErrors
-----------------------------------
If Me.Recordset.RecordCount = 0 Then
DoCmd.RunSQL "INSERT INTO
tbl_OrderErrors([RecordID])VALUES(Forms!frm_Orders!RecordID)"
End If
-----------------------------------
On the Form Open Event of frm_OrderErrors
-----------------------------------
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord, , acNewRec
End If

Any suggestions would be greatly appreciated!

Thank you,
Renee

 

[tina]

i wouldn't suggest using Insert to create a new record. if the user opens
the Errors form accidentally, for example, then there is no data to be
entered - yet the record exists and needs to be deleted somehow.

instead, you could try this:

add the following code to the command button's OnClick event, as

If Not IsNull(Me!RecordID) Then
DoCmd.OpenForm "frm_OrderErrors", , , "RecordID = " & Me!RecordID, , ,
Me!RecordID
End If

the DoCmd action should be all on one line, of course.

add the following code to the OnCurrent event of frm_OrderErrors, as

If Me.NewRecord Then
Me!OrderID = Me.OpenArgs
End If

as long as frm_OrderErrors is set to allow addition of new records, the
result should be either a) frm_OrderErrors opens showing only the existing
record that matches the current record in frm_Orders, or b) frm_OrderErrors
should open to a new record and automatically enter the OrderID, using the
OrderID value from the current record in frm_Orders. if you don't want to
allow more than one matching record to be entered in frm_OrderErrors by the
user, then try changing the OnCurrent code to:

If Me.NewRecord Then
Me!OrderID = Me.OpenArgs
Else
Me!AllowAdditions = False
End If

hth

 

[Klatuu]

I think tina is going in the right direction, but I would make a couple of
suggestions.
1. Why use the old style docom.openform...?
I would suggest [forms]![frm_OrderErros].....

2. I would also put the intelligence in the command button's On Click Event
in frm_Orders. I wont take time to write the code, but here is the logic:
before coding, create a query that will retrieve only the error record if it
exists, then

execute the query

if record count = 0 then
add a record to the table
endif
open the errors form

Also, to handle tina's concern about adding a record you don't want, in the
Form close event of the errors form, check to see if the record has any data.
If it does not, delete it

i wouldn't suggest using Insert to create a new record. if the user opens
the Errors form accidentally, for example, then there is no data to be
entered - yet the record exists and needs to be deleted somehow.

instead, you could try this:

add the following code to the command button's OnClick event, as

If Not IsNull(Me!RecordID) Then
DoCmd.OpenForm "frm_OrderErrors", , , "RecordID = " & Me!RecordID, , ,
Me!RecordID
End If

the DoCmd action should be all on one line, of course.

add the following code to the OnCurrent event of frm_OrderErrors, as

If Me.NewRecord Then
Me!OrderID = Me.OpenArgs
End If

as long as frm_OrderErrors is set to allow addition of new records, the
result should be either a) frm_OrderErrors opens showing only the existing
record that matches the current record in frm_Orders, or b) frm_OrderErrors
should open to a new record and automatically enter the OrderID, using the
OrderID value from the current record in frm_Orders. if you don't want to
allow more than one matching record to be entered in frm_OrderErrors by the
user, then try changing the OnCurrent code to:

If Me.NewRecord Then
Me!OrderID = Me.OpenArgs
Else
Me!AllowAdditions = False
End If

hth

Hello all,
I am using a command button on a form to open another form.
The first form (frm_Orders) is bound to tbl_Orders. The 2nd
(frm_OrderErrors) is bound to tbl_OrderErrors. tbl_Orders is related in a 1
to 1 relationship to tbl_OrderErrors using RecordID.
The relationship is set to enforce referential integrity, with cascading
updates & deletes.

On the OnClick event of the command button I want to do one of two things:
If there is already an error record for this order:
Open the frm_OrderErrors where the RecordID matches the RecordID of
frm_Orders
Otherwise, start a new record with the RecordID of frm_OrderErrors set to
match the RecordID of frm_Orders.

I have tried various combinations of:
-----------------------------------
On the command Button
-----------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_OrderErrors"

stLinkCriteria = "[RecordID]=" & Me![RecordID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.RecordID
-----------------------------------
On the Form Load Event of frm_OrderErrors
-----------------------------------
If Me.Recordset.RecordCount = 0 Then
DoCmd.RunSQL "INSERT INTO
tbl_OrderErrors([RecordID])VALUES(Forms!frm_Orders!RecordID)"
End If
-----------------------------------
On the Form Open Event of frm_OrderErrors
-----------------------------------
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord, , acNewRec
End If

Any suggestions would be greatly appreciated!

Thank you,
Renee

 

[tina]

why add a record that you may have to delete? seems to me that it's easier,
and cleaner, to add a record only when it is actually warranted. also, why
add the extra step of executing a query to check record count when you're
going to open the form regardless?

1. Why use the old style docom.openform...?
I would suggest [forms]![frm_OrderErros].....

i don't understand this suggestion, so i can't comment.
IMHO, each of us offers a *different* solution, but i see no advantages to
your solution over mine.

hth

I think tina is going in the right direction, but I would make a couple of
suggestions.
1. Why use the old style docom.openform...?
I would suggest [forms]![frm_OrderErros].....

2. I would also put the intelligence in the command button's On Click Event
in frm_Orders. I wont take time to write the code, but here is the logic:
before coding, create a query that will retrieve only the error record if it
exists, then

execute the query

if record count = 0 then
add a record to the table
endif
open the errors form

Also, to handle tina's concern about adding a record you don't want, in the
Form close event of the errors form, check to see if the record has any data.
If it does not, delete it

i wouldn't suggest using Insert to create a new record. if the user opens
the Errors form accidentally, for example, then there is no data to be
entered - yet the record exists and needs to be deleted somehow.

instead, you could try this:

add the following code to the command button's OnClick event, as

If Not IsNull(Me!RecordID) Then
DoCmd.OpenForm "frm_OrderErrors", , , "RecordID = " & Me!RecordID, , ,
Me!RecordID
End If

the DoCmd action should be all on one line, of course.

add the following code to the OnCurrent event of frm_OrderErrors, as

If Me.NewRecord Then
Me!OrderID = Me.OpenArgs
End If

as long as frm_OrderErrors is set to allow addition of new records, the
result should be either a) frm_OrderErrors opens showing only the existing
record that matches the current record in frm_Orders, or b) frm_OrderErrors
should open to a new record and automatically enter the OrderID, using the
OrderID value from the current record in frm_Orders. if you don't want to
allow more than one matching record to be entered in frm_OrderErrors by the
user, then try changing the OnCurrent code to:

If Me.NewRecord Then
Me!OrderID = Me.OpenArgs
Else
Me!AllowAdditions = False
End If

hth

Hello all,
I am using a command button on a form to open another form.
The first form (frm_Orders) is bound to tbl_Orders. The 2nd
(frm_OrderErrors) is bound to tbl_OrderErrors. tbl_Orders is related

in a
1

to 1 relationship to tbl_OrderErrors using RecordID.
The relationship is set to enforce referential integrity, with cascading
updates & deletes.

If there is already an error record for this order:
Open the frm_OrderErrors where the RecordID matches the RecordID of
frm_Orders
Otherwise, start a new record with the RecordID of frm_OrderErrors

set
to

match the RecordID of frm_Orders.

I have tried various combinations of:
-----------------------------------
On the command Button
-----------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_OrderErrors"

stLinkCriteria = "[RecordID]=" & Me![RecordID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.RecordID
-----------------------------------
On the Form Load Event of frm_OrderErrors
-----------------------------------
If Me.Recordset.RecordCount = 0 Then
DoCmd.RunSQL "INSERT INTO
tbl_OrderErrors([RecordID])VALUES(Forms!frm_Orders!RecordID)"
End If
-----------------------------------
On the Form Open Event of frm_OrderErrors
-----------------------------------
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord, , acNewRec
End If

Any suggestions would be greatly appreciated!

Thank you,
Renee