Combo box behaviour - value property using VBA

[alistair_m]

I have a combo box issue which has caused me many hours of pain. Hopefully this will explain the issue.

My application has many linked tables. When providing the user with a data input form, I have provided 3 combo boxes, based on 3 of the tables. All work fine in the initial design and can be used to populate a 4th table via an SQL Query.

I'm now improving the usability of the form and instead of the combo boxes reverting back to the initial values in each query on which they're based, I want to be able to write a statement to them prompting the user to enter data for the next update. I've accomplished by using combo1.value = "Select pupil". This works perfectly for two of the combo's but does not display in the third. I've traced all the way back through the properties and the queries and they are effectively identical. Through trial and error I've identified the following as the course, but just don't know how to get over it.

The two combo's that work have the following datatypes in the underlying table:

field 1: Autonumber (primary)
field 2: shorttext

(in both cases, field 1 is the bound column in the combo)

The combo that doesn't work is based on a table with datatypes as:
field 1: shorttext (primary)
filed 2: shorttext

I have got it to work, by changing the field 1 datatype to longint, as they are actually all numeric. But there is a reason why I would prefer them as text.

So my question is, how do I get around this with having a text based primary key - which appears to be the root cause!

I hope this is reasonably clear

Regards

Alistair

[CJ_London]

one way perhaps is to have a Union rowsource

SELECT Fld1,Fld2 FROM myTable
UNION SELECT "0","Enter Data" FROM myTable

then set the default for the combo to be "0"

edit: added quotes around the 0, not actually necessary in this case, but clearer

[alistair_m]

many thanks....Ill give it a shot in a little while and let you know.

[CJ_London]

for more visibility you can also use conditional formatting for when the value of the combo is "0"

Field Value Is.....equal to...…"0"

the set back/fore colours, font style as required

[alistair_m]

Ajax - thanks for your help with this problem. I did try your suggestion, but got myself so confused with my queries, I went to bed with a headache! However, refreshed this morning, I tried a different approach!

The problem doesn't actually appear to be anything to with the datatype of the primary field (it was just coincidence that this was different for the fields in the combo boxes that worked. The actual problem is even more obscure...but resolvable!!!

In the query that I used for the combo that didn't work, I built an expression for field 2. ie PupilFullName: [Surname] & ", " & [knownAs] & " (" & [regClass] & ")". This took data from the source table and concatenated to create the new field data.

Instead of creating the expression in the query, I have now used a calculated field in the table initially and used this in my query. IT WORKS!! And I can now set values in the combobox at form load and after update.

I haven't the faintest idea why this should be so. But it works, the integrity of the database is maintained and I'm very happy!!

Cheers!