Assign a value from select to a variable

[Gr3g0ry]

so i have a simple select query which selects one value from a table. how do i then pass the one value to a variable ?

the query in question is highlighted.

 

[theDBguy]

You can use DLookup().

 

[June7]

RunSQL is for action statements - DELETE, INSERT, UPDATE, not for SELECT. Use SELECT to set recordset object.

Use domain aggregate function instead of opening a recordset.

x = DLookup("Amount", "inventory", "InventoryID ='" & Me.Inventory_ID & "'")

If InventoryID is a number field, don't use apostrophe delimiters.

 

[CJ_London]

As has already been said, better to use dlookup for a single value from a single table or query. But if you have a more complex requirement - multiple fields and/or multiple tables use a recordset e.g.

dim rs as DAO.Recordset

set rs=currentdb.openrecordset("SELECT * FROM inventory WHERE InventoryID =" & Me.Inventory_ID )
'assign to controls
me.txtInvName=rs!InvName
me.txtColour=rs!colour

set rs=nothing

 

[Gr3g0ry]

RunSQL is for action statements - DELETE, INSERT, UPDATE, not for SELECT. Use SELECT to set recordset object.

Use domain aggregate function instead of opening a recordset.

x = DLookup("Amount", "inventory", "InventoryID ='" & Me.Inventory_ID & "'")

If InventoryID is a number field, don't use apostrophe delimiters.

thanks alot. worked like a charm. thanks for the pointer on the encapsulation and tutorial on how to use DLookup.

 

[Gr3g0ry]

As has already been said, better to use dlookup for a single value from a single table or query. But if you have a more complex requirement - multiple fields and/or multiple tables use a recordset e.g.

dim rs as DAO.Recordset

set rs=currentdb.openrecordset("SELECT * FROM inventory WHERE InventoryID =" & Me.Inventory_ID )
'assign to controls
me.txtInvName=rs!InvName
me.txtColour=rs!colour

set rs=nothing

thanks

 

[theDBguy]

thanks alot. worked like a charm. thanks for the pointer on the encapsulation and tutorial on how to use DLookup.

Glad to hear you got it sorted out. Good luck with your project.