DoCmd OpenForm - with WHERE clause not working ?

[ADIGA88]

The prompt is Access telling you it can't find that value. Is it a variable? See if this helps:

http://www.baldyweb.com/wherecondition.htm

I registered to the forums just to tell you thank you

 

[pbaldy]

I registered to the forums just to tell you thank you

Welcome, I'm glad it helped you!

 

[Dragonous24]

Hi, I'm struggling with this issue also.
I'm trying to open a form using 2 variable where conditions and keep getting type mismatch errors.

CourseMOD = "[fld_Name_course] = '" & Course & "'"
PersIDMOD = "[fld_PersID_trec] = '" & PersID & "'"
           
strCriteria = CourseMOD & " And " & PersIDMOD
DoCmd.OpenForm "frmTrainingRecord", , , strCriteria

I've also tried

DoCmd.OpenForm "frmTrainingRecord", , , PersonIDMOD And CourseMOD

which also doesnt work.

I imagine i there are Quotations in the wrong place or something when combine the strings but i've tried a few variations and cant find the solution.

EDIT: Soon after posting this i found my problem.
Solution:

CourseMOD = "[fld_Name_course] = '" & Course & "'"
PersIDMOD = "[fld_PersID_trec] = " & PersID

strCriteria = CourseMOD & " And " & PersIDMOD
DoCmd.OpenForm "frmTrainingRecord", , , strCriteria

PersID is an integer and didn't need the extra quotes around it.
I feel like such an idiot for not spotting that earlier.

 

[pbaldy]

Thanks for updating the thread, glad you got it sorted.

 

[luppi13]

Ok, I have read through all of these posts and I've tried everything that was suggested and I'm still getting the "Enter Parameter Value".

DoCmd.OpenForm "frmChecklistNavigation", , , "LOAN_NUMBER ='" & Me.cboSearchLoans.Column(0) & "'"

LOAN_NUMBER is a ShortText
The first field in the combobox is the Loan Number.
The form's Record Source as the LOAN_NUMBER in it.
The form data settings:

Please help!

 

[NauticalGent]

I am guessing the actual bound field to your combo box is a number and not text...?

 

[luppi13]

I am guessing the actual bound field to your combo box is a number and not text...?

No, it's the Loan Number so it is text.

 

[pbaldy]

The prompt is Access telling you it can't find something. Does the field name really have an underscore, or is it perhaps a space? Also, you can set a breakpoint on that line and make sure the value being returned is what you expect. Oh, and might the loan number have an apostrophe in it?

 

[Gasman]

What is the recordsource for the form?
Does that open manually anyway?

 

[luppi13]

The prompt is Access telling you it can't find something. Does the field name really have an underscore, or is it perhaps a space? Also, you can set a breakpoint on that line and make sure the value being returned is what you expect. Oh, and might the loan number have an apostrophe in it?

The field name does have an underscore in it.
I did set a breakpoint and the values were correct.
No, the loan number does not have an apostrophe in it.

 

[pbaldy]

Can you attach the db here?

 

[luppi13]

Can you attach the db here?

Unfortunately, I can't attach the db because of the nature of the data. Not shareable.

I have run the recordsource query to see if I could find the loan number. It's in the results. But when I try to open the form with the where statement, I get the enter parameter. I figured this part out, however --- IT IS STILL NOT POPULATING THE FORM! AHHHHHH!!!!!

Pretty frustrated! Thank you for your help in advance.

 

[pbaldy]

Access can't find whatever is specified in the prompt; what exactly is it asking for? Is that field in the record source of the form being opened (frmChecklistNavigation)?

 

[luppi13]

Access can't find whatever is specified in the prompt; what exactly is it asking for? Is that field in the record source of the form being opened (frmChecklistNavigation)?

I edited my post. I figured out what was wrong to get the Enter Parameter... Issue. Now, it's just not populating the form.

The form has many tabs with subforms on them. The previous version of this application used an sql statement and re-assignment of the recordsource to get the correct outcome. But I wanted to avoid the extrastep.

That's why I moved to the WHERE statement in the DoCmd.OpenForm.

 

[pbaldy]

The form isn't in data entry mode, is it?

You said you set a breakpoint and the value in the combo matches data in the LOAN_NUMBER field?

 

[Gasman]

I am going to have this printed on my kerbstone.

Walk through the code with F8, after setting a breakpoint!

 

[luppi13]

I did catch the LOAN_NUMBER error. However, that didn't fix anything. Let's try this. Here is the query that I use to populate the form.
SELECT tblLoanData.LOAN_NUM, tblLoanData.BOR_LAST1, tblLoanData.PROP_STATE, tblLoanData.LOAN_PROGRAM, tblLoanData.DT_FUNDED, tblLoanData.TITLE_INFO, tblLoanData.INVESTOR_INFO, tblFDR.FDocsID, tblFDR.LOAN_NUM, tblFDR.AuditDate, tblFDR.AuditorID, tblFDR.AuditorName
FROM tblLoanDataLEFT JOIN tblFDR ON tblLoanData.LOAN_NUM = tblFDR.LOAN_NUM
WHERE (((tblLoanData.DT_FUNDED)>#1/1/2019#));

The openform command:
DoCmd.OpenForm "frmFDRInput", acNormal, , "tblLoanData.LOAN_NUM = '" & Me.cboSearchLoans.Column(0) & "'"

I have stepped through all the lines from clicking the button, to this statement executing.
The value of "tblLoanData.LOAN_NUM = '" & Me.cboSearchLoans.Column(0) & "'" is "tblLoanData.LOAN_NUM = '123456'"

I put the loan number in the query in Access and found a record. So, I'm lost.

 

[pbaldy]

What is the Data Entry property of frmFDRInput?

 

[Gasman]

You have single quotes around loan_num?, is it text then?, as if numeric, no quotes should be used. I'm surprised you did not get type mismatch, if numeric.

 

[pbaldy]

From above:

LOAN_NUMBER is a ShortText

though I note the name has been changed.