VBA syntax in Access 2010: How to open form to specific record with composite primary key

I’m just finding my feet in VBA scripting and am a bit stumped with this rather simple bit of code. I suspect it is because I’m not getting the syntax of the Where statement right. I found plenty of examples and can make it work no problem if it is a simple Where statement with just one expression but this one requires two expressions with an AND in the middle and no matter what I try with quotation marks and brackets, it won’t compile. I can get the form to open (just not to a specific record) and I confirmed that the variables hold the correct values. The code below just shows one example of the Where statements I’ve tried out. See below the code for a more extensive description of what I’m trying to do, in case this isn’t a simple syntax issue after all.

Private Sub lsPrevObs_DblClick(Cancel As Integer)

Dim Microchip As String
Dim ObsDate As Date
Microchip = Me.Text24
ObsDate = Me.lsPrevObs
DoCmd.OpenForm "frmObservationsEdit", acNormal, , "ObsMicrochip ='" & Microchip & "'" AND "TrappingDate = #" & ObsDate & "#", acFormEdit

End Sub

Context for this sub:

I have a table containing basic information about individual animals with their microchip number (despite the name it does contain letters and is therefore a string, not an integer) as primary key. Then I have a table of observations, which uses the Microchip number and the observation date as a composite primary key.
I have a form linked to the first table. A text box (Text24) displays the microchip number of the current record and a list box (lsPrevObs) shows the dates of observations for the animal for which there already are records in the observation table. I want to tie some VBA code to the double click event of the list box so that when the user double clicks a date, a new form opens (frmObservationsEdit, DataEntry=No) and allows for that observation record to be edited.

Thanks for looking at this.

Source: syntax

Leave a Reply