MS Access form for multiple entry against a specific customer

[DaveHarding]

Hi, I am trying to create a order form for sales in Access to allow multiple purchases against a customer. These purchases (Products) will also have set process against each customer.


I have the form as below which works well but I have to enter the orders individually , for example a company called Smiths Bros orders 4 different items , I want to add these line by line in one form with Smiths Bros prices and not 4 times.



Any help would be greatly appreciated

Thanks

Dave

[Micron]

this requires form/subform arrangement or perhaps a split form.
In first case, main form contains all the static data ( the "one" side, usually positioned at the top) and the subform below with the "many" side of the relationship. You can find lots of info on subforms. As for split, I have only played with them while helping others. They present too many problems for me to every consider them as a solution - as evidenced by the number of forum issues around them.

[CJ_London]

it looks like you only have one table - the excel way. You need at least four tables, if not more

tblCustomers
CustomerPK
CustomerName
CustomerAddress - this may need to be in a separate table if your customers have multiple addresses - and you may have different billing and delivery addresses

tblInvoiceHeaders
InvoiceHdrPK
InvoiceNumber
InvoiceDate
CustomerFK
Comments

tblProducts
ProductPK
ProductName
ProductCost - these may be in a separate table so you can record cost changes over time
ProductPrice - these may be in a separate table so you can record price changes over time

tblInvoiceLines
InvoiceLinePK
InvoiceHdrFK
ProductFK
Quantity
InvoicePrice

the user narrative is 'I want to send these products to that customer on this invoice'

so your main form will be based on the invoiceheader table, you will have a subform to display the customer details once you have selected the customer (customerFK) and another one to allow you to add invoice lines - which will 'lookup' via ProductFK into the product table to get description and price

[Micron]

Good observation. I'd say your expertise is probably more commerce based whereas mine is more maintenance based, hence this question:
Why isn't the main form customer based? You can't invoice without customer data so I would have thought Customer to be the main/parent, and if you go to create an invoice as described, there is no CustomerFk to choose from the invoice header table for the new record? At best, you could have a combo bound to CustomerFK but its row source is CustomerPK? I probably would have approached this challenge as Customer main form with sub forms for Invoice header and Invoice Details, which would not be correct?

[CJ_London]

Why isn't the main form customer based?
...
I probably would have approached this challenge as Customer main form with sub forms for Invoice header and Invoice Details, which would not be correct?

it could be, but then the invoice header would be a subform and the invoice lines a subform off that (OK, could be a subform on the mainform, but then the linking becomes more complex and requires code). However I still maintain that your starting point is 'I want to raise an invoice'. It is a matter for personal preference but also process.

as you say, you would have a combo on the invoice header main form - source customer table, bound to customerFK. the customer subform would link child/parent to customerPK/FK so once the combo is populated, the subform would populate with the appropriate customer record, no code required.

Would be an interesting debate if anyone else wants to participate

[Micron]

(OK, could be a subform on the mainform, but then the linking becomes more complex and requires code

That's what I meant by
Customer main form with sub forms for Invoice header and Invoice Details but probably didn't express it clearly. Not sure what code you had in mind, but without actually getting in to this, I was thinking main form has cust details (how we load that isn't important) and invoice details is either disabled or just not loaded. Invoice CustIdFK links to Cust.CustIDPK. When invoice is validated, details shows. DetailsInvFK links to Invoice.InvPK. Would that not work?

To be honest, I'd probably just have an Invoice creation form with details subform and in its header, just populate the minimum customer fields. They don't have to be bound because the Customer combo can provide the CustIdFK to the invoice, and each detail record would have the CustIdFK. I hope this isn't seen as hijacking the original thread, although it seems related to the issue. Regardless of the approach, it requires properly designed tables as you say.