2

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?

smk081
  • 783
  • 1
  • 10
  • 36

1 Answers1

3

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
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thanks! Your last suggestion helped me clean up my code a bunch and now I am not getting an error BUT when it launches the form, its launches it to a blank/new record not the one that was selected. – smk081 Oct 29 '14 at 22:20
  • 1
    With that form open in Design View, check the Data tab on its property sheet. Find the Data Entry property. If it is Yes, change it to No. – HansUp Oct 29 '14 at 22:23
  • OK, looks like that was the final piece. Data Entry = Yes means the form will allow only addition of new records. It will not display existing records. – HansUp Oct 29 '14 at 22:34
  • Thank you sooooo much - I've waste quite a bit of time of this one issue! – smk081 Oct 29 '14 at 22:46
  • Could you think of a reason of why the DataEntry property of my form would change on its own? I notice that every once in a while during testing, it gets changed back to = "Yes" and when I click my button described above, it reverts back to a new record. I do have a new record button as well which just launches the same form in order to add a new record - but I am not setting the DataEntry property of the form explicitly. – smk081 Oct 30 '14 at 18:18
  • Sorry I've never seen that happen and have no idea how it could change on its own. Since that seems bizarre to me, I would suspect corruption and perform a decompile. I'm not confident it's the solution here, but if you want to try you can find instructions at http://stackoverflow.com/q/3266542/77335 – HansUp Oct 30 '14 at 20:47