Combo Box Restrictions

[tacieslik]

I've got a combo box which I select a supplier from. I then select loads of parts for that supplier in a subform.

What I need to happen is, when the first part is selected from the subform, the combo box for the supplier must not be changable unless a new record is created.
Therefore, only one supplier can be chosen per record.
This could get a few brains ticking.

Cheers.

 

[ntp]

YOu will need to use two events to handle this. There are twoi events you are trying to manipluate, the event where a new record is being added - information about parts for a supplier and the event where parts are bieng added for a supplier.

The first event occurs on your main form. You have to create an OnCurrent event.

Private Sub Form_OnCurrent()
Form![MainForm]![cboSupplier].locked = False
End Sub

The second event occurs on your subform,

Private Sub Form_AfterUpdate()
Forms![MainForm]![cboSuppliers].Locked = True
End Sub

What happens here is when the user selects a supplier and then adds a new part, the supplier combobox becomes locked so no user input is taken. Once all the parts are entered and on the main form the user attemps to add a new record, the supplier combobox becomes unlocked enabling user input.
I used mainform to be the form with the combobox, subform to be the part entry form and cboSupplier to be the combobox on the main form. You will need to change accordingly.

ntp

 

[Pat Hartman]

This is a table design problem. Supplier must be moved to the parent table. It would then be selected on the main form and therefore not changeable on each subform record.

 

[tacieslik]

Pat,

The supplier is on the main form. The Parts are on the subform.

Many Thanks

 

[tacieslik]

ntp,

I've tried what you said, but I get a runtime error refering to the Form name and saying that a field called that cannot be found?
I've checked all the syntax etc.?

Any ideas?
TAC

 

[Matthew Snook]

In reference to the latest runtime error: When I last received that error, it was because the combo box was returning the record ID instead of the associated name which I had in the code. Try inserting the hidden field from the combo box instead of the visible field.

Matt

 

[tacieslik]

I don't seem to get any runtime errors, but the restriction does not happen either.

Any other ideas?

Many Thanks

 

[Pat Hartman]

I get the problem now

ntp almost had it right. The second event should be the AfterUpdate event of the COMBO not the FORM.

 

[tacieslik]

Ok, Things are looking better!
It's not working quite, but when I choose a supplier for the first time after opening the main form and then select a part, when I select the next part regardless of what supplier I choose, I can only select parts from the supplier I first chose. This is fine, but when I create a new record (main form) it will only let me choose the parts from the supplier I first chose?

Cheers! TAC

[This message has been edited by tacieslik (edited 05-18-2001).]

 

[Pat Hartman]

Try requerying the subform in the AfterUpdate event of the supplier combobox.

 

[tacieslik]

Pat Hartman

I've been out of the office, sorry it's been so long.
The Supplier is in the main form. Should I requery this or the PartID combo in the subform?