Type Mismatch in Combo Box

[The_Doc_Man]

This is crazy. I need someone to reset my "thinking" button because right now this doesn't make sense. OK, here's my setup. I'm building a work-order management system for a small department. Projects have work orders. The projects have a field called MAC level that defines their importance. We tack the MAC level on the work order so we can go back and prioritize the work orders as a whole, doing MAC 1 first, then 2, then 3.

Tables involved:

tPRJ: PRJID (PK, Autonumber), PRJNAME (Text), etc etc, PRJMAC (Long), etc.

tMACDef: MACLev (PK, Long but not Autonumber because the number has external meaning), MACImp (text), MACLoss (text). This table is present for translation purposes on reports and to feed a combo box. (Otherwise, I wouldn't bother.)

Pre-defined Relationships: [tPRJ]![PRJMAC] (many-side) to [tMACDef]![MACLev] (one-side). Relational integrity enabled (but not cascade deletes).

Form fProjectMaint: The control of interest is called [PRJMAC] and is bound to underlying field [tPRJ]![PRJMAC]. It is populated with a Combo Box bound to do a lookup from tMACDef with two columns, MACLev and MACImp. I've told it to hide the key field [MACLvl] and store that field in [PRJMAC]; it displays [MACImp] in the box - correctly.

If I call up a record or navigate without trying to change the project's MAC level, nothing happens. If I edit the PRJMAC control, say change level from 2 to 3, it appears to do so until an attempt to store the change. Then, with only one field having been changed, the form tells me TYPE MISMATCH. I've got auditing code built in to scan the form for changed fields, but it doesn't fire because the field change apparently doesn't occur. (Guess the type mismatch stops it.)

I can't figure out why the error occurs, given that two fields of type LONG are what is bound to each other by relationship and by the combo box. This shouldn't be balking me like this. What should I be looking for?

 

[Banana]

Two things I would try:

1) Rename the control to something else. This could be a case of Access getting confused between the field and/or the combobox control with the same name.

2) Check that the Bound Column property corresponds to the MacLev's ordinal position. It could be a case of the combobox attempting to store the text MACImp into PRJMAC, maybe?

3) Failing this, create a new blank form, bind it to the same table, and add a new combobox and set it up the same way you wanted it to setup. Try and edit the changes. If this works, copy'n'paste into the problematic form and see if the problem goes away. If it persists even with the newly pasted combobox, then something about the form is broken and you probably want to create a new form & copy the controls & code into the new form.

 

[The_Doc_Man]

Found it. The auditing code had an exceptional case when auditing a change on that form, but because it was hidden by a button click, I didn't see that code fire right away. Wasn't anything but me trying (and succeeding too well) to make my code invisible to my end users.

It was REALLY bizarre in that the problem was an expression that somehow compiled correctly even though it had some quotes and some ampersand substitution all screwed up. But it was a balanced screw up.

Thanks for your suggestions.