Query in VBA

[ivo]

How can i get a record-value from a table by making a
query and put it in a variable ?
I would like to know the method in VBA-code.

Thanks,

Ivo

 

[John Spencer (MVP)]

Try looking at the DLookup function. You will end up with something that looks
like the following.

YourVariable = DLookup("SomeFieldName","SomeTableName","SomeOtherField = ""ABC"" ")

 

[Michel Walsh]

Hi,

Many ways, but generally we do not do that. At most, we bring the
data in a recordset and use the recordset, but if you want the data "in a
variable", someone can use GetRows() or, for ADO recordsets, GetString:

Dim x() As Variant
x= CurrentDb.OpenRecordset("SELECT * FROM someTable WHERE
somecondition").GetRows


x is then a table (matrix) where the fields are now lines and the records,
columns (ie, the data is "transposed"). You use two indices to reach an
element:

Debug.Print x(0, 0 )

ADO allows the same

x=CurrentProject.Connection.Execute("SELECT * FROM table1").GetRows

and also allow a not-transposed representation, in text:

? CurrentProject.Connection.Execute("SELECT * FROM
table1").GetString

If you want just a single value (just one field), you can use DLookup.


Hoping it may help,
Vanderghast, Access MVP

 

[Ivo]

Thanks,

Dlookup works great.
Is there a same kind of way to write to the db ?

Thanks,

Ivo

-----Original Message-----
Try looking at the DLookup function. You will end up with something that looks
like the following.

YourVariable = DLookup

("SomeFieldName","SomeTableName","SomeOtherField
= ""ABC"" ")