Question Fire Recordset Data into Multiple Textboxes

[zooropa66]

I can create a recordset of labels from table test OK. What I then want to do (but can't figure out how) is to put each recordset value label01, label02, ... into a series of text boxes txt_label_01, txt_label_02, that are on a form...

Do I have to fire the recordset into an array (or is it already in an array - I'm not sure). My knowledge becomes a bit shaky here and any help or pointers would be appreciated. Thanks

var_lot_id = Combo178.Value
var_recordset_labels = "SELECT label FROM tbl_test WHERE lot_id = " & var_lot_id & ";"

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()

Set rs1 = db1.OpenRecordset(var_recordset_labels)

While Not rs1.EOF

myvar1 = rs1.Fields(0)

rs1.MoveNext
Wend
Set rs1 = Nothing

 

[mdlueck]

Do I have to fire the recordset into an array

No, you may leave the data in the recordset and map to field controls.

The tricky point in you case is variablizing the field names and field control names, since it looks like you intend to loop through fields / controls.

I do not have sample code lying around as I do not happen to do that. Seems I never want ALL of the DB fields displayed on a given form, so I hard code the DB field / control mapping code.

 

[mdlueck]

The tricky point in you case is variablizing the field names and field control names, since it looks like you intend to loop through fields / controls.

Explaining a bit further... the variable part of the control / field name is the number. So you need to have a number counter variable, and concatenate that number to the rest of the name of the field / control. Then hand all of that off to code which actually interacts with said control / db field.

 

[zooropa66]

Thanks Michael. Yes you're right. I want to loop through the text box controls and populate with the recordset values. I get the general idea and know how to loop through a controls collection and through a recordset. What I can't do is link them together.

 

[mdlueck]

What I can't do is link them together.

Air code...

To read controls

for loop
  adoRS.[dynamic name of column] = Me.[dynamic name of control].Value
end for

To populate controls

for loop
  Me.[dynamic name of control].Value = adoRS.[dynamic name of column]
end for

You will have to come up with the correct syntax how to refer to dynamic columns / field controls. But basically... the = sign is your friend... just map between the objects. Syntax may depend on what type of DB object you are using.

 

[zooropa66]

Thanks but i understand the general idea of what i need to do. What i really need is specific code. I think that in this instance air code won't help me (although sometimes in the past it has)

 

[Beetle]

I think an important point that's being overlooked here is why are you doing this? More to the point, why do you think it's necessary to do it this way, rather than just binding the form to the recordset and the form controls (text boxes) to the appropriate fields.

I'm not saying there wouldn't be a reason, but understanding the reason may be helpful in determining a solution. A few more specifics would be helpful as well. For instance, does the recordset always return the same number of labels?

 

[zooropa66]

The following code works although it's a bit clunky - I've actually got 72 textboxes and i don't fancy writing 72 sets of If...Then...Else...End If The number of labels is variable and depends on the lot selected in Combo 178.
I'll try to post an updated version whereby i loop through the controls collection that contains the textboxes. I'm just posting so others can follow the progression.

Dim i As Integer

i = 1

var_fdd_lot_id = Combo178.Value
var_recordset_labels = "SELECT label FROM tbl_test WHERE fdd_lot_id = " & var_fdd_lot_id & ";"

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()

Set rs1 = db1.OpenRecordset(var_recordset_labels)

While Not rs1.EOF

myvar1 = rs1.Fields(0)

If i = 1 Then
txt_label_01 = rs1.Fields(0)
Else
If i = 2 Then
txt_label_02 = rs1.Fields(0)
Else
If i = 3 Then
txt_label_03 = rs1.Fields(0)
End If
End If
End If

i = i + 1
rs1.MoveNext
Wend
Set rs1 = Nothing

 

[mdlueck]

All right, so you are using a DAO.Recordset object to connect to the database table...

Dim rs1 As DAO.Recordset
txt_label_01 = rs1.Fields(0)

Here is a handy page showing how to attach to various controls... always a nice reference to keep at hand:
http://access.mvps.org/access/forms/frm0031.htm

Here is a thread that seems to talk about stepping through field within a DAO.Recordset
http://bytes.com/topic/access/answers/190118-cycle-though-fields-recordset

 

[Beetle]

Still not sure what the point of this is, but you seem to be pressing on, so one option would be to take advantage of the Tag property of the text boxes. Put the corresponding number (1 - 72) in the Tag property of each text box, then do something like the following (more air code);

Dim strSQL As String
Dim rs as DAO.Recordset
Dim i As integer
Dim ctl As Control

strSQL = "SELECT label FROM tbl_test WHERE fdd_lot_id = " & Me.Combo178

Set rs = CurrentDb.OpenRecordset(strSQL)

i=1

With rs
    .MoveFirst
    Do While Not .EOF
        For Each ctl In Me.Controls
            If ctl.Tag = i Then ctl.Value = !label
        Next
        i = i + 1
        .MoveNext
    Loop
End With

Keep in mind that with no Order By clause in your query there is no guarantee that the records will be in a given order. Not sure if that affects what you're trying to do here or not.

 

[zooropa66]

Here you go.

So we have a combo box that contains some manufacturing lot numbers (alphanumeric). The user selects a lot and the id value associated with the lot is stored in variable var_fdd_lot_id

This variable is used in the SELECT query to pull the labels for the lot selected into recordset rs1. We just loop through the recordset and associate each value in turn with a label on the form whose name takes the form txt_label_i e.g. txt_label_01, txt_label_02 and increment i by 1 as we loop through the recordset.

Thanks to Michael & Beetle for your help

i = 1

var_lot_id = Combo178.Value
var_recordset_labels = "SELECT label FROM tbl_test WHERE lot_id = " & var_lot_id & ";"

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()

Set rs1 = db1.OpenRecordset(var_recordset_labels)

While Not rs1.EOF


If i >= 1 And i < 10 Then
Me.Controls("txt_label_0" & i).Value = rs1.Fields(0)
End If

If i >= 10 Then
Me.Controls("txt_label_" & i).Value = rs1.Fields(0)
End If


i = i + 1
rs1.MoveNext
Wend
Set rs1 = Nothing

 

[zooropa66]

Do I need to mark this thread as resolved? If so how do i do that as i can't see an obvious way to do it?

 

[mdlueck]

Aaahhh, the controls collection... that's the ticket.

Does rs1.Fields(0) actually give you what you are looking for with a DAO.Recordset? I would think that syntax would always refer to the same field.

There is no "mark thread resolved" in this forum system. Coming back to say it is solved, better that you posted the solution you arrived at, is perfect.

 

[zooropa66]

Yes it gives me exactly what I want. rs1.Fields(0) refers to the label field which contains unique labels label01, label02, ... These were originally entered manually into these text boxes and Inserted into the label field in table tbl_test. The combo box contains lot numbers. When a lot that hasn't had the labels inserted is selected, the ability to do that is there by entering them then clicking a command button. For lots whose labels have already been inserted, selecting the lot from the combo pulls the values from tbl_ test and repopulates the text boxes with label01, label02, ... while disabling the command button.

 

[mdlueck]

rs1.Fields(0) refers to the label field which contains unique labels label01, label02, ...

Neat sleight of hands, zooropa66!

 

[zooropa66]

Thanks Michael. I still class myself as an Access Beginner but thanks to a big project I'm working on and help from people on the forums, it's starting to fall into place