Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20

    Unanswered: Help please with combo box and form

    Hi there. My problem is this:
    I have a database that allows me to enter and print invoices, using an on-screen form for the data input, and a report for the output. The invoice number is generated by an autonumber field.

    I need to be able to capture names and addresses of customers that regularly require invoices to save typing. At first, I put separate buttons on the form for each one and used the SETVALUE command in a macro to autopopulate the name and address fields, This is now getting difficult to implement (too many buttons!) and I want to try another way.

    I want to have a combo box on the form that will allow me to open a dropdown list of customer names and autopopulate the name and address fields when I choose an entry. I also want to put a button on the form that will allow me to enter a name and address into the fields and then save it into the combo list for future use.

    I think I need 2 tables: one for the main data and one for the combo list data. I know from previous help I can use a query to put the names in the combo box, but each name must appear only once on the list and I couldn't figure out how. Also, do I use an append query to capture the typed in data for the list somehow?

    I really need help with this as I am a complete novice. I don't know what I need to do to make this work. If anyone can help, I would appreciate it if you could also briefly explain the required steps so that I can learn from it.

    Thanks in advance for any assistance.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    What you are asking can take quite some time to explain within this forum. Perhaps if you could Zip and attach your DB (or a sample) to a post we can take a better look at it.

    One thing is for sure.....nothing is impossible.
    Last edited by CyberLynx; 12-01-03 at 19:09.

  3. #3
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    If you already store a list of customer names then, use the build button next to the RowSource property for your Combo/List Box (They both work the same) When the query window pops up set the Unique Values property to YES (this restricts duplicate records). Then select the fields you want to in your Combo box. If you use a unique ID for each customer and don't want to see it in your list then select that as your first field. Close this query window (Save yes). Set the following properties for the Combo Box:
    Column Count = the number of selected fields
    Column Widths = (set the widths in inches separated by ; if you want to hide a column set it to 0.
    Limit to List = YES
    I'm assuming that you have a table that includes your customer information. And that that table is referenced in the record source of the form. if this is true then you can set the control source of the combo box to the linking filed. Tie the name and address text boxes to those appropriate fields.
    Create code for the On Not In List event that will write a record to the customer table if one is not found. (I can send sample code if you need it) With this process you always select your customer form the combo box and if they don't exist they are created.
    Bob

  4. #4
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Thanks for the input guys.

    CyberLynx, all the stuff is at work, but attached is a (very) rough picture of what I am trying to achieve.

    Main problem is it's in my head, I'm not sure what I'm doing so it is very difficult for me to explain.

    Irwinb, are you saying I can do this using only one table?

    I know next to nothing about VB, so I would appreciate any and all coding help.
    Attached Thumbnails Attached Thumbnails sample layout.jpg  

  5. #5
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    Mordecai,
    I believe that the easiest way to get you going would be to create a separate form for the customer information and then use a subform to add that to your tab control. Yes I would use one table for the specific customer information, Make sure that you use an index column so that you can uniquely reference customers by this one value.
    I would have one table for the Invoice Detail and a third table that joins customer ID and Invoice ID.

    I hope this helps! Once you get the layout straight then we can get into the programming.
    Bob

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Ok...I'm assuming that all three tabs in your form (Customer Details, Invoice Details, and Payment Details) each contain seperate tables (at least they should) with pertinent relationships set..

    Create a Query, oh say..... "CutomerQry" and be sure to select ALL the necessary table fields from the different tables to make up the record(s) for your form (while making the query).

    Place the Query name into the RecordSource property of your main form.

    In the 'Click' event of the combobox located under the Customer Details tab enter the following code:

    Code:
            
    Me.Refresh
    Me.Filter = "[CustomerName] = '" & Me.myComboBox & "'"
    Me.FilterOn = True
    Me.Requery
    Enter this SQL statement into the RowSource property of your Combobox located under the Customer Destails tab:

    Code:
    SELECT myCustomerTable.CustomerName FROM myCustomerTable WHERE ((([myCustomerTable]![CustomerName])=[Forms]![theNameOfMyMainForm]![myComboBox]))
    ORDER BY myCustomerTable.CustomerName;
    [code]


    something like that....adding to the Customer Details table is pretty straight forward as is ensuring that there are no duplicates. more codeing is required.

    Annnnyyyyways...do you get the idea. Would be a lot easier if I had the DB to work with.

  7. #7
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Thanks. When I get to work, I'll post a backup copy for you to look at.
    What I've got and what I want are 2 different things...

  8. #8
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    OK. After 'discussions' with my Boss, I am informed that this is the design we will be implementing, not the one in the picture above

    It is a single form version, not tabbed. The main form (frmINVOICE) is the one I need to put the combo box and button on. The other forms are just for on-screen display on query results.

    This is a fully working version, produced on Access 2003, so if you encounter an error, it has probably been caused by me removing company names and data.

    This is the first database I have ever made, so my apologies for anything that may put your teeth on edge!!

    The file has been scanned using AVG 7.0, but please do check it again before opening.

    Thanks for your time and any help offered.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    I'll take a look at it as soon as time permits.

  10. #10
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Thanks CyberLynx, I appreciate the help.

  11. #11
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Hi there.

    I'm still looking for some help on this subject. I think I've found out how to populate the fields from a second table using the combo box to select by name, but I still need help with the rest.

    Thanks.

Posting Permissions

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