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.
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.
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.