DMAX

B

Brook

I have a payments subform on my Invoices form, I have a "PaymentNumber" that
I use to update as a DMAX after my Payment Date has been added, however, I
need it to "reset" for each invoice number, how would I do that?

I do have the field "invoiceNumber" in both my tblinvoicepayments and my
invoicepayments subform

Thanks,

Brook

Below is my code:

Begin Code:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments"), 0) + 1
End If
End Sub
End Code:
 
O

Ofer

Try and Add a Where statement to the DMAX

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum=" & forms![FormName].invoicenum), 0) + 1
End If
End Sub

Assuming that the invoice number is a Number
If t is text you should use
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum='" & forms![FormName].invoicenum & "'"), 0)
+ 1
End If
End Sub
 
B

Brook

Ofer,

Thanks for the help,

The invoice number is in text format : for example this is a sample
invoice number : SLD-0001.

I updated the code with the code you provided for text. Here is what I
have:
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenumber & "'"),
0) + 1
End If

However, when I add a payment, I am getting a debug error that goes to
this line item.

Any ideas?

Thanks,

Brook

If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenum & "'"), 0)
+ 1
End If

Ofer said:
Try and Add a Where statement to the DMAX

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum=" & forms![FormName].invoicenum), 0) + 1
End If
End Sub

Assuming that the invoice number is a Number
If t is text you should use
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum='" & forms![FormName].invoicenum & "'"), 0)
+ 1
End If
End Sub

Brook said:
I have a payments subform on my Invoices form, I have a "PaymentNumber" that
I use to update as a DMAX after my Payment Date has been added, however, I
need it to "reset" for each invoice number, how would I do that?

I do have the field "invoiceNumber" in both my tblinvoicepayments and my
invoicepayments subform

Thanks,

Brook

Below is my code:

Begin Code:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments"), 0) + 1
End If
End Sub
End Code:
 
O

Ofer

If you have the invoive number on the main form the refer to it as
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName].invoicenumber & "'"),
0) + 1
If you have it only on the subform then it should be written as

Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName]![SubFormName].Form!invoicenumber &
"'"),
0) + 1


טפסי×![Form2]![Form1].Form![Country]

Brook said:
Ofer,

Thanks for the help,

The invoice number is in text format : for example this is a sample
invoice number : SLD-0001.

I updated the code with the code you provided for text. Here is what I
have:
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenumber & "'"),
0) + 1
End If

However, when I add a payment, I am getting a debug error that goes to
this line item.

Any ideas?

Thanks,

Brook

If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenum & "'"), 0)
+ 1
End If

Ofer said:
Try and Add a Where statement to the DMAX

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum=" & forms![FormName].invoicenum), 0) + 1
End If
End Sub

Assuming that the invoice number is a Number
If t is text you should use
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum='" & forms![FormName].invoicenum & "'"), 0)
+ 1
End If
End Sub

Brook said:
I have a payments subform on my Invoices form, I have a "PaymentNumber" that
I use to update as a DMAX after my Payment Date has been added, however, I
need it to "reset" for each invoice number, how would I do that?

I do have the field "invoiceNumber" in both my tblinvoicepayments and my
invoicepayments subform

Thanks,

Brook

Below is my code:

Begin Code:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments"), 0) + 1
End If
End Sub
End Code:
 
B

Brook

Hello Ofer,

Thanks for all the help! I was able to get everything working properly.
However, I do have one more question for you. For each of my payment entries,
I have code that creates a new / running balance called "myNewBalance".

Below is the code:


Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate]) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function

Below is the code that I am using for my DMAX per your suggestions:

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"Invoicenumber=" & Me.invoicenumber), 0) + 1
End If
End Sub

What is happening is, is this.. When I add a new invoice and add new
payments MyNewBalance is "carrying" over from my previous invioce and
payments. Do you know how I can "reset" this for each new invoice?

Thanks for all your help!

Brook











Ofer said:
If you have the invoive number on the main form the refer to it as
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName].invoicenumber & "'"),
0) + 1
If you have it only on the subform then it should be written as

Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName]![SubFormName].Form!invoicenumber &
"'"),
0) + 1


טפסי×![Form2]![Form1].Form![Country]

Brook said:
Ofer,

Thanks for the help,

The invoice number is in text format : for example this is a sample
invoice number : SLD-0001.

I updated the code with the code you provided for text. Here is what I
have:
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenumber & "'"),
0) + 1
End If

However, when I add a payment, I am getting a debug error that goes to
this line item.

Any ideas?

Thanks,

Brook

If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenum & "'"), 0)
+ 1
End If

Ofer said:
Try and Add a Where statement to the DMAX

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum=" & forms![FormName].invoicenum), 0) + 1
End If
End Sub

Assuming that the invoice number is a Number
If t is text you should use
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum='" & forms![FormName].invoicenum & "'"), 0)
+ 1
End If
End Sub

:

I have a payments subform on my Invoices form, I have a "PaymentNumber" that
I use to update as a DMAX after my Payment Date has been added, however, I
need it to "reset" for each invoice number, how would I do that?

I do have the field "invoiceNumber" in both my tblinvoicepayments and my
invoicepayments subform

Thanks,

Brook

Below is my code:

Begin Code:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments"), 0) + 1
End If
End Sub
End Code:
 
O

Ofer

Couple of things
First the line
If Nz([paymentdate]) = 0 Then
Should be
If Nz([paymentdate],0) = 0 Then

Second, It look like you have few Global var, initiate them to 0 on the on
current event of the form.

Brook said:
Hello Ofer,

Thanks for all the help! I was able to get everything working properly.
However, I do have one more question for you. For each of my payment entries,
I have code that creates a new / running balance called "myNewBalance".

Below is the code:


Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate]) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function

Below is the code that I am using for my DMAX per your suggestions:

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"Invoicenumber=" & Me.invoicenumber), 0) + 1
End If
End Sub

What is happening is, is this.. When I add a new invoice and add new
payments MyNewBalance is "carrying" over from my previous invioce and
payments. Do you know how I can "reset" this for each new invoice?

Thanks for all your help!

Brook











Ofer said:
If you have the invoive number on the main form the refer to it as
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName].invoicenumber & "'"),
0) + 1
If you have it only on the subform then it should be written as

Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName]![SubFormName].Form!invoicenumber &
"'"),
0) + 1


טפסי×![Form2]![Form1].Form![Country]

Brook said:
Ofer,

Thanks for the help,

The invoice number is in text format : for example this is a sample
invoice number : SLD-0001.

I updated the code with the code you provided for text. Here is what I
have:
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenumber & "'"),
0) + 1
End If

However, when I add a payment, I am getting a debug error that goes to
this line item.

Any ideas?

Thanks,

Brook

If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenum & "'"), 0)
+ 1
End If

:

Try and Add a Where statement to the DMAX

Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum=" & forms![FormName].invoicenum), 0) + 1
End If
End Sub

Assuming that the invoice number is a Number
If t is text you should use
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum='" & forms![FormName].invoicenum & "'"), 0)
+ 1
End If
End Sub

:

I have a payments subform on my Invoices form, I have a "PaymentNumber" that
I use to update as a DMAX after my Payment Date has been added, however, I
need it to "reset" for each invoice number, how would I do that?

I do have the field "invoiceNumber" in both my tblinvoicepayments and my
invoicepayments subform

Thanks,

Brook

Below is my code:

Begin Code:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments"), 0) + 1
End If
End Sub
End Code:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top