How do I connect two forms...

[electro]

I have searched and not found a conclusive answer to this question.
I have two tables company(IDCompany unique key) and contact(IDContact unique key and IDCompany secondary key). I Select and enter companys in companyform when I have selected a company I press a button too go contactform, but I cant get IDCompany(in companyform) too connect the next contactform.
If I look at the code created it looks correct. But it they IDCompany is not transfer in the opened form.(As secondary question I would rather have the name than the IDcompany) Any suggestions... (Yes I have used access 2003 wizard)

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Contacts"
    
    stLinkCriteria = "[IDCompany]=" & Me![IDCompany]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command31_Click:
    Exit Sub

Err_Command31_Click:
    MsgBox Err.Description
    Resume Exit_Command31_Click
    
End Sub

Even tried using

Me.IDCompany= Forms!companyform!IDCompany

in Form.open but then I get an error message saying that
"You can't assign value to this object"

 

[mhartman]

Hello:

Have you established your relationship link between your two tables. This needs to done to keep your Primary key and Foreign key assignments synchronized.

Regards
Mark

 

[electro]

Hello:

Have you established your relationship link between your two tables. This needs to done to keep your Primary key and Foreign key assignments synchronized.

Regards
Mark

Yupp they are syncronized but I still get the error that Runtimeerror(80020009) "You cant assign a value to this object"

 

[RuralGuy]

Use the Load event of the next form. The Open event is too early to reference controls on a form. Still, the wizard created code should have worked. Does the IDCompany exist in the RecordSet for the next form yet?

 

[electro]

Use the Load event of the next form. The Open event is too early to reference controls on a form. Still, the wizard created code should have worked. Does the IDCompany exist in the RecordSet for the next form yet?

Yupp it does :/
I move the code to Load and now it does work :-D
Thanks.. sometime a solution can be so simple

Now is the only question to get CompanyName to show instead of CompanyID in the contactForm

 

[RuralGuy]

You can pass any number of parameters in the OpenArgs argument. I think you are creating a New record in the "Contacts" form and the CompanyID field is not completed yet. If that form was based on a query that joined the company and contact tables on the CompanyID, then the Company name would just be there when you put in the CompanyID. You don't want to duplicate the data field do you? That breaks normalization rules.

 

[electro]

You can pass any number of parameters in the OpenArgs argument. I think you are creating a New record in the "Contacts" form and the CompanyID field is not completed yet. If that form was based on a query that joined the company and contact tables on the CompanyID, then the Company name would just be there when you put in the CompanyID. You don't want to duplicate the data field do you? That breaks normalization rules.

Well yes... the contacts table have CompanyID and nothing else twice.
Will solve the name with a displayed query..
thanks

 

[electro]

You can pass any number of parameters in the OpenArgs argument. I think you are creating a New record in the "Contacts" form and the CompanyID field is not completed yet. If that form was based on a query that joined the company and contact tables on the CompanyID, then the Company name would just be there when you put in the CompanyID. You don't want to duplicate the data field do you? That breaks normalization rules.

Sorry missread your answer before. No the companyID key is the one thats get saved in the contacts table... normalisation rules are not broken

 

[RuralGuy]

Good! Is your problem resolved?

 

[electro]

Good! Is your problem resolved?

Nope... The problem is still...
Want to enter a new contact when opening the contact form but then I need to have a query to fill the form but I still need to make the selection in the query from the last form... still think I missed something. I did this in VB or VC++ I would have no problem at all but the limitations of access GUI interface/wizards is the problem(and which blocks the straight forward way of using VB).
Exactly how would you do this (all steps) when you have a company table and contact table... and when you press "create contact" on the company table you want the contact forum opening to possible to enter all info and still show the selected companys name...

 

[RuralGuy]

You are going to want to bind the Contacts form to a query that joins the Company and Contact table on probably the IDCompany field ,hopefully an AutoNumber. The moment you put the IDCompany in the new record, the Company name will show up assuming you are displaying the Company name in the Contacts form.