Dual purpose command button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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

tina said:
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


Renee said:
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
Click to expand...
Click to expand...
 
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].....
Click to expand...

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


Klatuu said:
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

tina said:
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


Renee said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
The suggestion worked perfectly! Thank you much. Also, I added a catch on the
OnLoad to cancel the open if the opening args are null (and a catch on the
button to ignore the 2501 error resulting if it is canceled).

Thank you again! Have a great weekend.
Renee

tina said:
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


Renee said:
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
Click to expand...
Click to expand...
 
you're welcome, Renee! glad it worked for you, but i'm surprised you would
have a situation where the 2nd form opens with a null value in the OpenArgs
property. the OpenForm action set the OpenArgs argument to Me!Record, and
the If statement does not run the OpenForm action unless Me!Record is *not*
null.


Renee said:
The suggestion worked perfectly! Thank you much. Also, I added a catch on the
OnLoad to cancel the open if the opening args are null (and a catch on the
button to ignore the 2501 error resulting if it is canceled).

Thank you again! Have a great weekend.
Renee

tina said:
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


Renee said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top