running multiple queries - help pls!

[kaushil]

Hi all,

Am really looking for some help with a database which has been dumped on me to complete and i'm not too experienced with MS Access 2000. So I have turned to the experts for help.

The db has a number of subforms, and I have a command button on each of the forms to calculate a total (from data from the 'master' form and also a external db) - this is no problem... works like a charm.

What I would like to do is have one 'command button' on a form which executes all queries and updates the info in the appropriate forms.


below is a list of all the queries... they are all basically the same however some of the variables are different...

Please help!!!!!!!!!


thx...kaushil


PS: apologies if the jargon may not be too correct - im a newbie to MSAccess 2000.

** Accounts sub-form **
----------------------------------------------------------------------------------
Private Sub Command45_Click()
Dim dbs As Database
Dim rs
Dim SQL_Str As String

SQL_Str = "SELECT Sum([tbl_Activity].[fld_ActualHours]) AS Total_Account_Hours " & _
"FROM tbl_Activity RIGHT JOIN tbl_Users ON tbl_Activity.fld_UserIDLink = tbl_Users.ID " & _
"WHERE tbl_Activity.fld_Billable = 'Yes' AND [tbl_Users].[fld_Department] = 'Accounts' AND tbl_Activity.fld_JobNumber = '" & [Acct Project Code] & "'"

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(SQL_Str)

If rs!Total_Account_Hours <> "" Then
AccountAdmin = rs!Total_Account_Hours
Else
[Acct Actual Account Admin Cost].Value = 0
End If

tmpAcct = [Forms]![Marketing Activity Manager]![Hourly Rates subform].Form![Accounts Admin Hourly Rate]
[Acct Actual Account Admin Cost] = tmpAcct * AccountAdmin

[Acct Total Actual Invoice Cost] = [Acct Actual Design Costs] + [Acct Actual Print Costs] + [Acct Actual Photography Cost] + [Acct Actual Invoice Processing Cost] + [Acct Actual Account Admin Cost]

Forms![Marketing Activity Manager]![Total Actual Accounts Cost] = [Acct Total Actual Invoice Cost]
Forms![Marketing Activity Manager]![Total Actual Cost] = Forms![Marketing Activity Manager]![Total Actual Writing Cost] + Forms![Marketing Activity Manager]![Total Actual Design Cost] + Forms![Marketing Activity Manager]![Total Actual Photopgrahy Cost] + Forms![Marketing Activity Manager]![Total Actual Print Cost] + Forms![Marketing Activity Manager]![Total Actual Accounts Cost] + Forms![Marketing Activity Manager]![Total Actual Traffic Admin Cost]

Me.Refresh
End Sub
----------------------------------------------------------------------------------
** Creative Writing sub-form **
----------------------------------------------------------------------------------
Private Sub Command12_Click()
Dim dbs As Database
Dim rs
Dim rs2
Dim SQL_Str As String
Dim SQL_Str2 As String

SQL_Str = "SELECT Sum([tbl_Activity].[fld_ActualHours]) AS Total_Writing_Hours " & _
"FROM tbl_Activity RIGHT JOIN tbl_Users ON tbl_Activity.fld_UserIDLink = tbl_Users.ID " & _
"WHERE tbl_Activity.fld_Billable = 'Yes' AND [tbl_Users].[fld_Department] = 'Writers' AND tbl_Activity.fld_JobNumber = '" & [CW Project Code] & "'"
SQL_Str2 = "SELECT Sum([tbl_Activity].[fld_ActualHours]) AS Total_Writing_Hours2 " & _
"FROM tbl_Activity RIGHT JOIN tbl_Users ON tbl_Activity.fld_UserIDLink = tbl_Users.ID " & _
"WHERE tbl_Activity.fld_Billable = 'Yes' AND [tbl_Users].[fld_Department] = 'Writer Admin' AND tbl_Activity.fld_JobNumber = '" & [CW Project Code] & "'"

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(SQL_Str)
Set rs2 = dbs.OpenRecordset(SQL_Str2)

If rs!Total_Writing_Hours <> "" Then
[CW Actual Writing Hours].Value = rs!Total_Writing_Hours
CWAdmin = rs2!Total_Writing_Hours2
Me.Refresh
Else
[CW Actual Writing Hours].Value = 0
[CW Actual Writing Cost].Value = 0
End If

cw1 = [Forms]![Marketing Activity Manager]![Hourly Rates subform].Form![Writing Hourly Rate]
cw2 = [CW Actual Writing Hours].Value
[CW Actual Writing Cost] = cw1 * cw2

CWAdmin2 = [Forms]![Marketing Activity Manager]![Hourly Rates subform].Form![Writing Admin Hourly Rate]
[CW Actual Writing Admin Cost] = CWAdmin * CWAdmin2
[CW Total Actual Writing Costs] = [CW Actual Writing Cost] + [CW Actual Writing Admin Cost] + [CW Actual Other Cost]

Forms![Marketing Activity Manager]![Total Actual Writing Cost] = [CW Total Actual Writing Costs]
Forms![Marketing Activity Manager]![Total Actual Cost] = Forms![Marketing Activity Manager]![Total Actual Writing Cost] + Forms![Marketing Activity Manager]![Total Actual Design Cost] + Forms![Marketing Activity Manager]![Total Actual Photopgrahy Cost] + Forms![Marketing Activity Manager]![Total Actual Print Cost] + Forms![Marketing Activity Manager]![Total Actual Accounts Cost] + Forms![Marketing Activity Manager]![Total Actual Traffic Admin Cost]
Me.Refresh
End Sub
----------------------------------------------------------------------------------
** Design sub-form **
----------------------------------------------------------------------------------
Private Sub Command22_Click()
Dim dbs As Database
Dim rs
Dim rs2
Dim SQL_Str As String
Dim SQL_Str2 As String

SQL_Str = "SELECT Sum([tbl_Activity].[fld_ActualHours]) AS Total_Design_Hours " & _
"FROM tbl_Activity RIGHT JOIN tbl_Users ON tbl_Activity.fld_UserIDLink = tbl_Users.ID " & _
"WHERE tbl_Activity.fld_Billable = 'Yes' AND [tbl_Users].[fld_Department] = 'Designers' AND tbl_Activity.fld_JobNumber = '" & [Des Project Code] & "'"

SQL_Str2 = "SELECT Sum([tbl_Activity].[fld_ActualHours]) AS Total_Design_Admin_Hours " & _
"FROM tbl_Activity RIGHT JOIN tbl_Users ON tbl_Activity.fld_UserIDLink = tbl_Users.ID " & _
"WHERE tbl_Activity.fld_Billable = 'Yes' AND [tbl_Users].[fld_Department] = 'Design Admin' AND tbl_Activity.fld_JobNumber = '" & [Des Project Code] & "'"


Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(SQL_Str)
Set rs2 = dbs.OpenRecordset(SQL_Str2)

If rs!Total_Design_Hours <> "" Then
[Des Actual Design Hours].Value = rs!Total_Design_Hours
DesignAdmin = rs2!Total_Design_Admin_Hours
Else
[Des Actual Design Hours].Value = 0
[Des Actual Design Admin Cost] = 0
End If

tmpDes = [Forms]![Marketing Activity Manager]![Hourly Rates subform].Form![Design Hourly Rate]
[Des Actual Design Cost] = tmpDes * [Des Actual Design Hours].Value

tmpDesAdmin = [Forms]![Marketing Activity Manager]![Hourly Rates subform].Form![Design Admin Hourly Rate]
[Des Actual Design Admin Cost] = tmpDesAdmin * DesignAdmin


[Des Total Actual Design Cost] = [Des Actual Design Cost] + [Des Actual Design Admin Cost] + [Des Actual Design Other Cost]

Me.Refresh
Forms![Marketing Activity Manager]![Total Actual Design Cost] = [Des Total Actual Design Cost]
Forms![Marketing Activity Manager]![Total Actual Cost] = Forms![Marketing Activity Manager]![Total Actual Writing Cost] + Forms![Marketing Activity Manager]![Total Actual Design Cost] + Forms![Marketing Activity Manager]![Total Actual Photopgrahy Cost] + Forms![Marketing Activity Manager]![Total Actual Print Cost] + Forms![Marketing Activity Manager]![Total Actual Accounts Cost] + Forms![Marketing Activity Manager]![Total Actual Traffic Admin Cost]

End Sub
----------------------------------------------------------------------------------
** Print Production sub-form **
----------------------------------------------------------------------------------
Private Sub Command62_Click()
Dim dbs As Database
Dim rs
Dim SQL_Str As String

SQL_Str = "SELECT Sum([tbl_Activity].[fld_ActualHours]) AS Total_Prt_Hours " & _
"FROM tbl_Activity RIGHT JOIN tbl_Users ON tbl_Activity.fld_UserIDLink = tbl_Users.ID " & _
"WHERE tbl_Activity.fld_Billable = 'Yes' AND [tbl_Users].[fld_Department] = 'Print Admin' AND tbl_Activity.fld_JobNumber = '" & [Prt Project Code] & "'"

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(SQL_Str)

If rs!Total_Prt_Hours <> "" Then
PrintAdmin = rs!Total_Prt_Hours
Else
[Prt Actual Print Admin Cost].Value = 0
End If

PrintAdminCost = [Forms]![Marketing Activity Manager]![Hourly Rates subform].Form![Print Admin Hourly Rate]
[Prt Actual Print Admin Cost] = PrintAdmin * PrintAdminCost
[Prt Total Actual Print Cost] = [Prt Actual Print Cost] + [Prt Actual Print Admin Cost] + [Prt Actual Print Other Cost]

Forms![Marketing Activity Manager]![Total Actual Print Cost] = [Prt Total Actual Print Cost]
Forms![Marketing Activity Manager]![Total Actual Cost] = Forms![Marketing Activity Manager]![Total Actual Writing Cost] + Forms![Marketing Activity Manager]![Total Actual Design Cost] + Forms![Marketing Activity Manager]![Total Actual Photopgrahy Cost] + Forms![Marketing Activity Manager]![Total Actual Print Cost] + Forms![Marketing Activity Manager]![Total Actual Accounts Cost] + Forms![Marketing Activity Manager]![Total Actual Traffic Admin Cost]
Me.Refresh
End Sub
----------------------------------------------------------------------------------

 

[dfitz]

If I understand the question correctly, you want one button to run all the queries. The queries are currently run from a number of buttons on different subforms. I also notice that the queries use values or update values on these subforms.
I can see no reason why the code would not work if you cut and pasted it into one button on the main form.
A few points to note:-
1.If any field is referenced on a form or sub form, the form or subform must be open.
2. You will need to check that any field referenced has the full 'title' [Forms]![Formname]![SubFormName]![FieldName]. I didn't notice any that were not complete but when you put the code together, you will get a popup box asking for a value for any that slipped through.
3. It would be helpful to tidy up the Dim statements when you bring it all together to avoid declaring the same variable more than once.
Hope this helps.
dave

 

[kaushil]

Heya Dave,

thanx for your reply... just got a quick question, when I copy and paste the code from each of the subforms do I need to change the name of each of the Dim statements, in order to make it run properly?

eg:
Dim rs (would turn to Dim Rs2, Rs3, Rs4...)?
Dim rs2
Dim SQL_Str As String
Dim SQL_Str2 As String

thanx for your help!

 

[dfitz]

That shouldn't be necessary bacause you are still running the sub programs sequentially. It may be worth setting any variables that carry over from one subprogram to another to a null value at the start of the sub program... jst to be on the safe side.
Dave

 

[kaushil]

all fixed now!!

thanx again for your help

cheers,kaushil.

 

[dfitz]

You're welcome Glad to help.
df