Help with Dmax Funtion

PhoenixCouriers

Registered User.
Local time
Today, 22:16
Joined
Feb 24, 2011
Messages
32
Hi all,

Right here goes,

I have a combobox on my form that lookups data in a query which gives me the last invoice number for the selected customer on then form then enters this number into field on the form called "bookings.invoicenumber"

and if there is no number in the query then this then gives me the dmax+1 from the invoicedetails table

here are the fields
ComboLookup ( combo box ) from tables customers
bookings.invoicenumber
invoicedetails.invoicenumber

query is called LookupLastInvoiceNumber
form is called BookingsForm


many thanks

simon
 
afterupdate event from combo box
Me.Bookings_InvoiceNumber = DMax(lookuplastinvoicenumber, InvoiceNumber)
which works fine

then i need something like this

if isnull (me.bookings_invoicenumber) then
me.invoicedetails.invoicenumber = dmax ("invoicenumber","invoicedetails)+1
end if
 
afterupdate event from combo box
Me.Bookings_InvoiceNumber = DMax(lookuplastinvoicenumber, InvoiceNumber)
which works fine
Is that exactly how you have it in your form? The syntax is actually incorrect. You've got the field name and query name the wrong way round.

then i need something like this

if isnull (me.bookings_invoicenumber) then
me.invoicedetails.invoicenumber = dmax ("invoicenumber","invoicedetails)+1
end if
Is this the answer to this question?
... and if there's no dmax in the invoicedetails table?
 
sorry is this

Me.Bookings_InvoiceNumber = DMax("invoicenumber","lookuplastinvoicenumber")

then I am stuck from here
 
and the second question?? You gave two scenarios,

If no max in query, check in table. If no max in table, do what??
 
I will ask again.

If there is no max in the INVOICE TABLE, what should happen?
 
so should be like this
if no max in query then use max from invoice table +1

thanks
 
I see.

So simply use an IF...END IF block. Syntax:
Code:
Dim invMax as Long

invMax = Nz(DMax("InvoiceNumber", "QueryName", "CustomerID = " & Me.CustomerID), 0)

If invMax <> 0 Then
    Me.txtbox = invMax + 1
Else 
    Me.txtbox = Nz(DMax("InvoiceNumber", "Table"), 0) + 1
End If
 
getting error on this line

invMax = Nz(DMax("InvoiceNumber", "activeinvoices", "CustomerID = " & Me.CustomerID), 0)

Runtime Error 2471
the expression you entered as a query parameter produced this error: "sim001"

the sim001 is the account number
 
The CustomerID is of type Text.

Here's the amended line:
Code:
invMax = Nz(DMax("InvoiceNumber", "activeinvoices", "CustomerID = [COLOR=Red]'[/COLOR]" & Me.CustomerID & "[COLOR=Red]'[/COLOR]"), 0)
Notice the single quotes in red.
 
now getting this

runtime error
to make changes to this field, first save the record.
 
Where are you running this code? In what event and under which control?
 
And you're trying to save that value into the field bound to the combo box??
 
in the afterupdate event of the customerid box
also tried the same in the combo box
 

Users who are viewing this thread

Back
Top Bottom