moving records

[kingsgambit]

I need some ideas.
On my main form I have clients details.
There is also a subform that you can select as a product and qty, these to forms are links and all works fine. I can produce an invoice
But if the client a week later wants to buy some more products, I can select them from a listbox which will open the main form with there details in it, but it also shows what they bought last. I need that to be clear.
Can you use code to clear the sunform which is in data view but without loosing the records, so a new invoice can be products from the new products bought

 

[jfgambit]

Have you considered adding an Order Complete Yes/No field to your table. Once the order is complete, you can manually or through code update the field to YES (-1). Then base the subform off Orders where the Order Complete field <> Yes.

Just a suggestion...

HTH

 

[kingsgambit]

How would that work?
I am not sure where I would put the yes/no orders complete.
It would need to be attached to the subform, the subform saves the orders into a orders table.

 

[jfgambit]

As Pat stated, I'm not sure you have all the correct tables. If you had an Order table that had all the Ordrs for each Customer, you could add the Completed Order Yes/no field to this table. As Pat suggested i would look into the Northwind example supplied with Access. It has excellent examples of what you are looking for.

HTH

 

[R. Hicks]

What Pat was trying to say is that you can have "many customers with many orders" ... this means you need a "many to many" relationship between the two tables.

Access does not directly support "many to many" relationships so to get by this ... you need a "Junction Table" to place between the two tables.

You can look at the "Northwind" sample database to see this setup.

Only thing is .. do not use a "Compound Primary Key" in the Junction Table as in the Northwind example. If you want to stop duplicate records ... use a "Compound Index" instead.

RDH

 

[kingsgambit]

iI have had a look at Northwind, I am not quite sure how it is do.
It brings up the subform as a new record, but how to assigns CustomerID with OrderID a bit lost.
I did want to do what Pat said add a Yes/no field, but my subform is in datasheet view so where would I put this field?
My subform was does with the wizard, so it opens all the records abtaining to that client, so I am not sure how I would put a filter in the subform to show only records with no ticked box.

Help

 

[kingsgambit]

Ihave been lookoing at Northwind, but I still can not work out how the subform is linked to the order No.
The Order No is autonumber, and all the records in the subform for one order come under that Order No.
Can anybody exolain how this works

 

[kingsgambit]

quotes

Hello again,
I have come back to this project but I still do not understand how Northwind works. I can not see why the subform is a query, and how if you pick a customer froma list it will generate a new autonumber when the list is pulling info from a table.
How does the products sold subform fill in the customer id?
I have attached a simple database, could you show how it works.
I looked at my tables that are linked but they don't show any info

 

[kingsgambit]

QUOTE

Hello again,
I have come back to this project but still can't get my head around it.
I can see how you attach the quote ID but not the customer ID.
Also when you select a customer from the combo box it treats it is a new record. I also noticed that the customers have a text for ID, is it not possible to give them a auto number?
I have attached a small database could you possible show me where I am going wrong.
I am not sure where you would put the tick box, it only lets me link the subform to the main form by my quote ID how would a add if tick is no display new record