Open and link forms

[Mary10k]

Hello,
I have a form with a button on it. I would like the button to open a second form when clicked. The problem I am having is linking the second form to the record in the first form. This is not a sub form/form example, they are two seperate forms.

Can this be done.
Thank you.

 

[FancyPrairie]

Filter the 2nd form based on the unique ID of the current record of the first form. Do this via the OnOpen event of the 2nd form.

Me.Filter = "uniqueid = " & Forms!form1!UniqueID
Me.FilterOn = True

 

[Mary10k]

I tried the code
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "EMPI = " & Forms!Contact!EMPI
Me.FilterOn = True
End Sub

and I am getting an error "missing operator in query expression". Any suggestions?
Thank you.

 

[Remou]

Hi
I tried the above and could not get an error "missing operator in query expression" . How about something like this in the OnClick code for the button (code generated by an Access wizard)?

    'If EMPI is a text field, use this:
    stLinkCriteria = "[EMPI]='" & Me![EMPI] & "'"
    'If EMPI is a numeric field use this:
    stLinkCriteria = "[EMPI]=" & Me![EMPI]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[willir]

linking the second form to the record in the first form

Filter = / Filter On is one approach. Works fine when openning a form, or when applying a filter.

Have you tried using the wizard for the combo box with the open form thread? One thread allows you to open the target form and find a specific record target record that matches a key from the source form -- sounds like something you are doing. The nice thing here is that the wizard does most of the work for you.

Another approach. You can actually use the OnCurrent event procedure so that if the source form is open, as you scroll through the source records, the records on the target form are synchronized.

Coding is not too difficult. This is one approach for this strategy...

- FormA, source form
- Contact, target form
- EMPI - is a text string, and is the name of text boxes on both form FormA and form Contact, and the corresponding field has the same name
- tblContact, source for Contact form

Dim strSQL as String, strQ as String
Dim Frm as Form, strForm as String
Dim booPass as Boolean

strQ = Chr$(34)
booPass = False
strForm = "Contact"

For Each Frm in forms
   If Frm.Name = strForm Then
      booPass = True
   End If
Next Frm

If booPass Then
   strSQL = "SELECT * From tblContact WHERE EMPI = " _
   & strQ & Me.EMPI & strQ

   Set Frm = Forms!strForm

   Frm!Recordsource = strSQL
   Frm.Requery

End If

If the EMPI field is numeric, then ...
strSQL = "SELECT * From tblContact WHERE EMPI = " & Me.EMPI

If the target form is not open, nothing happens. If the target form is open, then the displayed record in Contact[\i] will change to the corresponding record change in TableA[\i].

There are other stratagies...
Richard