Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146

    Unanswered: Search for account info

    I have a table called "Accounts" and a form with the same name. The form is justified format and displays the individual fields from "Accounts" table. I need to create a text box that I can enter information into and then click a command button to search for that information. A form should be pulled up that displays all of the information for that account. How can I do this?

  2. #2
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Heres something you might try. This will also allow partial searches, as well as place the results in order. Hope this helps.

    Create a text box for the search criteria, then create a command button to Open forms/Search for specific information. Then reedit it to look similar to this.
    ******************************************
    Private Sub Command61_Click()
    On Error GoTo Err_Command58_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormYouWantToOpen"

    stLinkCriteria = "[strSearchColumn] LIKE " & "'*" & Your![TextBox] & "*'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    With Me
    .OrderByOn = True
    .OrderBy = "strSearchColumn"
    End With

    Exit_Command58_Click:
    Exit Sub

    Err_Command58_Click:
    MsgBox Err.Description
    Resume Exit_Command58_Click
    End Sub

  3. #3
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    My text box is named "Text51" and my command button "Command55". The form in question is called "Accounts". I changed the code to the following, but when I type in the text box and press the button, I get the error "Object Required". What have I done wrong? Here's the code:

    Private Sub Command55_Click()
    On Error GoTo Err_Command58_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Accounts"

    stLinkCriteria = "[strSearchColumn] LIKE " & "'*" & Your![Text51] & "*'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    With Me
    .OrderByOn = True
    .OrderBy = "strSearchColumn"
    End With

    Exit_Command58_Click:
    Exit Sub

    Err_Command58_Click:
    MsgBox Err.Description
    Resume Exit_Command58_Click
    End Sub

    Quote Originally Posted by inzzane
    Heres something you might try. This will also allow partial searches, as well as place the results in order. Hope this helps.

    Create a text box for the search criteria, then create a command button to Open forms/Search for specific information. Then reedit it to look similar to this.
    ******************************************
    Private Sub Command61_Click()
    On Error GoTo Err_Command58_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormYouWantToOpen"

    stLinkCriteria = "[strSearchColumn] LIKE " & "'*" & Your![TextBox] & "*'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    With Me
    .OrderByOn = True
    .OrderBy = "strSearchColumn"
    End With

    Exit_Command58_Click:
    Exit Sub

    Err_Command58_Click:
    MsgBox Err.Description
    Resume Exit_Command58_Click
    End Sub

  4. #4
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    First Question..Waht is the name of the column in your table that you want this textbox to search through?

  5. #5
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    I have a table named "Accounts" and I want the search box to check through all of the fields located in that table.

    Quote Originally Posted by inzzane
    First Question..Waht is the name of the column in your table that you want this textbox to search through?

  6. #6
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    ok, then you might need more than one search box. What is the primary method you will be using to reference, or look up these accounts? Account number, name, or something like that?

    Whatever it is, replace "strSearchColumn" with the column name that contains that information.

  7. #7
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Then you'll need to change "Your" into the name of the Form the textbox is contained in. If it is contained on the same form you are viewing the information on, you can chabnge it to "Me" without the quotations.

  8. #8
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Ok, I used a Phone Number as the criteria. The only problem now is, if I type a phone number that is incorrect (meaning, not in the database), instead of displaying an error message, it just opens a new form for data entry. This is the code I have right now:

    Private Sub Command61_Click()
    On Error GoTo Err_Command61_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Accounts"

    stLinkCriteria = "[PhoneNo] LIKE " & "'*" & [Text51] & "*'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    With Me
    .OrderByOn = True
    .OrderBy = "PhoneNo"
    End With

    Exit_Command61_Click:
    Exit Sub

    Err_Command61_Click:
    MsgBox Err.Description
    Resume Exit_Command61_Click
    End Sub

  9. #9
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Hmm...not sure about that one. I use a long list, and sort it by name, so I rarely ever get that. You might need to include an error box into it if there are no results found. Unfortunately, I dont have that in anything I've done yet either.

  10. #10
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Ok, no prob. I might be able to use this to my advantage after all. If the Phone Number isn't found in the database, I can assume that an account doesn't exist for that person. Logically, the next step would be to create one, so this works out well. Thanks for the great help, I sure do appreciate it.

  11. #11
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Anytime...I'm new myself, and rarely find a topic to help on...(hoping to improve that :P)

  12. #12
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Yeah. I've got this classified ad database that I'm building and it's giving me a work out. The concepts are easy to follow, but getting everything to gel together is the tricky part.

  13. #13
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Last question. I've got it working now with various search strings, but how do I restore my list of records in the form after it's been filtered? When I type in a search string and click the command button, it filters everything but the records matching the criteria. How do I restore my full list of all records from the form?

  14. #14
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    ahhh, had the same question myself for a while. All you have to do, is delete the previous entry out of the filter box, and hit the filter button again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •