Combo box text/number issue

[Carthesis]

I have a pair of unbound combo boxes to filter a list of information. The second combo box is dependant on the first, using the AfterUpdate method.

Selecting Option 1 in the first combo box means that the selection list for the second combo box is numeric. Selecting Option 2 in the first means the list for the second is text. This works fine as long as you select Option 2 FIRST, ie.:

Select cbo 1 - Option 2
Select cbo 2 - [any text option]
Change cbo 1 - Option 1
Select cbo 2 - [any numeric option]

The above works fine, however:

Select cbo 1 - Option 1
Select cbo 2 - [any numeric option]
Change cbo 1 - Option 2
Select cbo 2 - [any text option]

This doesn't work, giving me an Access error of:

The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.

Now, there is no FieldSize setting, and I've not told it to be numeric. Changing the values queried from numeric to text (by changing the Field Data Type in the Table the values are pulled from) causes this to work fine, but I need the numeric field to STAY numeric.

Is this one of those bugs caused by using MS Access 2003 SP3 that the 'office2003-kb945674-glb.exe' will fix? If not, can anyone see a way to fix this?

Cheers for the help, and Merry Christmas to everyone still on here!

 

[Carthesis]

Update - having applied the patch, it still doesn't work.

I've checked the code behind the form, and it's getting and selecting all the right values. The problem seems to be that Access is for some reason taking it upon itself to limit the formatting of the combo box to the first selection, hence if you select a numeric value first, it won't then let you change it to a text value, but the other way around is ok.

Any thoughts?

 

[MajP]

you can force a number into text but not a text into a number that is why it works in the first case. Need to see
1)Sql for the 2nd combo
2)bound column number 2nd combo
3)format property of 2nd combo

 

[KirbyWallace]

I was able to reproduce, sort of, your behaviour.

I made two cbo boxes. combo0 and combo1

I made cbo1 with a value list of "1;2;3;4;5;6" and set it's format to "Short Date". This causes the error you are getting the very instant I select something from that list.

The only way I could make it work was by using the "After Update" event of the first combo box to:

combo1.Format = ""

Thus changing the expected value type of combo1 to be compatible with the selection in combo0.


Kirby

 

[Carthesis]

OK, here goes. I'll try to make this understandable.

Two combo boxes, one that selects a discipline, say either 'Structures' or 'Highways', so it's a straight text field search, as below:

Private Sub DisciplineCombo_AfterUpdate()

Dim strSQL As String

Me![SeriesStructureCombo].Value = ""

If Me![DisciplineCombo].Value = "Structures" Then
    
    Me![SeriesStructureCombo].RowSourceType = "Table/Query"
    Me![SeriesStructureCombo].RowSource = "qryStructureNames"

Else
    
    Me![SeriesStructureCombo].RowSourceType = "Table/Query"
    Me![SeriesStructureCombo].RowSource = "qrySHWClauses"
    
End If

strSQL = "SELECT tblDwg.CombinedDwgNo, tblDwg.DwgRev " & _
            "FROM tblDwg " & _
            "WHERE (tblDwg.DwgDiscipline='" & Me!DisciplineCombo & "') " & _
            "ORDER BY tblDwg.DrawingNumber;"
            
Me![DwgList].RowSource = strSQL

End Sub

This updates a list box that is initially set to select all entries in tblDwg to only select those from the specified discipline. Selecting 'Structures' queries two fields from one table (a structure ID code and the full structure name), selecting 'Highways' queries fields from another table (a numeric series ID and the Series name, both of which are predefined).

The problem occurs in that if you select 'Structures', then the next part of the filter is a text string, but if you select 'Highways' it's numeric. The code for this AfterUpdate event is below:

Private Sub SeriesStructureCombo_AfterUpdate()

Dim strSQL As String

If Me![DisciplineCombo].Value = "Structures" Then
    
    strSQL = "SELECT tblDwg.CombinedDwgNo, tblDwg.DwgRev " & _
            "FROM tblDwg " & _
            "WHERE ((tblDwg.DwgDiscipline='" & Me!DisciplineCombo & "') " & _
            "AND (tblDwg.StructureID ='" & Me!SeriesStructureCombo & "')) " & _
            "ORDER BY tblDwg.DrawingNumber;"
            
Else
            
'    Temp = Me!SeriesStructureCombo.Value
'    MsgBox Temp
    
    strSQL = "SELECT tblDwg.CombinedDwgNo, tblDwg.DwgRev " & _
            "FROM tblDwg " & _
            "WHERE ((tblDwg.DwgDiscipline='" & Me!DisciplineCombo & "') " & _
            "AND (tblDwg.CivilsSeries=" & Me!SeriesStructureCombo & ")) " & _
            "ORDER BY tblDwg.DrawingNumber;"
            
End If
           
Me![DwgList].RowSource = strSQL

End Sub

The bound column for the second combo box is '1' in both cases, and there is no format specified. The code works perfectly well as long as you go 'Structures' - 'Structure ID' and then change to 'Highways' - 'Series number'. The problem occurs when you go the other way around.

As best as I can tell, the combo box is picking up default formatting from the first entry - ie. either numeric or text, and then applying that until the form is closed and reopened.

I'm just gonna try Kirby's method of forcing the format property. I'd not thought of it, because having checked the 'format' property through Access in all three conditions, it was blank every time. It might be something hidden that Access isn't displaying though, so I'll see if I can force it.

 

[MajP]

Also double check there is nothing listed in the control source of the combobox?

 

[Carthesis]

Ok. That didn't work.

Also double-, triple- and quadruple-checked nothing is listed as a control source.

Still can't work out where it's picking this format up from. Just in case there's something I've not mentioned, or overlooked, or anything, I've uploaded the file to the following link (also listed as an attachment)

http://www.yourfilelink.com/get.php?fid=478967

If you fancy a go at working out what's gone wrong, feel free to download and see what's what!

 

[RoyVidar]

Would it help if you converted the number to string in your query?

SELECT Str(TheID), ...
SELECT Format(TheID,"0"), ...
SELECT CStr(TheID), ...

Roy-Vidar

 

[Carthesis]

Roy!

You legend! I love you!

(In case you've not figured it out, that worked.)

Changing qrySHWClauses from:

SELECT * FROM tblSHWClauses;

to:

SELECT Str(tblSHWClauses.SeriesNo), tblSHWClauses.SeriesName FROM tblSHWClauses;

worked like a charm!

Still doesn't explain why Access is doing what it's doing with regard to forcing the formatting, but at this point I don't care!

Many thanks, and Merry Christmas!

 

[RoyVidar]

You are welcome!

I think that some metadata is added to some controls when applying row source, recordsource, control source..., which might not be reset when changing this programmatically.

Roy-Vidar