Passing values from a form to a query

D

DV

I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I execute the
query from the form, I'm still prompted for parameter values for the fields
I'm trying to pass to the query. In other words, the query is not using the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
 
O

Ofer

For the sub Form try

Yours
[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Should be
[Forms]![xfrm Research Database]![xfrm Research Database
Subform].Form![SYSTEM]

Addinf the word Form before the name of the field
 
D

DV

Here's the sql:
SELECT [xResearch Database].[RACF ID], [xResearch Database].SYSTEM, [xPhone
Directory - After Move].[LAST NAME], [xPhone Directory - After Move].[FIRST
NAME], [xPhone Directory - After Move].DEPT, [xPhone Directory - After
Move].[OFFICE EMAIL], [xResearch Database].[ACTIVE DATE], [xPhone Directory -
After Move].[OFFICE PHONE], [xPhone Directory - After Move].[OFFICE
MAILCODE], [xPhone Directory - After Move].[EMPLOYEE NAME], [xResearch
Database].[ACCESS TYPE], [xResearch Database].[TERMINAL LOCATION], [xResearch
Database].[DATE ENTERED], [xResearch Database].STATUS
FROM [xResearch Database] INNER JOIN [xPhone Directory - After Move] ON
[xResearch Database].[RACF ID] = [xPhone Directory - After Move].[RACF ID]
WHERE ((([xResearch Database].[RACF ID])=[Forms]![xfrm Research
Database]![RACF ID]) AND (([xResearch Database].SYSTEM)=[Forms]![xfrm
Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]));

I tried Ofer's recommendation, still had the same problem.

Roger Carlson said:
Check carefully for mis-spelling. That's the most common problem. Can you
post the SQL of the query too?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


DV said:
I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I execute the
query from the form, I'm still prompted for parameter values for the fields
I'm trying to pass to the query. In other words, the query is not using the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
Click to expand...
Click to expand...
 
O

Ofer

Open the form and insert a value in the system field, open the Immidiate
window (press Ctrl + G) type in it

?Forms![xfrm Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]

Press enter and see if any value returned or if you are getting an error
message

--
\\// Live Long and Prosper \\//
BS"D


DV said:
Here's the sql:
SELECT [xResearch Database].[RACF ID], [xResearch Database].SYSTEM, [xPhone
Directory - After Move].[LAST NAME], [xPhone Directory - After Move].[FIRST
NAME], [xPhone Directory - After Move].DEPT, [xPhone Directory - After
Move].[OFFICE EMAIL], [xResearch Database].[ACTIVE DATE], [xPhone Directory -
After Move].[OFFICE PHONE], [xPhone Directory - After Move].[OFFICE
MAILCODE], [xPhone Directory - After Move].[EMPLOYEE NAME], [xResearch
Database].[ACCESS TYPE], [xResearch Database].[TERMINAL LOCATION], [xResearch
Database].[DATE ENTERED], [xResearch Database].STATUS
FROM [xResearch Database] INNER JOIN [xPhone Directory - After Move] ON
[xResearch Database].[RACF ID] = [xPhone Directory - After Move].[RACF ID]
WHERE ((([xResearch Database].[RACF ID])=[Forms]![xfrm Research
Database]![RACF ID]) AND (([xResearch Database].SYSTEM)=[Forms]![xfrm
Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]));

I tried Ofer's recommendation, still had the same problem.

Roger Carlson said:
Check carefully for mis-spelling. That's the most common problem. Can you
post the SQL of the query too?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


DV said:
I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I execute the
query from the form, I'm still prompted for parameter values for the fields
I'm trying to pass to the query. In other words, the query is not using the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
Click to expand...
Click to expand...
Click to expand...
 
E

Eric Heinold

It looks right after you add the .Form to the line. One thing that I had
problems with for this was that the name of the subform object should be
just before .FORM, and it is not necessarily the same as the subform:

[Forms]![xfrm Research Database]![xfrm Research Database
Subform].Form![SYSTEM]
^^^^^^^^^^^^^^^^^^^^^^^^^

In xFrm Research Database, make sure the subform object is called xfrm
Research Datbase Subform
Depending on how you created the subform, it may have a name like Child128
or if you have changed the name of the Source Object of the subform, the
actual name of the object can be different from the subform itself. Access
wants the name of the object, not the datatbase name of the subform.
 
R

Roger Carlson

Are you certain the control (not the field itself, but the texbox control
holding it) is named "RACF ID"? The name of the textbox does not have to be
the same as the name of the field. Also, for the subform, you should use
the name of the subform CONTROL (which is the control on the mainform that
displays the subform), and not the subform name itself. Make absolutely
sure of the Name property of each of these controls.

If they are correct, I have no idea what's wrong.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




DV said:
Here's the sql:
SELECT [xResearch Database].[RACF ID], [xResearch Database].SYSTEM, [xPhone
Directory - After Move].[LAST NAME], [xPhone Directory - After Move].[FIRST
NAME], [xPhone Directory - After Move].DEPT, [xPhone Directory - After
Move].[OFFICE EMAIL], [xResearch Database].[ACTIVE DATE], [xPhone Directory -
After Move].[OFFICE PHONE], [xPhone Directory - After Move].[OFFICE
MAILCODE], [xPhone Directory - After Move].[EMPLOYEE NAME], [xResearch
Database].[ACCESS TYPE], [xResearch Database].[TERMINAL LOCATION], [xResearch
Database].[DATE ENTERED], [xResearch Database].STATUS
FROM [xResearch Database] INNER JOIN [xPhone Directory - After Move] ON
[xResearch Database].[RACF ID] = [xPhone Directory - After Move].[RACF ID]
WHERE ((([xResearch Database].[RACF ID])=[Forms]![xfrm Research
Database]![RACF ID]) AND (([xResearch Database].SYSTEM)=[Forms]![xfrm
Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]));

I tried Ofer's recommendation, still had the same problem.

Roger Carlson said:
Check carefully for mis-spelling. That's the most common problem. Can you
post the SQL of the query too?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


DV said:
I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I
Click to expand...
execute
the
query from the form, I'm still prompted for parameter values for the fields
I'm trying to pass to the query. In other words, the query is not
Click to expand...
using
the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
Click to expand...
Click to expand...
Click to expand...
 
D

DV

I gave it a go and got... Compile error: Expected:=

This is what I have in the query criteria
[Forms]![xfrm Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]

Ofer said:
Open the form and insert a value in the system field, open the Immidiate
window (press Ctrl + G) type in it

?Forms![xfrm Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]

Press enter and see if any value returned or if you are getting an error
message

--
\\// Live Long and Prosper \\//
BS"D


DV said:
Here's the sql:
SELECT [xResearch Database].[RACF ID], [xResearch Database].SYSTEM, [xPhone
Directory - After Move].[LAST NAME], [xPhone Directory - After Move].[FIRST
NAME], [xPhone Directory - After Move].DEPT, [xPhone Directory - After
Move].[OFFICE EMAIL], [xResearch Database].[ACTIVE DATE], [xPhone Directory -
After Move].[OFFICE PHONE], [xPhone Directory - After Move].[OFFICE
MAILCODE], [xPhone Directory - After Move].[EMPLOYEE NAME], [xResearch
Database].[ACCESS TYPE], [xResearch Database].[TERMINAL LOCATION], [xResearch
Database].[DATE ENTERED], [xResearch Database].STATUS
FROM [xResearch Database] INNER JOIN [xPhone Directory - After Move] ON
[xResearch Database].[RACF ID] = [xPhone Directory - After Move].[RACF ID]
WHERE ((([xResearch Database].[RACF ID])=[Forms]![xfrm Research
Database]![RACF ID]) AND (([xResearch Database].SYSTEM)=[Forms]![xfrm
Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]));

I tried Ofer's recommendation, still had the same problem.

Roger Carlson said:
Check carefully for mis-spelling. That's the most common problem. Can you
post the SQL of the query too?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I execute
the
query from the form, I'm still prompted for parameter values for the
fields
I'm trying to pass to the query. In other words, the query is not using
the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
Click to expand...
Click to expand...
Click to expand...
 
D

DV

Eric,

Where can I find the name of the subform CONTROL - the control on the
mainform thatdisplays the subform?

Roger Carlson said:
Are you certain the control (not the field itself, but the texbox control
holding it) is named "RACF ID"? The name of the textbox does not have to be
the same as the name of the field. Also, for the subform, you should use
the name of the subform CONTROL (which is the control on the mainform that
displays the subform), and not the subform name itself. Make absolutely
sure of the Name property of each of these controls.

If they are correct, I have no idea what's wrong.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




DV said:
Here's the sql:
SELECT [xResearch Database].[RACF ID], [xResearch Database].SYSTEM, [xPhone
Directory - After Move].[LAST NAME], [xPhone Directory - After Move].[FIRST
NAME], [xPhone Directory - After Move].DEPT, [xPhone Directory - After
Move].[OFFICE EMAIL], [xResearch Database].[ACTIVE DATE], [xPhone Directory -
After Move].[OFFICE PHONE], [xPhone Directory - After Move].[OFFICE
MAILCODE], [xPhone Directory - After Move].[EMPLOYEE NAME], [xResearch
Database].[ACCESS TYPE], [xResearch Database].[TERMINAL LOCATION], [xResearch
Database].[DATE ENTERED], [xResearch Database].STATUS
FROM [xResearch Database] INNER JOIN [xPhone Directory - After Move] ON
[xResearch Database].[RACF ID] = [xPhone Directory - After Move].[RACF ID]
WHERE ((([xResearch Database].[RACF ID])=[Forms]![xfrm Research
Database]![RACF ID]) AND (([xResearch Database].SYSTEM)=[Forms]![xfrm
Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]));

I tried Ofer's recommendation, still had the same problem.

Roger Carlson said:
Check carefully for mis-spelling. That's the most common problem. Can you
post the SQL of the query too?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I execute
the
query from the form, I'm still prompted for parameter values for the
fields
I'm trying to pass to the query. In other words, the query is not using
the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Carlson

If you move your pointer to the very edge of the subform control and
right-click there, you will be able to pull up the Properties. If the
properties sheet has Link Parent and Link Child properties, you've found the
right one. Also, in any code window behind the form, if you type Me. you
will get a list of controls as well as form properties. Scroll down that
list until you find your subform control. The name listed in the
Intellisense is the control name.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


DV said:
Eric,

Where can I find the name of the subform CONTROL - the control on the
mainform thatdisplays the subform?

Roger Carlson said:
Are you certain the control (not the field itself, but the texbox control
holding it) is named "RACF ID"? The name of the textbox does not have to be
the same as the name of the field. Also, for the subform, you should use
the name of the subform CONTROL (which is the control on the mainform that
displays the subform), and not the subform name itself. Make absolutely
sure of the Name property of each of these controls.

If they are correct, I have no idea what's wrong.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




DV said:
Here's the sql:
SELECT [xResearch Database].[RACF ID], [xResearch Database].SYSTEM, [xPhone
Directory - After Move].[LAST NAME], [xPhone Directory - After Move].[FIRST
NAME], [xPhone Directory - After Move].DEPT, [xPhone Directory - After
Move].[OFFICE EMAIL], [xResearch Database].[ACTIVE DATE], [xPhone Directory -
After Move].[OFFICE PHONE], [xPhone Directory - After Move].[OFFICE
MAILCODE], [xPhone Directory - After Move].[EMPLOYEE NAME], [xResearch
Database].[ACCESS TYPE], [xResearch Database].[TERMINAL LOCATION], [xResearch
Database].[DATE ENTERED], [xResearch Database].STATUS
FROM [xResearch Database] INNER JOIN [xPhone Directory - After Move] ON
[xResearch Database].[RACF ID] = [xPhone Directory - After Move].[RACF ID]
WHERE ((([xResearch Database].[RACF ID])=[Forms]![xfrm Research
Database]![RACF ID]) AND (([xResearch Database].SYSTEM)=[Forms]![xfrm
Research Database]![xfrm Research Database
Subform].[Form]![SYSTEM]));

I tried Ofer's recommendation, still had the same problem.

:

Check carefully for mis-spelling. That's the most common problem.
Click to expand...
Can
you
post the SQL of the query too?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I created a query to to be excuted from a command button on a form after
being passed criteria from values on that form. However, when I execute
the
query from the form, I'm still prompted for parameter values for the
fields
I'm trying to pass to the query. In other words, the query is not using
the
values from the form.

I entered the following in the query Criteria for the specific fields:

[Forms]![xfrm Research Database]![RACF ID]

I'm having the same problem with the subform where I entered:

[Forms]![xfrm Research Database]![xfrm Research Database Subform]![SYSTEM]

Pleae help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Possible syntaxt problem getting value from a form field for a que 0
Problem passing a value from a form to query 3
Creating a Chart on form from selected data in query. 0
DLookup in Continuous form 0
Adding data to MS Access from csv file 0
Dynamic Naming of Form Button 3
Populate main form from subform record 0
Can I save a sql statement generated by a search form? 1

Top