Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening a Form with a Macro (Access 2000)

    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

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Form with a Macro (Access 2000)

    >>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?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Form with a Macro (Access 2000)

    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.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Form with a Macro (Access 2000)

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •