Display Title on Datasheet View's Title Bar

[joesmithf1]

Hi, I have a script that looks like this:



For Each qdf In dbs.querydefs
If qdf.NAME = "qryBiweeklyReports" Then
dbs.querydefs.Delete qdf.NAME
dbs.querydefs.Refresh
End If
Next qdf

Select Case Category
Case "Health Detail"
SQL = "SELECT Biweekly_Temp_Table.EMP_ID, Biweekly_Temp_Table.FORMAT_NM, First(Biweekly_Temp_Table.DEDPLAN_CD) AS FirstOfDEDPLAN_CD, " & _
"First(Biweekly_Temp_Table.DEDTYPE_CD) AS FirstOfDEDTYPE_CD, Sum(Biweekly_Temp_Table.[Employer Amt]) AS [SumOfEmployer Amt], " & _
"Sum(Biweekly_Temp_Table.[Employer Actl]) AS [SumOfEmployer Actl], Sum(Biweekly_Temp_Table.[Admin Amt]) AS [SumOfAdmin Amt], " & _
"Sum(Biweekly_Temp_Table.[Admin Actl]) AS [SumOfAdmin Actl], Sum(Biweekly_Temp_Table.[Employee Amt]) AS [SumOfEmployee Amt], " & _
"Sum(Biweekly_Temp_Table.[Employee Actl]) AS [SumOfEmployee Actl], Biweekly_Temp_Table.STATUS, Biweekly_Temp_Table.AGENCY, " & _
"Biweekly_Temp_Table.TITLE, Biweekly_Temp_Table.RepUnit, Biweekly_Temp_Table.Type, Biweekly_Temp_Table.LeftType, " & _
"Biweekly_Temp_Table.SumOfNBR, First(Biweekly_Temp_Table.Tier) AS FirstOfTier, First(Biweekly_Temp_Table.Carrier) AS FirstOfCarrier, Plan.PlanDesc " & _
"FROM Biweekly_Temp_Table LEFT JOIN Plan ON (Biweekly_Temp_Table.DEDPLAN_CD = Plan.Plan) AND (Biweekly_Temp_Table.LeftType = Plan.Type) " & _
"GROUP BY Biweekly_Temp_Table.EMP_ID, Biweekly_Temp_Table.FORMAT_NM, Biweekly_Temp_Table.STATUS, Biweekly_Temp_Table.AGENCY, Biweekly_Temp_Table.TITLE, " & _
"Biweekly_Temp_Table.RepUnit, Biweekly_Temp_Table.Type, Biweekly_Temp_Table.LeftType, Biweekly_Temp_Table.SumOfNBR, Plan.PlanDesc " & _
"HAVING (((Biweekly_Temp_Table.Type)='01') AND ((First(Biweekly_Temp_Table.Tier))<>'17' And (First(Biweekly_Temp_Table.Tier))<>'00'));"

Case "Health Discrepancy"
SQL = "SELECT qryDedparmDedetail" & payp & ".EMP_ID, qryDedparmDedetail" & payp & ".[Employer Amt], qryDedparmDedetail" & payp & ".[Employer Actl], " & _
"qryDedparmDedetail" & payp & ".[Admin Amt], qryDedparmDedetail" & payp & ".[Admin Actl], qryDedparmDedetail" & payp & ".[Employee Amt], qryDedparmDedetail" & payp & ".[Employee Actl], " & _
"qryDedparmDedetail" & payp & ".FirstOfSTATUS, qryDedparmDedetail" & payp & ".FirstOfTITLE, qryDedparmDedetail" & payp & ".LeftType " & _
"FROM qryDedparmDedetail" & payp & " " & _
"WHERE (((qryDedparmDedetail" & payp & ".[Employer Amt])


Set qdf = dbs.CreateQueryDef("qryBiweeklyReports", SQL)

DoCmd.OpenForm "FrmBiweeklyReports"
DoCmd.OpenQuery "qryBiweeklyReports"

Users are given the option to run either the "Health Detail" or "Health Discrepancy." The results will show in Datasheet View. Right now, when the datasheet view window opens, the 'title' bar will show "qryBiweeklyReports" because this is what I named the query. The question is, what can I change to have the datasheet view title bar display whatever the users have selected(Health Detail or Health Discrepancy), rather than the 'generic' qryBiweeklyReports title?

Regards,
Joe

 

[rainman89]

Do you have the datasheet as a form? if so you can set the caption of the form to whatever you need it to be
this is done in the onload event of the form.
you would either a case statement or an if statement to show the change.
Me.Caption = "your new title"
do a search on if statements or case statements if u cant figure it out. u should find your answer there