Run Time 3075 error on combo filter

[SmartCarFun]

Private Sub cboTeam_AfterUpdate()
' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboUser.RowSource = "SELECT User1Name FROM" & _
" Users WHERE Region = " & Chr(34) & Me.cboTeam & Chr(34) & _
" ORDER BY User1Name"
 
Me.cboUser = Me.cboUser.ItemData(0)
SetFilter1
End Sub
 
Private Sub SetFilter1()
Dim strFilter As String
If Not IsNull(Me.cboTeam) Then
strFilter = strFilter & " AND Team = " & Chr(34) & Me.cboTeam & Chr(34)
End If
If strFilter = "" Then
Me.FilterOn = False
Else
Me.Filter = Mid(strFilter, 6)
Me.FilterOn = True
End If
End Sub
 
Private Sub cboUser_AfterUpdate()
SetFilter2
End Sub
 
Private Sub SetFilter2()
Dim strFilter As String
If Not IsNull(Me.cboTeam) Then
strFilter = strFilter & " AND Team = " & Chr(34) & Me.cboTeam & Chr(34)
End If
If Not IsNull(Me.cboUser) Then
strFilter = strFilter & " AND Assigned = " & Me.cboUser
End If
If strFilter = "" Then
Me.FilterOn = False
Else
Me.Filter = Mid(strFilter, 6)
Me.FilterOn = True
End If
End Sub

I have continuous form & 2 x combo boxes, cboTeam & cboUser

1st bit of code allows section of the team by cboTeam, then filters cboUser with members of that team, then fiters the form on cboTeam alone.
2nd bit of code should then filtre on both combo boxes.

1st combo works fine...
2nd combo gives 3075 error (unless empty) and debug points to Me.Filter = Mid(strFilter, 6)

if i change
strFilter = strFilter & " AND Assigned = " & Me.cboUser
to
strFilter = strFilter & " AND Assigned = " & Chr(34) & Me.cboUser & Chr(34)

I get 3464 error

Thanks very much for any help I've been at this for days!!!! and I'm sure it's just something small

 

[SmartCarFun]

'If Not IsNull(Me.cboUser) Then
'strFilter = strFilter & " AND Assigned = " & Chr(34) & Me.cboUser & Chr(34)
'End If

This is definately the problem, if I isolate it from the code with a single quote the codes runs as expected except it doesnt consider the code in the cboUser and so only half the filter works....

 

[SmartCarFun]

Private Sub cboTeam_AfterUpdate()
' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboUser.RowSource = "SELECT ID FROM" & _
" Users WHERE Region = " & Chr(34) & Me.cboTeam & Chr(34) & _
" ORDER BY User1Name"

Me.cboUser = Me.cboUser.ItemData(0)
SetFilter1
End Sub
Private Sub SetFilter1()
Dim strFilter As String
If Not IsNull(Me.cboTeam) Then
strFilter = strFilter & " AND Team = " & Chr(34) & Me.cboTeam & Chr(34)
End If
If strFilter = "" Then
Me.FilterOn = False
Else
Me.Filter = Mid(strFilter, 6)
Me.FilterOn = True
End If
End Sub
Private Sub cboUser_AfterUpdate()
SetFilter2
End Sub
Private Sub SetFilter2()
Dim strFilter As String
If Not IsNull(Me.cboTeam) Then
strFilter = strFilter & " AND Team = " & Chr(34) & Me.cboTeam & Chr(34)
End If
If Not IsNull(Me.cboUser) Then
strFilter = strFilter & " AND Assigned = " & Me.cboUser
End If
If strFilter = "" Then
Me.FilterOn = False
Else
Me.Filter = Mid(strFilter, 6)
Me.FilterOn = True
End If
End Sub

I have the solution, by changing
Me.cboUser.RowSource = "SELECT User1Name FROM" & _
to
Me.cboUser.RowSource = "SELECT ID FROM" & _
it now works....

BUT

Now my cboUser (combo) displaays a number (ID) not the name (User1Name)

What do I need to change????????

Thanks

 

[SmartCarFun]

SOLVED

Me.cboUser.RowSource = "SELECT Users.ID, Users.User1Name FROM" & _

 

[tehNellie]

Glad you got it sorted and thanks for posting how you fixed it