Select criteria in vba to reference loaded form Results

I have a form in my database that I want to refer to in VBA code to limit the records it returns.

I have a calendar form that pulls data from a SchedulingTable called ROSchedule, in this table there is a BUID (Business Unti ID) column.
The calendar is launched from a form that has a drop down list where the user can select a particular business Unit, this form is called BusinessUnit.
All of the queries filter out the records specific to the business unit in the form BusinessUnit
Sample Query SELECT RoScheduling.ArrivedDate, RoScheduling.ABSRO, RoScheduling.SchedulingHours, RoScheduling.Customer, RoScheduling.BUID FROM RoScheduling WHERE (((RoScheduling.BUID)=[Forms]![BusinessUnit]![BUID]));
but when the calendar loads the VB select Where doesn't seem to filter out the records.
Here is the VB code that works without the BUID filter. I need to be able to have the VB code filter out the records the same way the Sample Query above does. I only want the records that match the BUID in the BusinessUnit form.
Any help with the VBA code would be greatly appreciated, I'm not that good with the VBA side of Access so please don't make any assumptions that I know what I'm doing with it.
Thanks
Randy

Private Sub cbo_Month_Change()
'Month has changed so we have to recalculate the form
Dim cnt As Long
'First step is to find out what the month is composed of

Dim DaysInMonth As Long
DaysInMonth = DateDiff("d", cbo_Month, DateAdd("m", 1, cbo_Month))
Dim FirstDay As Long
FirstDay = Weekday(cbo_Month, vbMonday)

'Hide the first few forms that dont count
For cnt = 1 To 6
Me.Controls("SF" & CStr(cnt)).Visible = True
Next
If FirstDay > 1 Then
For cnt = 1 To FirstDay - 1
Me.Controls("SF" & CStr(cnt)).Visible = False
Next
End If

'Hide the last few forms that dont count
For cnt = 28 To 37
Me.Controls("SF" & CStr(cnt)).Visible = True
Next
If FirstDay < 7 Or DaysInMonth < 31 Then
For cnt = FirstDay + DaysInMonth To 37
Me.Controls("SF" & CStr(cnt)).Visible = False
Next
End If
Dim frm As Form
Dim DayOfMonth As Long
'Now we need to set the record source for each day.....
For cnt = FirstDay To ((DaysInMonth + FirstDay) - 1)
DayOfMonth = (cnt - FirstDay) + 1
Set frm = Forms!frm_Calendar.Controls("SF" & CStr(cnt)).Form
frm.RecordSource = "SELECT ArrivedDate,ABSRO,SchedulingHours,Customer FROM ROScheduling WHERE ArrivedDate = #" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_Month, "mmm/yyyy") & "#"
frm.lbl_Date.Caption = CStr(DayOfMonth)
frm.Controls("Date").DefaultValue = "#" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_Month, "mmm/yyyy") & "#"
Me.Controls("SF" & CStr(cnt)).Requery
Next

End Sub

Please refer to the attached file. The form of concern is frmAssessmentInput.

Select the appropriate criteria per instructions (in red), including entries on the continuous subform. Then click the "Input data" command button. A 2nd form opens (frmObservations) that contains a textbox labeled DocumentStreamID, and this textbox SHOULD contain the value from the corresponding DocumentStreamID textbox contained in the master form, where you clicked the command button. However, even though the sources are linked properly, the value doesn't pass if you click the command button immediately after adding a new record to the continuous form; it only passes if 1) the master form is closed and reopened first, or 2) if you close the 2nd form, toggle to design view and back to form view for the master form, and then click the "Input data" button again.

What is going on here? Given that the value passes once another procedure is run, do you think this a requery problem? I tried placing a VBA control requery in the 2nd form's On Current and On Load events, but this didn't solve things for me so I took out those event procedures (code could have been wrong though).

Note: This is condensed version for the benefit of the board. For much more detailed info, read here.

Steps to take BEFORE you post anything
Debug the problem. Don't forget to verify the spelling and referencing! (See below for tips) Get all information about the error. See the next list to see what you need to know before posting. Consult Access Help files. This is very important, especially in discovering correct keywords and terminology that will make your search easier. Search Google. Search Usenets. (You can use Google Groups for that; search Groups separately from the Google engine, though) Search the Board. If you are still stuck, go ahead and post.
What to post
Version of Access Error Number and message, if included. A concise description of the problem. The desired solution. Steps you've already taken to fix the problems. May be helpful to mention how you searched the web, and what keywords you used. Post the relevant code, highlighting the offending line where error is risen. If the error is not from VBA, but from Access (e.g. you get a pop up error message), be sure to specify exactly what steps you took to rise the error. If this is a bad query, it may be helpful to show the recordset and tell what recordset you want to see.
Debugging Tips

List of Contents:How to debug VBA codeTo step through the code To inspect the variables within the code While stepping through the code, you can... How to debug queries and/or functions on the forms within Query BuilderFor queries or controls on form, use "Build..." whenever possible! Always check the queries by clicking on View. For bad queries... Use Access Help to get the right keywords for your search

How to debug VBA code
To step through the code

In Visual Basic Editor (VBE), go to the relevant snippet's heading (e.g. the line starting with Private Sub or Private Function or similar), and click on the gray margin to left of the window to activate a breakpoint. If this is in a module, you can execute this by pressing the "play" button. If this is for a form, you need to switch the form from "Design View" to "Form View" and attempt to fire the event (e.g. editing a value on the form or make a bogus record).

When the event fires, you will be immediately taken to VBE and the line at the breakpoint you just created will be highlighted. You now can then step through the code line by line by pressing F8. Alternatively, you can execute the code normally by pressing F5. If you never ever see the VBE, it means that event didn't fire at all.

To inspect the variables within the code

You can mouse over the variables to see what values are as you move through the code. This is very helpful in checking whether variables are behaving as expected.

Alternatively, you can add a line in your code:

	Code:
	Debug.Print MyVariableName

. This is useful for some situations where you cannot step through the code and get same results (e.g. VBE will choke on any acRunCommand if you try to step through those lines). In this case, the values stored in the variables get printed out to the Immediate Windows. This is the tiny windows to the bottom of the code windows.

While stepping through the code, you can...

Use the immediate windows to get more information that is not available from mouseover. You can do so by issuing the command in the immediate windows:

	Code:
	?MyVariableName

Which will then print your variable's value. You also can execute another code not otherwise included in your routines. For example, you can type:

	Code:
	MyVariableName=SomeValue

And your variable will be changed accordingly. This is useful in identifying problems that are intermittent due to bad values but does not show up if it has good values.

This is also useful for seeing variables or data that isn't within the scope of the routine, as well.
How to debug queries and/or functions on the forms within Query Builder
For queries or controls on form, use "Build..." whenever possible!

A common problem that arise is referencing the forms/subform controls and using function correctly. Instead of trial and error, you can use Build... to give you the correct references. For example, suppose you want to set a query's criteria to a textbox in a subform. You would right click on the Criteria field in Query Builder and select "Build..." which you will then get the Expression Helper. You would then navigate to "Loaded Forms" -> "MainFormName" -> "SubformName" -> "ControlName" then double click the control's name and it will give you the full reference without any hassle or errors.

This is also useful in helping building functions within the query. Experiment with it a bit to see how it work would be much more productive than me trying to describe it.

Always check the queries by clicking on View.

This is very useful in ensuring that you are getting the expected resultset. Even though you may have made 100s of similar queries before, you can never know if the query will behave the same with different data or database. This also can tell you what to post any problems that may not be as self-evident if the query is for a control on the form.

For bad queries...

If you're getting errors that seems entirely random or unrelated to what you are encountering in QBE, it usually helps to start by deleting one column at a time, executing the query and see if it raises the same error, and if so, repeat the process until error no longer raises, then you know that the problem was with the previous column you just removed from the query.

Alternatively, you can start out with a simple query, then add one column or criteria or function at a time to ensure that everything is correct.
Use Access Help to get the right keywords for your search

A recurring problem with searching is that if you do not have the right keyword, searching anywhere, be it on Google, Usenet, or the board is basically going to be a hunt for a needle in a haystack.

The first step in finding what you should be looking for is to consult the Access Help and the Properties Windows. By starting with what you already know (for example, you want to do something with AfterUpdate event), you can read the help on AfterUpdate, which it may link to other terms that are associated with the event and provide you with the correct keywords to use for searching.




I hope this has been helpful.

Hello everyone

I have an issue with my database. The database works fine in 2003. However when I try run it in 2007, one of the reports based on a cross-tab query (that works fine in 2007) would not open and comes up with a 3070 error ("Microsoft Access Database engine does not recognize " as a valid field name or expression"). It does not specify a field but only ".

The interesting thing is that there are other two dynamic crosstab reports that do work just fine. I'm puzzled.

Query Code:

	Code:
	PARAMETERS [Forms]![EmployeeSalesDialogBoxProjectID]![BeginningDate] DateTime,
[Forms]![EmployeeSalesDialogBoxProjectID]![EndingDate] DateTime;
TRANSFORM Sum([REPORT BY EMPLOYEE 3MTH OUT by Project].SumOfWorkload) AS SumOfSumOfWorkload
SELECT [REPORT BY EMPLOYEE 3MTH OUT by Project].Type AS Manager, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name],
[ProjectID] & " " & [Reference Name] AS Project
FROM [REPORT BY EMPLOYEE 3MTH OUT by Project] LEFT JOIN AccessImport ON [REPORT BY EMPLOYEE 3MTH OUT by Project].ProjectID =
AccessImport.[Project #]
WHERE ((([REPORT BY EMPLOYEE 3MTH OUT by Project].Week) Between [Forms]![EmployeeSalesDialogBoxProjectID]![BeginningDate] And
[Forms]![EmployeeSalesDialogBoxProjectID]![EndingDate]))
GROUP BY [REPORT BY EMPLOYEE 3MTH OUT by Project].Type, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID]
& " " & [Reference Name]
ORDER BY [REPORT BY EMPLOYEE 3MTH OUT by Project].Type, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID]
& " " & [Reference Name]
PIVOT [REPORT BY EMPLOYEE 3MTH OUT by Project].Week;


the vba fo cross tab:


	Code:
	' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 13
' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset
' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long
Private Sub InitVars()
 
Dim intX As Integer
' Initialize lngReportTotal variable.
lngReportTotal = 0
 
' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX
End Sub
 
Private Function xtabCnulls(varX As Variant)
 
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.
 
Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX
 
' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
End If
 
End Sub
 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
 
Dim intX As Integer
Dim lngRowTotal As Long
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0
 
For intX = 4 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX
 
End If
End Sub
 
Private Sub Detail_Retreat()
' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious
End Sub
 
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
 
Dim intX As Integer
 
' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX
 
' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub
 
Private Sub Report_Close()
 
On Error Resume Next
' Close recordset.
rstReport.Close
 
End Sub
 
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
 
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("EmployeeSalesDialogBoxProjectID")) Then
Cancel = True
MsgBox "To preview or print this report, you must open " _
& "Date Period Dialog in Form view.", vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If
 
' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!EmployeeSalesDialogBoxProjectID
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("EmployeeSalesProjectID")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!EmployeeSalesDialogBoxProjectID!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!EmployeeSalesDialogBoxProjectID!EndingDate") _
= frm!EndingDate
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
 
' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
 
End Sub
 
 
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub

Any help will be appeciated!
Here's the cross tab query code:


Not finding an answer? Try a Google search.