Passing info between forms

[BillyL]

I have a main form that contains company information. I want to place three subforms in the main form which will be contact information for three different contacts for the company. I envision an ADD button on each of the subforms that when clicked the Contacts form will open with the company information already entered and I will be able to add a contact to the contact table. I would also like the Contact Name, phone and email only to appear in the subform when I close the Contacts form.

Here are the form names:
Company form: frmCompany
Contact form: frmContacts
Contact subfroms: sfrmContact1; sfrmContact2; sfrmContact3

The Company table primary key is ComoanyID, it also has fkContacts to related to the Contacts table.
The Contact table primary key is ContactID it also has fkCompany to relate to the Contact table.
It is a one to many relaionship Company (one)->Contacts (many).

Is the Openargs property what I need? If it is, I am not clear on how it works.

I also envision that if contact name in the subforms is not blank, the ADD button will change to VIEW and by clicking the VIEW button the Contact form will appear with the contact record displayed.

Any help would be greatly appreciated.

Thanks!

 

[ssecca]

Unless for some reasonthe three contacts are comong from (or being entered in) three seperate tables then this is going to be very difficult. Have you considered using a continuos form sub form? This would list all the contacts for a company in one easy to use listing and allow you to take special action as desired per contact, or add new contacts delete old ones etc.

SSECCA

 

[dakota81]

My work with Access has been more towards manually running the forms through vb. I've got a module sitting in the module tab with a bunch of public variables. When I need to pass miscellanious data to a form that's being opened, I write to a variable in the module and read it off in the opened form.

There's always ways to accomplish what you want, you just gotta figure out exactly what you want first.

 

[ssecca]

I applaud your use of VBA insted of relying on Acess itself to do a lot of things, and I agree with that in principle.

Frankly, I have used openargs in the past, but have found using the record source property easier to control (and understand). You can assign the SQL statement for each form on the fly and then requery the form thereby making the information available.

ssecca

 

[BillyL]

After I posted the message I re-thought the issue and I agree with you ssecca. With that being said, I assume that I would need to have a query of contacts based on the CompanyID field and then a subform based on that query. Would I use Querydef for the generation of the query? Now I need a little help with that. I would like the functionality to be something like this.
1. Have an Add button to add a contact. This would open the Contact form and upon closing it the subform would be updated.
2. By double-clicking on one of the records in the subform that record would open for viewing and editing.

Also, this database will be used by several users. and they may be accessing the same company and/or contact records at the simultaneously. I am planning on installng Office Developer to create a run-time version of this app. Am I on the right track?

I very much appreciate any help with this, I know it is quite a project, at least for me, but that is what sites like this are all about. Life is good.

 

[ssecca]

OK I need some more background info. I'm going to assume that you have fully (or at least nearly fully) normalized your table structure behind this application.

If so you have one tabl ethat holds all the company info and another that holds all the contact info with a forign link to the companies. If this is so..

Create the main form pulling you company information as you want but make sure your forign key is included in the pull. You can hide that field later if you want (recomended).

Now add a sub form do not use the wizard as there is no need to link any parent child info at all. The sub form will get it's data from the contact info table.

The Querydef needs to be named and will need to get the company's foreign key on the open main form. On whatever event you want probabbly a combo or list box item selected kick off the procedure that creates the query def and set the recordsouce of the sub form to the name of the query. Requery the sub form and your contacts names will appear. You can include a button on the sub form that will allow you to popup a form to allow editing of the exsisting contact and on the main form place a button that will pop a form to allow you to add a new contact. On the close of the add new contact form requery the sub form so that the new information will present itself.

Hopefully this is enough to get you started. Unfortuatly creating the perfect contact management application is very very huge project. Good Luck

ssecca

 

[BillyL]

I think I am on the right track but I could still use a little help. I created a subform in table format based on a query and it works well. The contacts are displayed in the subform correctly. I have two questions:

1. How can I select a record in the subform and open the Contacts form. Do I need a button in the main form to do this? If so, how do I refernce the current record in the subform?

2. I have a button in the main form that will open the Contacts form and allow me to enter a new record. The CompanyID field is filled in automatically from the Company form. If I close the form without entering any information (as I am sure users will do) it still is entered in the table because the Company ID field has been modified. I can't figure out a way to check if the Contact field is null and if so, either add the record or delete it from the table immediately.

Thanks again for your assistance.

 

[ssecca]

Question 1) I would place a button on the subform that opens a contact editing form. This can be the same form as the add new contact only using the filter property so that you only display the wanted record. The button should read the unique Id of the contact it is currently on and pass it as the filter in the openform command.

Question 2) a little more dificult to do but I beleive the best way is to make thsi an unbiund form. That way you can verify any and all data before placing it in the table. If you are not comfortable doing this then option two is do not fill the Company id in at the time of the form opening. instead store the value in a hidden field and only move it to the correct place when the user closes the form and you have verified the validity of the data.

Hope this helps
ssecca