Opening a recordset form based on value from another form

[Hammy01]

Hi all

I'm pretty new at Access.

I have a customer search form (CustSearch) which contains a subform (CustSearchSubform) - , when entering different values in the available textboxes in the mainform and pressing the search button, the subform refreshes showing the correct matches to the selection (Standard Query by Form).

the primary key is CUSTID

I would like to be able to double click a record on the subform (from the mainform) and have the form (CustDetails) open at that particular record.

I managed to get the form to open to the CustDetails form but it only shows to the first record, I understand that I need to point the stLinkCriteria to the record that I have selected, but I can't seem to be able to point to it, as it is in the subform.

I have been looking through the different forums around but cannot seem to get it to work, any help would greatly be appreciated.

Thanks

 

[namliam]

Try rightclicking the link field and using the "statement builder" to find your field(s)

 

[Hammy01]

Thanks namliam,

You've completely lost me. could you please ellaborate?

from all the forums that I read I think that the code should be something along the line of:

stDocName = "FrmCustDetails"
Set stLinkCriteria = Forms.CustSearch.CustSearchSubform.(selected record)
DoCmd.OpenForm stDocName, , , stLinkCriteria

unfortunately I think I either got my syntax wrong or I'm missing something, thanks for your help

Also one important factor needs adding. The View in the subform is a Datasheet view which makes things more complicated (to me anyway...)
Cheers

 

[namliam]

.(selected record) should be .requiredField

To use the builder to make those strings.... Make a new query, dont add any tables.
Now take any field, right click it and find the "Build..." option. Click that.
You get a popup called the "expression builder", just browse to the field you require (Forms => Main form => Subform => Field) and the builder will build the expression for you.
Select => Copy => Paste into your code and you are done. Once you done it a few times you will know how to handtype these things.... Me I am lazy and still do a lot of stuff this way...