Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19

    Question Unanswered: "Select Customer" combo w/ population of form fields upon selection

    Hello, I'm fairly new to Access, but am doing fairly well and learning it fairly easily. However, there is something I have to add in for my client so it's easier for them...

    What I have now, is a form on which they enter all of their invoices... A feature that I'm trying to implement for the system is a way for them to pull up a previous customer's information by selecting any of the combo boxes...

    So far, I've figured out how to populate the textboxes with the information from the selected record (Click the "CustomerID" drop-down, select the customer ID number, and it fills in the First Name, Last Name, etc., fields with the correct information for that particular customer record)... What I need to accomplish, however, is a way to display the entire customer database upon clicking on one of the combo boxes (first name, last name, etc), and have a way for the user to click one of those records, and have it populate the proper textboxes.

    Right now, if you click on the Customer ID combo, it just shows the autonumber values of the CustomerID column of each row... Is there a way to display ALL the columns at once? I sure don't want to click on 1, then 2, then 3, and have to continue clicking to eventually find out that customer id #2310 is the one I want.. and I sure don't want to have to pull up the customers table to sort it by last name (or whatever) to figure out the customer number, and then have to go back in and select that number...

    Am I making sense? I hope so... I've doing so much stuff for my client right now my head is spinning! Everything looks like it makes sense to me!

    This invoice form is based on the INVOICES table; what I would like to do is have the FIRST NAME, LAST NAME, etc.. fields do a lookup from the CUSTOMERS table in the manner above, and have it all work out on the form.

    Any helpful hints would be very nice...

    Thanks very very much,
    Brian Tate

  2. #2
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    The combo box has a recordsource property. If you right click it to bring up the property sheet, then goto to the data tab.
    Set RowSource type to Table/Query. Then Click in the row source box. Click on the build button to the right of the box(...). A query pane will open up, choose the table you would like to select your fields from, and then choose the fields. Make sure your ID(primary key) is the first field chosen.
    When you are done, close the query. Access will ask you if you want to save changes. Say yes.
    On the format tab, set column count to the number of columns you chose, and then set the width of each column, seperated by ";"
    eg 5 Columns
    1cm;2cm;2cm;4cm;1cm

  3. #3
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    AWESOME!!! That worked great! Thank you so very very much!

    Now, is there a way to make it so that when John Q. Doe calls up and places an order, that they can enter his information into the proper fields, and have those values saved into the CUSTOMERS table automatically (mostly very very simple minds will be using this software; I HAVE to make it idiot proof!)??

    Also, is there a way to change the position of the drop-down that I just added? Right now, it left-justifies itself with the left side of the combobox, and that makes it a little difficult when the box needs to be on the right side of the screen!

    Another feature I need to add, is in the middle of the form, where it has the Item Number fields, I need to be able to put the part number in the box, and have it query a table created by another program (originally this database was strictly for inventory and parts management... then I started learning about DBs and figured out that I could combine my invoice system with the parts system...), as to be able to fill in the Item Description field with whatever is set as "Title" for the particular part number... So, say, put in '2610' in Item Number, and after update have it query the table and insert "Grease Cup, .6oz" into the description.

    Anyways, thanks again! I really appreciate it!

    Brian

  4. #4
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Now, is there a way to make it so that when John Q. Doe calls up and places an order, that they can enter his information into the proper fields, and have those values saved into the CUSTOMERS table automatically
    I'm not entirely sure what you are asking for here. You can make it so that they select his name from a list, and then you fill in boxes with values depending on who the selected. first, last, etc.

    Also, is there a way to change the position of the drop-down that I just added? Right now, it left-justifies itself with the left side of the combobox, and that makes it a little difficult when the box needs to be on the right side of the screen
    I'm not sure about this, as far as I am aware it alwasy starts where your combo box starts.

    If you give me the field names you need the fields you would like looked up after a part number is entered.

    Have you done anything in VBA yet?

  5. #5
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    Part 1:

    The more I thought about it, the more it was going to be better if I create a form that will add a new customer into the CUSTOMERS table... It's going to be the easiest way, and will eliminate problems... So, scratch that idea.

    Part 2:

    I currently have a test form ("PartLookup") that I had set up with the wizard when I first started to play with databases, and it is based on a query, also called "PartLookup".

    In the query design of "PartLookup", I have it based on a table that the standalone parts program uses, called "PN". In the first query box, I have the field set for "PNPartNumber", table "PN", Sort Asc., Show, and "[Forms]![PartLookup]![QueryBox]" for the criteria. I also have the rest of the fields I want to show set up to be included...

    Now, when I open the query by itself, it prompts me for a part number, and when I enter it, it brings up a datasheet with that particular information. Cool.

    This form I mentioned, "PartLookup", has the fields I want to display, and one textbox ("QueryBox") where you can actually type something in, which is unbound. I have the After Update event set to run the macro "PNQuery2" (which is just "ReQuery"), and it works great.

    What I want to do, is in my "Invoicing" form, I want to put the part number into the "Part1" field, and have it run the query, and return the value of "PNTitle" (From "PN") to the "Part1Desc" field (from "Invoicing").

    The problem I can't seem to get around is that the "Invoicing" form is based on the "Invoices" table, and I can't seem to figure out exactly what needs to go where.. I've got about 30 pages of VBA code in the invoicing form, and it's hard to keep track of everything.

    The next thing I need to figure out is how to get VB 6 to work with my database, and then figure out how to migrate it over to SQL. Luckily, I'm a pretty quick learner, once I find good information.

    Thanks again for your help!
    Brian

Posting Permissions

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