Solved docmd.openform Where condition not working

[5hadow]

Hi all,

I just don't understand why it's not working...

Private Sub fldIQAID_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmIQA", , , "fldIQAID = " & Me.fldIQAID
    MsgBox "Your ID is " & fldIQAID & ""
End Sub

Few details:
MsgBox shows that the ID it pulls is correct, but the form opens in empty, or add? mode. (fldIQAID shows "(New)" and form is blank)
Neither of my forms are in Data entry mode. Data Entry = No
I tried DoCmd.OpenForm "frmIQA", , , "fldIQAID = " & Me.fldIQAID
DoCmd.OpenForm "frmIQA", , , "fldIQAID = #" & Me.fldIQAID & "#"
DoCmd.OpenForm "frmIQA", , , '"fldIQAID = " & Me.fldIQAID & "'"

Any ideas?

 

[plog]

1. Your msgbox isn't a good test. You're not referencing it that same was as in the docmd. Me.fldQAID is looking for an input on the form with that name, fldQAID is just looking for that field in the forms data source.

2. If fldQAID in your table is a text field you need to escape the variable in the docmd with single quotes:

...='" & Me.fldQAID & "'"

 

[arnelgp]

maybe your form is set to DataEntry?

 

[5hadow]

1. Your msgbox isn't a good test. You're not referencing it that same was as in the docmd. Me.fldQAID is looking for an input on the form with that name, fldQAID is just looking for that field in the forms data source.

2. If fldQAID in your table is a text field you need to escape the variable in the docmd with single quotes:

...='" & Me.fldQAID & "'"

Yes, I see what you mean with msgbox test.
fldIQAID is an AutoNumber. Unfortunately that didn't work.

 

[5hadow]

maybe your form is set to DataEntry?

It's set to = No

 

[5hadow]

Not sure if this is relevant, but:
I had to change my form "frmIQA" from Dynaset to Dynaset (Inconsistent Updates) because it was loading blank for some reason.

 

[arnelgp]

check to Recordsource of the form, does it has the field you need to filter.

 

[5hadow]

check to Recordsource of the form, does it has the field you need to filter.

Ok, it works if I specifically set recordsource to table only "tblIQA". If I try to set it to a query then it breaks everything.

Here's the query in question:

SELECT tblIQA.*, tblObservation.*, [tblMember]![fldLastName] & ", " & [tblMember]![fldFirstName] AS Auditor, [tblMember_1]![fldLastName] & ", " & [tblMember_1]![fldFirstName] AS [Doc Owner], [tblMember_2]![fldLastName] & ", " & [tblMember_2]![fldFirstName] AS [App Auth] FROM tblMember INNER JOIN ((tblIQA INNER JOIN tblObservation ON tblIQA.fldIQAID = tblObservation.[IQAID]) INNER JOIN ((tblSection INNER JOIN tblMember AS tblMember_1 ON tblSection.fldDocOwner = tblMember_1.fldMemberID) INNER JOIN tblMember AS tblMember_2 ON tblSection.fldAppAuth = tblMember_2.fldMemberID) ON tblIQA.fldSection = tblSection.fldSectionName) ON tblMember.fldMemberID = tblIQA.fldAuditorID;

So no need to use this bad query. I have corrected the issue. Thanks for your help.