How to open a second connected form in new window 1

[rew2009]

Sorry - I didn't complete the subject line before I sent off the last thread.

I have an access form bound to a query and navigate records by means of a list box on that form. I want to open up a second form in a new window with a command button on the first form to display additional information from the same record being displayed on the first form.

I can create a subform embedded in the first form where the record displayed is connected to the main form, but I can't get it to open in a separate window. How do I do this? I am somewhat new to access with only limited knowledge of vb. Thanks for your help. Russ

 

[dhookom]

I believe this is possible to create with the command button wizard.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

dhookom, Yes I can ceate the command with the wizard to open a second form in its own window but that second form is not connected to the first form; i.e., if the first form openned to record 1 but subsequently had been navigated to say record 5, if then opening up the second form with the command button I find that the second form has openned to record 1. I need to find out how to link the two.

Thanks Russ

 

[dhookom]

Again, I thought the wizard provided the opportunity to open the form to a specific record. If you can't find this, then modify the code created by the wizard to use a WHERE CONDITION in the DoCmd.OpenForm method. If you don't know how to do this, come back with:
-Your wizard generated code
-Your field names in the main form and opened form that are the link
-The data type of the field

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

dhookom, The following is what the wizard created for the command button “Command42”.

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MemoForm1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub

The second form is “MemoForm1” and displays two fields “ClientName” which is a text field and “Memo1” which is a memo field. Both are in the table “tblClients”.

First (main) form: tblClients3
Second form: MemoForm1
Database table: tblClients
The fields to be linked:
ClientName, text field,
Memo1, memo field

I think the answer is probably in the “stLinkCriteria” in the DoCmd.OpenForm but I don’t have a clue beyond that. Thanks Russ

 

[dhookom]

Usually a link criteria field involves a primary key in one table and a foreign key in the other. If your primary key is a text field named "ClientName" then try the following code:

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria ="ClientName =""" & Me.ClientName & """"
    stDocName = "MemoForm1"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
    Exit Sub

Err_Command42_Click:
    MsgBox Err.Description
    Resume Exit_Command42_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Dhookom, This worked in that it linked the two forms. But ClientName has duplicates in the table and is not the primary key. My primary key in the table is "ID" and that is a number field. I know it has to be converted to string, I tried:

stLinkCriteria = "ID =""" & Me.ID & """"

But I knew it wouldn't work as I was mixing numbers and text. Let me know the correct syntax and we will be there!

Thanks Russ

 

[dhookom]

stLinkCriteria = "ID =" & Me.ID

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

dhookom, thanks, that worked, you were a great help, Russ