+ Reply to Thread
Results 1 to 7 of 7

Retrieving multiple records to create history of client using VB

  1. #1
    Registered User
    Join Date
    04-30-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003 -VB
    Posts
    3

    Post Retrieving multiple records to create history of client using VB

    Dear forum members. This is my first time using such a forum so please be patient with me.
    I would like to create a database which can enter data, retrieve data and edit data, and display multiple data entries of the same client pulling in all the relevant data.
    I have created in the past VB forms and entered data, retrieved single line data entries and edited with success. My problem is to retrieve all the data from a single client, from an extensive list of clients, display the selected cells from predefined columns. I have used VLook up and match together, usually as excel coding not VBA. All the above was by studing VB examples and ploding through them.

    There is currently no unique identifier with each client (although if required, this could be easily be done by concatenating say date of birth figures and say three letters of their surname with the initial of their forename for example).
    There is date of entry against each client, (and also shown as a period within the financial calendar and year), forename, surname, data of birth, type of care packages etc.

    My ideal solution is to enter data in a VB form it will search the relevant columns of data, with the user adding more data to refine the search in the following order:

    Surname
    Forename
    Date of Birth
    Other – subject to feed back

    Then display all of the records against that client.

    Best Regards Emanon57

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Retrieving multiple records to create history of client using VB

    Sounds like a lot of work to duplicate Excel's built-in Autofilter (Table) functions.

    Turn on the Data > Filter > Autofilter for that database, them just use the drop-downs at the to filter the DB any way you wish. You will be left viewing only the rows for those filters, edit as you see fit, then use new filters for new data, all from the main DB page. No VB, no forms, no maintenance.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-30-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003 -VB
    Posts
    3

    Re: Retrieving multiple records to create history of client using VB

    Quote Originally Posted by JBeaucaire View Post
    Sounds like a lot of work to duplicate Excel's built-in Autofilter (Table) functions.

    Turn on the Data > Filter > Autofilter for that database, them just use the drop-downs at the to filter the DB any way you wish. You will be left viewing only the rows for those filters, edit as you see fit, then use new filters for new data, all from the main DB page. No VB, no forms, no maintenance.
    Thank you for your comments and they would apply if I was managing the database. However, it has to be used by administrators who will not habe the same level of skill. In addition I have attached a financial package to calculate cost when ever the care package was introduced or ended. This info wil be required on another micro button. Hence the reason to do a lot of work to create a seamless presentation to the user.

    Many Thanks

    emanon57

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Retrieving multiple records to create history of client using VB

    Well, I don't believe you would be asking us to design a whole form-based user interface, for you, right? So, your specific question for us would be...?

    We expect you will design your form and do the work to layout it out (learn as you go, that's how we all do it), and posit us queries when you get stuck along the way.

  5. #5
    Registered User
    Join Date
    04-30-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003 -VB
    Posts
    3

    Re: Retrieving multiple records to create history of client using VB

    Quote Originally Posted by JBeaucaire View Post
    Well, I don't believe you would be asking us to design a whole form-based user interface, for you, right? So, your specific question for us would be...?

    We expect you will design your form and do the work to layout it out (learn as you go, that's how we all do it), and posit us queries when you get stuck along the way.
    Thank you for your interest. I will create the VB form which will provide the user to have three options Review, Edit and enter new client details. The latter two parts I have coded successfully on previous programmes.

    I would like guidance to code in VB the following: Review (History) - When the user selects a surname from a dynamic drop menu, say Smith, all the Smiths are then displayed, this also drags in all selected data from a number of selected cells against each Smith. [E.g. Title: Mr, Surname: Smith, Forename: Peter, DOB; 28 November 1990, Address line 1: 3 Springside Cottages.] The users will have the facility to refine the search by selecting from another dropdown menu forename, such as Peter, the screen may still display all Peter Smiths’. The final menu which may be used if there are a number of Peter Smiths’ would be the data of birth drop down. I realise that this can be done by filters, this would be fine if all user had the same basic skill level, this is not the case. I guess I am creating a similar approach which can be done in Access relationship tables. However, the reason I am sticking to excel that the shared users will not all have MS Access as this is not widely available throughout the organisation. MS Excell is. I hope this can be achieved

    Many Thanks

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Retrieving multiple records to create history of client using VB

    I can't help but chuckle at the idea that teaching your users to use the AutoFilter drop downs is anything other than "simple", whereas the FORM you're designing will take far more work, training, and maintenance. Don't get me wrong, people make this choice all the time, but the conclusion all this work is necessary because the autofilter is too hard to use is ludicrous. Forms have a high-neato factor, but making use of builtin features that do exactly the same thing in a slightly different but equally "easy to use" method once you take 2 minutes to show someone how to use the filters... that's an even higher "easy-neato-factor".

    I'm just saying. You can turn on the filters and show them how to use them right now. Or you can keep working on this Form project for x-hours, then spend the same or more time teaching them to use that.

    Anyway, you can use a ListBox to collect and display a collection of matching names, or it can even list all the info you noted above. Search for ListBox method.

    You can use a ListBox Selected Item to select an item in your Listbox of choices and have all the values for that item fill out a form for editing.
    Last edited by JBeaucaire; 05-06-2012 at 07:03 PM.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Retrieving multiple records to create history of client using VB

    for what its worth, JB, i couldnt agree more with you. I have designed many "systems" for end users, and in the end, just showed/taught them to use already-built-in features that give them exactly what they need....but gee, it cant be THAT easy, can it....?

    no offence meant to the OP by the way
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1