How do I open a form to a specific record based on a clicked value of another form in MS Access?

Question

I am trying to replicate something I saw in the MS Access database about Nutrition Tracking. It has a form called "Food List". Clicking on the foodID of a record in that list opens a new form with the food details of the food that the user clicked on. I saw that the text box runs a macro to open the new form, but I don't understand how it tells the Food Details form which food item to give the details of. I am trying to recreate this in my database about homeowners, but can't get it to work. When I click on the text field I labeled "HomeownerIDText" in my homeowners list form, it opens the homeowner details form properly, but it asks me to enter a parameter value. The prompt for the text entry is the homeowner whose details I am trying to see, but changing the order of the arguments in the macro did not fix the issue. Any help will be appreciated.

Answer 1

Is HomeownerIdentifier a numeric field in the [Homeowner Details] form? Is HomeownerIDText the name of a field or text box control in the Homeowner List form?

You mentioned "I labeled HomeownerIDText*"* but this isn't clear. Is this the name of a control or the Caption property of a label control.

Answer 2

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

Here’s how you can open a form to a specific record based on a clicked value of another form in MS Access as an expert:

• Open the form that contains the control that you want to use to open another form.
• In Design view, select the control that you want to use to open another form.
• On the Event tab of the Property Sheet, click the On Click property box, and then click the Build button (ellipsis).
• In the Choose Builder dialog box, click Code Builder, and then click OK.
• In the Code Editor window, type or paste code that opens another form and passes a parameter value to it.

Here’s an example of how you can use OpenArgs property:

Private Sub Command1_Click() DoCmd.OpenForm "Form2", , , "ID=" & Me.ID End Sub

In this example, Form2 is opened with a specific record based on the value of ID field in Form1.

For more Information, please refer to following resources :-

1. How to move to a specific record from a Combo Box selection in Microsoft Access - https://learn.microsoft.com/office/troubleshoot/access/move-to-specific-record-from-combo-box-selection

If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.

Best Regards, Sneha

Answer 3

You might like to take a look at FormsDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

In this little demo take a look at the example to open a linked form in synchronized mode.  In this case a form in continuous forms view is opened from a form in single form view, rather than vice versa as in your case, but the methodology is the same.

The code in the Click event procedure of the button on the clients form is:

Private Sub cmdEvents_Click()

    Const FORMNAME = "frmEvents"

    Const MESSAGETEXT = "No current client record."

    Dim strCriteria As String

    strCriteria = "ClientID = " & Me.ClientID

    ' be sure a record has been entered

    If Not IsNull(Me.ClientID) Then

        ' save current client record

        Me.Dirty = False

        ' open linked form

        DoCmd.OpenForm FORMNAME, _

            WhereCondition:=strCriteria, _

            OpenArgs:=(Me.ClientID)

    Else

         MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"

    End If

End Sub

This builds an expression to open the events form at the records for the current client::

    strCriteria = "ClientID = " & Me.ClientID

ClientID is the name of the key columns in the clients table and the events table which relate the two tables.  In your case you are opening a form based on the same table as the current one so you'd use the name of the primary key column of that table in both parts of the expression.

The OpenForm method is then called with that expression as its WhereCondition argument.  The ClientID is also referenced as the OpenArgs argument, which passes its value to the other form.

In the clients form's Current event procedure the following code is executed:

Private Sub Form_Current()

    On Error Resume Next

    Forms("frmEvents").Filter = "ClientID = " & Nz(Me.ClientID, 0)

    Forms("frmEvents").cboClient.DefaultValue = """" & Me.ClientID & """"

End Sub

Firstly this ignores any error if the events form is not open.  It then sets the Filter property of the events form so that if the user moves to a different record in the clients form and the events form is open, the events form is filtered to show the records for the newly selected client, if any.

It then sets the DefaultValue property of the events form to the current ClientID.  If the user enters a new record in the events form this will set the ClientID value to the current client automatically. You should not do this in your case as you are not opening the second form at related records in another table.

If you are unfamiliar with entering code into a form's, report's, report section's  or control's event procedures, this is how it's done in form or report design view:

1.  Select the form, report, section or control as appropriate and open its properties sheet if it's not already open.

2.  Select the relevant event property in the Event tab, and select the 'build' button (the one on the right with an ellipsis (3 dots)).

3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new line(s) between these.