autopopulating fields in MS Access

  dimercaprol 07 Jun 09

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 08 Jun 09

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 08 Jun 09

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 11 Jun 09

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.

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

IKinema aims to banish droopy shoulders and wonky spines in animated CG characters

How to use Apple Music in the UK: Complete guide to Apple Music's features

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message