Can't get my query filter command button to work.

[Manolo123]

I have a main form with 2 subforms. On the main form I have 3 unbound combo boxes (Item #, date received and date shipped)that I have the user select and depending on there selection the two subforms should display the matching data. The first subform contains the "received" information based a query and the second subform contains the "shipped" information based on a different query. I have linked the subforms using the Link Master/Child fields dialouge box. For example, if the user selects Item # 101 and received date 1/1/10 and shipped date 1/31/10. I would like all the records for item 101 that were received on or after 1/1/10 and shipped on or before 1/31/10 to appear. But all I get is a compile error. The Main form is call "Inventory" and the two subforms are called "Received Information" and "Shipped Information". Any help would be greatly appreciated.

Thank you

 

[dhookom]

You can't use "Link Master/Child" with "on or after 1/1/10 and shipped on or before 1/31/10". Links must match exact values. I typically use the after update event of the combo boxes to run some code that updates the SQL property of the subform(s). You would need to substitute your control and fields into code like:

Public Sub UpdateFilter()
   Dim strSQL as String
   Dim strWhere as String
   strWhere = " Where 1=1 "
   strSQL = "SELECT * FROM qryReceived "
   If Not IsNull(Me.cboItemNum) Then
      strWhere = strWhere & " AND ItemNum = " & _
         Me.cboItemNum & " "
   End If
   If Not IsNull(Me.cboRecvd) Then
      strWhere = strWhere & " AND RecvdDate >= #" & _
         Me.cboRecvd & "# "
   End If
   If Not IsNull(Me.cboShipped) Then
      strWhere = strWhere & " AND ShipDate <= #" & _
         Me.cboShipped & "# "
   End If
   strSQL = strSQL & strWhere & " ORDER BY ..."
   Me.[Received Information].Form.RecordSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[Manolo123]

Duane,

First I want to thank you for your quick reply. I was stuck on this for about 6 hours yesterday before I decided to go to a higher power. At least you confirmed that what I was doing would never work. However I am still a bit confused as to where I should place your code. Here is what I currently have for the first date button after update event:

Private Sub Combo48_AfterUpdate()

If Not IsNull(Me.Combo21) Then
Me.Combo54 = Null
Me.Combo54.Visible = True

Else

Me.Combo21.SetFocus
Me.Combo48 = Null
Me.Combo54 = Null
Me.Combo48.Visible = False
Me.Combo54.Visible = False
End If

End Sub

Question: Should I replace this with what you sent me? and what should my requery statement look like?

I am sorry but when it comes to this code, I am lost.

Thanks again!

 

[dhookom]

I guess I would modify my code to be a public function in the form's module. You would also need to change names of stuff as I mentioned.

Then, you could enter the function name in the after update property of any controls you want to use for filtering.

After Update: =UpdateFilter()

Duane
Hook'D on Access
MS Access MVP

 

[Manolo123]

Thanks agin for you insight but my lack of knowledge with VB is making this difficult to understand. I have the following code. The first combo box deals with my item number, the second one with my Received date and the last combo box with my Shipped Date.



Private Sub Combo21_AfterUpdate()

Me.Combo48 = Null
Me.Combo48.Visible = True
Me.Combo54.Visible = False

End Sub

Public Sub UpdateFilter()
Dim strSQL As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQL = "SELECT * FROM qryReceived "
If Not IsNull(Me.Combo21) Then
strWhere = strWhere & " AND ItemNum = " & _
Me.Combo21 & " "
End If
If Not IsNull(Me.Combo48) Then
strWhere = strWhere & " AND RecvdDate >= #" & _
Me.Combo48 & "# "
End If
If Not IsNull(Me.Combo54) Then
strWhere = strWhere & " AND ShipDate <= #" & _
Me.Combo54 & "# "
End If
strSQL = strSQL & strWhere & " ORDER BY ..."
Me.[Received Information].Form.RecordSource = strSQL


End Sub

Public Sub UpdateFilter()
Dim strSQL As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQL = "SELECT * FROM qryReceived "
If Not IsNull(Me.Combo21) Then
strWhere = strWhere & " AND ItemNum = " & _
Me.Combo21 & " "
End If
If Not IsNull(Me.Combo48) Then
strWhere = strWhere & " AND RecvdDate >= #" & _
Me.Combo48 & "# "
End If
If Not IsNull(Me.Combo54) Then
strWhere = strWhere & " AND ShipDate <= #" & _
Me.Combo54 & "# "
End If
strSQL = strSQL & strWhere & " ORDER BY ..."
Me.[Received Information].Form.RecordSource = strSQL


End Sub

Private Sub Combo48_AfterUpdate()
Dim strSQL As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQL = "SELECT * FROM qryReceived "
If Not IsNull(Me.Combo21) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.Combo21 & " "
End If
If Not IsNull(Me.Combo48) Then
strWhere = strWhere & " AND Date Received >= #" & _
Me.Combo48 & "# "
End If
If Not IsNull(Me.Combo54) Then
strWhere = strWhere & " AND Date Shipped <= #" & _
Me.Combo54 & "# "
End If
strSQL = strSQL & strWhere & " ORDER BY ..."
Me.[Received Information].Form.RecordSource = strSQL

End Sub

Private Sub Combo54_AfterUpdate()
Dim strSQL As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQL = "SELECT * FROM qryReceived "
If Not IsNull(Me.Combo21) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.Combo21 & " "
End If
If Not IsNull(Me.Combo48) Then
strWhere = strWhere & " AND Date Received >= #" & _
Me.Combo48 & "# "
End If
If Not IsNull(Me.Combo54) Then
strWhere = strWhere & " AND Date Shipped <= #" & _
Me.Combo54 & "# "
End If
strSQL = strSQL & strWhere & " ORDER BY ..."
Me.[Received Information].Form.RecordSource = strSQL

End Sub

My problem is that i continue to get an error about an Ambiguous name detected: UpdateFilter when i select each combo box. What am I doing wrong?

 

[dhookom]

There should be only one copy for the code in the form's module. In addition, when I entered:

   strSQL = strSQL & strWhere & " ORDER BY ..."

the "..." clearly should be a list of your field names that identifies the order you would like for your records.

You should also find and use a naming convention for your Access objects. IMO, "Combo48" is not acceptable when used in code.

Public Function UpdateFilter()
   Dim strSQL As String
   Dim strWhere As String
   strWhere = " Where 1=1 "
   strSQL = "SELECT * FROM qryReceived "
   If Not IsNull(Me.Combo21) Then
      strWhere = strWhere & " AND ItemCode = " & _
         Me.Combo21 & " "
   End If
   If Not IsNull(Me.Combo48) Then
      strWhere = strWhere & " AND Date Received >= #" & _
         Me.Combo48 & "# "
   End If
   If Not IsNull(Me.Combo54) Then
      strWhere = strWhere & " AND Date Shipped <= #" & _
         Me.Combo54 & "# "
   End If
   strSQL = strSQL & strWhere & " ORDER BY [your fields here];"
   Me.[Received Information].Form.RecordSource = strSQL
End Function

This code should be only once in your form's module. Then, you can set the PROPERTY of the after update to the function name like I suggested in a previous posting.

Duane
Hook'D on Access
MS Access MVP

 

[Manolo123]

Thank you again Duane. I think that I am getting closer to getting this to work but still not there. I have rewritten my code as you suggested, see below:

Public Function UpdateFilter()
Dim strSQL As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQL = "SELECT * FROM qry Received Information "
If Not IsNull(Me.cboItemNum) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.cboItemNum & " "
End If
If Not IsNull(Me.cboRecvd) Then
strWhere = strWhere & " AND Date Received >= #" & _
Me.cboRecvd & "# "
End If
If Not IsNull(Me.cboShipped) Then
strWhere = strWhere & " AND Date Shipped <= #" & _
Me.cboShipped & "# "
End If
strSQL = strSQL & strWhere & " ORDER BY itemcode, date received;"
Me.[Received Information].Form.RecordSource = strSQL
End Function

I have two subforms and each one uses a different query to get the data. For example, the Received subform uses a query called "qry Received Information" and the second subform (Shipped Subform) uses a query called "qry Shipped Information". Question: should these query names appear somewhere in the code?

Also I am not sure what you meant by strSQL = strSQL & strWhere & " ORDER BY itemcode, date received;" and how are the fields separated (colons, semi colons?) or if I need this at all if I have subforms that have a define order.

And I am still unclear where to place the =UpdateFilter()? On a new command button or on the 3 combo box (Date Received)on after update event. I have a command button called "Filter Records" that I prefer to use but I am getting a user-defined function or event procedure error.

Any suggustions?

Thank you in advance.

 

[dhookom]

I see you have spaces in your object names. You must pay the penalty of having to wrap these names in []s

SELECT * FROM [qry Received Information]

If you are working with two different record sources, you may need to build two different sql statements.

If you look at the sql view of any query that has any sorting, you should figure out what the Order By is and how it is used.

As I stated, you enter the =UpdateFilter() into the After update [red]property[/red] of the controls that set the filter values.

Duane
Hook'D on Access
MS Access MVP

 

[Manolo123]

Thanks again Duane but when you say that I may have to build two different sql statements, where would the second one go? I placed it right under the first one (see below)but that does not work or maybe I have some other code wrong. Would you mind checking my code below to see if it is correct? Thank you

Public Function UpdateFilter()
Dim strSQL As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQL = "SELECT * FROM [qry Received Information]"
strSQL = "SELECT * FROM [qry Shipped Information]"
If Not IsNull(Me.cboItemNum) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.cboItemNum & " "
End If
If Not IsNull(Me.cboRecvd) Then
strWhere = strWhere & " AND [Date Received] >= #" & _
Me.cboRecvd & "# "
End If
If Not IsNull(Me.cboShipped) Then
strWhere = strWhere & " AND [Date Shipped] <= #" & _
Me.cboShipped & "# "
End If
strSQL = strSQL & strWhere & " ORDER BY [date received];"
Me.[Received Information].Form.RecordSource = strSQL
End Function

 

[dhookom]

You need two SQL strings dim'd and built. Assuming you want to apply the same filter to each subform, I would try something like:

Public Function UpdateFilter()
   Dim strSQLReceived As String
   Dim strSQLShipped As String
   Dim strWhere As String
   strWhere = " Where 1=1 "
   strSQLReceived = "SELECT * FROM [qry Received Information]"
   strSQLShipped = "SELECT * FROM [qry Shipped Information]"
   If Not IsNull(Me.cboItemNum) Then
      strWhere = strWhere & " AND ItemCode = " & _
         Me.cboItemNum & " "
   End If
   If Not IsNull(Me.cboRecvd) Then
      strWhere = strWhere & " AND [Date Received] >= #" & _
         Me.cboRecvd & "# "
   End If
   If Not IsNull(Me.cboShipped) Then
      strWhere = strWhere & " AND [Date Shipped] <= #" & _
         Me.cboShipped & "# "
   End If
   strSQLReceived = strSQL & strWhere & " ORDER BY [date Received];"
   strSQLShipped = strSQL & strWhere & " ORDER BY [date Shipped];"
   Me.[Received Information].Form.RecordSource = strSQLReceived 
   Me.[Shipped Information].Form.RecordSource = strSQLShipped 
End Function

Thank you for changing your control names ;-)

Duane
Hook'D on Access
MS Access MVP