I'm have never created a VBA module before this my first attempt so please bear with me
I'm currently using Access 2000
I'm using VBA to run 2 Queries
Query 1 prompts user to enter Billing Date in Table1
Begin Date
<=[Enter Billing Date]
End Date
>=[Enter Billing Date]
This outputs 1 record {which is correct}
I need to put 1 of the fields [Daily Rate] into a variable
I also need to keep the Billing Date as a variable for the 2nd query
How do I do this?
Query 2 is an update Query for Table2 that uses Billing Date to get multiple records
I need to multiply [Table2]![possible] * Daily Rate to update [Table2]![billing_amt]
Here is what I have so far in the VBA module
Option Compare Database
Private BillingDate As Date
Private DailyRate As Double
Sub Update_Billing()
DoCmd.OpenQuery "Get Daily Rate", acViewNormal
'Prompts me for Billing Date in Query
DoCmd.SetWarnings False 'Turn Warnings Off
DoCmd.OpenQuery "Monthly Billing", acViewNormal
'Prompts me for Table1!Daily Rate
'Prompts for Billing Date
DoCmd.SetWarnings True 'Turn Warnings On
End Sub
I want to get rid of the prompts for the 2nd query... Am I going in the right direction??
Any help is appreciated
Thank you
Jeff
I'm currently using Access 2000
I'm using VBA to run 2 Queries
Query 1 prompts user to enter Billing Date in Table1
Begin Date
<=[Enter Billing Date]
End Date
>=[Enter Billing Date]
This outputs 1 record {which is correct}
I need to put 1 of the fields [Daily Rate] into a variable
I also need to keep the Billing Date as a variable for the 2nd query
How do I do this?
Query 2 is an update Query for Table2 that uses Billing Date to get multiple records
I need to multiply [Table2]![possible] * Daily Rate to update [Table2]![billing_amt]
Here is what I have so far in the VBA module
Option Compare Database
Private BillingDate As Date
Private DailyRate As Double
Sub Update_Billing()
DoCmd.OpenQuery "Get Daily Rate", acViewNormal
'Prompts me for Billing Date in Query
DoCmd.SetWarnings False 'Turn Warnings Off
DoCmd.OpenQuery "Monthly Billing", acViewNormal
'Prompts me for Table1!Daily Rate
'Prompts for Billing Date
DoCmd.SetWarnings True 'Turn Warnings On
End Sub
I want to get rid of the prompts for the 2nd query... Am I going in the right direction??
Any help is appreciated
Thank you
Jeff