autopopulating fields in MS Access

  dimercaprol 18:01 07 Jun 2009

I have a database of clients related in a "one-many" relationship to their visits. At the first "baseline" visit, some product fields are filled in for a particular client. Then, on future visits, these products usually remain the same although one or two (out of about 5) may need to be changed occasionally (say once in 5 visits). I need to be able to keep all the products ever used by the client but also be able to easily produce a list of their current products. e.g. for any client, I need to be able to produce a report saying "This client currently uses the following products "a, b, c, d, e" and has used all the following products ever "a, b, c, d, e, f, g, h". However, I don't want to have to fill in all the product fields on every visit but only the changes. Is there anyone who can advise me about the best way to design this database?
Many thanks in anticipation.

  Chris the Ancient 10:33 08 Jun 2009

Well done on doing the planning first!

I'm presuming that products are derived from a table. The forms could then use combo boxes to list the products for entering into a particular client's record in a table.

When you open the form for a particular client, you could use a bit of coding to copy the previous record for the customer, update the date (or whatever) if needed, and then just make appropriate products purchased changes with combo boxes.

However, that is a straw in the wind idea as I'm not 100% sure of what you need in your db.

A VERY good forum (if you don't have enough success here)on Access is available at click here. It needs you to register - but it is free.

I have had lots of help in there. Responses might be delayed because the vast majority of the gurus are in the USA - but, I wish I had 1% of the knowledge on Access that some of them have.



  dimercaprol 21:56 08 Jun 2009

Thank you very much. The "bit of coding" is the stumbling block, I think.
I am very grateful for the link - I didn't come across this forum on previous googling. Very useful.

  Main Access 14:49 11 Jun 2009

One way to do this is to have a master record for each client, this is populated on the form through code into the correct unbound fields.
If any field is changed, you copy the master record to an audit table with the current date and the client ID. Then you update the master record. It would be best to do this in a transaction so that is any error occurs you can roll it all back and try again

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Visual Trends 2018: This year’s must-know colour, design, branding & photography trends

iMac Pro review

Apple Music : comment obtenir 3 mois gratuits ?