Command button-Build Query/Report

[jnb22019]

https://www.accessforums.net/queries...ria-15080.html



Found answer on another Thread, I was over complicating things.



I need some help with VBA, Access 2003. Basically I have a Database that track temporary employees: Names, Agency, line employee occupies, rate of pay and hours worked/week.

I want to create a command button that when I click on it, a Query or Report will be created after selecting "Agency" and "Week", and writing a name for the Query/Report, showing the following:

Agency
Temps who work for the agency
Line occupied
Hours worked that week.

I know almost nothing of VBA, if someone would be able to give me some "general" code from which I can start figure out.

[jzwp11]

The approach I generally use is to create a query that has all of the information I need and base a report on that query.

Then create a unbound form for putting in your selection criteria. Add a command button to that form that opens the report filtered on the criteria provided. The criteria would go in the WHERE clause portion of the do command used to open the report.

I have attached a simple example that illustrates the technique. As a note, if you are using dates, the date values supplied via the form must be enclosed in # signs (see the code behind the button on the form). If the form is supplying numerical values, they do not need to be enclosed in any special characters. For text values, they have to be enclosed by single apostrophes.

BTW, if you are using the word "name" as a field name, I would strongly suggest changing it. The word name is a reserved word in Access and it is best not to use reserved words as field or table names. Additionally, it is best not to use special characters in your table or field names.

[jnb22019]

Thank you, I will try out. I already knew the "Base Query" and the form but have no idea on the code.

[jzwp11]

You're welcome. Please post back with any questions.

[jnb22019]

Not working out, obviously my fault since I don't know much about VBA. I attached a "Sample" database.

What I want is to when I click the command button for the "BaseQuery" to be "filtered" by the selection I make in the two Combo boxes and to name the report with what I put in the text.

Hopefully attached database will help out.

[jnb22019]

I have managed to come this far, see below.

Private Sub cmdBuildQuery_Click()

Dim queryName As String
Dim queryAgency As String
Dim queryWeek As String

Dim Result

txtQueryName.SetFocus
queryName = txtQueryName.Text

cboWeek.SetFocus
queryWeek = cboWeek.Text

cboAgency.SetFocus
queryAgency = cboAgency.Text


If Len(Trim(queryName)) = 0 Or _
Len(Trim(queryAgency)) = 0 Or _
Len(Trim(queryWeek)) = 0 Then

MsgBox "You did not make all of the appropiate selections"

End If
'Build Query

Result = Application.CurrentDb.CreateQueryDef(queryName, _
"SELECT ReqInfo.CostCenter, " & _
"ReqInfo.Req, " & _
"ReqInfo.TempID, " & _
"TempInfo.TempName, " & _
"TempInfo.Agency, " & _
"ReqInfo.Title, " & _
"TitleInfo.BillRate, " & _
"HoursWeek.Week WHERE " & _
"(((HoursWeek.Week)=" & _
queryWeek & " ) AND " & _
"((TempInfo.Agency) =""" & _
queryAgency & """))")

MsgBox "Your Query was Successfuly Created!"

End Sub

Code runs from beginning to end and Query is created based on the name I write in the text, but shows no values whatsoever.

[jzwp11]

So, the query is created successfully but yields no results? Does you data meet all of the criteria you included in the query? Since you are using AND each criteria must be satisfied in order to return any results. Additionally, is HoursWeek.Week a number field or a text field?