Display field value from query in form textbox

[zerokono]

i need a build for ........



I want to lookup expirydate
the data is in tblreceipts
the criteria will be the fields - materialdescription1 & batchnumber (located in tblreceipts)

I want to put this into a form called frmusage with a textfield called expirydate

I have tried to use the dlookup function but keep coming unstuck, please help me i'm going insane!

[Minty]

What have you tried so far?

Dlookup("expirydate",tblreceipts","[materialdescription1] = 'MyDescription' AND [batchnumber] = 'Batchw3w2'")

This assumes Material and Batch are text fields.
You would replace them with form controls by concatenating them;

Dlookup("expirydate",tblreceipts","[materialdescription1] = '" & Me.MyDescriptionControlName & "' AND [batchnumber] = '" & Me.MyBatchNumberControlName & "'")

[zerokono]

Hi, ive tried everything (including the kitchen sink!). the batch is text but the material is a lookup from a table called `inventroy'

Cheers

[Minty]

Okay - please show us what you have tried. The control source or VBA.
And possibly paste up a picture of your inventory table.

Are you looking up the inventory name or is there a Primary Key autonumber being used in the combo for it? (I'm assuming it's a combo?)

[Welshgasman]

If you are using table lookups (bad bad idea) then you need to use the actual value stored, not what you see.

[zerokono]

i put it in control source but have deleted it. below is a snapshot of my inventory and receipts tables. The actual inventory table are just textboxes but in the receipts table it is a combobox.

[Welshgasman]

Yes, table lookups. You need to find what the ID is for that entry and use that when you lookup the field in the table.
Best to get rid of them now.