How to have bound form with bound combobox that doesn't update record?

[ittechguy]

Not sure whether this is a query problem or a problem with vba coding of form. I suspect its a VBA problem.

I've got a form I've been working on which tracks equipment and allows me to add new equipment and also make changes to existing equipment. The form is bound to a query which contains the main table tblEquipment where data is actually stored as well as several lookup tables.

On the form, I have a couple set of cascading combo boxes. One is for tblEquipment.Ca binetFK. I'm trying to select a Cabinet Name from the Cabinet combo box and have it insert that CabinetPK into tblEquipment.Ca binetFK.

The problem I'm having is the two combo boxes above it, cboBuildingName and cboRoomName, being both bound to the query when I select a building, then select a room, then select a cabinet and hit Save it will try to add a record in tblRooms and then tell me that it can't because it would create duplicate entries (as it should, I don't want duplicates in tblRooms).

So basically, I think I need to be able to use cboBuildingName and cboRoomName to be able to view information, and also be able to select buildings and rooms but it can't update the record. I'm not sure how this is done.

What I tried to do is, in the BeforeUpdate event of cboCabinetName, is I make cboBuildingName and cboRoomName unbound using cboBuildingName .ControlSource = "". That works, but as soon as I select a value from cboCabinetName, cboBuildingName and cboRoomName don't show anything.
[IMGNOTHUMB]https://bytes.com/attachments/attachment/8542d1448129887/newequipmentfor m23.jpg[/IMGNOTHUMB]

[NeoPa]

If you're using one (or more) ComboBoxes to select records to view on the form then they shouldn't be bound controls.

If you need to show the same data from the records as is reflected in your ComboBox(es) then they should be shown in separate controls (Typically TextBoxes).

Trying to use the same controls for selecting as well as displaying records is a path to complication and problems.

[ittechguy]

I figured it out. I needed to us the .controlsource property to unbind the control before saving the record, and then rebind them after saving the record.

I agree, I shouldn't be using bound controls. The problem was though, I wanted the combo box to display the current value.

[NeoPa]

As I said in my earlier post, you're better off showing the value in a separate control. Mingling the two together will only lead to confusion. If not for you then certainly for those trying to use your project.

Ultimately it's your choice of course, but I'd strongly advise against it.

[ittechguy]

Well im sure youve been doing this a lot longer than I have so I'm ears.

By separate control, do you mean I have a text box (likely locked) to display current values, and a combo box to change values? Not sure if I want to waste space on the form for that.

But if it'd cause too much headaches then I guess I'd have to.

How would it lead to confusion to have it as I've set it up?

[ittechguy]

Not sure, but we might have a communication problem too. I'm not using the same control to select records and view records per se.

I have a unbound search form in the footer (which I realize wasn't in my first photo, my bad). This form filters the main form (bound cbo and txt boxes) and also filters the subform in the middle (which is a unbound subform in datasheet view.

The form in the header (what this thread is about) has bound controls and is used to edit records if the form is not in data entry mode. If form is in data entry mode, then it's used to add new records.

The problem I was having is that I was trying to use cbobuildingname and cboRoomName to display the current value for that record, but also be used to filter cboCabinetName and update tblEquipment with that CabinetPK while not updating the building id or room id. If that makes sense.

[NeoPa]

ITTechGuy:
How would it lead to confusion to have it as I've set it up?

It leads to threads such as this one ;-)

Think about it. You drop the ComboBox down and select another item from the list.

Are you doing that to change the value in the current record, or are you doing that to show a different set of records? The system can't possibly be set up to know as there's nothing there to differentiate. Even the user can't know as they're expected to use the same approach to do both actions.

ITTechGuy:
Not sure, but we might have a communication problem too. I'm not using the same control to select records and view records per se.

It seems maybe we have. That's what I've understood from what I've read.

As it seems that isn't the case I reread, but I'm still confused.