Using Form Textbox as Query Criteria. Received Error. (1 Viewer)

[Zak14]

I've got a continuous form based on a query.
Each of the fields have a search box below it (in the footer) which should ideally filter the query.
I'm starting with the FirstName field.

In the form's query, I've set the criteria to the following for the FirstName field:

Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*"

frmStudentDetails is the form name.
txtSearchFirstName is the search box's name (in the footer).

The AfterUpdate event for txtSearchFirstName is:

Me.Requery

But, it doesn't work; when I switch to form view, it displays an error:

The Microsoft Access database engine does not recognize '[Forms]![frmStudentDetails]![txtSearchFirstName]' as a valid field name or expression.

What am I doing wrong here? Any guidance?
Thanks!

 

[Steve R.]

The issue is the quotation marks. Try the construct below.

FieldName="[Forms]![frmStudentDetails]![txtSearchFirstName]"
Like "*" & FieldName & "*"

I have preferred embedding a variable as the quotation marks do not seem to be as much of an issue then.

Take a look at: How to: Include Quotation Marks in String Expressions

 

[Zak14]

The issue is the quotation marks. Try the construct below.

FieldName="[Forms]![frmStudentDetails]![txtSearchFirstName]"
Like "*" & FieldName & "*"

I have preferred embedding a variable as the quotation marks do not seem to be as much of an issue then.

Take a look at: How to: Include Quotation Marks in String Expressions

Thanks, but where do I put that code?
I've tried entering it into a single line in the query criteria and on two separate lines, but to no avail.

FirstName="[Forms]![frmStudentDetails]![txtSearchFirstName]"
Like "*" & FirstName & "*"

(They gave no error, but no records show)
Excuse my ignorance if I put it in the wrong place.

Btw, I've tried to search for a solution myself for this error issue and I saw somewhere, that you need to have the default value of the search box to "", otherwise it returns a null value and causes errors; so, I tried that, but it got me nowhere.

I've also understood that it could be something got to do with crosstab queries.
The underlying query in reference includes some fields from another crosstab query.
However, I've tried removing the crosstab query fields from the query to see if the problems persists, and it does.
(I've added them again fyi)

Thanks

 

[JHB]

Post you database with some sample data, (zip it) + info in which form you have the problem.

 

[Zak14]

Hi JHB,
I can't upload the original and it's a big database that's not easy to understand at first even if I did.
I've uploaded a sample database with the same conditions in the area of concern with sample data - the same error occurs when trying to set the search box as the query criteria.

 

[JHB]

You are using a crosstab query, then you can't do it like you want.
The problem is describe here: http://support.microsoft.com/kb/209778

 

[Zak14]

You are using a crosstab query, then you can't do it like you want.
The problem is describe here: http://support.microsoft.com/kb/209778

Thanks. I've read the content and understand why the error occurs.
Here's the solution the page describes:

To avoid this error, define [XXX] as an explicit parameter by adding it to the Query Parameters dialog box. To do so, follow these steps:
1. Start Microsoft Access.
2. In the Database window, click Queries under Objects.
3. Open the query in Design view.
4. To explicitly define the parameters, click Parameters on the Query menu.
5. Type the name of the parameter or the form reference under Parameter in the Query Parameters dialog box, and then set the appropriate data type.
6. Close the Query Parameters dialog box.The query now works as expected.

But I don't understand step 5. What does "the name of the parameter or the form reference" mean?

I can confirm that the crosstab query is the problem here - I did say earlier that if I remove the fields from the crosstab query, the error is still there, but now I just tried removing the whole crosstab table from the query and the search box works.

The thing is, I need the crosstab query fields within this query.
Is there no workaround to this problem?

 

[JHB]

Here's the solution the page describes:

But I don't understand step 5. What does "the name of the parameter or the form reference" mean?

Below is how they meant it:

PARAMETERS [Forms]![frmStudentsDetails]![txtSearchFirstName] Text ( 255 );
TRANSFORM First(tblStudentPhone.PhoneNumber) AS FirstOfPhoneNumber
SELECT tblStudentPhone.StudentID
FROM tblStudents LEFT JOIN tblStudentPhone ON tblStudents.StudentID = tblStudentPhone.StudentID
WHERE (((tblStudents.FirstName)=[Forms]![frmStudentsDetails]![txtSearchFirstName])) OR ((([Forms]![frmStudentsDetails]![txtSearchFirstName])=""))
GROUP BY tblStudentPhone.StudentID
PIVOT tblStudentPhone.PhoneType;

I can confirm that the crosstab query is the problem here - I did say earlier that if I remove the fields from the crosstab query, the error is still there

Sorry I've forgotten you've mention the crosstab query.
In your case you can't use a crosstab query because each student has different amount of phonetypes!
I've made 2 solutions for you, one which include a temp table and one which use a user defined function in a query, ("NewQuery").
Form "frmStudentsDetails" has the recordsource set to the "NewQuery"
Form "frmStudentsDetailsTable" has the recordsource set to the "TempTable"

 

[Zak14]

Below is how they meant it:

Sorry I've forgotten you've mention the crosstab query.
In your case you can't use a crosstab query because each student has different amount of phonetypes!
I've made 2 solutions for you, one which include a temp table and one which use a user defined function in a query, ("NewQuery").
Form "frmStudentsDetails" has the recordsource set to the "NewQuery"
Form "frmStudentsDetailsTable" has the recordsource set to the "TempTable"

Wow, thanks JHB. Can you please briefly explain what you've done for both these forms and anything else I should know, so that I can reproduce it.
Thank you

P.S: My objective here is to have a search box beneath all the fields on the continuous form (e.g. Name, DOB, Email, Mobile, Telephone, Address, etc).

 

[JHB]

Can you please briefly explain what you've done for both these forms and anything else I should know, so that I can reproduce it.

Try to analyse what happen in the 2 solutions, and then ask specific question of what you don't understand, (take it as a leaning process )?

 

[Zak14]

I looked at it and understood some of it. I guess I'm asking just in case I miss a bunch of stuff out - wouldn't be good.
I will look at it deeper tomorrow; don't go anywhere.
Oh and since our replies tend to be quite far apart, please just for now, name the steps you took and I'll do my research.
And which method is cleaner/easier/works best and with fewer potential problems?
Thanks.

 

[JHB]

...don't go anywhere.

I'll be around, don't worry!

And which method is cleaner/easier/works best and with fewer potential problems?

On a big amount of data (many students), I would use the TempTable solution, (many people doesn't like it, I use them when I need them).
UDF, (user defined function), has a speed issue on a big amount of data!

 

[Zak14]

I understand what you've done there, but I just don't get how to replicate it.
It looks like too much effort - even if you explained it - and impractical for a workaround to what should've been really easy (using the filter query method).

I'm just gonna exclude the phone number fields from the form.

Thanks for your help here. Very much appreciated