"SELECT"

How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Yes now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are feild 4 or
field 8 or field 10,
three different fields that hold the rates for a 4 hour rate, 8 hr rate or a
10 hr rate...



"Dan Artuso" wrote:

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Something like this:

Dim strSql As String
Dim rs As DAO.Recordset

strSql = "Select " & nameOfField & " From yourTable"

Set rs = CurrentDb.OpnRecordset(strSql)

I don't what your reluctance is to provide the actual names of your tables, forms and controls
but it sure would make it easier if you did so.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Yes now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are feild 4 or
field 8 or field 10,
three different fields that hold the rates for a 4 hour rate, 8 hr rate or a
10 hr rate...



"Dan Artuso" wrote:

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

I did Dan, 4, 8, and 10 hour rates...

Bennie

"Dan Artuso" wrote:

Something like this:

Dim strSql As String
Dim rs As DAO.Recordset

strSql = "Select " & nameOfField & " From yourTable"

Set rs = CurrentDb.OpnRecordset(strSql)

I don't what your reluctance is to provide the actual names of your tables, forms and controls
but it sure would make it easier if you did so.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Yes now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are feild 4 or
field 8 or field 10,
three different fields that hold the rates for a 4 hour rate, 8 hr rate or a
10 hr rate...



"Dan Artuso" wrote:

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Hi,
You're just not being very clear. Are you saying that you have three fields named:
4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc...

See what I mean? And what is the name of the table these fields are in?
In any case, I've answered the best I can with the info you've provided.

Good Luck!

Dan Artuso, MVP

"REVBJONES" wrote in message ...
I did Dan, 4, 8, and 10 hour rates...

Bennie

"Dan Artuso" wrote:

Something like this:

Dim strSql As String
Dim rs As DAO.Recordset

strSql = "Select " & nameOfField & " From yourTable"

Set rs = CurrentDb.OpnRecordset(strSql)

I don't what your reluctance is to provide the actual names of your tables, forms and controls
but it sure would make it easier if you did so.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Yes now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are feild 4 or
field 8 or field 10,
three different fields that hold the rates for a 4 hour rate, 8 hr rate or a
10 hr rate...



"Dan Artuso" wrote:

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Thanks again Dan, you're really a big help to me. The table is tblDayRate,
the form is my frmInvoiceDetails. There is a field on the form (A different
Source) that requires an entry of 4, 8, or 10. I need to access the DayRate
table to pull the rate for that amount of hours based on the Currency Code
and Vehicle Code that match the C Code and V Code on my current form...

Is that good Dan?

Bennie...

"Dan Artuso" wrote:

Hi,
You're just not being very clear. Are you saying that you have three fields named:
4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc...

See what I mean? And what is the name of the table these fields are in?
In any case, I've answered the best I can with the info you've provided.

Good Luck!

Dan Artuso, MVP

"REVBJONES" wrote in message ...
I did Dan, 4, 8, and 10 hour rates...

Bennie

"Dan Artuso" wrote:

Something like this:

Dim strSql As String
Dim rs As DAO.Recordset

strSql = "Select " & nameOfField & " From yourTable"

Set rs = CurrentDb.OpnRecordset(strSql)

I don't what your reluctance is to provide the actual names of your tables, forms and controls
but it sure would make it easier if you did so.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Yes now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are feild 4 or
field 8 or field 10,
three different fields that hold the rates for a 4 hour rate, 8 hr rate or a
10 hr rate...



"Dan Artuso" wrote:

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie

Hi Bennie,
Yes! That's more like it. Now you're giving me a better understanding of what you're after.
DLookup() seems like it will fit the bill for this.

There are a few more things I need to know.
What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field
that we can use??? And do you actually have 3 fields called 4, 8, and 10??

If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need
to know how we can relate the DayRate to these values.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message ...
Thanks again Dan, you're really a big help to me. The table is tblDayRate,
the form is my frmInvoiceDetails. There is a field on the form (A different
Source) that requires an entry of 4, 8, or 10. I need to access the DayRate
table to pull the rate for that amount of hours based on the Currency Code
and Vehicle Code that match the C Code and V Code on my current form...

Is that good Dan?

Bennie...

"Dan Artuso" wrote:

Hi,
You're just not being very clear. Are you saying that you have three fields named:
4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc...

See what I mean? And what is the name of the table these fields are in?
In any case, I've answered the best I can with the info you've provided.

Good Luck!

Dan Artuso, MVP

"REVBJONES" wrote in message ...
I did Dan, 4, 8, and 10 hour rates...

Bennie

"Dan Artuso" wrote:

Something like this:

Dim strSql As String
Dim rs As DAO.Recordset

strSql = "Select " & nameOfField & " From yourTable"

Set rs = CurrentDb.OpnRecordset(strSql)

I don't what your reluctance is to provide the actual names of your tables, forms and controls
but it sure would make it easier if you did so.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
Yes now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are feild 4 or
field 8 or field 10,
three different fields that hold the rates for a 4 hour rate, 8 hr rate or a
10 hr rate...



"Dan Artuso" wrote:

Hi,
Once you know what field you want to query, how do you determine
which record you want?
I mean you could build a Select statement and open a recordset, but do you
not want to limit it in some way?

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
Thanks, you're a big help. Now I need to know how to pull the right field
from another table based on the value of a field on my current form... ex:
value of the rate field on the form is 4, options in table are 4 or 8 or 10,
three different fields...

Bennie...

"Dan Artuso" wrote:

Hi,
As I said in your other thread, DLookup() will be easier than writing a SQL statement
and opening up a recordset.
The code would probably go in the Current event of the form, although without more info
I can't be sure. It depends on when you want to populate the control.

Here is the syntax for DLookup:
DLookup(expr, domain[, criteria])

so you would have something like this:

yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _
"' And [someOtherField] = '" & control2 "'"

If you want exact code, you have to provide the names of your controls, fields, tables
etc
Also the datatype of your criteria is important as well (string,numeric or dates) as each
one requires a different delimiter.

I noticed you have three threads going, please reply to this one so everyone can follow
what's happening and you'll have more chance of other people replying as well.

--
HTH
Dan Artuso, Access MVP


"REVBJONES" wrote in message
...
How do I use SELECT to pull into a form a value from another source based on
2 values on the currenr form and record and where do I put the code?

Bennie