Replies

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! Will probably return with my next dilemma.

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?

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.

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

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.

Sometimes I think that people try to make things a lot more difficult than they need to be. I stepped back and looked at what I was trying to achieve with my limited programming abilities. I tried to do it with relatively simple queries. Sure enough, I got just what I needed with one append and two delete queries. Worked the SQLs into the Do/Loop and If/Then statements and got just what I needed. Thanks for politely reminding me to refocus.

Thanks for your assistance and patience. Quite often what a person is thinking and what they said are not properly communicated or understood by another. It is now closer to what I want. Thank you again for your help.

Reattach attachment

I have attached a small database with two forms – Continuous & Datasheet. The words that I have entered further explain and show what I am trying to achieve.

Is there any way to get into the inner workings of the system and figure out how Microsoft made it happen? What I am trying to accomplish is to duplicate the default cursor movement that occurs in datasheet view. Thanks.

Thank you!

It works just fine!
Thank you very much for your assistance.
You did it again.

The malfunction was caused by the defect midway between my two ears. Under the Action Arguments I had typed: Function Name: InitApplication(). After removing the (Function Name:) it worked great.

You may now proceed to ROFL.

Thank you for any and all help provided.

First I must admit that I am not a programming guru.

Attempted to duplicate the KB article (Q245746). Everything was accepted by the program but It did not return the desired results. Went through the Step-by-Step Example on a copy of my program and when starting the program, was presented with the following message box:

(your program) can’t find the name ‘Function’ you entered in the expression.

You may have specified a control that wasn’t on the current object without specifying the correct form or report context.

To refer to a control on another form or report, precede the control name with the name of a collection, usually either Forms or Reports, and the name of the form or report to which the control belongs. For example, Forms![Products]![Units In Stock].

Was this example designed for A2000 and does not work on A97?
Was there an assumption of additional information that needs to be entered in order for the function to work?
I don’t know.

Thanks again.

Got the desired results from the table. You guys are great!!! Thanks a lot . The more I learn, the more I realize how little I know.

I entered the expression in the criteria for the query. Of the 2684 records in the table, the query only returned one record. That record was the only one that had 6 characters total in the field.