How to requery form based upon value in combo box 1

[HockeyFan]

I have a form that is based upon a query. When it opens up it displays all of the records. This is working great.

I have a three combo boxes on this form. Box 1 is name, box 2 is project, and box 3 is workstream. They all have values in them that the user can select. This is working great.

I have a button that is pressed after the user selects their name, project and workstream, I would like the form to do a requery and base the results on the selected criteria from the combo boxes, instead of the initial query that the form is based upon. This is where the problem is at.

The button currently points to a macro that does a runcommand with a requery with no control specified. I was hoping this would accomplish what I wanted, but it's not working.

 

[dhookom]

You haven't suggested how the record source of the form is filtered by the combo boxes.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I have a query that the form is based upon and it displays all records when it opens. I have a row source for each combo box of course where it gets its values from, but i'm not real sure what you mean. Perhaps that is where i'm going wrong?
I have no form filters. My plan is to click the button, do a requery and only display records that match the criteria from my combo boxes.

 

[dhookom]

How does your form's record source know anything about your combo boxes or is this what you are asking to achieve?

Do you have names of combo boxes and names and data types of fields that match the combo boxes? Do you have the SQL view of the form's record source? Do you have the On Click event code of the button?

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

The form's record source which is a query, doesn't know anything about the combo boxes. My desire is to have the form open with all of the records displayed, and then the user can choose criteria from the combo boxes and then click the button, and for the form to do a requery based upon the combo boxes.

the first combo box is called PgmrId. it is the name of the person. However it is represented as a number field in my table.
The second combo box is called appscmb. It gets its values from a table, but the field type is text.
The third combo box is called workstream. It gets its values from a row source table also, but it is defined as text as well.
I can give you the sql of the record source, but it is very long. it is simply all of the available fields from the table, no critera or anything other than all of the available fields.

The on click action of the button points to a macro. in the macro I have a 'Runcommand' that does a refresh, and the next line is a requery, with no control listed.

Hope this all helps. thanks in advance.

 

[MajP]

most people will do this by modifying the sql using the controls as criteria, but I prefer to do this by using a subform and changing the link. See demo

http://www.4shared.com/office/XylOssOG/2combo.html

This requires little code and the subform automates all of this.

Here is all the code

Private Sub cmboCustomer_AfterUpdate()
  changeLink
End Sub


Public Sub changeLink()
  Dim strMaster As String
  Dim strChild As String
  
  If Not Trim(Me.cmboCustomer & " ") = "" Then
    strMaster = "cmboCustomer; "
    strChild = "customerID; "
  End If
  If Not Trim(Me.cmboEmployee & " ") = "" Then
    strMaster = strMaster & "cmboEmployee; "
    strChild = strChild & "EmployeeID; "
  End If
  
  'strip off the ending ;
  If Not strMaster = "" Then
    strMaster = Left(strMaster, Len(strMaster) - 2)
    strChild = Left(strChild, Len(strChild) - 2)
  End If
  
  'clear out the links
  Me.subFrmOrders.LinkChildFields = ""
  Me.subFrmOrders.LinkMasterFields = ""
  
  Me.subFrmOrders.LinkChildFields = strChild
  Me.subFrmOrders.LinkMasterFields = strMaster

End Sub

Private Sub cmboEmployee_AfterUpdate()
  changeLink
End Sub

But this will allow you to choose one or more combos, and the requery happens as soon as you change any of the combos.

 

[dhookom]

I prefer to modify the SQL property. MajP's solution works well if you have exact field value matches. You can't implement criteria like:
BETWEEN #3/1/2010# and #3/31/2010#
or
Like "SOR*"
or using multi-select list boxes.

Save your record source as a saved query with a name like [qselHockeyFan].

Public Function ChangeRecordSource()
  Dim strSQL as String
  Dim strWhere as String
  strWhere = "1=1 "
  If not IsNull(Me.PgmrID) Then
    strWhere = strWhere & " AND PgmrID=" & Me.PgmrID
  End If
  If Not IsNull(Me.appscmb) Then
    strWhere = strWhere & " AND appscmb=""" & Me.appscmb & """ "
  End If
  If Not IsNull(Me.workstream) Then
    strWhere = strWhere & " AND workstream=""" & Me.workstream & """ "
  End If
  strSQL = "SELECT * FROM qselHockyFan WHERE " & strWhere
  Me.RecordSource = strSQL
End Function

You can save this function in your form's module and call the function by entering =ChangeRecordSource() in the After Update event of the combo boxes.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

Thank you both for your suggestions. I think I'm going to try dhookoms however. I will leave a post and let everyone know how it works out after I complete it.

 

[MajP]

I should have caveatted mine. I would go that approach for a quick fix. I would do something like Duane's, but slightly modified. I would call the function to return the "where" string. Then I can use it as either as a filter or a sql where criteria in many places. I also like to be able to specify either "Match All" or "Match Any" using And or Or between the criteria
example

Public Function getFilter() As String
  On Error GoTo errLable
  Dim strType As String
  Dim strManufacturer As String
  Dim strSerial As String
  Dim strSet As String
  Dim strLocation As String
  Dim andOR As String
  Dim removeEnd As Integer
    'Set AND or OR
    If Me.framAndOr.Value = 1 Then
      andOR = "' OR "
      removeEnd = 4
    Else
      andOR = "' AND "
      removeEnd = 5
    End If
    
    If Not Trim(Me.qType1 & " ") = "" Then
        strType = "[TypeID] = '" & qType1 & andOR
    End If
      
    If Not Trim(Me.qSerial1 & " ") = "" Then
        strSerial = "[Serial] = '" & qSerial1 & andOR
    End If
    
    'ManID not working.  No idea why
    If Not Trim(Me.qMan1 & " ") = "" Then
        strManufacturer = "[ManID] = '" & qMan1 & andOR
    End If
    
    
    If Not Trim(Me.qSet1 & " ") = "" Then
        strSet = "[Set] = '" & qSet1 & andOR
    End If
    
    If Not Trim(Me.qLoc1 & " ") = "" Then
        strLocation = "[LocID] = '" & qLoc1 & andOR
    End If
    
    getFilter = strType + strSerial + strManufacturer + strSet + strLocation
    getFilter = Left(getFilter, Len(getFilter) - removeEnd)
   
  'debug.print "Filter Criteria: " & getFilter
  
  Exit Function
errLable:
  MsgBox Err.Number & "  " & Err.Description
End Function

 

[HockeyFan]

I have a meeting coming up for a couple of hours, so I won't get a change to try this out until later in the day. I'll let you know how it goes. thanks again.

 

[HockeyFan]

Duane, I was wondering if you could explain what you meant by, 'Save your record source as a saved query with a name like [qselHockeyFan]' Currenly, the record source of the form is a query.

Also I was wondering, since the user needs to make a selection from all three of the combo boxes and then click the button, can I put the =ChangeRecordSource() in the on-click action of the button, or put it in the code of the button instead?

 

[dhookom]

If your record source is already a saved query then substitute its name in place of qselhockeyFan.

Yes, you can use the function as the on-click of a button rather than each combo box. I would probably open the form with no records displayed as this might increase the performance. Possibly set the form's record source to:
[tt][blue]
SELECT * FROM [YourQueryName] WHERE 1=2;
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I am planning on keeping my form's record source opening up and displaying all the records, becuase that Is the whole point of what I am doing. I'm showing all of the records and then limiting them based upon the criteria they select in the combo boxes.

I did have a question for you if you didn't mind,... you said to 'save this function in your form's module'. Do all forms share the same module? I am only very slightly familiar with modules. I have one called basNavigate that is used by some other forms I have for some custom buttons. Can I simply put your function in that same module?

 

[HockeyFan]

I actually think I answered my own question because I put your function into my existing module and tested it and it does call that module when I click the button. However the code seems to have a problem becuase I am receiving an error when I click the button.
I opened the form and it displayed all the records as I wanted it to.
Then I selected some criteria in the combo boxes and clicked the button. upon clicking it, I get the error, 'Invalid use of Me keyword'. It has highlighted the first 'Me' in the expression 'Me.PgmrId'. Just as a side note, the persons name I selected in the combo box does not have any records currently entered.

 

[HockeyFan]

I did a test and noticed that I still get that same error, even If my selection from the combo boxes is for a record that I know is in there. It is on the 'If Not IsNull(Me.PgmrId) Then' line. It highlights the word 'me' as I mentioned above.
I greatly appreciate your assistance.

 

[dhookom]

You entered the code into a public module, not the form's module. If you are in form design, you should be able to select View->Code to open the form's module. The VBA window title bar should show something like "Microsoft Visual Basic - ... - Form_... (Code)"

If you do this correctly, then "Me." will be understood.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I changed what you said and tried it again. However this time I got a different error. It says,
'The expression ON Click you entered as the event property setting produced the following error: The record source 'SELECT * FROM PPFpredictorFrm2 WHERE 1=1 AND PgmrID=1 AND appscmb="Purple Core" AND workstream="Automation phase 1"' specified on this form or report does not exist.

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

I tried it several times and I am sure I have this record in my table. Any ideas?

 

[HockeyFan]

If it helps, I clicked the help button on the error message said the following,"
Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a module.
Expressions can resolve a user-defined function only if the function is declared as one of the following:
- A public function in a module
- A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.
"

could it have anything to do with it being called Public Function in my forms module?

 

[dhookom]

Please share your code. Also, use a break point in your code so you can step through it.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

My code is your code. but here it is. Are you referring to code from a different area?


Public Function ChangeRecordSource()
Dim strSQL As String
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.PgmrId) Then
strWhere = strWhere & " AND PgmrID=" & Me.PgmrId
End If
If Not IsNull(Me.appscmb) Then
strWhere = strWhere & " AND appscmb=""" & Me.appscmb & """ "
End If
If Not IsNull(Me.workstream) Then
strWhere = strWhere & " AND workstream=""" & Me.workstream & """ "
End If
strSQL = "SELECT * FROM PPFpredictorFrm2 WHERE " & strWhere
Me.RecordSource = strSQL
End Function