IF code to incorporate two criteria

[Nick W]

Hi All,

I am working on a database someone has created for processing orders.
There is one table for all transactions whether they are sales orders
or purchase orders. There is a field called ParentId which is being
used as the sale s or purchase order number. What I have done is added
two columns to this table called SalesReference and PurchaseReference
so that the Sales Order numbers are concurrent and the Purchase Order
numbers are concurrent. The form knows whether it is a sales order or
a purchase order by the CorS (Customer or Supplier) field. I've added
code to the Before Insert on the Order form as follows:

If Me.NewRecord And Me.CorS = "C" Then
Forms![frmTransactionHeader]![ParentId] = Nz(DMax("[SalesReference]",
"tblTransactionHeader") + 1, 0)
If Me.NewRecord And Me.CorS = "C" Then
Forms![frmTransactionHeader]![SalesReference] =
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
If Me.NewRecord And Me.CorS = "S" Then
Forms![frmTransactionHeader]![ParentId] =
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
If Me.NewRecord And Me.CorS = "S" Then
Forms![frmTransactionHeader]![PurchaseReference] =
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
End If
End If
End If
End If

However it does not work, if I just have the following code it will
create the next Parent Id:

If Me.NewRecord Then
Forms![frmTransactionHeader]![ParentId] = Nz(DMax("[SalesReference]",
"tblTransactionHeader") + 1, 0)

Can anyone let me know where I'm going wrong,

Thanks in advance,
Nick

 

[Douglas J. Steele]

If Me.NewRecord Then
If Me.CorS = "C" Then
Forms![frmTransactionHeader]![ParentId] = _
Nz(DMax("[SalesReference]","tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![SalesReference] = _
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
ElseIf Me.CorS = "S" Then
Forms![frmTransactionHeader]![ParentId] = _
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![PurchaseReference] = _
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
End If
End If

or

If Me.NewRecord Then
Select Case Me.CorS
Case "C"
Forms![frmTransactionHeader]![ParentId] = _
Nz(DMax("[SalesReference]","tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![SalesReference] = _
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
Case "S"
Forms![frmTransactionHeader]![ParentId] = _
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![PurchaseReference] = _
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
End Select
End If

 

[Nick W]

Hi Doug,

Thanks for your quick reply, I did get back to you yesterday but I'm
not sure what's happend to the posting.

I've used the code you mentioned above and it does work when I run one
of my append queries which auto generates purchase orders based on the
sales processed but it doesn't work on the form when I create a new
sales or purchase order. I have put the code in the Before Insert on
the form properties, is that correct? Any idea's where I'm going
wrong?

Thanks,
Nick

 

[Douglas J. Steele]

Not sure what you mean by "in the Before Insert on the form properties". You
don't mean you typed that code into the property, do you? If so, that's not
correct: it's VBA code, and needs to be put in a VBA event procedure.

(Please don't cut so much from the post when you reply. It's a pain having
to go back and find the original post to reply!)

 

[Nick W]

Not sure what you mean by "in the Before Insert on the form properties". You
don't mean you typed that code into the property, do you? If so, that's not
correct: it's VBA code, and needs to be put in a VBA event procedure.

(Please don't cut so much from the post when you reply. It's a pain having
to go back and find the original post to reply!)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)









- Show quoted text -

Thanks Doug,

I've set the code up as VBA within the Before Insert Event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Select Case Me.CorS
Case "C"
Forms![frmTransactionHeader]![ParentId] =
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![SalesReference] =
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
Case "S"
Forms![frmTransactionHeader]![ParentId] =
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![PurchaseReference] =
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
End Select
End If

End Sub

Is that ok? I'm not sure why it's not working, as I mentioned earlier
if a record is appended to the tblTransactionHeader using a command
button it automatically creates the ParentId but not if you manually
input an order using the form.

Thanks,
Nick

 

[Douglas J. Steele]

Not sure what you mean by "in the Before Insert on the form properties".
You
don't mean you typed that code into the property, do you? If so, that's
not
correct: it's VBA code, and needs to be put in a VBA event procedure.

(Please don't cut so much from the post when you reply. It's a pain
having
to go back and find the original post to reply!)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)









- Show quoted text -

Thanks Doug,

I've set the code up as VBA within the Before Insert Event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Select Case Me.CorS
Case "C"
Forms![frmTransactionHeader]![ParentId] =
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![SalesReference] =
Nz(DMax("[SalesReference]", "tblTransactionHeader") + 1, 0)
Case "S"
Forms![frmTransactionHeader]![ParentId] =
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
Forms![frmTransactionHeader]![PurchaseReference] =
Nz(DMax("[PurchaseReference]", "tblTransactionHeader") + 1, 0)
End Select
End If

End Sub

Is that ok? I'm not sure why it's not working, as I mentioned earlier
if a record is appended to the tblTransactionHeader using a command
button it automatically creates the ParentId but not if you manually
input an order using the form.

I missed the fact that your use of Nz is incorrect (the perils of
copy-and-paste!), but that shouldn't matter after the first record's there.

It should be

Nz(DMax("[SalesReference]", "tblTransactionHeader") ,0) + 1

Are you saying that the code isn't firing when you manually input an order
using the form? Try putting a break point in the code and single-stepping
through the code to see what's happening. (You do this by clicking on the
margin to the left of the statement "If Me.NewRecord Then" in the VB Editor.
That should put a circle in the margin, and highlight that line. You then
use your form, and, assuming the event fires, execution will halt at that
line. Use the F8 key to advance from line to line, watching to determine
exactly which lines get executed.)