Return a value from a record based on the record's position in a q

[Guest]

I have a form in which I've positioned a whole bunch of unbound text box
controls to appear like a spread sheet. In the first 'column' of text boxes I
want to return values from a particular field from a query in the order they
appear in the datasheet view of this query. That is, in the first Text Box on
my form I want to return the value from field "x" that appears in the first
record of this query. In the Text Box directly below this on my form I want
to return the value from field "x" that appears in the second record of my
query.

Is there a function I could use in the Control Source of each text box on my
form that looks up a value from a field in a query based on the 'record
number' so to speak?

 

[Guest]

Jon,

You could try something like this:

Private Sub Test_Click() -- button or on load from form I used a button..
Dim rst As ADODB.Recordset
Dim i As Integer

Set rst = New ADODB.Recordset

i = 1
rst.Open "[your queryname here]", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

With rst
Do While Not rst.BOF And rst.EOF
Me.Controls("txtData" & i) = rst(0) ---- this being the first field from
the query
rst.MoveNext
i = i + 1
Loop
End With
rst.Close
set rst=nothing
End Sub

Keep the following in mind;

You will get an error if the number of records is greater than the number of
controls on your form.

Make sure the controls are named "txtData1", "txtData2" etc... (see code)

Maurice

 

[Guest]

Thanks very much for the reply Maurice, but this solution's a bit beyond my
capabilities I'm afraid. I'm not very profitient with the visual basic (code)
side of Access. What I mean by that is that I know absolutely nothing of it.

Jon.

Jon,

You could try something like this:

Private Sub Test_Click() -- button or on load from form I used a button..
Dim rst As ADODB.Recordset
Dim i As Integer

Set rst = New ADODB.Recordset

i = 1
rst.Open "[your queryname here]", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

With rst
Do While Not rst.BOF And rst.EOF
Me.Controls("txtData" & i) = rst(0) ---- this being the first field from
the query
rst.MoveNext
i = i + 1
Loop
End With
rst.Close
set rst=nothing
End Sub

Keep the following in mind;

You will get an error if the number of records is greater than the number of
controls on your form.

Make sure the controls are named "txtData1", "txtData2" etc... (see code)

Maurice

I have a form in which I've positioned a whole bunch of unbound text box
controls to appear like a spread sheet. In the first 'column' of text boxes I
want to return values from a particular field from a query in the order they
appear in the datasheet view of this query. That is, in the first Text Box on
my form I want to return the value from field "x" that appears in the first
record of this query. In the Text Box directly below this on my form I want
to return the value from field "x" that appears in the second record of my
query.

Is there a function I could use in the Control Source of each text box on my
form that looks up a value from a field in a query based on the 'record
number' so to speak?