Hi All

I might have missed a chapter or what I'm trying t do is just not possible, but here we go:
I've got a Subform in datasheet view, based on a temporary table with about 100 records, where the left column is part #'s and in the 5th column I've got pallet Id's.
What I am hoping to achieve is to have a combo box on the pallet Id for each record, that will look up pallet Id's matching the part # in the left field.
The lookup SQL is based on a different table.
Basically when the temporary table is written, it automatically chooses an available pallet, but I'd like the operator to be able to choose a different pallet for various reasons.
What I've got so far is a combo box that looks up all pallets for all part #'s on the left, rather than the part # in the same record.

Is this possible to achieve or am I dreaming?

Any help would be greatly appreciated.

Thanks / Fred

I would try setting an OnFocus event on the Combobox control. This event then creates the RowSource for its lookup based on the current value of the part #.

OnFocus event code segment e.g.
strSQL = "SELECT [part#] FROM LookupTable WHERE [part#] = " & Me.part#

(if part# is numeric)

Me.palletID.Rowsource = strSQL
Me.palletID.Requery


Cheers Ray

Small mod. to previous rushed reply:

On Got Focus event code segment: e.g.
strSQL = "SELECT palletID, part# FROM LookupTable WHERE part# = " & Me.part#

(if part# is numeric)

Me.palletID.Rowsource = strSQL
Me.palletID.Requery


Cheers Ray

Hi Ray

Thanks for your help, but part# is text.
I tried your code but I get a "Data type missmatch in expression" msg.
Since I'm very fresh on this, do you know what to do to get the right data type for the query to work.

Many thanks.
Cheers / Fred

If part# is a text string, you need to enclose it in quotes. Change it to read as follows:

strSQL = "SELECT palletID, part# FROM LookupTable WHERE part# = """ & Me.part# & """"

Darrell L. Embrey

Many many many many thanks. Phew... it's working.
Again thank you both so much.

Cheers heaps / Fred