Help with Dmax Funtion

[PhoenixCouriers]

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

 

[vbaInet]

Let's see your attempts.

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

... and if there's no dmax in the invoicedetails table?

 

[PhoenixCouriers]

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

 

[vbaInet]

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?

 

[PhoenixCouriers]

sorry is this

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

then I am stuck from here

 

[vbaInet]

and the second question?? You gave two scenarios,

If no max in query, check in table. If no max in table, do what??

 

[PhoenixCouriers]

add +1 from max number in the invoice table?

 

[vbaInet]

I will ask again.

If there is no max in the INVOICE TABLE, what should happen?

 

[PhoenixCouriers]

the way our database works there will always be an invoice number

 

[PhoenixCouriers]

so should be like this
if no max in query then use max from invoice table +1

thanks

 

[vbaInet]

I see.

So simply use an IF...END IF block. Syntax:

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

 

[PhoenixCouriers]

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

 

[vbaInet]

The CustomerID is of type Text.

Here's the amended line:

invMax = Nz(DMax("InvoiceNumber", "activeinvoices", "CustomerID = [COLOR=Red]'[/COLOR]" & Me.CustomerID & "[COLOR=Red]'[/COLOR]"), 0)

Notice the single quotes in red.

 

[PhoenixCouriers]

now getting this

runtime error
to make changes to this field, first save the record.

 

[vbaInet]

Where are you running this code? In what event and under which control?

 

[PhoenixCouriers]

could be the problem!

after the update event of the combo box??

 

[vbaInet]

And you're trying to save that value into the field bound to the combo box??

 

[PhoenixCouriers]

i have now put in the afterupdate event of the customerid box

 

[vbaInet]

i have now put in the afterupdate event of the customerid box

That works?

So all you want to do is generate a Max() number when a new record is created?

 

[PhoenixCouriers]

in the afterupdate event of the customerid box
also tried the same in the combo box