WHERE clause problem with DoCmd.OpenForm

[JenniferM]

Back again with a brand new problem. My head hurts....

I've got a couple of listboxes on a form, FrmPatientDemog raphics. One contains knee surgeries for that patient (LbxKneeSx) and the other contains all follow-up forms for the patient (LbxKneeExam), each of which is linked to the knee surgery ID.
I have an open button for the first listbox (LbxKneeSx) that opens up the form FrmKneeSurgeryI nfo to the specified record. The surgery record ID contains letters and numbers.... This works fine, and I've included the code I've used here:

Private Sub SrcOpenKneeSxForm_Click()

DoCmd.OpenForm "FrmKneeSurgeryInfo", , , "[TblKneeSurgeryInfo.SxKneeID]=" & "'" & Me.LbxKneeSx.Column(2) & "'"

End Sub

Where I'm specifically running into a problem is the second listbox. I've set up the WHERE clause to mimic the first listbox, which is working, but haven't been able to get this one going yet. The primary key is FUKneeID, which is located in the first column of the box (bound column is set to one). I've included my code here:

Private Sub SrcOpenKneeExamForm_Click()

DoCmd.OpenForm "FrmKneeExamInfo", , , "TblKneeExamInfo.FUKneeID =" & "Me.LbxKneeExam"

End Sub

I know that the syntax around the quotes changes depending on whether the desired field is numeric or text. FUKneeID is a numeric field, but I don't know if basic syntax is where my problem is.
When I attempt to open a form using this button, I am prompted to enter values for TblKneeExamInfo .FUKneeID and Me.LbxKneeExam (which shouldn't be happening anyways). In this particular case, I wanted to open the form where both were 6, but it just took me to the first record when I entered a value of 6 for both fields (NOT the correct record).

Any help would be much appreciated.... I'm not really sure if my problem is incorrect setting up of the WHERE clause or just a simple grammatical error (or heaven forbid something much more troublesome). Thanks!

[NeoPa]

Jennifer, look at the different formats of the code you have. The working one refers to "'" & Me.LbxKneeSx.Co lumn(2) & "'" whereas the failing one refers to "Me.LbxKneeExam ". Quite different I can assure you.

Other than that, referencing fields (not controls but the fields of the record source of the form) need not be qualified. In fact that can cause confusion (I think it has in this case). I can only think of very rare cases where your first example (you say works) could work correctly as stated with [] sorrounding both table name and field. The second example is somehow not matching what is available (hence the request to enter it). The table reference should not be required and the field cannot hurt to have the [] around it.

[JenniferM]

I made the following changes to the code on the second listbox:

Private Sub SrcOpenKneeExamForm_Click()

DoCmd.OpenForm "FrmKneeExamInfo", , , "[FUKneeID] =" & Me.LbxKneeExam

End Sub

I removed the name of the tables when I was referencing both fields, with no problems, and added brackets around the field names as well. Originally I had double-quotes around Me.LbxKneeExam in Line 3, but as I was still being prompted to enter a value there, I just took them out. Works now!

On a side note, NeoPa, I feel like I should send you a Christmas present or something... Thanks for helping me out! That's twice today!

[NeoPa]

It's been a pleasure Jennifer :-)

As far as a Chrissy present goes - It's much too early for that. I'll happily settle for a warm feeling knowing that I've helped ;-)