how to connect query with form field

E

eleftheriak

Hello guys,

I was just wondering if anyone knows how to get the form field and put
it in a query?

SELECT jobs.title FROM Trying WHERE [jobs.job_no]=Form.job_no.Value;

This does not work!!!!!

Thanks in advance

Riri
 
A

Allen Browne

If Form1 is open, and has a text box named job_no, you could use:
SELECT jobs.title FROM Trying
WHERE [jobs.job_no] = [Forms].[Form1].[job_no];
 
E

eleftheriak

in my table i have the data like this

j7001 hello
j7001 goodbye
j7002 night
j003 afternoon
j003 day

I have a combo box that I do the query and i do need to get the values
in the combo box? Is it right with one select or do I have to do a loop
and write some vb?

Thanks in advance

Riri
 
T

Tom Wickerath

Hi Riri,

Try something like this:

SELECT Jobs.Title
FROM Jobs
WHERE Jobs.job_no=[Forms]![frmJobs]![txtjob_no];

where the name of the form is shown as frmJobs and the name of the textbox
on the form is txtjob_no (make the appropriate substitutions). Of course, the
form must be open when you attempt to run the query.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello guys,

I was just wondering if anyone knows how to get the form field and put
it in a query?

SELECT jobs.title FROM Trying WHERE [jobs.job_no]=Form.job_no.Value;

This does not work!!!!!

Thanks in advance

Riri
 
E

eleftheriak

Hello Tom,

I m trying to get the job_no from the field of the form and put the
title of that job in a combo box. But this code just gives me only 1
record. It does seem that select does not work. Any ideas? I put inside
the combo box the select statement is that right?

Thanks in advance

Riri
 
A

Allen Browne

No loop is needed. You just need to match the value that is actually stored
in your field with the value in the control on the form.

If you used the lookup wizard to get the combo in the field in your table,
you may have Access lying to you about what is actually in that field. More
info in this article:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm
 
T

Tom Wickerath

Hi Riri,
I m trying to get the job_no from the field of the form and put
the title of that job in a combo box.
Click to expand...

Is the combo box on the same form? If so, I'm really not understanding why
you want to do this...
But this code just gives me only 1 record.
Click to expand...

That's probably to be expected. The WHERE clause of the SQL statement
includes a criteria for a specific job number.
I put inside the combo box the select statement is that right?
Click to expand...

I don't know....because I'm still trying to figure out what you are trying
to do. The first question is are you trying to use a combo box to find a
record, or are you trying to use a combo box to add/edit existing data? If
you are trying to use a combo box to find an existing record (ie. this
control does not change any data), then try out my tutorial here:

Combo box to find a record
http://www.access.qbuilt.com/html/find_a_record.html

If you are trying to use the combo box to add/edit data, then please provide
the names and data types of the fields for each table (the applicable
fields), the name of each table involved, the names of any queries that are
involved and their SQL statements, the name(s) of the form(s) involved, and
the names and types of controls on the forms that are involved. Also provide
some sample data for each of the involved fields in the tables. Finally,
provide an example of what data you want displayed in the combo box.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


:

Hello Tom,

I m trying to get the job_no from the field of the form and put the
title of that job in a combo box. But this code just gives me only 1
record. It does seem that select does not work. Any ideas? I put inside
the combo box the select statement is that right?

Thanks in advance

Riri
 
Top