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.

Business Tech Help

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

View Forum Rules | Contact Forum Editor

 

autopopulating fields in MS Access

dimercaprol

Likes # 0

Posted June 7, 2009 at 6:01PM

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

Posted June 8, 2009 at 10:33AM

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

Posted June 8, 2009 at 9:56PM

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

Posted June 11, 2009 at 2:49PM

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.



Check out PC Advisor's other tech forums

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story. Both your name and the recipient's name and address will not be used for any other purpose.