Need to assign a query to Combo boxes using VBA. (1 Viewer)

[Ryobi]

Hello,

I am trying to assign query to combo boxes via VBA. I have about 10 combo boxes which data is a table name supply. All the Combo boxes are name Cbobox1. CboBox2... etc. I want to create procedure that let to assign a query to each combo box. The procedure be call would be something like this Cbox "Suppy", '10", "Select * from Supplies". I have already written the procedure which is shown below. The procedure works except that I can only assign a single value to the Combo boxes. I need something like ctl.value = Strqry which does not work. I have also tried using [Form]![MyForm].[CboBoxName] = Stqry, but I can not get to the pass the name of the combo box even if I use a Gobal variable. I am using Msaccess 2007. Any I on how to resolve this issue ?

Sub CboBox(ByVal CboName As String, ByVal CboNo As Integer, StrQry as string)
Dim ctl As Access.Control
Dim BoxNo As Integer
BoxNo = 1
For Each ctl In Forms!MyForm.Controls
CtlName = CboName & LTrim(Str(BoxNo))
If ctl.Name = CtlName Then
ctl.Value = "Sample" ' Here is where I need to assign the query
BoxNo = BoxNo + 1 ' Used to count the number of Combo Box
If BoxNo > CboNo Then
Exit Sub
End If
End If
Next ctl

 

[Mr. B]

Try replacing the line:
ctl.Value = "Sample" ' Here is where I need to assign the query

with:
ctl.RowSource = StrQry

 

[Ryobi]

I was going to try your suggestion, however, now not realize that the
procedure is not working because for some reason the names of the combo boxes do not equal the name that I submit when I call the procedure. The problem is in the line.

If ctl.Name = CtlName Then

I place at stop after the line and and it stop, so I know for certain that the two values
are not equal. The question is why ?

 

[Mr. B]

Because you are in a loop that is checking the name of every control in your form, many of the controls will not match the name of the control you are expecting.

As the loop goes through each control, it is checking the name of the current control against a known value. Did you run the code through all of your controls and the control name you were expecting was not found?

 

[Ryobi]

You are correct I am looping through the controls, but I can not determine whether the known value is equal to any of the control names. I probably need to send the control name to list or a table. I wonder if the control names are in the hidden table ?

 

[Mr. B]

If you want to be able to see the name of each control as they are processing during testing, try declaring a variable that you can assign the name of each control to as you loop through all of the controls.

Something like:

Dim ctl As Access.Control
Dim BoxNo As Integer
dim strControlName as string
BoxNo = 1
For Each ctl In Forms!MyForm.Controls
     CtlName = CboName & LTrim(Str(BoxNo))
     'place a break point at the next "If" line and you can then check the value of 
     'the "strControlName" variable
     strControlName  = ctl.Name
     If ctl.Name = CtlName Then
          ctl.[URL="http://www.access-programmers.co.uk/forums/showthread.php?p=1137250#"][COLOR=darkgreen]Value[/COLOR][/URL] = "Sample"        ' Here is where I need to assign the query
          BoxNo = BoxNo + 1    ' Used to count the number of Combo Box
          If BoxNo > CboNo Then
               Exit Sub
          End If
     End If
Next ctl

I may be somewhat confused about your use of some of your variables.

You are passing in a value in the "CboName" parameter but then you are concatenating a value to the end of that value with the:

CtlName = CboName & LTrim(Str(BoxNo))

line. I would have thought that you would simple pass the entire name of the desired combo box in the parameter value.

Hope this helps.

 

[Ryobi]

That is a good Idea... This morning when I woke up it occurred to me that the problem might be variable that I am passing to the procedure. I was sending "Supply" instead of "CboSupply". I will test my theory and let you know if that is the problem so that I can proceed to the problem of assign a query to the combo boxes. By doing the procedure it will save a lot of code lines and make the program easier to debug.