Form/Combo box/Requery'S (1 Viewer)

[tacieslik]

I've asked this question before but had no success, so I'll try and explainthis a bit better.
I've got a purchase orders form which has a subform inside it. There is a combo box in the main form where I select a supplier.
I'm then able to choose parts for that supplier only from the subform.
I want to be able to create only one Main form record per supplier.
Once I choose a supplier, I must not be able to choose a different one for that record.
At present, I'm able to select a supplier, select the parts to order and then change the supplier and select their parts.
If it help's, I can send a cut down version of the database in 2000 or 97.

Many Thanks, Tim Cieslik

 

[Pat Hartman]

Check for changes in the supplier field in the BeforeUpdate event of the form.

If Len(Trim(me.supplier.OldValue)) = 0 Then
Else
If Me.Supplier.OldValue <> Me.Supplier Then
msgbox "you may not change the supplier', vbOKonly
Me.Supplier.Undo
Cancel = True
End If

Alternatively, you could lock and unlock the field depending on whether or not supplier already has a value.

 

[KevinM]

Why not set the Supplier Field in table design view to 'NO DUPLICATES'.

You should still allow your users to change the Supplier just in case they select the wrong one by mistake.

 

[tacieslik]

Hello Pat,

What goes after the End If statement?

Regards,

Tim Cieslik

 

[tacieslik]

Hello Kevin,

I tried what you said, but it does not do what I want?
It will let me select the Part, but it does not display it?

Regards, Tim Cieslik

 

[Pat Hartman]

Nothing goes after the End If.

If the length of the old value is zero, the code accepts the new value. If the old value has a length > 0 then the record has been saved at least once, so you don't want to allow the supplier to be changed. The code displays a message box and cancels the update thereby preventing the change from being saved.

If you want to get a little more user friendly, you could allow the change provided no records have been added using the sub form.

 

[tacieslik]

Thanks Pat,
I know what was wrong, I needed another End If statement.
Unfortunatley, it does not work for some reason?
I'm able to choose a supplier, choose parts and then choose different suppliers on the same form?
Since I added the code you gave, the text box that is filled when a part number is selected does not display the result unless I do a refresh. Do I need a requery?