Combo box has a data type? How to reset the data type of a combo box? 2

[MickelR]

Question applies to: MS Access 2003

This looks totally weird to me, so I hope that the forum could shed some light on this mystery. I have three comboboxes:

Combo1: value list (manually filled with selected tablenames)
Combo2: field list (automatically filled with all fields of existing selected table in combo1)
Combo3: table/query (automatically filled with all possible values used in the field of combo2)

All comboboxes have "limit to list" property set to "yes" (I did try limit to list "No", though with the same result).

I select the existing table in combo1 and then select a field from that that has FieldType "date" in combo2. After that I select an existing value from the list in combo3. So far so good...

The issue:
I go back to combo2 and select a different field with datatype "Text". With an combo2_afterupdate I reset the value of combo3 to NULL (me.combo3.value = NULL) and I change the rowsource to the new possible values for the new selected field. To be absolutely sure I requery combo3 (combo3.requery).

Selecting a value (which obviously is a string) results in the "limit to list" trigger telling me: "The value you entered isn't valid for this field". Obviously I selected a value that is shown in the (new) list of possible values.

Assumptions:
It looks like as if the combobox is set to a certain data type the first time and that using a different data type leads to the combobox getting confused. This assumption is supported by changing combobox2 to a numeric field in the table and then selecting a low number. Combobox3 reformats the number into a date (which I selected the first time).

Questions:
1) What's going on here with the comboboxes?
2) Is it possible to reset a combobox entirely? The only way to reset the combobox is to close the form and go back into it. I would like to reuse combobox3 for a different datatype without having to close the form first.

I really look forward to your insight.

 

[dhookom]

I just tried what I thought was the same using this code:

Option Compare Database
Option Explicit

Private Sub Combo1_AfterUpdate()
    Me.Combo2.RowSource = Me.Combo1
End Sub

Private Sub Combo2_AfterUpdate()
    Me.Combo3.RowSource = "SELECT DISTINCT [" & Me.Combo2 & "] FROM [" & Me.Combo1 & "]"
End Sub

It worked exactly as expected with no errors unless I selected a memo or other weird field type.

Duane
Hook'D on Access
MS Access MVP

 

[MickelR]

Thank you Duane for looking into this.

I have narrowed this issue down to a point that I could actually reproduce it. First the code that I use is this

Me.cboTable.RowSource="tblIssuer;tblCustomer"
Me.cboField1.RowSource = Me.cboTable
Me.cboSelection1.RowSource = "SELECT DISTINCT " & Me.cboTable & "." & Me.cboField1 & " FROM " & Me.cboTable & ";"

The code looks pretty much the same as what you did.

The issue pops up when in cboField1 I first select a table field that has FieldType "date" or "number". After selecting an existing value from the field and then I go back to cboField1 to change the combobox to a field with FieldType "Text". The system gives me the error "Value not in list" after I selected an existing value in the new list of cboSelection1. The code I use to reset the boxes is:

Private Sub cboField1_AfterUpdate()
Me.cboSelection1 = Null
Me.cboSelection1.RowSource = "SELECT DISTINCT " & Me.cboTable & "." & Me.cboField1 & " FROM " & Me.cboTable & ";"
Me.cboSelection1.Requery
End Sub

I really hope that you see something different in this, which I could fix to make this work better.

 

[MickelR]

I have created an example of my problem that shows my issue. to reproduce the problem:

- Open database
- Open Form1
- In combo1 select "tblTest"
- In combo2 select "Field3" (Date type)
- In combo3 select a date (can be any date)
- Select "Field1" in combo2
- Select any value in combo3

This give me the "Value is not in list" error. Once again: this is in MS Office 2003 (11.8203.8172 -- SP3)

I hope a few people are willling to test this database to see what they get.

Thank you again for looking into this.

 

[MajP]

Here is a work around. Set the bound column to 0. This returns the index of the return record, and will not through the error.

 

[dhookom]

I got the same error message as you. This code seemed to work for me:

Private Sub Combo0_AfterUpdate()

    Me.Combo2.RowSource = Me.Combo0
    Me.Combo4 = Null

End Sub

Private Sub Combo2_AfterUpdate()

    Me.Combo4.RowSource = "SELECT DISTINCT [" & Me.Combo2 & "] FROM [" & Me.Combo0 & "];"
    Me.Combo4 = Null

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Shoul be "throw" the error, not through.

 

[MickelR]

Thanks for your help so far! I'll test the workarounds tomorrow. A little help for people that find this entry helpful: I did code Duane's solution (me.combo4 = Null). However, I first set the combobox to Null and then reset the rowsource. Doing it the other way around might also help.

I'll wrap this up tomorrow.

 

[MickelR]

Thank you for trying to help me on this. I have tried to implement both suggestions from Duane and MajP. Duane's solution still gives me the "not in list" error on my end.

MajP's solution did solve the error message in combo3. Because the combobox is bound to 0, it doesn't store/hold/keep the value in combo3. I need the value as input for an SQL statement.

Is anyone able to solve this last piece of the puzzle?

---- More background information on what I am trying to accomplish ----

Eventuatlly my idea is to fill a list box with all fields of a table, based on selections.

In a first combobox I select the table I would want to use. In a second combobox I select the field that I would want to use. In a third combobox I am trying to narrow down the selection to the selected value of the selected field.

With the property CurrentDb.TableDefs(Table_name_selected_in_first_combobox).Fields(Field_name_selected_in_second_combobox)).Type I determine the type of the field. I use a select case on the outcome of TableDefs.Field.Type to determine the setup of the correct SQL string. After that I set the rowsource of the listbox to the SQL string.

A totally different solution of what I am trying to accomplish would work as well.

 

[MajP]

you can now use the column property of the combobox to pull the value out of any column that is unbound or bound.
Simply
yourComboName.column(0)

where 0 is the first column. Columns are 0 based indexed in vba, in design view they are 1 based.

 

[MickelR]

MajP: I totally forgot to let you know that your solution worked wonderfully with the latest additions that you gave.

Well done!