Query Retrieval From Form

[lhooker]

Can somone tell me what is wrong with my attempt to retrieve of a value (Last_Name) from an open form (Questions) to a query ?

Form name is Questions
Form field name is Last_Name

This query criteria is [Forms]![Questions]![Last_Name].
I also tried CStr([Forms]![Questions]![Last_Name]). The field definition in the table is "Short Text". The form is displayed when running the query.:banghead:

 

[MarkK]

You have not described a problem. Presumably there is a failure?
Mark

 

[lhooker]

Any hints on resolving this failure ?

 

[MarkK]

What is the failure? Are you getting a wrong result? An Error? You have not described the symptoms of the problem.

 

[lhooker]

I would appreciate it if someone could tell me where to look for the resolution of this problem.

 

[plog]

As Mark asked--what is the failure? Error message? Unexpected results? House catching on fire? Developing tumors when running it? What specifically is occuring that you are calling a failure?

 

[lhooker]

Thanks . . . No record(s) are displayed.

 

[lhooker]

That is, the current record on the form.

 

[isladogs]

Because we seem to be going around in circles here, I'll make a suggestion.
Its pure guesswork as you haven't given us the context!

If using VBA code with that string, you need to add text delimiters ...
Single quote then double quote before the string and the opposite afterwards

'" & [Forms]![Questions]![Last_Name] & "'

 

[lhooker]

I thought I gave everything that is needed in my opening request. I'm using a query to retrieve a data field in a form. This query criteria is [Forms]![Questions]![Last_Name].
I also tried CStr([Forms]![Questions]![Last_Name]). The field definition in the table is "Short Text". The form is open when running the query, but no record retrieved. What else is needed ?

 

[isladogs]

You really didn't give us much useful info - that's why 3 of us responded as we did

1. Do you get any results running the query without the filter?
2. Have you tried running the SQL with a debug print line to see the output in the VBE immediate window?
3. Have you tried adding text delimiters to the query criteria?

Suggest you copy the query in SQL view and upload it here so we have something to go on ....

 

[lhooker]

Yes, I can retrieve all records from the table without the filter. Here is the SQL code:

SELECT Spiritual_Gift_Totals.Survey_Taker_First_Name, Spiritual_Gift_Totals.Survey_Taker_Middle_Name, Spiritual_Gift_Totals.Survey_Taker_Last_Name, Spiritual_Gift_Totals.Leadership, Spiritual_Gift_Totals.Administration, Spiritual_Gift_Totals.Teaching, Spiritual_Gift_Totals.Knowledge, Spiritual_Gift_Totals.Wisdom, Spiritual_Gift_Totals.Prophecy, Spiritual_Gift_Totals.Discernment, Spiritual_Gift_Totals.Exhortation, Spiritual_Gift_Totals.Shepherding, Spiritual_Gift_Totals.Faith, Spiritual_Gift_Totals.Evangelism, Spiritual_Gift_Totals.Apostleship, Spiritual_Gift_Totals.[Service/Helps], Spiritual_Gift_Totals.Mercy, Spiritual_Gift_Totals.Giving, Spiritual_Gift_Totals.Hospitality, "Summary For " & [Survey_Taker_First_Name] & " " & [Survey_Taker_Middle_Name] & " " & [Survey_Taker_Last_Name] & "'s" & " Gifts" AS Name
FROM Spiritual_Gift_Totals
WHERE (((Spiritual_Gift_Totals.Survey_Taker_Last_Name)=[Forms]![Questions]![Last_Name]));

 

[Pat Hartman]

1. Make sure there actually is data that matches the criteria you are using.
2. Make sure that you have no compile errors.
3. It is possible that the form or the table or the query has become corrupted. This would require creating a new database and importing everything except the table, form, and query involved in the problem. Those objects should be exported to text and then imported from text or rebuild entirely if they are not too complex.

 

[lhooker]

1. The data is in the form that I'm trying to retrieve
2. No error codes returned
3. The form, table, query, or report does not appear to be corrupted. How can I tell ?

 

[lhooker]

I created a new database ("Database3") and the same thing happens. See attachment.:banghead:

 

[plog]

Your database works for me. I open the form, then I open the query, the query has 1 record that matches the one on the form.

 

[isladogs]

It also works for me

 

[lhooker]

The only way a record shows in the query is when I advance to the next record (new record), then back to the previous record on the form. Is this the way that it suppose to work ? I wanted to retrieve the current record on the form without saving (i.e. advancing to the next record).

 

[isladogs]

This is what I see:

The only change I've made was to get replace tabbed documents with overlapping windows in database options. You had this:

However, what I think you're getting at is that new records don't appear in the query until you've moved to another record.
That is by design - the record isn't saved until you move on.
There are ways around it but it is how databases are meant to work

Tables are used for storing data.
Forms are used for viewing & editing existing records as well as entering new records.
Queries are used for finding out various things about records that have already been saved

Not sure if that answers your question at all ...

 

[lhooker]

OK,I found out that I can get the information in VBA. I guest I have to somehow (through VBA and SQL) insert the form information into a table via SQL query in VBA (via DoCmd command). Do you think this is the solution. I originally wanted to pass the firstname, middilename, and lastname into a query for a report.