synchronize forms with differents records

[Nelson Bile]

Hi:
I¡¦m learning the basics of MS Access to create Forms. I don¡¦t know anything about Macro. I¡¦ve created six forms. One of them is the main one. I used the command button from the main form to calls the others forms. My problem is how I can go from the main form from a specific record, to the other forms with the same record number, automatically, with different tables. So that the same record number has related data for each form.
Ex.
Form2
Record3 from Table2
Form3
Record3 from Table3
Form1 ---„³ Form4
Record3 Record3 from Table4
(Table1) Form5
Record3 from Table5

Can I do it by modifying table lookup, key primary, form properties structure, etc? I do not know how to do it in macro.

Regards,

Nelson

 

[dcx693]

There are a couple of ways to achieve this, but all of the ones I know involve VBA. I never use macros, so maybe someone else can respond if they know how to do it with macros.

With vba, you can have forms look for values on other forms. For example, if your main form is looking at record # 5, when you open forms 3 you can have the Open event on form 3 check to see if the main form is open, and if so, bring up the same record number.

And let's say you have all four forms open, and you want to synch them all when you change the current record on any one of them. That's possible also. Using the Current event of the forms (the event that "fires" when you move from record to record), you can check to see if the other forms are open, and if so, move them to the same record number as the one you just moved to.

If you're interested in using VBA to solve this problem, respond and I'm sure I or someone else can help you along.

If you'd prefer to stick with macros, hopefully someone knowledgeable will respond. Good luck.

 

[soundsfishy]

If your relationships are setup correctly, why dont you use the wizard to open form2 with the matching ID.

dcx693 is right, there are several ways but the easiest for what you want is using the wizard. The wizard is a good starting point.

 

[aussie_user]

I have a similar problem and would love to know how to use VBA to get all of the forms on the same record.

My records all deal with a Project ID number. Because there is so much detail (eg room for 12 reports,with 12 dates due and 12 dates rec'd and 12 payments, with dates due and dates paid plus GST) I need several queries plus several forms to display all the information for a project. Currently I have a form with 7 tabs. Each tab contains a subform. When I open the form I must click on each tab and do a find so that they all display the same record.

Any suggestions on how to go about setting up VBA so that each subform would look to the main form for the record number would be greatly appreciated.

 

[Rich]

Are you saying that you have 12 DateDue fields etc?

 

[Mile-O]

Any suggestions on how to go about setting up VBA so that each subform would look to the main form for the record number would be greatly appreciated.

You could forget about VBA for a while and normalise your database.

 

[Pat Hartman]

Normalizing your tables is an excellent suggestion and will ultimately save you tons of work.

To open a form/report on a particular record, simply use the where argument of the OpenForm and OpenReport methods. Look up the syntax in help. If you use the button wizard to build the code, this is one of the options (open the form/report to a specific record). So by choosing the correct option, you won't need to write the code yourself.

 

[aussie_user]

Thanks for your suggestions.

As far as I understand normalization, my database is normalized. I have the data stored in separate tables. All the data pertaining to reports required is in one table, all the data pertaining to payments is in another table etc.

I have to allow for the potential of 12 reports or 12 payments for each project alothough not all use that many. Some may only have 4 or 6.

After my main project table which only holds fields that don't seem to fit in any other category, I have 6 major tables. 4 of them average 20 fields and 2 average 45 fields. I also have various small tables with 5 or less fields.

My understanding is that you should not create a HUGE query combining all of tables. My solution to being able to display all of the required information about the projects was to create a tabbed form with separate pages relating to each of the major tables. Since I don't have one query that hold all the data, I had to create subforms on each of the tabs so that I could display all of the data relating to a project. I currently have almost 300 projects in the database so if I want to look at the details for #150 I have to do a search on each page to find it. If I then want to move to record #151 I must either do another search or manually advance each record.

 

[Rich]

You don't need 12 fields to hold 12 payments, just one with 3,4 12 etc records, and probably another field which indicates what the payment relates to

 

[Pat Hartman]

The simplest way to handle your synchronization problem is to use a main form that is bound to a query based on your primary table. Then place the subforms on separate tabs. Use the master/child links and Access will automatically synchronize the subforms to match the mainform record.

The main form doesn't actually need to show any fields if you don't want to take the space. But, it should have an unbound combo in the form header. You would use the combo to find the desired record. The wizard will build the code for you. Choose the option that says to find a record on the current form.

Your tables are not normalized. Separating the data into separate tables by whether it it used for reports or payments is not an appropriate division. You need to do a little reading on the first three normal forms. Search the archives here for lots of references to good articles.