Hello,
This is a long question- please read to the end. I really need help!
Users want to see a simple form where for each vendor in the database, they can see the total budgeted amount, spent amount, and left over amount- per vendor, per budget code (BC), and per object code (OC).
They need the flexibility to set the budget code and object code filters or to leave them blank and just see the total for the vendor for the year.
The fiscal year is chosen from a listbox- FiscalYearlistbox, BC and OC typed by user in textboxes- OCtoSearch and OCtoSearch.
My goal is to display the Budgeted, Spent, and Left Over numbers in textboxes. Budgeted and Spent are to be populated from sql select statements.
Here is my try for Budgeted. In the OnCurrent event of the form, I create the sql statement with part 1 which is the main select, and part 2, which is additional where criteria if the user chooses to specify BC and/or OC. Then I set this 2-part string as the SQL statement of a placeholder query. Then I use dlookup on this query (returns single field with single record) to set the control source of the BudgetedSum textboxt. Or at least that's what I want to do- I am not successful. The value that I get in BudgetedSum is #Name? for all vendors.
For some vendors, there is no budgeted value- they weren't entered in the budget table at the beginning of the year, we just bought stuff from them later. For these vendors, I understand that there would be no value in BudgetedSum or an error value or whatever. But why shouldn't I get a legitimate value when I scroll to those vendors who do have a budgeted amount? The sql statement that I wrote does return the right values when I run it as a stored query. Why doesn't the the control source get set correctly?
Here is my code. I would be really grateful if someone could tell me if I am being blind to something completely obvious here.
Thank you.
This is a long question- please read to the end. I really need help!
Users want to see a simple form where for each vendor in the database, they can see the total budgeted amount, spent amount, and left over amount- per vendor, per budget code (BC), and per object code (OC).
They need the flexibility to set the budget code and object code filters or to leave them blank and just see the total for the vendor for the year.
The fiscal year is chosen from a listbox- FiscalYearlistbox, BC and OC typed by user in textboxes- OCtoSearch and OCtoSearch.
My goal is to display the Budgeted, Spent, and Left Over numbers in textboxes. Budgeted and Spent are to be populated from sql select statements.
Here is my try for Budgeted. In the OnCurrent event of the form, I create the sql statement with part 1 which is the main select, and part 2, which is additional where criteria if the user chooses to specify BC and/or OC. Then I set this 2-part string as the SQL statement of a placeholder query. Then I use dlookup on this query (returns single field with single record) to set the control source of the BudgetedSum textboxt. Or at least that's what I want to do- I am not successful. The value that I get in BudgetedSum is #Name? for all vendors.
For some vendors, there is no budgeted value- they weren't entered in the budget table at the beginning of the year, we just bought stuff from them later. For these vendors, I understand that there would be no value in BudgetedSum or an error value or whatever. But why shouldn't I get a legitimate value when I scroll to those vendors who do have a budgeted amount? The sql statement that I wrote does return the right values when I run it as a stored query. Why doesn't the the control source get set correctly?
Here is my code. I would be really grateful if someone could tell me if I am being blind to something completely obvious here.
PHP:
'part 1 of SQL statement: always filter by fiscal year
'----------------------------------------
Dim str_part1 As String
str_part1 = "select sum(budgetAmount) as SumBudgeted from (Select budgetAmount, fiscalyearend, budgetcode, objectcode from budgetedOTPS inner join " & _
"(Select servicesubcat, budgetcode from Services inner join budgetcodes " & _
"on services.agreementtype= budgetcodes.agreementtype where budgetcodes.reportingcat " & _
"like 'OTHER DIRECT COSTS') as A on BudgetedOTPS.Servicesubcat= A.ServiceSubcat) as B inner join " & _
"FYStartDate on B.FiscalYearEnd= FYStartDate.FyEnddate " & _
"where FYstartdate.FYname like '" & Me.FiscalYearlistbox.Value & "' "
'----------------------------------------
'part 2 of SQL statement: check if any if the other filters are not blank
'----------------------------------------
Dim str_part2 As String
str_part2 = ""
If Len(Me.OCtoSearch & vbNullString) > 0 Then
str_part2 = " and B.ObjectCode like " & Chr(34) & Me.OCtoSearch & Chr(34) & " AND "
End If
If Len(Me.BCtoSearch & vbNullString) > 0 Then
str_part2 = str_part2 & " and B.BudgetCode like " & Chr(34) & Me.BCtoSearch & Chr(34) & " AND "
End If
If Right(str_part2, 5) = " AND " Then
str_part2 = Left(strWhere, Len(strWhere) - 5) & ";"
End If
If str_part2 = "" Or str_part2 = " AND " Then
str_part2 = ";"
End If
'----------------------------------------
str_budgeted = str_part1 & str_part2 'this is the string that will replace the sql of placeholder query
Dim qdf_budgetedsum As DAO.QueryDef
Set qdf_budgetedsum = CurrentDb.QueryDefs!PURCHASING_budgeted_sum_placeholderquery
qdf_budgetedsum.SQL = str_budgeted
'set value of budgeted sum field
Me.BudgetedSum.ControlSource = DLookup("SumBUdgeted", "PURCHASING_budgeted_sum_placeholderquery")Thank you.
