Setting textbox in a form equal to data in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 5 text boxes named: Value1, Value2, Value3, Value4, Value5.

I have a query with 5 rows. One of the column names is CardNumber.

Setting Value1 equal to Cardnumber in the first row of the query is easy:
Value1 = DLookup("[CardNumber]","[QueryName]")

How do I get Value2 = to CardNumber in the second row of the query,
Value3 = to CardNumber in the third row of the query,
Value4 = to CardNumber in the forth row of the query,
Value5 = to CardNumber in the fifth row of the query.

Please help.

Thanks
 
Is there an unique identifier you could use to do
Value1 = DLookup("[CardNumber]","[QueryName]","[id] = " & ID1)
Value2 = DLookup("[CardNumber]","[QueryName]","[id] = " & ID2)

or in the forms op open

Dim sqlst As String
Dim con, rs As Object

sqlst = "Select CardNumber From [QueryName] "

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1
Value1 = rs![CardNumber]
rs.MoveNext
Value2 = rs![CardNumber]
rs.MoveNext
Value3= rs![CardNumber]
rs.MoveNext
Value4 = rs![CardNumber]
rs.MoveNext
Value5= rs![CardNumber]

rs.Close
set rs = nothing

This is not tested so there may be typos.
 
Back
Top