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
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

autopopulating fields in MS Access


dimercaprol

Likes # 0

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.

Like this post
Chris the Ancient

Likes # 0

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.

HTH

CtA

Like this post
dimercaprol

Likes # 0

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.
Regards,
dimercaprol

Like this post
Main Access

Likes # 0

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

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Android One vs Android Silver vs Google Nexus: What is the difference?

IDG UK Sites

iOS 8 review: Hands on with the iOS 8 beta

IDG UK Sites

Thinking robots: The philosophy of artificial intelligence and evolving technology

IDG UK Sites

How to shoot a robot rom-com in three days