How to filter Autonumber

S

Sunnylei

Registered User.
Local time
Today, 15:48
Joined
Mar 5, 2011
Messages
87
Hi
I'm designing a simple purchase order database. The order number will be automatically generated. I created a querySearch, and I set a search button to filter an order number. However, it's not working. The code is as follows,

Code: 
Private Sub CmdSearch_Click()
    Dim qdf As DAO.querydef
    Dim LSQL  As String
    Dim Number As Integer
    If Len(txtOrderID) <> 0 Or Not IsNull(txtOrderID) = True Then
        MsgBox "You must enter an search Order Number."
    Else
    Set qdf = CurrentDb.QueryDefs("querySearch")
        LSearchString = txtOrderID
        'Filter results based on search string
        LSQL = "select [OrderID] from ABS Ordertbl "    'table
        LSQL = LSQL & " where OrderID LIKE '" & Number & "'"
      Debug.Print LSQL
 
 
      qdf.sql = LSQL
            If DCount("'", "querySearch", "OrderID Like '" & Number & "'") > 0 Then
                   DoCmd.OpenQuery "querySearch"
            Else
                MsgBox "There are no records with Search string " & vbCrLf & vbTab & "--->" & Number & "<---"
            End If
        'Clear search string
        ' Number = ""
        'MsgBox "Results have been filtered.  All Descriptions containing " & LSearchString & "."
         End If
End Sub

Can anyone spot what's problem?:confused:
 
If it's a numeric data type you don't want the single quotes surrounding the value:

LSQL = LSQL & " where OrderID = " & Number
 
pbaldy said:
If it's a numeric data type you don't want the single quotes surrounding the value:

LSQL = LSQL & " where OrderID = " & Number
Click to expand...

Hi
I still have a little problem ie I entered a number, it appears an error ie "Run-time error '3131' Syntax error in FORM Clause. Can you have a look the following code:

Code: 
 qdf.sql = LSQL
 
            If DCount("", "querySearch", "OrderID =" & Number) <> 0 Then
                   DoCmd.OpenQuery "querySearch"
            Else
                MsgBox "There are no records with Search Number " & vbCrLf & vbTab & "--->" & Number & "<---"
            End If
Thanks
 
Hi..:

Use the joker for the domain..:

DCount("*", "querySear.....
 
The inadvisable space in your table name will require it to be bracketed.
 
Change the Word "Number" to something else.

Number is a reserved word.

Might help might not but should be changed in any case.
 
pbaldy said:
The inadvisable space in your table name will require it to be bracketed.
Click to expand...

Hi Paul
I have put "ABS Ordertbl" in a bracket, and I also give "Number" another name ie "Numb" in terms of Rain's comment. However, there is a compile error: "Wrong number of argument or invalid property assignment" for DCount Function. I'm not sure what went wrong. I like you to have a look attached Database. The database is very simple, only one table. I just want to achieve search autonumber from Form "ABS Order"

Thanks:)
 

Attachments

You added an extra comma and quotes in the DCount, which was that error. You changed the variable to Numb but set a different one (LSearchString) which you never actually used. You didn't bracket the table name, you parenthesized it. You don't have anything in the field argument, just "". You used the name of the QueryDef variable in the DCount instead of the name of the query.
 
pbaldy said:
You added an extra comma and quotes in the DCount, which was that error. You changed the variable to Numb but set a different one (LSearchString) which you never actually used. You didn't bracket the table name, you parenthesized it. You don't have anything in the field argument, just "". You used the name of the QueryDef variable in the DCount instead of the name of the query.
Click to expand...

Hi Paul
I have changed errors which you indicated. I also changed table name. The errors are disappear, however, it's still not working. Can you have a look the following updated code:
Code: 
Private Sub CmdSearch_Click()
    Dim qdf As DAO.querydef
    Dim LSQL  As String
    Dim Numb As Integer
    If Len(txtOrderID) = 0 Or IsNull(txtOrderID) = True Then
        MsgBox "You must enter an Order Number."
    Else
    Set qdf = CurrentDb.querydefs("querySearch")
        Numb = Len(txtOrderID)
        'Filter results based on Order Number
        LSQL = "select [OrderID] from ABSOrdertbl"
        LSQL = LSQL & "where OrderIDLIKE" * "& Numb " * ""
'
      Debug.Print LSQL
 
 
      qdf.sql = LSQL
 
            If DCount("*", "querySearch.[OrderID] =" & Numb, "*") <> 0 Then
                   DoCmd.OpenQuery "querySearch"
            Else
                MsgBox "There are no records with Search Number " & vbCrLf & vbTab & "--->" & Number & "<---"
            End If
        'Clear Order Number
        ' Number = ""
        'MsgBox "Results have been filtered.  All Descriptions containing " & Numb "."
         End If
End Sub

Thanks:)
 
Well, your DCount syntax is all off. It was fine in post 3 except you needed a field name or * in the first argument. You realize this is giving you the length of whatever was entered, not the value:

Numb = Len(txtOrderID)

The second line of the SQL statement should be like I had in post 2, not with Like and wildcards. Your spacing is all off in the SQL anyway.
 
By the way, since your SQL already restricts by OrderID, there's no need for a criteria in the DCount. This appears to work:

Code: 
  Dim qdf                As DAO.querydef
  Dim LSQL               As String
  Dim Numb               As Integer
  If Len(txtOrderID) = 0 Or IsNull(txtOrderID) = True Then
    MsgBox "You must enter an Order Number."
  Else
    Set qdf = CurrentDb.querydefs("querySearch")
    Numb = txtOrderID
    'Filter results based on Order Number
    LSQL = "select [OrderID] from [ABS Ordertbl] "
    LSQL = LSQL & "where OrderID =" & Numb
    '
    Debug.Print LSQL


    qdf.sql = LSQL

    If DCount("*", "querySearch") <> 0 Then
      DoCmd.OpenQuery "querySearch"
    Else
      MsgBox "There are no records with Search Number " & vbCrLf & vbTab & "--->" & Number & "<---"
    End If
    'Clear Order Number
    ' Number = ""
    'MsgBox "Results have been filtered.  All Descriptions containing " & LSearchString & "."
  End If
 
pbaldy said:
By the way, since your SQL already restricts by OrderID, there's no need for a criteria in the DCount. This appears to work:

Code: 
  Dim qdf                As DAO.querydef
  Dim LSQL               As String
  Dim Numb               As Integer
  If Len(txtOrderID) = 0 Or IsNull(txtOrderID) = True Then
    MsgBox "You must enter an Order Number."
  Else
    Set qdf = CurrentDb.querydefs("querySearch")
    Numb = txtOrderID
    'Filter results based on Order Number
    LSQL = "select [OrderID] from [ABS Ordertbl] "
    LSQL = LSQL & "where OrderID =" & Numb
    '
    Debug.Print LSQL
 
 
    qdf.sql = LSQL
 
    If DCount("*", "querySearch") <> 0 Then
      DoCmd.OpenQuery "querySearch"
    Else
      MsgBox "There are no records with Search Number " & vbCrLf & vbTab & "--->" & Number & "<---"
    End If
    'Clear Order Number
    ' Number = ""
    'MsgBox "Results have been filtered.  All Descriptions containing " & LSearchString & "."
  End If
Click to expand...

Hi Paul
It's working now! Thanks
 
pbaldy said:
Happy to help.
Click to expand...

Hi Paul
I use another approach to filter out Autonumber, for some reason it's no any reaction when I click Search Button. Can you have a look what is missing from it.

Code: 
Private Sub CmdSearch_Click()
 
Dim txtOrderID As String
Dim filterString As String
If IsNull(Me.txtOrderID) Then
MsgBox " You must enter an Order Number."
Else
txtOrderID = "Form_ABSOrder.[OrderID]"
End If
filterString = "[OrderID]"
Me.Filter = filterString
Me.FilterOn = True
End Sub

Thanks:)
 
Your filter string would be the same as the SQL "where" clause you built but without the word "where".

FieldName = 123

You just have

FieldName
 
pbaldy said:
Your filter string would be the same as the SQL "where" clause you built but without the word "where".

FieldName = 123

You just have

FieldName
Click to expand...

Hi Paul
I updated my code. I'm trying to open Form, not "querySearch". I changed all "querySearch" to Form "ABSOrder", and deleted "Where", Added FieldName =123. Now there is an error: Run time error '3265s' Item not found in this collection.
Can you have a look the updated code below:

Code: 
Private Sub CmdSearch_Click()
'On Error GoTo Err_CmdSearch_Click
  Dim qdf                As DAO.QueryDef
  Dim LSQL               As String
  Dim Numb               As Integer
  If Len(txtOrderID) = 0 Or IsNull(txtOrderID) = True Then
    MsgBox "You must enter an Order Number."
  Else
    Set qdf = CurrentDb.QueryDefs("ABSOrder")
    Numb = txtOrderID
    'Filter results based on Order Number
    LSQL = "select [OrderID] from [ABS Ordertbl] "
    LSQL = LSQL & "OrderID =" & Numb
    '
 
    If DCount("*", "ABSOrder") <> 0 Then
      DoCmd.OpenForm "ABSOrder"
    Else
      MsgBox "There are no records with Search Number " & vbCrLf & vbTab & "--->" & Number & "<---"
 filName = 123
Me.Filter = filterName
Me.FilterOn = True
 
    DoCmd.RunCommand acCmdApplyFilterSort
Exit_CmdSearch_Click:
    Exit Sub
Err_CmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_CmdSearch_Click
 
    End If
      End If
 
End Sub

Thanks:)
 
Why did you change the parts of the previously working code that were working? You need to start understanding what each line of code does, and not just copy/paste. What you have there is totally screwed up compared to what we had working before. The filter would look like:

filterName = "OrderID =" & Numb
 
pbaldy said:
Why did you change the parts of the previously working code that were working? You need to start understanding what each line of code does, and not just copy/paste. What you have there is totally screwed up compared to what we had working before. The filter would look like:

filterName = "OrderID =" & Numb
Click to expand...

Sorry Paul,
Original work is still there. I opened another new DB, and I'm trying to install a filter on the form, rather than open "querySearch". My intention is filter out specific OrderID from Form, and print out information with the OrderID ie current Form.:)
 
Last edited:
So take the code you know works and put it here, then just change the bit that opens the query to the filter code.
 
You must log in or register to reply here.

Similar threads

E
Generating report on a dynamic subform
Replies
6
Views
499
The_Doc_Man
The_Doc_Man
N
How to send filtered transactions in the MS Access Combo box with one Click
Replies
11
Views
544
Minty
Minty
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
825
Minty
Minty
D
Clearing data in a form after clicking print report
Replies
9
Views
1,227
Ken Sheridan
Ken Sheridan
T
Solved Filter form after update with code
Replies
8
Views
1,111
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom