MS Access 2007 Open Separate Form to Specific Record Using VBA

Question

I have two forms, one which is a data entry form, the other is a summary form which lists all the records in the database. On the summary form there is a listbox which lists all the records. I want the user to be able to select a record from the listbox and using a command button, open the second form to the specific record. I've gotten this work with using a specific field, in one case "Name", using the code below:

DoCmd.OpenForm "frmEditAddPerson", acNormal, ,"[PersonName]='" & Me.listPeople.Value & "'"

but when I realized two people could have the same name, I decided it made sense to use the PersonID which is the primary key and its datatype is "AutoNumber". I can't seem to get this to work:

DoCmd.OpenForm "frmEditAddPerson", acNormal, "[PersonName] = " & SelectPersonID

Note, I am getting SelectedPersonID by pulling it from ListBox from a hidden column. I confirmed that I am infact getting correct the number value for the AutoNumber field by displaying it in a MessageBox while trying to debug.

For the WHERE argument of this method/command, I know you are supposed to contain String values in quotes, Integers without, and dates with "#" like in a SQL statement. I've tried delcaring SelectedPersonID as a string and as an integer and I still cannot get the above to work. I've even tried the below just to be sure:

DoCmd.OpenForm "frmEditAddPerson", acNormal, "[PersonName] = " & CInt(SelectPersonID)

Each time I get "Type mismatch". Is the AutoNumber field special in the sense that it cannot be used for something like this or does it need to be handled in a special way?

HansUp HansUp · Accepted Answer · 2014-10-29T22:08:57

If PersonID is the autonumber primary key, reference it in the fourth OpenForm argument (WhereCondition). Your OpenForm examples still include PersonName instead of PersonID.

Also, in your last two examples, PersonID was referenced in the third OpenForm argument (FilterName).

DoCmd.OpenForm "frmEditAddPerson", acNormal, , "[PersonID] = " & SelectPersonID

It can be easier to keep track of which option is which by including the option names.

DoCmd.OpenForm FormName:="frmEditAddPerson", View:=acNormal, _
    WhereCondition:="[PersonID] = " & SelectPersonID

MS Access 2007 Open Separate Form to Specific Record Using VBA

1 Answers