VBA first timer needs help

[visualjoy]

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 &quot;Get Daily Rate&quot;, acViewNormal
'Prompts me for Billing Date in Query
DoCmd.SetWarnings False 'Turn Warnings Off
DoCmd.OpenQuery &quot;Monthly Billing&quot;, 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

 

[mikeyb540]

Is there a parameter that needs to be passed from the Monthly Billing Query?

 

[visualjoy]

No Parameters need to be passed from the &quot;Monthly Billing&quot; query. I just use the &quot;Monthly Billing&quot; query to update records based on the Billing Date. I need to have the Daily Rate from the &quot;Get Daily Rate&quot; saved into a Variable then use it in the &quot;Monthly Billing&quot; to multiply by number of days... And I would like to use the Billing Date without getting prompted twice.

Thanx
Jeff