opening a form with a particular record

  • Thread starter Thread starter Don Seckler
  • Start date Start date
D

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

As long as the form has access to DrawIndex from the calling
form, you should be able open the form utilizing the WHERE
clause of the OpenForm method. In the OnClickEvent of the
Command Button that you create place something like the
following...

DoCmd.OpenForm "frmWhateverYourFormNameIs",,,"[DrawIndex] =
" & Me![DrawIndex]

This should open the new form based on the DrawIndex
displayed on the form.

Gary Miller
 
Gary,

Does it matter that Draw Index is not on the first form?

Also, how do I set up the fields on the second form? Do they need to be
bound or have something as their record source?

Would it be better or easier to just query the table using the info from the
first form to bring up that record in the second form?

Thanks!

Don
Gary Miller said:
Don,

As long as the form has access to DrawIndex from the calling
form, you should be able open the form utilizing the WHERE
clause of the OpenForm method. In the OnClickEvent of the
Command Button that you create place something like the
following...

DoCmd.OpenForm "frmWhateverYourFormNameIs",,,"[DrawIndex] =
" & Me![DrawIndex]

This should open the new form based on the DrawIndex
displayed on the form.

Gary Miller

Don Seckler said:
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 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 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
Click to expand...
Click to expand...
 
Bingo! Yes, it does, although I am a bit confused. You say
you have a 3 field combined primary index, do you also have
a named index of DrawIndex that is independent of the three
crossindexed fields?

If the value of DrawIndex is not available, how will the
calling code be able to grab it? What you can do is to put
the DrawIndex on the form and then set the Visible Property
to False which will hide it. Then any references to the form
will be able grab it.

I still have a bit of confusion, so forgive me if I haven't
nailed the answer.

Gary Miller

Don Seckler said:
Gary,

Does it matter that Draw Index is not on the first form?

Also, how do I set up the fields on the second form? Do they need to be
bound or have something as their record source?

Would it be better or easier to just query the table using the info from the
first form to bring up that record in the second form?

Thanks!

Don
Don,

As long as the form has access to DrawIndex from the calling
form, you should be able open the form utilizing the WHERE
clause of the OpenForm method. In the OnClickEvent of the
Command Button that you create place something like the
following...

DoCmd.OpenForm "frmWhateverYourFormNameIs",,,"[DrawIndex] =
" & Me![DrawIndex]

This should open the new form based on the DrawIndex
displayed on the form.

Gary Miller

Don Seckler said:
I have a data entry form called Draw. This form is
Click to expand...
used
to enter data
in the table called Draw. The table has the following fields:
WholesalerID, MagID, IssueID, CopiesDist, and the
Click to expand...
index is
called
DrawIndex. The DrawIndex is a combination index that
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Bingo! Yes, it does, although I am a bit confused. You say
you have a 3 field combined primary index, do you also have
a named index of DrawIndex that is independent of the three
crossindexed fields?
Click to expand...

I have a named autonumber index called DrawIndex that is a combination
index based on the WholesalerID,MagID, IssueID fields.
If the value of DrawIndex is not available, how will the
calling code be able to grab it? What you can do is to put
the DrawIndex on the form and then set the Visible Property
to False which will hide it. Then any references to the form
will be able grab it.
Click to expand...

I guess that's my question. Let me explain what I'm trying to do. I
probably wasn't clear enough before.

I have a database that is going to track how many copies of each issue
of a magazine that we send to various wholesalers. It will also track
how many copies are returned (unsold). I will also use the db to
track sales and do billing.

The form I'm starting with is the form used to enter the number of
copies distributed to each wholesaler. I have the form set up so that
the used can use combo boxes to select the Wholesaler, the Magazine
and the issue and there is a text box that allows them to enter the
number of copies of that particular issue that are sent to that
wholesaler.

When the user enters this info and hits submit, the form checks the
table "Draw" based on the info entered in the WholesalerID,MagID,
IssueID fields to see of that exact combo exists. If it doesn't the
record is written to the table. If the combo does exist an error
occurs.

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.

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
 
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?
 
Not inherently. It just throws the error. You would have to
handle the navigation yourself.

Gary Miller
 
Back
Top