Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Unanswered: Help me with IF , Else

    Hi guys

    Trying to make a button running a VBA that does the following:

    - checks if the fields are empty (if so, prompt message)
    - if all fields are filled out then:
    + save the record
    + open the report in print preview OR
    (maybe a pop up asking if u want to print YES or NO)
    ** despite no or yes then carry on **

    lastly, it should go to the nextNewRecord.


    The form is called [Sales Order]
    the (combobox) fields are

    - cboTyreSize
    - cboTyreType
    - cboProductID

    Report name: PrintSales



  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What did you try so far?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if cboTyreSize = "" then 'no size selected
    msgbox "....."
    endif
    'repeat as neccesary

    Alternative approaches could be
    use the background of required controls to some other colour, then reset when a valid value is there (use the add event to set the required colour, the on lost focus event to clear the colour

    build a single error message and dispaly any.all errors at the same time

    or both

    validate the values in the forms before update event and set cancel =true if there are errors
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2013
    Posts
    72
    This is what I done so far:

    Private Sub Submit_Click()

    If Me!txtSoldQuantity = "" Then
    MsgBox ("Quantity is missing!")
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Update Sales SET UnitPrice = " & Me!txtUnitPrice & " WHERE SalesID = " & Me!SalesID & ""
    DoCmd.RunSQL "Update Sales SET SubTotal = " & Me!txtSubTotal & " WHERE SalesID = " & Me!SalesID & ""

    DoCmd.GoToRecord , , acNewRec

    End If

    End Sub

    BUT when I use more IF it wont work ... for some reason it wont check the combobox if null
    I tried this but didnt work

    If Me!txtSoldQuantity = "" AND me!cboTyreSize ="" THEN
    MsgBox ("Missing fields ... !")
    End IF
    Else ...

    Besides, I also want to implement the PRINT or PREVIEW REPORT option
    so once i press SAVE button, it should run the stuff above and then display the report (with a text box - yes or no )
    either way, it then should go to new record.
    Last edited by Bucki; 02-06-14 at 15:07.

  5. #5
    Join Date
    Apr 2013
    Posts
    72
    update

    Ok now using the below code
    trying to include the report feature:

    Private Sub Submit_Click()

    If Me!txtSoldQuantity = "" Then
    MsgBox ("Quantity is missing!")
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Update Sales SET UnitPrice = " & Me!txtUnitPrice & " WHERE SalesID = " & Me!SalesID & ""
    DoCmd.RunSQL "Update Sales SET SubTotal = " & Me!txtSubTotal & " WHERE SalesID = " & Me!SalesID & ""

    DoCmd.OpenReport "PrintSales", acViewPreview, , "[SalesID] = " & [SalesID]

    DoCmd.GoToRecord , , acNewRec

    End If

    End Sub
    Report opens fine in print preview
    but getting a DEBUG with the last line: DoCmd.GoToRecord , , acNewRec

    what is wrong with it??
    maybe I am ending it wrong ?

  6. #6
    Join Date
    Apr 2013
    Posts
    72
    come onnnnnnnnnn ... any advise ?????

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what error message are you getting?
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2013
    Posts
    72
    Run-time error: 2046

    The command or action GoToRecord isnt available now


    * see attached pic **
    Attached Thumbnails Attached Thumbnails error.jpg  
    Last edited by Bucki; 02-06-14 at 17:23.

  9. #9
    Join Date
    Apr 2013
    Posts
    72
    help ??

    how can i fix this issue

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so the debugger halts at the indicated line but reports
    'The command or action GoToRecord isnt available now'?
    thats inconsistent

    the reason why your if statement didn't work is that you are testing both values using an AND
    either test individual values or use an OR in place of the AND

    if you set warnings off be sure to set warnings back on again as soon as possible. warnigns can also include error reporting

    if the code is breaking on
    DoCmd.OpenReport "PrintSales", acViewPreview, , "[SalesID] = " & [SalesID]
    then check the value of salesid...
    check it has the value you expect
    BTW there is no need to delimit tble and or column names with square brackets assuning you have named your columns according to ISO standards and Access reccommendations
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by healdem View Post

    the reason why your if statement didn't work is that you are testing both values using an AND
    either test individual values or use an OR in place of the AND
    Didnt u had a look at the image attached???
    I did not use more then one parameter to check box
    there is only one IF statement and it checks only one field

    Here:

    Private Sub Submit_Click()

    If Me!txtSoldQuantity = "" Then
    MsgBox ("Quantity is missing!")
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Update Sales SET UnitPrice = " & Me!txtUnitPrice & " WHERE SalesID = " & Me!SalesID & ""
    DoCmd.RunSQL "Update Sales SET SubTotal = " & Me!txtSubTotal & " WHERE SalesID = " & Me!SalesID & ""

    DoCmd.OpenReport "PrintSales", acViewPreview, , "[SalesID] = " & [SalesID]

    DoCmd.GoToRecord , , acNewRec

    End If

    End Sub

    where do you see that I used more IF's ??
    I tried with [ ] and without them ... didnt solve the issue.


    The reason why I set SetWarnings FALSE is because
    the command RunSQL updates the certain fields
    and it is annoying having to click OK twice at the message boxes

    Unless u know a better way of doing it ...?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Bucki View Post
    Didnt u had a look at the image attached???
    yes and the image is irrelevant to the if statements. and its possibly irrelevant to the debug issue as you report an error message that is not applicable to the highlighted line of code

    Quote Originally Posted by Bucki View Post
    where do you see that I used more IF's ??
    never said you did use more than one 'if' statement... that's the reason for your problem

    either
    If (conditiona) OR (conditionB) or (ConditionC)
    means if any of the conditions (A,B or C, or any permutation) are true then execute the next block of code
    so
    Code:
    If Me!txtSoldQuantity = "" AND me!cboTyreSize ="" THEN
    means exectue the following statements if both combobox values were null.

    personally I'd do 3 separate if tests, build an error message adding a line for each fault, then display if errors are detected

    Quote Originally Posted by Bucki View Post
    I tried with [ ] and without them ... didn't solve the issue.
    well it wouldn't, square brackets are used to delimit table and/or column names

    Quote Originally Posted by Bucki View Post
    The reason why I set SetWarnings FALSE is because
    the command RunSQL updates the certain fields
    and it is annoying having to click OK twice at the message boxes
    fine, but turn them back on again as soon as the reason to suppress warnings has finished, otherwise code may fail without warning as you suppressed warnings....
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2013
    Posts
    72
    I turned on the SetWarning and same issue again.
    I tried without [ ] and same issue again

    At moment, I like to get this GoToRecord issue fixing
    and not much bothered with other IF fields


    Guess, ur out of clues in this case??

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it aint my project, its not a coding style Id use. its not my problem

    so where is the error being reported
    is it on the report open
    ..if so my guess is there isn't an appropriate value in the = salesid
    whether thats no value or an incorrect type I can't
    it could be the salesid isn't in the report
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by healdem View Post
    it aint my project, its not a coding style Id use. its not my problem

    so where is the error being reported
    is it on the report open
    ..if so my guess is there isn't an appropriate value in the = salesid
    whether thats no value or an incorrect type I can't
    it could be the salesid isn't in the report
    If it SalesID being wrong
    then how come, it works for ther "Update ,,, where SalesID ="
    I mean, if I get rid of the DoCmd.GoToRecord

    it works fine and it records the data BUT
    it would not go to the NEW next page

    I thought having one button that does this:

    - saves the data
    - opens the report *for that new data*
    - moves on to the new record

    otherwise I am ending up with 3 silly buttons
    SAVE - PRINT - NEW RECORD

Posting Permissions

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