How to pass the record between forms?

[Don Seckler]

I have a data entry form called Draw. This form is used to enter data
in the table called Draw. The table has the following fields:
WholesalerID, MagID, IssueID, CopiesDist, and the index is called
DrawIndex. The DrawIndex is a combination autonumber index that uses
the
combination of these three fields WholesalerID, MagID, IssueID to
ensure that each record is unique.

When a used tries to enter data that has a the same WholesalerID,
MagID, IssueID combination as an existing record, I set it up so that
that error triggers a yes/no box. I'd like the user to have the
option to edit the existing record in the table. I want another form
called EditDrawForm to open so they can modify the existing record if
they choose to do so.

How do I pass the existing record to the new form?

Here's my script so far. Any help would be greatly appreciated.
Someone suggested OpenArgs, but I'm not sure how to use that here...

Option Compare Database

Private Sub ButtonAddDraw_Click()
On Error GoTo Err_ButtonAddDraw_Click
DoCmd.GoToRecord , , acNewRec

Exit_ButtonAddDraw_Click:
Exit Sub

Err_ButtonAddDraw_Click:
Select Case Err.Number
Case 2105
Err.Clear
'* Display my custom message box
If MsgBox("The draw has already been entered for this issue.
Would you like to edit it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "MyForm"
Else
End If
GoTo Exit_ButtonAddDraw_Click
Case Else
MsgBox Err.Description
End Select
Resume Exit_ButtonAddDraw_Click

End Sub

I guess one of my questions is that when the user inputs the info in
the first form and the form checks the table and finds that a record
already exists and triggers the error does that make that record have
the focus?

That's where I'm kind of stuck. I don't know if it's best to open an
editing ofrm with the existing record using openform and openargs (and
I'm not sure how to do this even after having read the help on
openargs) or is it best to use a query to pop up the editing form.

The thing I'm having trouble with is how to pass the record between
the forms.

Don

[Larry Linson]

Look at the WhereCondition argument of DoCmd.OpenForm.

But also consider the option of using the Form you have displayed by
locating to the existing record (how you'd do this will depend on the Form's
RecordSource... it may be as simple as finding the record in the
Recordsetclone, which I suspect you have already done, and resetting the
Form's Bookmark property from the Recordsetclone's Bookmark property, then
changing the AllowEdits property.

Larry Linson
Microsoft Access MVP

"Don Seckler" <dj*******@hotmail.com> wrote in message
news:fe**************************@posting.google.c om...

I have a data entry form called Draw. This form is used to enter data
in the table called Draw. The table has the following fields:
WholesalerID, MagID, IssueID, CopiesDist, and the index is called
DrawIndex. The DrawIndex is a combination autonumber index that uses
the
combination of these three fields WholesalerID, MagID, IssueID to
ensure that each record is unique.

When a used tries to enter data that has a the same WholesalerID,
MagID, IssueID combination as an existing record, I set it up so that
that error triggers a yes/no box. I'd like the user to have the
option to edit the existing record in the table. I want another form
called EditDrawForm to open so they can modify the existing record if
they choose to do so.

How do I pass the existing record to the new form?

Here's my script so far. Any help would be greatly appreciated.
Someone suggested OpenArgs, but I'm not sure how to use that here...

Option Compare Database

Private Sub ButtonAddDraw_Click()
On Error GoTo Err_ButtonAddDraw_Click
DoCmd.GoToRecord , , acNewRec

Exit_ButtonAddDraw_Click:
Exit Sub

Err_ButtonAddDraw_Click:
Select Case Err.Number
Case 2105
Err.Clear
'* Display my custom message box
If MsgBox("The draw has already been entered for this issue.
Would you like to edit it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "MyForm"
Else
End If
GoTo Exit_ButtonAddDraw_Click
Case Else
MsgBox Err.Description
End Select
Resume Exit_ButtonAddDraw_Click

End Sub

I guess one of my questions is that when the user inputs the info in
the first form and the form checks the table and finds that a record
already exists and triggers the error does that make that record have
the focus?

That's where I'm kind of stuck. I don't know if it's best to open an
editing ofrm with the existing record using openform and openargs (and
I'm not sure how to do this even after having read the help on
openargs) or is it best to use a query to pop up the editing form.

The thing I'm having trouble with is how to pass the record between
the forms.

Don

[Pieter Linden]

> How do I pass the existing record to the new form?

One of the parameters of the DoCmd.OpenForm command is a filter. You
just pass a valid WHERE clause to it. The wizard pretty much builds
everything for you...

Private Sub cmdOpenForm_Click()
On Error GoTo Err_cmdOpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff"

stLinkCriteria = "[StaffName]=" & "'" & Me![FirstName] & "'"

'----Following is the line you want...
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenForm_Click:
Exit Sub

Err_cmdOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenForm_Click

End Sub

[Carlo Patel]

"Don Seckler" <dj*******@hotmail.com> wrote in message
news:fe**************************@posting.google.c om...

I have a data entry form called Draw. This form is used to enter data
in the table called Draw. The table has the following fields:
WholesalerID, MagID, IssueID, CopiesDist, and the index is called
DrawIndex. The DrawIndex is a combination autonumber index that uses
the
combination of these three fields WholesalerID, MagID, IssueID to
ensure that each record is unique.

When a used tries to enter data that has a the same WholesalerID,
MagID, IssueID combination as an existing record, I set it up so that
that error triggers a yes/no box. I'd like the user to have the
option to edit the existing record in the table. I want another form
called EditDrawForm to open so they can modify the existing record if
they choose to do so.

How do I pass the existing record to the new form?

Here's my script so far. Any help would be greatly appreciated.
Someone suggested OpenArgs, but I'm not sure how to use that here...

Option Compare Database

Private Sub ButtonAddDraw_Click()
On Error GoTo Err_ButtonAddDraw_Click
DoCmd.GoToRecord , , acNewRec

Exit_ButtonAddDraw_Click:
Exit Sub

Err_ButtonAddDraw_Click:
Select Case Err.Number
Case 2105
Err.Clear
'* Display my custom message box
If MsgBox("The draw has already been entered for this issue.
Would you like to edit it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "MyForm"
Else
End If
GoTo Exit_ButtonAddDraw_Click
Case Else
MsgBox Err.Description
End Select
Resume Exit_ButtonAddDraw_Click

End Sub

I guess one of my questions is that when the user inputs the info in
the first form and the form checks the table and finds that a record
already exists and triggers the error does that make that record have
the focus?

That's where I'm kind of stuck. I don't know if it's best to open an
editing ofrm with the existing record using openform and openargs (and
I'm not sure how to do this even after having read the help on
openargs) or is it best to use a query to pop up the editing form.

The thing I'm having trouble with is how to pass the record between
the forms.

Don

Best place to get answers is at the official MVP site:
http://mvp.org
Carlo Patel (MVP)