Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44

    search multiple fields

    Hi, first of all I am a newcomer to Access and VBA coding in general and I am asking for your help.


    I have a problem that I can't find anywhere how to make a shortcut key for example F2, where I could easily choose between several fields when entering a post when searching for a customer. So I want to search either between Customer ID field or Customer Name and not just Customer ID. How could I do that? See screen for details.

    Any idea or code is welcome! Thank you very much!Click image for larger version. 

Name:	customer-switch.jpg 
Views:	58 
Size:	231.4 KB 
ID:	42205

    Greg

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a continuous form that shows all records. In the header, put unbound controls for the user to enter search criteria.
    When user clicks the Find button, Test all controls for a possible filter then build the where clause:
    Code:
    sub btnFind_click()
    sWhere = "1=1"
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if chkContact then sWhere = sWhere & " and [Contact]=" & chkContact.value
     'then filer
    if sWhere = "1=1" then
      me.filterOn = false
    else
      me.filter = sWhere
      me.filterOn = true
    endif
    end sub

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    To clarify so that I understand, you would like to use a shortcut key to essentially "tab" between columns within a combobox so that you can essentially use autocomplete to find a Customer record by different combobox columns?

    I could be wrong but I'm not sure that's possible. First, it looks like the first column is the customer's ID column? I would hide the ID from the user and just let the user type in the customer name to jump to that customer. For a more advanced search functionality you might consider a popup dialog form that is loaded, let's say when the user double clicks the combobox, that you can search by multiple fields that will return the customer's ID to the calling subform.

    Certainly a "proper search form" is subjective but this is what I have in mind: http://allenbrowne.com/ser-62.html

  4. #4
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi ranman256, Thanks for your code. I thought otherwise I would search directly (without using the mouse) inside the Customer combobox field. If I understand correctly, your code makes filter in additional Button where can I further filter between state, name etc?

  5. #5
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    To clarify so that I understand, you would like to use a shortcut key to essentially "tab" between columns within a combobox so that you can essentially use autocomplete to find a Customer record by different combobox columns?

    Yes, that was in one of my previous job. However, I can then omit this.

    I would hide the ID from the user and just let the user type in the customer name to jump to that customer.

    No, I don't want to hide the ID because I want to do the same for the Account field. Usually you know the account by heart (number), but sometimes you need to look at the name of the account (text). So I would make a similar solution here.

    For a more advanced search functionality you might consider a popup dialog form that is loaded, let's say when the user double clicks the combobox, that you can search by multiple fields that will return the customer's ID to the calling subform.

    Can you please explain to me how I could do this?

    Thanky you

  6. #6
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    No more ideas?

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    In one of my databases (IT Devices database) I have a single devices form with 3 unbound combo boxes to search for device. Combos are exactly at same position in devices form. All combos are linked to DeviceID (a PK field), and the visible field is combination from DeviceID, Producer, Device Mark and current user in different orderings
    DeviceID & "; " & Producer & "; " & Mark & "; " & CurrentUser (the default ordering)
    Producer & "; " & Mark & "; " & DeviceID & "; " & CurrentUser
    CurrentUser & "; " &DeviceID & "; " & Producer & "; " & Mark

    Always only one of those selection combos is visible - rest of them are hidden.

    On form is also an unbound combo where user can select which ordering to use. The On Change event of this combo resets the visibility of selection combos, and changes the Order property of form according to selected ordering, and then refreshes the form.

    When another device is selected from visible selection combo, according device is activated in form. The Current event of form set's all selection combos equal to active DeviceID (All combos for case when new record was activated using eg. navigation buttons or shortcuts, and also for case when app was just opened).

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Greg

    A little puzzled by your Form structure.

    It looks like 1 Invoice can have Multiple Customers ?

    Normal structure would be that 1 Customer can Have Many Invoices.

    Your Main Form is showing Document as "INV001"

    The Subform is also showing many occurrences of Document "INV001" ??

    Can you please explain the purpose of the Forms ?

  10. #10
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Mike,
    it's just mean that wehn you post one invoice you have to choose to which supplier the invoice belongs to.
    One Document have always at least 2 occurrences (normal one Debit and one Credit), but it can also have have more records (tax etc...). But in Main Form can be just one occurence.

    One invoice have just one Customer. This is Ok. Other record are cost accounts, there is no customers.

    So in the main Form you put for example document number (entry invoice) and invoice date. In the subform, are these data automatically written because I put link between (Link master and child fields). In the subform you then complete the post with customer, account, amount etc. Here (subForm) you can have more records with many occurrences of the same Document "name".

  11. #11
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Greg

    Sorry but I still believe your structure is wrong.

    I will leave it for someone else to puzzle through, or maybe you can upload a zipped copy of the database?

  12. #12
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Here is a copy of the database. This is still all in the initial development I would like to set the basic things first. Thank you for help!
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    EntryHead(Voucher) table makes no sense. It duplicates data in GeneralLedger.

    Link between DocumentType and GeneralLedger should be between DocumentType ID and GeneralLedger DocType (needs to be changed to a number long integer field).

    Balance should not be a field in table. It should be calculated when needed.

    Advise not to use punctuation/special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    EntryHead make sense because one Invoive have unique data but in the subform are then the data supplemented...


    So if I go back to the original topic of searching and entering data between several fields in Access is this than not possible?

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    EntryHead is duplicating fields in GeneralLedger. This is not appropriate relational database design. Still does not make sense.

    It is certainly possible to use multiple fields in search criteria. Data entry can certainly be done into multiple fields.

    It is not clear what you mean by "between several fields".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search multiple fields
    By Gregory23 in forum Access
    Replies: 2
    Last Post: 03-30-2016, 08:24 AM
  2. Replies: 6
    Last Post: 02-26-2014, 05:06 PM
  3. Search across multiple fields
    By Nexus13 in forum Programming
    Replies: 2
    Last Post: 07-08-2011, 02:38 PM
  4. combo box to search multiple fields
    By jo15765 in forum Forms
    Replies: 21
    Last Post: 12-23-2010, 03:28 PM
  5. Replies: 4
    Last Post: 09-22-2010, 01:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums