DLookup Or DMax To Find Value

[bkatsma]

I am sure this is fairly simple but I am having trouble getting started on this. I use a Form to invoice clients which includes the field [Billing_Month]. What I'm looking to accomplish is this. When I create a new invoice and enter the [cboAccountID] value, the [Billing_Month] field will look to the last invoice created (use [Invoice_#] with DMax?), and populate that value.

I have thought to use: Billing_Month = DMax ("Billing_Month", "frmInvoices"), but this doesn't specifically get me the last invoice, it would just look for the highest Billing_Month, which is a text field.


I have thought to use: Billing_Month = DLookup ("Billing_Month", "frmInvoices"), But this doesn't get me the last invoice to pull from.

I am fairly new to VBA so feel free to dumb it down for me

 

[Rijnsent]

Hi bkatsma,

the dmax/dlookup functions can also have a "where/if" part, like: DMax("UnitPrice", "Order Details", "OrderID = 10248"). So in your case something like: DMax ("Invoice_#", "tblInvoices", "cboAccountID=" & [cboAccountID] )
The [] bit would refer to the field in your form, the rest to the table/query where you have to find the Invoice_#
See more examples e.g. here: MS Access: DMax Function

Cheers,

Koen