Show Last record of specific field in Form (1 Viewer)

Ahmed73

New member
Local time
Today, 19:55
Joined
Jul 20, 2021
Messages
21
I have a FORM where I enter new quote number. The new quote number is text field and enters manual input. I need the form to display
the “Quote Number” FIELD of the LAST RECORD in an Unbound field on the same form. The table name is “Customer RFQ Detail”. Please help.
 

June7

AWF VIP
Local time
Today, 06:55
Joined
Mar 9, 2014
Messages
5,423
You could try a DMax() domain aggregate function expression in textbox. This assumes "Quote Number" is incremented and increasing.

In a multi-user database, always a risk that simultaneously users will generate the same identifier. Code can create this incremented value instead of relying on user to enter. This should reduce risk.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Jan 23, 2006
Messages
15,364
Forms are really a window onto data stored in tables.Where exactly would you store quote number? Would you also store the date of entry?

Can you tell us about, and show us the structure of table Customer RFQ Detail?
Aren't quotes against jobs or projects of some sort?
I think we need more info about the business involved.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:55
Joined
May 7, 2009
Messages
19,169
if Quotation number is serial, you can use DMax() aggregate function as suggested.
the best method is to Add an Autonumber field to your Quotation table and use it in DMax()/DLookup:

DLookup("[Quote Number]", "[Customer RFQ Detail]", "ID = " & DMax("ID", "[Customer RFQ Detail]"))
 

Ahmed73

New member
Local time
Today, 19:55
Joined
Jul 20, 2021
Messages
21
if Quotation number is serial, you can use DMax() aggregate function as suggested.
the best method is to Add an Autonumber field to your Quotation table and use it in DMax()/DLookup:

DLookup("[Quote Number]", "[Customer RFQ Detail]", "ID = " & DMax("ID", "[Customer RFQ Detail]"))
Hi,
The field type is text. Autonumber is not suitable for the time being.
DLookup is not working. It giving error #size!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:55
Joined
May 7, 2009
Messages
19,169
it will give error because you don't have ID (autonumber) field.
 

June7

AWF VIP
Local time
Today, 06:55
Joined
Mar 9, 2014
Messages
5,423
It certainly can, as shown in arnel's example. You need autonumber field (or some field that can be relied on to be unique increasing value) for the DMax.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Feb 19, 2002
Messages
42,970
Why are you storing a number as text? If it is because you want leading zeros,, that is simply a formatting issue.

The problem with using dMax() on a text field that is not zero filled is that strings are evaluated left to right one character at a time. Therefor 2 is > 10 because 2 is >1.

Numbers need to be evaluated by magnatituede and that required decimal position alignment. If you do have leading zeros then 00002 will be less than 00010 because 1 is greater than 0
 

Users who are viewing this thread

Top Bottom