Results 1 to 14 of 14
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: How to Search by ID number on a Form from a specific table/query

    Hi All,
    I hope someone kind enough would be able to help.
    Basically, I have a form called frminvoice, currenly we have a drop down of clients names. As soon as I select a client from the Dropdown list, then it presents me with all bookings entered against that client account.
    This is the code I use for the dropdown option

    Private Sub cboFindClient_Change()
    Dim strSQL As String
    Dim intClientID As Integer

    If IsNull(Me.cboFindClient.Value) Then
    ' strSQL = "SELECT * FROM tblContacts WHERE [txtCompanyName] LIKE '" & strCompanyName & "*'"
    strSQL = "SELECT * FROM qryInvoices"
    'SELECT tblContacts.* FROM tblContacts ORDER BY tblContacts.txtCompanyName;
    Me.RecordSource = strSQL
    Else
    'intClientID = DLookup("id", "Client", "Name =" & Chr(34) & Me.cboFindClient.Value & Chr(34))
    intClientID = Me.cboFindClient.Value
    strSQL = "SELECT * FROM qryInvoices WHERE [ClientID] =" & intClientID
    Me.RecordSource = strSQL
    End If
    Me.Refresh

    End Sub
    This is working fine.

    What I want now is to be able to search for clients based on their ID using a Text Box and that it should give me the same result i.e. if a client has two bookings against his account, then the search result should find that client and then it should list all the 20 bookings linked that client.

    I am under pressure and my manager has asked me to fix this and I am strugling. Any help would be highly appreciated.
    Last edited by Emal; 10-30-13 at 12:20.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so consider using a combo box that has the client ID and client name
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    386
    HI, thank you for your response. I have done that, but it did not work, and it does not link all bookings against that client, neither does it update the search. Would you be kind enough to help me do it right?
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its your project, its your data your design. I don't have the knowledge of that nor to be hoinest the time
    the combo box should give you the what ever you need to query your system

    in essence you already have it working... all you need to do is either replicate that code and pull the value fromt he new combo box

    or bastardise you current design so that, say you have a toggle box (checkbox) adjacent to your current combo cboFindClient. in one state the combo box hides the client ID and the users use client name to find the ID, thje other mode the user puts in the client id

    the only difference is which columns you show.
    you can change the settigns of a combo box on the fly by adjusting its properties
    whatr I'd do is make the client ID visible only when entering client ID's, otherwise leave it hidden (what I guiess is going to be your current design.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2006
    Posts
    386
    Thank you for your advice, I have tried everything in my knowledege but unfortunately it is not working, so I hope someone will be able to help me out with this. I did try to change my currentcode but could not get it working.
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    May 2006
    Posts
    386
    I have just amended my code (see below) and it works fine using a Combobox, but as it is very time consuming to go through the list. I would like to be able to search for the same result through a sipmle Text Box i.e. enter a unique client ID and then ENTER or click on search button it should find the same result. Any help on this please?

    here is the code:
    Private Sub Combo310_Change()
    Dim strSQL As String
    Dim intClientID As Integer

    If IsNull(Me.Combo310.Value) Then
    ' strSQL = "SELECT * FROM tblContacts WHERE [txtCompanyName] LIKE '" & strCompanyName & "*'"
    strSQL = "SELECT * FROM qryInvoices"
    'SELECT tblContacts.* FROM tblContacts ORDER BY tblContacts.txtCompanyName;
    Me.RecordSource = strSQL
    Else
    'intClientID = DLookup("id", "Client", "Name =" & Chr(34) & Me.cboFindClient.Value & Chr(34))
    intClientID = Me.Combo310.Value
    strSQL = "SELECT * FROM qryInvoices WHERE [ClientID] =" & intClientID
    Me.RecordSource = strSQL
    End If
    Me.Refresh

    End Sub
    Emi-UK
    Love begets Love, Help Begets Help

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what I'd do is
    place a combo box in the form footer or header
    add a checkbox adjacent to the combo and caption it something like 'select by name'
    add the client_id in brackets to the combo
    set up the rest of the combo as normal

    your code current code should work fine in the combo's on click event, after all the only difference is that your users will either be specifying the client id or the client name (as determined by the checkbox) but in any event you will be getting the client id from the combo.

    place some code behind the checkbox's on click event
    Code:
    If ckFilterbyname.Value = vbTrue Then 'we are searching by name
      With mycombobox
      .ColumnCount = 2
      .ColumnWidths = "0;5"
      .BoundColumn = 1
      End With
    Else 'we are searching by id
      With Combo2
        .ColumnWidths = "1,4"
        .ColumnCount = 2
        .BoundColumn = 0
      End With
    End If
    make certain that when you open the form up for first use the checkbox is tiocked
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2006
    Posts
    386
    Hi, thank you so much. I have tried it and it is not working. To make sure i got it right, please have a look at the screenshot of my comobox.
    Also, to reconfirm, I do not want a combox search, I want to be able to just type client ID manually and then either ENTER or hit the search button and it should find it.
    Attached Thumbnails Attached Thumbnails comobox settings.png  
    Emi-UK
    Love begets Love, Help Begets Help

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why complicate it
    your users are going to get used to typing in a client name, this way round allow them to use the same control and type in either the name or the id

    the code was aircode, untested, unproven, written ont he fly
    its an idea of how to solve the problem, not the solution to the problem
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2006
    Posts
    386
    I understand, the reason I posted this threat is to get some practical help as my knowledge is unfortunately limited. I would really appreciate any help on this.
    Emi-UK
    Love begets Love, Help Begets Help

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you cnat make the single combo idea work then an alternative would be two have two combo's and toggle visibility based on a check box or radio button. the user thinks they are seeing the same combo
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2006
    Posts
    386
    Hi Healdem, Thank you for your response. I did get a second combo box working, however, it lists me all Clients IDs and it is very time consuming to scroll down and up in order to search for an ID/Client. what I wanted was to be able to enter an ID in a tex box and then hit the ENTER or click a command button and it should bring up that client ID and all of their related bookings which by the way is working through the combo box option.
    Emi-UK
    Love begets Love, Help Begets Help

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The RowSource property of the second combo should be:
    Code:
    SELECT DISTINCT ClientID FROM Tbl_Clients;
    Or:
    Code:
    SELECT ClientID FROM Tbl_Clients GROUP BY ClientID ;
    Have a nice day!

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you use a combo box, then typing the value will automatically select the first vaslue that matches
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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