Lookup combo box

J

Jurgen

We have a form: Frmpallet and a combobox for the location. The Pallet can
only have one location.
After selecting a Location in the form and going to the next entry (pallet)
the previous selected location must not be available
What is the best way to handle this, temporary fix is to close/open the form

Thank You
Jurgen
 
A

Al Campagna

Jurgen,
Making Location a Indexed/NoDuplicates in your table should do it. That's the easiest
way.
Otherwise, you could use the BeforeUpdate event of Location to do a DLookup for that
value existing on another record, but that's really just duplicating the NoDuplicates
function.
 
J

Jurgen

A pallet can have one location
except: If pallet is shipped i change location to LocationID '1' (shipped
location)

ComboBox
SELECT TblLocation.LocationID, TblLocation.LocationGang,
TblLocation.LocationPositie, TblLocation.LocationDiepte,
TblLocation.LocationNiveau
FROM TblLocation
WHERE (((TblLocation.LocationGang)<>99999) AND
((TblLocation.LocationStatus)=2));

99999 is the location for shipped pallets and locationStatus 2 is Enabled
(free to use)

Is it beter?: one pallet can have one pallet and if pallet is shipped copy
to history table
I need the history for printing invoice
 
D

DBrown

You said the temporary fix was to close/open the form. If this is to update
the combobox, just requery it in the On Current event. It looks as though
your record source for the combobox is to show only what has an ID of '2'.
Replace "YourComboBox" with the name of your combobox.

Private Sub Form_Current()
on error goto Err_Form_Current

me.YourComboBox.requery

Exit_Form_Current:
Exit Sub

Err_Form_Current:
msgbox Err.Description
Resume Exit_Form_Current
End Sub

Hope this helps.
 
Top