Mismatch error when setting Me!CboBx1.RowSourceType = "Value List"

[Hector H]

This is my first time creating a combo box.

I have a button that opens a form that contains the combo box. I am trying to load all the table names into the combo box. I am getting a mismatch error when I get to this line

Me!ComboBox1.RowSourceType = "Value List"

Here is entire code for this form

Private Sub UserForm_Activate()
Dim tblNames As String
tblNames = " "
Dim tbl As AccessObject
For Each tbl In CurrentData.AllTables
If Right(tbl.name, 10) = "Comparison" Then
tblNames = tblNames & Chr(34) & tbl.name & Chr(34) & ";"
End If
Next tbl
Me!ComboBox1.RowSourceType = "Value List"
Me!ComboBox1.RowSource = tblNames
Me!ComboBox1.Value = Me!ComboBox1.ItemData(0)
Me!ComboBox1.LimitList = True
End Sub

 

[SOS]

Change this:

tblNames = tblNames & Chr(34) & tbl.name & Chr(34) & ";"

to this

tblNames = tblNames & tbl.name & ";"

You don't want quotes around everything.

 

[Hector H]

Did not work. I don't know if this matters but when I llok at property menu for the combo box it oes not have a rowSourceType listed. It does have Row source though.

I am using 2003 not 2007 if that matters.

Thanks for fast repy.

 

[SOS]

Did not work. I don't know if this matters but when I llok at property menu for the combo box it oes not have a rowSourceType listed. It does have Row source though.

I think you're overlooking it then (see attachment).

As far as "doesn't work" I would put a debug.print just after the final time when the string is built. It should not have quotes within the data. The quotes would normally be on the end but you don't need them here (if I remember correctly). You do need to have them separated by semi-colons.

 

[Hector H]

After seeing your attachment I realized that I am try to creating the form in the VBA editor and you created it from the access forms. I think I know what to do now for the most part but I now I have new questions.

Assumption- once user clicks on desired table name that string gets stored in form.value.

What is the best way to get that value for use?

 

[SOS]

If the user clicks on something in the combo box then that is the value of the combo box, if you are using a value list and the bound column is set to 1.

 

[gemma-the-husky]

without being silly, what possible use could a combo box of the tables be in a normal database?

 

[SOS]

without being silly, what possible use could a combo box of the tables be in a normal database?

Good question -

I can think of at least one where you select the table you want to query and build a query using that selection.

 

[Hector H]

If any body is wondering the reson that my code did not work is that
Me!ComboBox1.RowSourceType = "Value List"
Me!ComboBox1.LimitList = True

does not work in 2003 or least not for me. I removed that and it worked perfectly. Thanks S.O.S for steering me in the right direction.