Software

Ask something
Question Locked

MS Access: pre-populate form based on existing record

0 helpful
8 replies

by mirandapearce 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm new to MS Access. I've created a table and a linked form. When I open a new record within the form, I'd like it to be pre-populated with my customers' basic details (name, address etc). I will then add the additional information about my contact with them on that occasion. Is this possible and how do I do it?

Many thanks in anticipation

Miranda

8 replies

by shellbot

How did you create the form?

Reason I ask, is this is pretty simple, if you use the Form Wizard, it asks you which table you want the data from, and which fields you want on the form. When you specify those, it binds the data to the form, you don't need to do anything else.

If you manually created the form and added in labels and text boxes...
Right click a text box, choose properties.
Choose Data tab, click into Control Source space and a drop down should appear. Choose your column you'd like to display in that text box. This is all dependant on you having the form linked to your table.

Make sense?

by mirandapearce

Thanks for the reply.

In answer to your question, I did use the wizard so the table and form are linked. The form's fields reflect the field names in the table and when I open a blank form, the data I input goes back into the table. However...

I don't think I explained myself very well.
My aim is as follows: I set up and maintain the database but my husband conducts the consultations with clients. Each time he has a consultation, I'd like him to be able to open a new form record to input the details on. However, we have repeat clients so, when he opens a new form record, instead of it being entirely blank, I'd like to set up the database so that the basic client details (name etc) are already populated in the form and the the new data relating to the current consultation can be input as well and therefore fed back into table (along with the repeat of the client's name etc).

Many thanks

M

by shellbot

ok, so when you want to enter a new client record, simply go to the end of the table (record navigation arrows) and a blank record will exist to enter data into.

Now to bring up an existing client, you'll have to have some sort of search I guess.

So are you looking to start a "new" row for a client, or simply edit the or add to the existing row?

by mirandapearce

Hi, thanks again.

I'm looking to add a new row because the client is having a new consultation. However, I'd like that new row to be part-populated with the basic client details that already exist because the client has had a consultation with us in the past.

If part-population is not possible, a duplicate of the old record (for that client) would also be OK because my husband could then write over the old data with the new information relating to the new consultation, whilst still keeping the information that stays the same about the client (ie name).

Many thanks

M

by shellbot

I'm pretty busy today, so won't get a chance to give you many details, but basically, you need to decide on your design.

Having the same details in multiple rows is not the best way to go. Normally one would store details such as name, phone ect in 1 table and other details such as new consultation details in another table. They would then be linked by an ID which was common to both tables.
For example:
Table 1:
client_id, client_name, client_phone
table 2:
client_id, appt_date, appt_time

Its called a one to many relationship.
One client , many appointments.

If you have 2 tables, then you should be able to get it sorted relativly easily. A couple ways you could do it.. have 1 form for client, then you could launch another form for the appointment details and have it prefilled with certain data if you needed to.. or you could have the form kinda split, have client details up top, then underneath have thier appt details. What you'd do there is just have the fields linking to thier respective tables and you could then even manually enter in the client_id for the new row.

I've not had coffee yet..does any of this make sense??

by mirandapearce

Hi there

Thanks very much for this answer, it makes sense that I would need more than one table. I'll have a bash at this new set up on Monday.

I might be back with more questions soon, but hopefully not!

Rgs

Miranda

by shellbot

I'm off on holidays for 2 weeks at the end of this week, but I'm sure someone else can help ya out if you need to figure out how to get it to all hang together properly.

This discussion is closed to new replies.