Opening a Form with a Macro (Access 2000)

Topic

New Reply

I would like to have my subform open with a command button (cmdDirectory) with OnClick macro (mDirectory.)

mDirectory
Form Name: frmDirectoryRpt
Data Mode: Add

I like the idea of using the macro because then the Rep will only have access to a new record. Everytime I direct the button to the macro, I lose the link to the member that the Rep will be updating. When you add a subform to a form, the wizard prompts you for the link. Is there a way to combine these two features so the command button stays with the current record on the form?

This is the code that I get if I just link it to the subform and don’t try to use the macro:

Private Sub cmdDirectory_Click()
On Error GoTo Err_cmdDirectory_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “frmDirectoryRpt”

stLinkCriteria = “[JDE]=” & Me![JDE]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdDirectory_Click:
Exit Sub

Err_cmdDirectory_Click:
MsgBox Err.Description
Resume Exit_cmdDirectory_Click

End Sub

Reply | Quote

Replies

>>I like the idea of using the macro because then the Rep will only have access to a new record. Everytime I direct the button to the macro, I lose the link to the member that the Rep will be updating.

Which one is it? I’m confused. Do you mean that the rep will be updating information on the current record in a new form? Or the rep will be adding a new record in a subtable linked by a primary key?

Reply | Quote

Sorry-
Each member (JDE) can submit multiple requests for our directory. The Reps work off of a form which provides them with a lot of information on the member. If the member indicates that they want to make a change to the directory, the Rep then clicks on the command button which will take them to the subform with the Directory options on it. I need to have the subform stay with the member (JDE) that is on the main form.

Reply | Quote

Ahhh, that makes a little more sense. If your main form stays open, you can simply refer to objects on it in your next form (the form for change requests). I think your best bet would be to use an unbound form, and use ADO or DAO to add records. To set a text box equal to the same record on the main form, you may use something like this:

Main form name = frmMain
Main form record ID field = txtMember

You can set the record ID (Member) to equal the same as the main form by setting the control source of a text box:

=forms!frmmain.txtMember

Hopefully that makes sense, I’m working on an example to post.

Reply | Quote