DLast for a Sub Form (1 Viewer)

wizcow · Mar 19, 2003

Hi

My Inventory form has StockTakeDate and IDNumber.
The InventorySub form has the Parts data including Quantity.

On another form I am trying to use a DLookUp function to look up the part's last Invnetory date.

=DLast("[StockTakeDate]","[Inventory]","Form.[ItemNo]=[ItemNo]")

This doesn't work, probably because it does not reference the Sub Form.

How can I re-write this to make it work?

Tom

Pat Hartman · Mar 19, 2003

For one thing, you need to use DMax() since you want the most recent date (the date with the highest value). DLast() would return the record positionally last in the recordset which might be the first date, the last date, or more likely something in between. Then you need to refer to a form field correctly:

=DMax("[StockTakeDate]","[Inventory]","[ItemNo] = Forms!YourFormName!YourSubformName.Form![ItemNo])

wizcow · Mar 23, 2003

Pat Hartman

I gave this a try;

=DMax("[StockTakeDate]","[Inventory]",[ItemNo] =Forms!Inventory!InventorySub.Form![ItemNo]")

and I get an #Error cycling in the text box.

I'm stuck!

Tom

Fornatian · Mar 23, 2003

type error caused that error, try:

=DMax("[StockTakeDate]","[Inventory]","[ItemNo] =" & Forms!Inventory!InventorySub.Form![ItemNo])

wizcow · Mar 23, 2003

Ian

Now I get this error

#Name?

Spelling is correct. I tried a short version;

=DMax("[StockTakeDate]","[Inventory]","[ItemNo] = [ItemNo]")

This produces a date (the wrong date, but the naming convention is correct)

Tom

Pat Hartman · Mar 23, 2003

The syntax posted by Fornatian is correct if ItemNo is numeric. If ItemNo is text, you'll need to enclose the value in quotes.

=DMax("[StockTakeDate]","[Inventory]","[ItemNo] ='" & Forms!Inventory!InventorySub.Form![ItemNo] & "'")

wizcow · Mar 23, 2003

I thought using a DLookUp was going to be easy!

The [ItemNo] in the Parts table is an 'AutoNumber'
The [ItemNo] in the InventorySub table is a 'Number'

Will this make a difference?

Tom

Pat Hartman · Mar 24, 2003

The syntax is correct so that leaves the form reference suspect. Open the main form in design view. Click on the subform and open its properties dialog. Make sure that "Name" on the "Other" tab = "InventorySub". If it does not, change the subform name in the DLookup() so that it matches the actual one.

DLast for a Sub Form (1 Viewer)

wizcow

Registered User.

Pat Hartman

Super Moderator

wizcow

Registered User.

Fornatian

Dim Person

wizcow

Registered User.

Pat Hartman

Super Moderator

wizcow

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread