Hi all,

Please can someone help. I need to open a form "frmViewContact", with a preselected record in it from 2 buttons, each on a different form.

frmA has cmdViewContact (criteria from a combobox, cboContactID)
frmB has cmdViewContact (criteria from a combobox, cboContactID)

Here is my so far incorrect code.

Each button has a Click Event Procedure:

Private Sub cmdViewContact_Click() *** on frmCalls ***
    Dim strContact As String
    strContact = "SELECT mtblContacts.*, mtblContacts.idsContactID FROM mtblContacts " _
        & " WHERE ((mtblContacts.idsContactID) Like [Forms]![frmCalls]![cboContactID])"

    DoCmd.OpenForm "frmViewContact"
    With Forms!frmViewContact
        .SetFocus
        .RecordSource = strContact
        .Requery
    End With
End Sub

AND

Private Sub cmdViewContact_Click() *** on frmTransactions ***

    Dim strContact As String
    strContact = "SELECT mtblContacts.*, mtblContacts.idsContactID FROM mtblContacts " _
        & " WHERE ((mtblContacts.idsContactID) Like [Forms]![frmTransactions]![cboContactID])"

    DoCmd.OpenForm "frmViewContact"
    With Forms!frmViewContact
        .SetFocus
        .RecordSource = strContact
        .Requery
    End With
End Sub

The idea is to set the RecordSource for frmViewContact in code. How do I pass the parameter directly to this form depending on which button I access it from? At the moment it the little parameter input dialog comes up (I don't want that) and I have a SubForm on frmViewContact which only shows if I manually fill in the parameter input dialog.

I hope this all makes sense. Please help. Any help is greatly appreciated.

Thank you in advance,
Clinton

'---------- Code Starts ----------

Dim stDocName As String
Dim stContact As Integer
Dim stLink As String

stContact = Me.cboContactID
stLink = "[idsContactID] = " & stContact
stDocName = "frmViewContact"

DoCmd.OpenForm stDocName, acNormal, , stLink

'---------- Code Ends ----------

Put this code on each buttons' On Click event.
Bind the form to mtblContacts

HTH


mmcdonal

Hey mmcdonal,

Cheers for your help. Unfortunately it still don't work. I still get a dilog box come up before frmViewContact opens saying, "Enter parameter value for mtblContacts.idsContactID". cboContactID should give this parameter. I can't understand why it's not passing the variable.

Any ideas.

Thanks again in advance,
Clinton

How many columns are you retrieving in your combo box? If you retrieve more than 1, you need to tell it which column to use.

Kevin

dartcoach

Kevin,

You're a genius. Many thanks.

Why are the hardest things always the simplest?

Cheers,
Clinton

You're more than welcome.
Kevin

dartcoach