how to open form with data from another table (1 Viewer)

[drshahriyar]

Dear friends

i am a novice in access. i have created a database where appointments are given to patients for some procedures. the appointment form saves data in appointment table. when the procedure is done, i have another form called the procedure form which stores data into procedures table. my problem is that i need some macro that will take data (like name, age, sex, address, tel) from the appointment form and paste it into procedure form. this way i don't have to retype so many fields again.

thanks
dr sherry

 

[Steve R.]

As a quick solution, create a query and use the query as the recordsource for your procedure form.

 

[Mr. B]

dr sherry,

Although you can do what you have asked about doing, it would appear that you may need to look at the design of your database. You should not ever need to duplicate the information like name, age, sex, address, tel at any time. You should have this type if information in a table for Patients and have it entered only one time.

You would also then have a table for Appointments and a table for Proceedures. You would have Primary key fields in each of these tables but you would also have Foreign key fields in the Appointments and Proceedures tables to hold the Primary Key field from the Patients table to relate them to the appropriate Patient. This will create a one to many relationship between a Patient and their appointments and a Patient and their proceedures. If you would ever need to know what proceedures were performed at any specific appointment, you may also need a Foreign key field in the proceedures table to link the proceedures to an appointment.

My next question is, "Can Patients have multiple appointments and multiple proceedures at any one appointment?" If the answer to this question is "Yes" then you would need to have an intermediate table that will allow you to have a many-to-many link between patients, their appointments and the proceedures performed. It is even possible that you might need to have this "join table" between the Patient table and the Appointments table and then another "join table" between the Appointments table and the Proceedures table. (This could get a little complicated, depending on just how you need the data structured and what you need to the data to do.)

I realize that I have raised more questions than I have even attempted to answer, but it just seemed to me that you might need to rethink your database design in order to achieve the desired result before attempting to start duplicating data in multiple tables.

 

[Mike375]

The others have explained why you don't do it.

But to do it....this is something I just copied out of my own stuff

DoCmd.OpenForm "LN", acNormal, "", "", acEdit, acNormal
DoCmd.GoToRecord , "", acNewRec

Forms!ln!LName = Forms!PrintAndClose!LetterName

Forms!ln!ID = Forms!PrintAndClose!NameNumber

DoCmd.Close acForm, "LN"

In that case the form PrintandClose is open and the code opens a form called LN and then the data from PrintandClose is inserted into the field in form LN. In this case the form LN has been opened for a new record. in your case you would probably be opening it for a matching record, that is, matching ID.

If done with a macro then use the SetValue action. Item will be the form/field details where the data goes and Expression will be the form/field details of where the data is coming from. So in the above example Forms!ln!LName would be the Item. but when using macros surround the names in []

 

[drshahriyar]

thanks mike and others for your help. i really appreciate it.