Srch a field Frm unbnd txtbx based on selection in field list comb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Any help is greatly appriciated. This is my situation. I have a form with 8
price breaks,Qty1 thru Qty8. I have created a combo box[Combo111] as a field
list for the query i am using. I have also created 2 unbound text boxes to
search the from price[fromQty] and the to price[toQty]. The code in the on
click of my button is as follows:

Private Sub Command74_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.fromQty) Then
strWhere = strWhere & "([Combo111] >= " & Format(Me.fromQty) & ") And"

End If

If Not IsNull(Me.toQty) Then
strWhere = strWhere & "([Combo111] <= " & Format(Me.toQty) & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!CopyOfQuoteBook!fromQty1.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

My goal is to find everything in the query by price break between the
specified dollar amounts. I know im missing something but im pretty new to
access. Again any help is greatly appriciated, Thanks.
 
Asib said:
Any help is greatly appriciated. This is my situation. I have a form with 8
price breaks,Qty1 thru Qty8. I have created a combo box[Combo111] as a field
list for the query i am using. I have also created 2 unbound text boxes to
search the from price[fromQty] and the to price[toQty]. The code in the on
click of my button is as follows:

Private Sub Command74_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.fromQty) Then
strWhere = strWhere & "([Combo111] >= " & Format(Me.fromQty) & ") And"

End If

If Not IsNull(Me.toQty) Then
strWhere = strWhere & "([Combo111] <= " & Format(Me.toQty) & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!CopyOfQuoteBook!fromQty1.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

My goal is to find everything in the query by price break between the
specified dollar amounts. I know im missing something but im pretty new to
access.
Click to expand...


The field name needs to be inserted into the criteria (you
have the name of the combo box). There's also some missing
spaces and a little more code than you really need. Here's
an air code rewrite you can try:

Private Sub Command74_Click()
Dim strWhere As String

If Not IsNull(Me.fromQty) Then
strWhere = strWhere & " And [" & Me.Combo111 & _
"] >= " & Me.fromQty
End If

If Not IsNull(Me.toQty) Then
strWhere = strWhere & " And [" & Me.Combo111 & _
"] >= " & Me.toQty
End If

If Len(strWhere) = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Forms!CopyOfQuoteBook!fromQty1.SetFocus
Else
strWhere = Mid$(strWhere, 6)
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
Thanks for the quick reply Marshall. I have inserted the code but it wasn't
working. I had to change the second line to <=, and now it works fine. I have
one more question if i may. Is there any way to exclude certain fields from
the combobox field list? Right now it gives me everything in the query and I
only want the price breaks?

Marshall Barton said:
Asib said:
Any help is greatly appriciated. This is my situation. I have a form with 8
price breaks,Qty1 thru Qty8. I have created a combo box[Combo111] as a field
list for the query i am using. I have also created 2 unbound text boxes to
search the from price[fromQty] and the to price[toQty]. The code in the on
click of my button is as follows:

Private Sub Command74_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.fromQty) Then
strWhere = strWhere & "([Combo111] >= " & Format(Me.fromQty) & ") And"

End If

If Not IsNull(Me.toQty) Then
strWhere = strWhere & "([Combo111] <= " & Format(Me.toQty) & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!CopyOfQuoteBook!fromQty1.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

My goal is to find everything in the query by price break between the
specified dollar amounts. I know im missing something but im pretty new to
access.
Click to expand...


The field name needs to be inserted into the criteria (you
have the name of the combo box). There's also some missing
spaces and a little more code than you really need. Here's
an air code rewrite you can try:

Private Sub Command74_Click()
Dim strWhere As String

If Not IsNull(Me.fromQty) Then
strWhere = strWhere & " And [" & Me.Combo111 & _
"] >= " & Me.fromQty
End If

If Not IsNull(Me.toQty) Then
strWhere = strWhere & " And [" & Me.Combo111 & _
"] >= " & Me.toQty
End If

If Len(strWhere) = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Forms!CopyOfQuoteBook!fromQty1.SetFocus
Else
strWhere = Mid$(strWhere, 6)
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
 
Asib said:
Thanks for the quick reply Marshall. I have inserted the code but it wasn't
working. I had to change the second line to <=, and now it works fine. I have
one more question if i may. Is there any way to exclude certain fields from
the combobox field list? Right now it gives me everything in the query and I
only want the price breaks?
Click to expand...


Sorry about the paste mistake. Glad you spotted it.

You can not filter a field list type combo/list box. Since
having repeating fields like that is a violation of the
rules of database normalization, you should redesign your
tables to eliminate the problem. You probably don't want to
do that because of the work involved, but it will simplify
things and save you effort over the long haul. If you don't
have any idea about how to normalize then I suggest that you
read up on the subject and ask followup questions in the
tabledesign forum.

Baring that, you will either have to live with what you have
or create a little lookup table with just the field names
you want in the list.
 
Well I'll do my homework on normalization. I'm just in the testing phase so
nothing is conctrete just yet.Again thanks for all the help.

Asib.
 
Back
Top