VBA Where Clause not bringing up the correct record

[Stephenson]

Hello all,

I'm having difficulties with what should be a simple open form command. My goal is to open frmVendor on the same record as cldVendorDS in the dblClick event of tboVendorName. frmVendor is in form view and cldVendorDS is in data sheet view as well as a subform on frmFilterVendor. Both frmVendor and cldVendorDS use tblVendor as their record source.

I'm using the following code, but getting strange results.

Private Sub tboVendorName_DblClick(Cancel As Integer)Dim sWHERE As String
    sWHERE = "[VendorID] = " & Me.VendorID
    DoCmd.OpenForm "frmVendor", , , sWHERE
End Sub

frmVendor does open, but on record 2. Record 1 is the record i'm trying to open to. The other strange part is none of the other records are there. There is only record 1, the record I wish to open to and record 2, a new record and the one that opens with my event.

Please let me know if you see where I've gone wrong with my code.

Thank you for looking,

John

[pbaldy]

Try setting a breakpoint or using this to test what's in the variable:

BaldyWeb-Immediate window

[Stephenson]

Paul,

Thank you for the advice. I'm not familiar with the immediate window. Thank you for the link, I'm going to see if I can get this figured out.

Have a nice day

[Stephenson]

I'm working with the immediate window and after switching my code around with no luck I've produced the following SQL lines.

[VendorID] = 5[VendorID] = Coeur d' Alene Paving
[VendorID] = Consolidated Supply'
[VendorID] = 'Consolidated Supply'
[VendorID] = City of Coeur d' Alene
[VendorID] = 4
[VendorName] = 3
[VendorName] = Consolidated Supply
[VendorName] = 6
[VendorID] = 4

I assume (I don't know SQL)

[VendorID] = 5

would produce the results I'm after, but this just opens frmVendor on the blank record number 2 with the correct record as number 1.

Any further ideas as to what I'm doing wrong?

[pbaldy]

Can you attach the db here?

[Stephenson]

I'm such a dummy.

I had

DoCmd.GoToRecord , , acNewRec

in the on open event for frmVendor. Once I removed this everything works fine.

Thank you again for helping me Paul.

[pbaldy]

No problem, glad you got it sorted.