Auto Fill Label based upon query

[lzook88]

I have a query that runs and shows active members of a group. I am attempting to create an unbound form that has labels and combo boxes to enter multiple records into a table when submitted. The issue is the labels need to be the full names of the active members that the query pulls

here is my vba code for this so far:

Private Sub Form_Load()
Dim i As Integer
Dim z As Integer

'fill labels with active explorers

For z = 1 To 25
    For i = 1 To 25
        Me.Controls("name" & i).Caption = DLookup("[Full Name]", "Explorers Query", "ID=" & z)
    Next i
Next z

End Sub

would anyone be able to help me with this?

[June7]

What is the issue? If the code doesn't work, what is happening - error message, wrong results, nothing?

The table name has a space, try enclosing with [] same as you did for the field.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Why do you have nested loop? Seems to me the i loop is not needed. There are 25 label controls then just the z loop and reference z in both concatenations. But how will referencing the z index for the ID return the appropriate name?

Open a recordset object and loop through the recordset

Dim rs As DAO.Recordset, x As Integer
Set rs = CurrentDb.OpenRecordset("SELECT [Full Name] FROM [Explorers Query] ORDER BY [Full Name];")
x = 1
While Not rs.EOF
Me.Controls("name" & x).Caption = rs.[Full Name]
rs.MoveNext
x = x + 1
Wend

There will never be more than 25 records in the query?

[lzook88]

With the above code used. Opening the form results with:

Run-Time Error '13':
Type Mismatch


taking out the second variable and using the same variable for both results with:

Run-Time Error '13':
Type Mismatch

adding the [] around the Explorers Query part also returns the Run-Time Mismatch error.


when you press debug on all three ways it highlights

Me.Controls("name" & i).Caption = DLookup("[Full Name]", "Explorers Query", "ID=" & i)

Me.Controls("name" & i).Caption = DLookup("[Full Name]", "[Explorers Query]", "ID=" & i)

[June7]

I edited my previous post probably while you were reading. Review again.

[lzook88]

And as of now no. We only accept 25 explorers on the roster at a time

[lzook88]

Okay I have updated my code to what you put:

Private Sub Form_Load()
Dim rs As DAO.Recordset, x As Integer

Set rs = CurrentDb.OpenRecordset("SELECT [FULL NAME] FROM [Explorers Query] ORDER BY [Full Name];")

x = 1
While Not rs.EOF
Me.contorls("name" & x).Caption = rs.[FULL NAME]
rs.MoveNext
x = x + 1
Wend

End Sub

when i load the form i am getting:

Compile Error:
Method or Data Member not found

.[Full Name]


I have attached the file in a ZIP folder

Attachment 22013

[June7]

First, need to spell Controls correctly.

Second, my goof, use ! instead of . : rs![FULL NAME]

[lzook88]

The misspelling on Controls was my fault .. I had fixed it right after I posted on my coding. The "!" worked like a charm..

Thanks for your assistance..