Reading Field Data into Variable

[MikeEller]

Hello,
I would like to read field data from a table and have the data placed into a variable. How can I do this?

Also, How do I address variables in SQL statements? I used to know this...just been too long.

Thanks,
Mike

 

[Bodisathva]

Hello,
I would like to read field data from a table and have the data placed into a variable. How can I do this?

DLookup(fieldname,tablename,[criteria])

Also, How do I address variables in SQL statements? I used to know this...just been too long.

option 1: Append or insert your variable into the SQL string

 "SELECT blah, blah FROM Blah WHERE blah = '" & stringVariable & "'"

(numeric variables don't need the quotes, obviously)

option2: Create a parameterized query, initialize the parameters, call the recordset.

qryDef.parameters!parameterName1 = blah, blah, blah
qryDef.parameters!parameterName2 = blah, blah, blah
set newRecordset = qryDef.OpenRecordset

 

[MikeEller]

OK,
That works as needed.
Thank you.

One more quick question....

I have a list box on a form

I would like to capture the selected data into a variable.
How can I do this? Everything I am trying just gives me the array value - a number. I want the text.

Again, thanks
Mike

 

[Bodisathva]

lstBox.ItemData(intIndex)

 

[MikeEller]

OK,
I switched up to a ComboBox....

Here is code snippet:

sel = Combo30.Text

tmpStr = DLookup("[rank]", "tbl_user", "[lname] = " & sel)


It gets the selected ComboBox text and reads it into the variable sel
Then I want to use DLookup to read the value of the "rank" field from the "tbl_user" table where the "lname" field value is equal to what is in the variable "sel".

When I run this I get an error box that states:

Run-time error'2471':
The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'Reviere."

Reviere is the name selected from the ComboBox...and it does exist in the tbl_user table.
this code example follows the example found in the help section.

Any idea what is going on here?

Thanks,
Mike

 

[Bodisathva]

try:

sel = Combo30.Text

tmpStr = DLookup("[rank]", "tbl_user", "[lname] = [COLOR="Red"]'[/COLOR]" & sel [COLOR="red"]& "'"[/COLOR])

If you are doing text comparisons, the text must be enclosed in single quotes in order to denote the string. The error stems from attempting to locate a variable name, not a string value.

 

[tennant]

I'm a bit rusty on my Access programming; but am looking to do a similar thing.

I use a table with a single record to store variables for my application (exchange rates, markups etc.). I can use DLookup to read the values into a form in order to calculate from them. However how do I write back amended figures to the table from a form (the form is doing a number of other things tied to a different table...ie I can't use the form which has the variables table as its source).

Tennant