Search code examples
ms-accessmethodsopenform

MS Access 2007 OpenForm method, can't get the where clause to produce the correct result

I can not get the OpenForm method to open a form with the correct record loaded. I'm going to do my best here to provide the details:

The 'source' form is based off Table A, and it is read-only. A button on the source form is being used to open a 'target' form that is used for editing records, also from Table A.

I have tried using the wizard to create the button that opens the form based on primary key equality. The result from the wizard is that no matter what record is in context on the source form, only the first record in Table A is ever loaded by the target form.

I have tried using a procedure with the following variations:

Dim frm As String, whr As String
frm = "Target Form"
whr = "Forms![Source Form]!ID = Forms![Target Form]!ID"
'whr = "[ID] = [ID]"
'whr = "[ID] = Forms![Target Form]!ID"
DoCmd.OpenForm frm, acNormal, , whr, , , 1

I could either get only the first record to load in the target form or get a new record to load in the target form. But I can not get the source form's loaded record to determine the record loaded into the target form.

Thanks for any help

Solution

  • I think you got close. You want the WhereCondition to be the same as a WHERE clause for a query of Target Form's record source, but without the word WHERE.

    Dim frm As String, whr As String
frm = "Target Form"
whr = "[ID] = " & Me.ID
Debug.Print "whr: " & whr
DoCmd.OpenForm frm, acNormal, , whr

    I intended Me.ID as the value of a control whose name is ID and is bound to a field in the current record of Source Form. Me is shorthand for "this form". One reason it's useful is because you wouldn't have to revise your code if you later decide to give the form a different name.

    I added the Debug.Print statement so you can switch to the Immediate Window (Ctrl+g), and copy the whr string, then paste it into a new query based on the same record source which Target Form uses. That could be helpful in case you still don't get the correct record displayed when Target Form opens.

    Your version also included 1 as OpenArgs to OpenForm. I didn't see how you were using that, so left it off. If Target Form includes an event procedure which does something with OpenArgs, make sure it doesn't override your WhereCondition.