Link records in different forms

[PhilippeM]

I am very new at this and find myself lost in the numerous possibilities Acces has to offer, some help with the folowing problem would be very nice.

I have created 2 forms, named 'Contact List' and 'Contact Details'. The form Contact List, has a subform with a table: 'Contact subform', which displays all the contacts in a table. I also created a button (in the subform) to go from the list to the details, now I would like to go to the record that is selected in the subform's table.

Thank you!
Kind regards,

Philippe

[NeoPa]

Are you trying to go to the specific record within the full list? Or would filtering the [Contact Details] form be more appropriate? That way ONLY the relevant record would be opened in the form.

[PhilippeM]

That is a good question. It would give more possibilities if the user still has the possibility to navigate between the records after the specific record is opened. So I guess that makes it more difficult to work through a filter. I was thinking more in the direction of introducing a 'go to record' action with appropriate conditions, but which I would not know how to define. Is this a possibility?

Thank you!

Philippe

[NeoPa]

I think so Philippe.

However, allowing that extra operator flexibility may well prove a problem. If the operator always navigates via your main form the situation will be more predictable, which will probably make the project more stable.

But let me know if you still want the ability to navigate between records on a form, and also if this will need to be invoked remotely (as in from the OTHER form).

[PhilippeM]

I guess you are right and that I could reduce navigating possibilities to the main form. So when 'Contact Details' are asked for the selected record, they show up as a 'pop up' which can easily be closed again.

Could you help me with the filter settings though? Should I use a macro to solve this problem?

Thank you!

I think so Philippe.

However, allowing that extra operator flexibility may well prove a problem. If the operator always navigates via your main form the situation will be more predictable, which will probably make the project more stable.

But let me know if you still want the ability to navigate between records on a form, and also if this will need to be invoked remotely (as in from the OTHER form).

[NeoPa]

I can help - and lead, but I can't do it for you.

Get it started and let me know what you can do and where you get stuck.

If I'm to help though, you will probably need to be more clear and precise in your communications. This isn't just you - this applies to most people. Trouble is, working via a web page is a lot lot harder than working on a database you can see, so I need all the clues you can give me to understand where you are.

[PhilippeM]

Dear NeoPa,

If you could get me started, it would be of great help.
I will try to explain - the best I can - what I am trying to do:

I have a main form (Contact List) with a subform (Contacts subform). This subform has a table in it displaying the fields (ID, Company, Last Name, First Name) of my table. The ID is basicily an autonumber for each record.

Now I would like to create a link between the main form and another form (Contact Details). When the user clicks on the ID of a record in the subform (example: number 5) the Contact Details form should open on this same record (5).
Basicily, when clicked on a contact, the contact details should 'pop up'.

Is this possible? I have searched the internet and forums for a similiar case, without any results. How should I get started?

Should I write a macro with an open form action, folowed by an apply filter action? I have tried this but got stuck defining the filter.

Thank you in advance!
Kind regards,

Philippe

[hjozinovic]

Hi P.

I would use double click on ID in subform to open a pop-up in order to avoid unintentional openings.
In double click event of ID field in subform you could put a code doing this:

Store the value of current ID in a variable
Open a pop-up form with details
Move focus to ID field on pop-up form
Docmd.FindFirst .....(find a value stored in a variable)

[NeoPa]

Firstly, H makes a good point about using Double-Click to trigger the code.

Next, you start by designing a form (Contact Details) to display the data from the table you need to display. The design of the form will be unrestricted and will show (when not filtered) all records in the table.

This form should have the .PopUp property set to True if you want it as a Pop-Up.

In the Double-Click event procedure for your control where [ID] is shown, have something like :

Private Sub ID_DoubleClick()
  Dim strFilter As String

  strFilter = Replace("[ID]=", "%I", Me.ID)
  Call DoCmd.OpenForm(FormName:="[Contact Details]", WhereCondition:=strFilter)
End Sub

[PhilippeM]

Thank you for your help!

I tried your code, it opens the contact details form, but not on the right record (where I double clicked on)

I tried almost anything, but no results so far.
This is my final code, could you take a look at it please?

Private Sub ID_DblClick(Cancel As Integer)
  
    Dim strFilter As String
    Dim lngrecordnum As Long

    lngrecordnum = frm.CurrentRecord

    strFilter = Replace("[ID]=", "%I", lngrecordnum)
    
    Call DoCmd.OpenForm("Contact Details", acNormal, strFilter)

End Sub

Thanks!

[NeoPa]

I had an error in the code I posted - but you also didn't copy it properly.

Try with this :

Private Sub ID_DoubleClick()
  Dim strFilter As String

  strFilter = Replace("[ID]=%I", "%I", Me.ID)
  Call DoCmd.OpenForm(FormName:="[Contact Details]", WhereCondition:=strFilter)
End Sub