Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code Help (2000)

    Hi,
    I have the following code and it is working but I'm finding to help the user "stay out of trouble" I want to add 2 more pieces of criteria but I'm not sure how to do it. There is more code after this but I'm mostly dealing with the theCountSQL line. Right now I'm checking for curUnitPrice is not null AND ysnCCOrder=no. After that I would like to say OR curUnitPrice is null AND ysnCCOrder=yes OR curUnitPrice is null AND ysnCCOrder=no. If any one of these 3 criteria are true I want the message box to appear. Thanks, Deb
    Dim theCountSQL As String
    theCountSQL = "SELECT Count(lngPartsID) as PartCount " & _
    "FROM qryReceiveQuotePartsMech " & _
    "WHERE curUnitPrice IS NOT NULL " & _
    "AND ysnCCOrder=no " & _
    "AND lngMachineID=" & Me.lngMachineID & " " & _
    "AND lngRecSourceID=" & Me.lngRecSourceID


    Dim TheCountRS As ADODB.Recordset
    Set TheCountRS = New ADODB.Recordset

    TheCountRS.Open theCountSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    'MsgBox TheCountRS!PartCount

    If TheCountRS!PartCount > 0 Then
    MsgBox "Please make sure all parts are priced and checked for Credit Card/BPA Order"
    TheCountRS.Close
    Set TheCountRS = Nothing
    Exit Sub
    End If

    TheCountRS.Close
    Set TheCountRS = Nothing

  2. #2
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2000)

    I've done some playing around since I've posted this and I have the followig code which seems to be working with the first 2 criteria, but I can't get it to work with the 3rd criteria which is curUnitPrice is null AND ysnCCOrder=no. When I add this in, I get the message when the unit price is filled in and CCOrder=yes. Thanks, Deb
    Dim theCountSQL As String
    theCountSQL = "SELECT Count(lngPartsID) as PartCount " & _
    "FROM qryReceiveQuotePartsMech " & _
    "WHERE curUnitPrice IS NOT NULL " & _
    "AND ysnCCOrder=no " & _
    "OR curUnitPrice IS NULL " & _
    "AND ysnCCOrder=yes " & _
    "AND lngMachineID=" & Me.lngMachineID & " " & _
    "AND lngRecSourceID=" & Me.lngRecSourceID

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2000)

    If you want "curUnitPrice is null AND ysnCCOrder=yes OR curUnitPrice is null AND ysnCCOrder=no", that is equivalent to "curUnitPrice is null", for you want to include both records where ysnCCOrder = yes and records where ysnCCOrder = no, in other words it doesn't matter what ysnCCOrder is.
    Furthermore, you need some parentheses to indicate that you ALWAYS want to specify the lngMachineID and lngResourceID fields:

    theCountSQL = "SELECT Count(lngPartsID) as PartCount " & _
    "FROM qryReceiveQuotePartsMech " & _
    "WHERE (curUnitPrice IS NOT NULL " & _
    "AND ysnCCOrder=no " & _
    "OR curUnitPrice IS NULL) " & _
    "AND lngMachineID=" & Me.lngMachineID & " " & _
    "AND lngRecSourceID=" & Me.lngRecSourceID

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2000)

    That makes sense and cleans it up a little. I've added that code and I get the message on the 2 criteria but I should NOT (or don't want to) get the message when the UnitPrice is not null AND ysnCCOrder=yes. But I am getting the message.
    Thanks,
    Deb

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2000)

    The SQL excludes that possibility, so either there is a typo somewhere, or another factor is involved that you haven't told us about.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help (2000)

    Scratch my last post. After thinking throught the logic I can't have that 3rd criteria where both are blank. Well, let me explain the screen and maybe you have a suugestion. It's a subform where parts will be listed to be ordered, could be anywhere from 1 part to numerous parts. When they're ready to place an order for a part they put in the price and mark if it's going to be a credit card order and then click a button that takes them to the Order Screen. Which means if the screen is showing them 50 parts but they only 5 of those are to be on this order, 45 will be blank (unitprice is null & CCorder=no) therefore causing the message to appear.

    I was trying to stop the user from being able to get to the Order Screen unless they have a price filled in and the check box checked. What was happening is they would click the order button which would bring up a dialog box to have them put in an Order #, then it would take them to the order screen where they would fill in more information. Well, if they left everything blank, but clicked the button it would allow them to put in a order # which would be added to the order table but the order would not be complete because no parts would appear and then they couldn't reuse that same order #. I'm trying avoid that.

    I've attached the entire code.

    thanks,
    Deb

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Help (2000)

    I think you need to perform two separate checks:

    I would use the Before Update event of the subform to cancel the update if ysnCCOrder has been ticked but curUnitPrice has been left empty.

    In the On Click event of the comment button, see if anything has been filled in at all by counting the records for which (curUnitPrice Is Not Null Or ysnCCOrder = Yes) And (lngMachineID = ... And lngRecSourceID = ...)
    If this is zero, issue a warning that the user must fill in something and get out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •