Combo histerisis

[Andrew Wrigley]

Hi

My combos seem to have an acquired memory syndrome.

On a query facility, the after update of combo1 fills the rowsource of
combo2 using a sql string, like so:

Public Sub Combo1_AfterUpdate()

Dim nameField as String
Dim sql as String

nameField = Me.Controls("Combo1")
sql = "SELECT " & nameField & " FROM ContactsTbl;"

Me.Controls("Combo2").RowSourceType = "Table/Query"
Me.Controls("Combo2").RowSource = sql

End Sub

The problem is that when this process is done repeatedly, the second
combo wont accept updates that are not a compatible data type as the
previous run.

ie, if on the first run, nameField held a Number data type and on the
second run nameField holds a text data type, then I get an access
error.

I can't find any way to flush all histerisis of the previous data that
was ploncked into Combo2, nor can I find a reason for this behaviour.
As this process is used repeatedly, it is a critical flaw in the app
(a simplified Query Designer).

Regards

Andrew Wrigley

#IF

Giving Users access to the Access Query Designer is like giving a cage
full of monkeys a box full of machine guns.

#THEN

Teaching Users to use the Access Query Designer is like showing the
said monkeys what happens when they pull the triggers of the said
machine guns.

#END

[Rich P]

Based on the sql statement you are using to populate the rowsource of
combo2 I would assume you are referencing field names in a table called
Contacts. Here is syntax that may help you.

Combo2.RowSourceType = "Table/Query"
Combo2.RowSource = "Select [" & combo1 & "] From Contacts"
Me. Requery

What I did was to add brackets to your argument (combo1). Note: if
combo2 will always be of type Table/Query, you can set that in the
combo2 property sheet in the form design view. Then you can eliminate
that line of code in your routine.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This is strange. What version of Access do you have?

I once had a ListBox that I constantly changed the RowSource & the
number of columns displayed. I found that I had to change the Column
Count before changing the RowSource for it to work correctly.

Perhaps you don't need to do as many things as your are doing. E.g.:

Public Sub Combo1_AfterUpdate()

Dim sql as String

If Not IsNull(Me!Combo1) Then
sql = "SELECT " & Me!Combo1 & " FROM ContactsTbl"
Me!Combo2.RowSource = sql
End If

End Sub

If you know the data type you may wish to do this:

sql = "SELECT CLng(" & Me!Combo1 & ") FROM ContactsTbl"

If the result set will return a Long in the column. You can use
whatever conversion function that is required.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEZdaoechKqOuFEgEQJT4wCeNZsFW0FKFABEy6aJWBldjg unMMQAn3C1
DKYio2JJA+c5t+zRhphI+zPt
=I8IC
-----END PGP SIGNATURE-----
Andrew Wrigley wrote:

Hi

On a query facility, the after update of combo1 fills the rowsource of
combo2 using a sql string, like so:

Public Sub Combo1_AfterUpdate()

Dim nameField as String
Dim sql as String

nameField = Me.Controls("Combo1")
sql = "SELECT " & nameField & " FROM ContactsTbl;"

Me.Controls("Combo2").RowSourceType = "Table/Query"
Me.Controls("Combo2").RowSource = sql

End Sub

The problem is that when this process is done repeatedly, the second
combo wont accept updates that are not a compatible data type as the
previous run.

ie, if on the first run, nameField held a Number data type and on the
second run nameField holds a text data type, then I get an access
error.

I can't find any way to flush all histerisis of the previous data that
was ploncked into Combo2, nor can I find a reason for this behaviour.
As this process is used repeatedly, it is a critical flaw in the app
(a simplified Query Designer).