Solved: Combo Box Question

[mud2]

I want to set up a combo box (Combo11) with a variable Row/whatever source. I have tried (all last three days) using an SQL that looks just like the one that is obtained when the Row source is obtained by the "WIZARD". No luck, So then I try (in underlying VBA) Combox11.RowSource= (My variable), get a NULL, repeat the rowsource and then it works...but only sometimes!
Help??

[Killian]

Can you post the VBA code that's not working?
It sounds like the rowsource property is being set before the variable is initialized...

[mud2]

When in Design view, the properties of a combo box include a 'Row Source". If you put a valid SQL statement in this position the combo box works fine, BUT if you put in the name of a variable (An SQL generated someplace else) which is the same valid sql, the combo box either doesn't work, or ACCESS returns with a message saying Such and Such is not found! So how do I put a variable in the combo box's row source?

[Norie]

Where/When/How are you generating this variable?

[MOS MASTER]

Hi,

Seams like you're using a field value as a variable in you're SQL-Statement and those have very specific rules on how you should pase them in a SQL-string (In means of Quotes, Ampersands and other characters)

Like Killian said can you post you're code?

By the way this belongs to:
http://www.vbaexpress.com/forum/showthread.php?t=3100

Can a Forum admin concenate this question to the original question?

Enjoy!

[mud2]

To: Killian and Norie:
I have several tables, each one contains data and comments, or key words. I want to search these tables for the record(s) containg my choice of key word. As I add records, I might also add new key words. I (want to) choose the key word from a Combo Box.
Note...Given a combo box,I CAN do this!
I want to assemble an SQL statement with the table and field names needed to set up a combo box.
My form (Form1) has an option box corresponding to each table. The code under each option box contains the table and field names needed for the SQL statement.

I use a function to assemble and return the SQL statement. The function is called from the chosen option box, and is passed table and field names. It returns an SQL called MySql.
MySql is saved as a Public variable.
In Design form, the Combo box|Properties|Row Source, I type "MySQL", without the quotes.
The Combo box's Control source is left blank.
If the Row Source Type is Table/Query I get an error message:
'~cq_cForm1~sq_cCombo6' specified on this form does not exist
If the Row Source type is blank, or SQL, No error message, but an empty Combo box.
My form name is Form1
The Function is Function MakeMySql(Table,Field)
I use many MsgBoxes to help...
Any/All help will be appreciated!
........................................................................... ....
[VBA]Option Compare Database
Public MySql As String
Private Function MakeMySql(ByVal Table As String, ByVal Field As String) As String
Dim TableField As String
MsgBox ("Table = " & Table & ", Field = " & Field)
Table = "[" & Table & "]"
Field = "[" & Field & "]"
TableField = Table & "." & Field
MsgBox ("Now Table = " & Table & ", and TableField = " & TableField)
MySql = "SELECT " & TableField & " FROM " & Table & ";"
'MySql = Chr(34) & MySql & Chr(34) 'Adding the Chr(34)s didn't help!
MsgBox ("Final sql = " & MySql)
MakeMySql = MySql
End Function
Private Sub Option2_GotFocus()
MsgBox ("Option 2, Got Focus")
Dim Table As String
Dim Field As String
Table = "Table1"
Field = "Color"
MySql = MakeMySql(Table, Field)
MsgBox ("In Option2, MySql = " & MySql)
End Sub[/VBA]

The same assembled SQL statement placed as Row Source in a diferent Combo Box Does Work.

Sorry to be so wordy, but what can be expected from an old retired professor?
mud2

[Zack Barresse]

mud2,

Please do not start 3 threads on the same subject. I have merged all related threads. Use the POST REPLY button on the left hand side of the screen (link here: http://www.vbaexpress.com/forum/newr...uote=1&p=25492).

Also, [uvba]info[/uvba] I have edited your thread to contain them, hope you don't mind.

[mud2]

After some 42 views and 6 "replys", I'm ready to give up.
Simply put, I want to put the String Mysql, where
MySql = Select Table.Color From Table;
Into the Row Source Property of a Combo Box.
Simple question??
No, I cannot use the actual string, as it is a variable.

[OBP]

Hello, mud2, I hope you haven't given up, as MOS Master says it is probably a syntax or decleration problem. I have the code that you need but it uses Query definitions to pass the finished SQL string to a query that already exists.

[Killian]

Hi mud2,

I don't belive you can use a variable for the rowsource proerty at design-time, what you need to do is assign it at run time (which, from your code, it looks like you need to do with the Option2 gotfocus event, when you build the SQL string).
To illustrate this, if you create a form and a simple table and add this code the the form's load event[VBA]Private Sub Form_Load()

Dim MySql As String

MySql = "SELECT " & "*" & " FROM " & "Table1" & ";"

Forms!Form1!.Combo0.RowSourceType = "Table/Query"
Forms!Form1!.Combo0.RowSource = MySql

End Sub[/VBA]You'll notice that while the form is still running, it's rowsource property is now set to "SELECT * FROM Table1;"

However, having said that, the error message you report ('~cq_cForm1~sq_cCombo6') appears to indicate that the combobox you're referring to doesn't exist. Is it named correctly?

[mud2]

The programming suggested, using Form_Load, only woked with ONE MySql, but after seeveral days of rest, I found that Defining the MySql in the on Enter event for the combobox worked...IF MySql was a Global variable. Code for two choices follows:

[VBA]Option Compare Database
Dim Mysql As String 'Having been burned with "Global" values, Ikeep
them to a minimum

Private Sub Combo1_Click()
Text13.Value = Combo1 'Signals to developer that he/she has
'something to go on!
End Sub

Private Sub Combo1_Enter()
Combo1.RowSource = Mysql
End Sub

Private Sub Form_Load()
Forms!Form1.Combo1.RowSourceType = "Table/Query"
End Sub

Private Sub Option6_MouseDown(Button As Integer, Shift As Integer,
XAs Single, Y As Single)
Dim MyComments As String
Dim MyTable As String

Forms!Form1.Combo1 = "" 'Clears out contents from last choice.
MyTable = " Table1 "
MyComments = " Comments "

Mysql = "Select " & MyComments & "FROM " & MyTable & ";"

End Sub

Private Sub Option8_GotFocus()
Dim MyComments As String
Dim MyTable As String

Forms!Form1.Combo1 = ""
MyTable = " Table2 "
MyComments = " Comments "

Mysql = "Select " & MyComments & "FROM " & MyTable & ";"

End Sub [/VBA]

Now A simpler priblem: How to "Click' in the Combo after clicking on an option?

[Norie]

What do you mean 'click' on a combobox?

Perhaps you should start a new thread with your new query.