Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If...Then...Else, Yes/No Procedure (A97SR2)

    I would like to evaluate a yes/no field on one record with an If...Then...Else Statement. Could someone help me with the procedures and variables for this. The yes/no field is named [fldTax]. The yes/no has been previously selected by the user and is called from a query named [qryTaxable]. If the field has been selected (yes), then I want to run -> DoCmd.OpenQuery "qryRunTaxedReport". If it has not been selected, then I want to run -> DoCmd.OpenQuery"qryRunNoTaxReport". Thanks for the help.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    When you say the yes/no field is called from a query (qryTaxable), what do you mean by this? Is there only one record returned by this query? How do you access the record with the yes/no in it?
    If only one record is returned by qryTaxable then the following should work in VBA code:
    Set rs = dbs.OpenRecordset("qryTaxable")
    If (rs.yes/no fieldname) Then
    DoCmd.OpenQuery "qryRunTaxedReport"
    Else
    DoCmd.OpenQuery"qryRunNoTaxReport"
    End If

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    I tried your VBA code and it came back with - compile error - variable not defined. A further explanation of what I am doing is this: From a one field form the user can enter many different workorder numbers - - when a command button is pressed the workorder numbers are processed one at a time and printed with a Do...Loop. Within this Do...Loop, I want to evaluate whether the workorder is taxable or not taxable (this taxable yes/no field is within each workorder number record has been pre selected for each workorder number). With one query, it will print the workorder with tax and the other query will print it without tax. The Do...Loop works just fine. The method that is used for processing one at a time is: AppendQuery, SELECT TOP1 to a table and then process it - when done, delete that record. I have much difficulty understanding Dim, Set and variables. I have been looking at many error messages; variable not defined, object required, keyword not found, etc., etc. The help information is leaving me confused.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    For a start you said you wanted to run queries, I now know you mean reports. These queries are the record source of the reports you want to run, right?

    Why don't you put the Yes/No field (let's call it TaxYesNo) on the form, but NOT enabled and LOCKED so users cannot change it but the VBA code can use it.

    Tell me, are there 2 different reports defined or is there 1 report and you just use the different queries as the record source of the report?

    If there are 2 different reports, then the following code should be inserted into your DO loop:
    If TaxYesNo Then
    DoCmd.OpenReport "TaxReportName",,strCriteria
    Else
    DoCmd.OpenReport "NoTaxReportName",,strCriteria
    End If

    Be sure to substitute your report name in the above code and your fieldname for the YesNo tax field.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    Actually the two queries, "qryUpdateSummaryTable" & "qryUpdateSummaryTableNoTax" are update queries that send all the different dollar totals for the one workorder to a summary table. This summary table is then used through a query to provide information to one of the 4 subreports of the main report (& not a part of the event procedure). The following is the event procedure with comments.


    Private Sub Command3_Click()

    Dim Db As Database
    Dim rs As Recordset
    Set Db = CurrentDb
    Set rs = Db.OpenRecordset("tblBatchWO")
    'the form, "frmPrintWO" is used to enter the workorder numbers
    'in the "fldWONUM" of the "tblBatchWO"

    Do
    If rs.RecordCount = 0 Then
    Exit Do
    Else
    DoCmd.SetWarnings False
    'take the first wo number and append it
    'to the tblWOnumber table
    DoCmd.OpenQuery "qryApnd1toWOnum"

    'evaluate taxable or not
    DoCmd.OpenQuery "qryPrintOneWO"
    'the "qryPrintOneWO" contains the yes/no field, "fldNotTaxable"
    'the next If...Then...Else should update the Summary table

    If "fldNotTaxable" Then 'this line does not work. It comes
    'back with a run time error 13 - type mismatch. This is the
    'place where I am totally lost. The two following queries work
    'just fine if I process them manually
    DoCmd.OpenQuery "qryUpdateSummaryTable"
    DoCmd.Requery
    Else
    DoCmd.OpenQuery "qryUpdateSummaryTableNoTax"
    DoCmd.Requery
    End If

    'now print a workorder
    DoCmd.OpenReport "rptPrintOneWO", acViewNormal
    'now delete the w.o. number just printed
    'and the corresponding w.o. number in Batch
    DoCmd.OpenQuery "qryDel1fromBatchAndWO"
    'now requery the subform
    DoCmd.Requery "frmPrintWOsubform"
    End If
    Loop While rs.RecordCount > 0

    Set Db = Nothing
    Set rs = Nothing

    End Sub

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    Hi Ron,
    What does qryPrintOneWO do?
    Can you post it here? This will determine what the following line should look like:
    <<If "fldNotTaxable" Then >>

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    The "qryPrintOneWO" ties together the workorder number with its basic information - registration # - customer number - date started - date finished - labor rate - work description - and if the workorder is taxable. Then via relationship, the registration # gets the appropriate details from the aircraft table - make - model - serial number. Also it is related to the customer table with the customer number to get the company name - address - notes - etc. It is the source for the report that is generated. I was hoping that I could also use it to feed the information to the summary table which is the first subreport on the main report.

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

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    You write
    <hr>If "fldNotTaxable" Then 'this line does not work.<hr>
    You should use the actual field name without quotes; if the actual field name contains spaces, it should be enclosed in square brackets [ and ]. So if your field is literally named fldNotTaxable, you should use

    If fldNotTaxable Then

    If the field is named Not Taxable, you should use

    If [Not Taxable] Then

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    Is "fldNotTaxable" a control on your form that you are running this from?
    If so, then take away the quotes as Hans has suggested and put Me! prior to it, eg. Me!fldNotTaxable .
    If not, then where does it come from and what is it's name.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    fldNotTaxable is not a control on the form. I am going to try to simplify this whole thing and disregard all previous discussion. As an example, I have just run a query "qryA" that returns one record with two fields: [fldWOnum], the workorder number; and [fldNotTaxable], a yes/no field. What I want to do is: If the [fldNotTaxable] is "yes", I want to run another query, "qryNoTax". If the [fldNotTaxable] is "no", I want to run a query, "qryWithTax". I am trying to accomplish this with an If...Then...Else within an event procedure. Might there be an easier way to accomplish this?

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    What you could do is:
    1. Put an extra column in both queries being the fldNotTaxable field from the query qryA.
    For the Criteria of field FldNotTaxable in query qryNoTax you would set it to True.
    For the Criteria of field FldNotTaxable in query qryWithTax you would set it to False.
    In this way all you have to run in the Event PRocedure is both queries, without any tests.
    or
    2. You could read query qryA in a recordset and make the decision from that, a la:
    Set rs = dbs.OpenRecordSet("qryA")
    If rs.fldNotTaxable Then
    DoCmd.OpenQuery "qryNoTax"
    Else
    DoCmd.OpenQuery "qryWithTax"
    End If

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...Then...Else, Yes/No Procedure (A97SR2)

    The two query program in the event procedure worked PERFECTLY!!! Tested it over and over again. I am one happy camper and will put the Excedrin away. Thanks again! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Will probably return with my next dilemma.

Posting Permissions

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