I would like a form that has 2 list boxes on it relating to 2 tables
One is customer (customer has fields customer id, customer name etc) and I want customer name displaying
One is invoices (invoices has invoice id and invoice number) and I want invoice number displaying
I’d like to choose a customer and only ‘see’ the invoices for that customer.
Once the 2 are selected I want a way to pass to a separate form (or possibly the same form) to display a third table, invoice details (data from invoice table plus item, price, quantity etc from invoice details table).
I’ve tried to follow numerous examples on this site from a dog/vets example to progressive lists and I’m not getting it. I’ve tried including a form filter and leaving it out. I’m not sure what should even go in this filter and how it gets there. If I use id for the listboxes I get the id displayed, but switch to something more recognisable I get a blank listbox. I was going to put sometimes as it sometimes appears to work and then doesn’t! I’ve tried the wizard for the form and just creating it. I’m IT literate and can work out what a macro is supposed to be doing with the help of comments although I’m trying not to use them unless I have to. I’m half way through filter/forms which may give me further insight.
Can someone tell me in words of one syllable the approach I should take. I know they’ll be half a dozen but one that I can just follow and it will work. I’ve picked up so much from this site but this is beating me hands down.
Hello,
A filter table is a place to store temporary information. You only use one record. Have attached a sample for you based upon your description. There are no macros needed - push buttons used instead.
The first form is a list box for Customers.
The second form is a list box for Invoices and a push button for a newly chosen customer.
The second form has a sub form for invoice table. The sub form has a push button for a newly selected invoice number. The sub form is linked to the second form via the invoice ID.
So you select a customer in the customer list box. That ID goes to the filter table CUST_ID field. Press the button to select that customer. The button action is set to refresh the second form. So the second form list box is updated with invoice numbers only form the selected customer.
Selecting an invoice number will put that ID into the INV_ID filed of the filter table. Press the button to select that invoice. The button action is set to refresh the sub form and that displays only the selected invoice.
None of the forms are set to allow new records to be entered.
Sample ------- ProgressiveLB.odb
That’s exactly what I’m after thank you so much. I now have to put that into mine and get it to work. I did start off with a filter with 2 ids for customer and invoice but abandoned that a long time ago as I went through many iterations. Just one tiny question on what method you used to apply this design. Did you, in the main, use the wizard to create the form or from design view?
I’m now going to go away and see if I can get this working. Thanks again.
Once again thank you for this. I spotted my schoolboy error. I’d tried to use the filter table and had set it up but then only used it in the first selection to pick up the customer id. I then didn’t use it in the invoice selection so was either getting all invoices, or none, displayed. Brilliant, cheers!
For the form I actually used both methods. I started by using the wizard to create the form for the Invoice detail. My way of getting to the end result quicker and making it easier through the wizard. Then created the other two forms and finally moved the wizard created form (used navigator for this - drag & drop) to become a sub form.
I have it working starting from scratch. Going to try the wizard approach next. Cheers