Say I've created many queries to search for specific records based on a department.
Then I want the user to type in a textbox the department that they are in, and open a form based on that query.
I know that I could do this by creating more than one query and use it as the forms control source, but I was wondereing if it was possible through vba so that I could have many queries applied to one standard form.
Could someone please help me to understand creating parameters for queries?
I have found the following code to help me do this, but I'm not sure what it all means.
Here's the code I am using to set parameters:
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set prm = New ADODB.Parameter
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
'Setup the Command object
.CommandText = "qryDeptSearch"
.CommandType = adCmdUnknown
'Create the parameter
Set prm = .CreateParameter("DeptSearch", adVarChar, adParamInput, 50)
'Set the parameter's value
.Parameters("DeptSearch") = "Marketing"
'Associate the command object with a connection
.ActiveConnection = CurrentProject.Connection
'Request the recordset
Set rs = .Execute
Basically I wanted to create a parameterized query which I could link to, open a form and bring back only the results based on the parameters. The field I want to set the parameters to is set to the name of a department e.g. Marketing, Finance, I.T. etc.
Is it possible to add more parameters for Finance and I.T. for example?
I've tried using SQL as well, the main problem is how do I link this SQL query to a form in vba so it only brings back that data.
Obviously I'd put the SQL into a variable, but do I simply do an OpenForm command and place the name of the variable into the filter properties or maybe the where properties to open the form with that specified data?
I had planned on creating some kind of search, using a form with a textbox and command button.
When they enter "Marketing" for example, and click the button it will open up the form, with all records relating to marketing. But they could put any of the 7 or 8 departments into the texbox to get the related department records on that form.
But the problem I can see us that I'm going to create 7 or 8 queries to bring back the records based on one of the 7 or 8 departments.
But I don't want to have to create say 7 or 8 forms that are exactly the same, and assign the queries to one of those forms. It would be much better if I had just one form, and link the 7 or 8 SQL queries to get the records.
What you are trying to do is really simple and doesn't require VB. The following query:
Select Col1, Col2
WHERE Col1 = Forms!MyForm!Textbox
will return the records where Col1 = the entry in Forms!MyForm!Textbox. If we suppose that Col1 is your team field then if you type "marketing" and run the query then the result will be all rows with Marketing in Col1. Change your textbox to "Finance" and run the query again and it will be all rows with Finance in Col1.
Why don't you use what I have which does the same. I use a ComboBox, set it to list all the choices a user can make (not having to guess or misspell). Then, as soon as they click their choice in the ComboBox it opens the Form with the data requested.
Private Sub FindPO_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOrderStatus"
stLinkCriteria = "[PONumber]=" & "'" & Me![FindPO] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
The above is the code in the ComboBox. The person selects the PO Number from the ComboBox, it then pulls up the frmOrderStatus with the criteria that is set.
SELECT DISTINCT OrderStatus.PONumber, OrderStatus.VendorName
The above is the SQL that goes with it. I used DISTINCT to prevent it from showing all duplicates in the ComboBox, however, they show in the form.
Give it a whirl. TextBoxes are good, but you run into people typing in Incorrect words, some just can't spell well, not to mention, you HAVE to know what Departments, or whatever you can select from. The ComboBox shows you the available choices. Just to make it real simple.
Hope this helps you out some and have a nice one,