Combobox with varying Rowsourcetypes

[georgesOne]

Dear All:
I have a combobox which rowsource pulls up lists with various rowsourcetypes; strings, numbers, dates depending on the selection in another combobox.
Once a date rowsourcetype has been selected and then the user changes to a text rowsourcetype the combobox does not work anymore... obviously it still only can accept dates.
What do I have to do in VBA to clean the rowsource so that a change of datatypes is possible?
Thanks, georgesOne

 

[dhookom]

What do you mean by "the combobox does not work anymore"? Can you provide some context regarding your setup? How exactly are you using various data types?

Combo box values are all typically string variants.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Complicated to explain:
I have a search form.
First combobox dropdown shows all tables to search. Once a table is selected a
second combobox pulls up all fields (and fieldtypes) in the dropdown. Once a field is selected a
fourth combobox pulls up all the values in this field in the dropdown.
(The third combobox shows =, <>, <=, >= etc.), so that I can generate a SQL string: Select second combobox (fieldname) from first combobox (tablename) where second combobox (fieldname) is (third operator combobox e.g. =) fourth combobox (expression).
The fieldtypes can be numbers, texts, memos and dates.

Now I select a date in the second combobox and the fourth shows all dates in the dropdown.
If I change my mind and want to change the second combobox to select text fields, the fourth combobox does not accept this (the rowource is not adjusted), because it obviously only takes dates (same is true when I select numbers and change to text).

Sorry no better explanation.
My way around is now that I exclude all dates and numbers - most people will look for text. But it could be that they want to see all entries > a certain date. If they change their mind the combobox will not function properly.

Any help is appreciated, georgesOne

 

[georgesOne]

Hi Duane,

I think I got it... but need to check in detail... your tip that everything is typically a string helped... I added the CStr function to the SQL.
Let's see... I will post back if it does not work.

Thanks, georges

 

[georgesOne]

Yes, seems to work...

If vVar = 7 Then (vVar = fieldtype number)
Controls("cboDescription" &i).Rowsource = "SELECT DISTINCT [" & Controls("cboFieldName" & i) & "] FROM [" & cboTable & "];"
Else (vVar = fieldtypes text, date (and memo with some restrictions later)
Controls("cboDescription" & i).Rowsource = "SELECT DISTINCT CStr([" & Controls("cboFieldName" & i) & "]) FROM [" & cboTable & "];"
End If

Thanks again, georges