Open Form from list

[Nbene]

Hi,

I have gone through this forum and have been going over the same
problem for the past week.

my tables are such
tblContact 1>M tblContactOrganisation M<1 tblOrganisation

i have, from tblcontact, made a form called "frmContactsList" this
form displays the list of contacts (Continuous form)

I have added filtering at the top allowing user to search for
contacts.

In the continuous form there is a Cmd button on each row to open
another form "frmContactDem" with that contacts details from
"tblContact"


frmContactDem.recordsource SQL string SELECT * from tblContact and it
has a subform "frmContactDemSub" which has a record source of
qryContacts (tblContactOrganisation and tblOrganisation)

The form frmContactDem and sub form open perfectly however it only
opens at the one record, obviously the first record.

My issue is that i can not filter the form to display only the contact
that corresponds with the CMD button.

When i click on the CMDbutton i gives me an input box with Garbled
characters but when input an id number into it and press ok it gives
me the right record on the new form.



Private Sub cmdOpen_Click()


Dim SQL, SQL2 As String
Dim IDString As Variant

IDString = ContactId.Value

SQL = "SELECT tblContact.ContactId, tblContact.Title,
tblContact.FirstName, tblContact.MiddleName, tblContact.Surname,
tblContact.TelephoneMobile FROM tblContact "
SQL2 = "WHERE tblContact.ContactId = " & IDString

Form_frmContactDem.RecordSource = SQL + SQL2

DoCmd.OpenForm "frmContactDem", acNormal


End Sub


I hope someone can help me as this is getting frustrating.

Thanks

Nathan

 

[Guest]

Hi Nathan,

I noticed a couple of things in your code.

Dim SQL, SQL2 As String

In this line, you have declared SQL as a varient and SQL as a string.

IDString = ContactId.Value

".Value" is the default property and is not needed. Doesn't hurt, but is not
needed.

Form_frmContactDem.RecordSource = SQL + SQL2

AFAIK, you cannot set the recordsource using this syntax. It would be
something like:

Forms!frmContactDem.RecordSource = SQL & SQL2

but the form would need to be open first.


Now your problem...........

**On a COPY of you database, try this.

**Open the form "frmContactDem" in design view and set the recordsource for
the form to:

"SELECT tblContact.ContactId, tblContact.Title,
tblContact.FirstName, tblContact.MiddleName, tblContact.Surname,
tblContact.TelephoneMobile FROM tblContact "

**Save and close the form.

**Open form "frmContactsList" in design view. Move the button "cmdOpen" to
the form header.

**Change the code for the button to:

'------------
Private Sub cmdOpen_Click()

DoCmd.OpenForm "frmContactDem", ,,ContactId = " & Me. DoCmd.OpenForm
"frmContactDem", ,,ContactId = " & Me.ContactId

End Sub
'------------

** Save the form and try it.



You could also delete the button and use the double click event of the
control named "ContactId".

'-----------------
Private Sub ContactId_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmContactDem", ,,ContactId = " & Me.ContactId
End Sub
'-----------------

Put a label in the header that says to double click the ContactId to open
the form.


HTH

 

[Nbene]

Hi Steve,

Thanks.

This helped.

Am on the right track now.