Selecting a record in a a form and populating another form with those values.

[prisyasir]

Hi,



I have just started to work on MS access. Came across this situation. Really donno how to go about.

Say I have a 'Member_master' table that holds Mem Id(primary key), Name, address, Post code and phone no.

On a form,I have to get the name as input. And based on the name,I have to populate address, post code and phone number fields on the same form.

Now,
1) When there is only one record for an entered name, I just populate the fields on the form(Form1). (I did this).

2) When there are more than one record for a given name, I want a to open another form (Form2) that would display all the records having the entered name.
(This part is also done.)

3) Actual difficulty for me is,
In the form (form2) which displays the records having same name (say, may be 3 records). i want to click on one record that I needed and this should populate the address, post code and phone no fields in the first form based on the record I chose in form2.
(i.e Once I click the record on form2, the record should be selected and the form2 should be closed, while form1 should have selected values displayed)

It would be of great help if someone could throw some light on this.

[Dal Jeanis]

There are lots of methods possible. For example, selecting the record in the second form could set a global variable or Tempvar, then cause a "requery" on the main form to populate the form. Then you'd go back to the main form and modify its code slightly to use that same Tempvar.

[prisyasir]

Thank you very much for your response.

The second form is a Form wizard linked to table 'Member_master'

Can you please tell me how to access individual record displayed in this form?

[Dal Jeanis]

I'm not very familiar with using Form Wizards. The conceptual problem I have with using MS's inbuilts is that, like macros, the more Access is doing for me, the less I know exactly *what* it's doing. Which means, I have to depend on Microsoft's documentation of the features, and they tend to hide all the knobs and levers that I need to adjust to do my job.

After a quick google, it looks like you meant that you used a form wizard to create the form, rather than you are using form wizard as a form. What does your form look like? Are you using a drop-down box, or a continuous form that shows the value of each record, and the user clicks one, or what?

[prisyasir]

Yea that's right. I used form wizard to create a form. Also, It is a continuous form.

[June7]

Are you duplicating data? Are you saving Name, address, Post code and phone no from Member_Master to another table? Should only save the record ID, not duplicate all the data. The related info can be displayed by query that joins tables. Names make very poor unique IDs.

[prisyasir]

No. I'm trying to view data or display record based on name as input.
(i.e)Assuming I just know the name of the member but not his mem_id.

[June7]

Simplest thing to do is have a main form bound to Members and a subform showing the related records. Filter the main form and subform will synchronize related records.

Next simplest is to have a multi-column combobox that shows all the dependent records, pick the one you want and use that selection to filter the main form. And then there is dependent/cascading comboboxes to narrow down the selections.

All those easier than opening another form and passing value back to the calling form.

Depends why you need to isolate one of the related records.