Open form from selected record on datasheet

[imperator]

Hi all

I have a form which lists records from a search in a datasheet view. I want to open another form based on a selected record from that search. I have achieved this with

DoCmd.OpenForm "frmClient", acNormal, , "[ClientID] = " & Me.frmsubClients![ClientID]

but the client also has enquiries in the search results. How do I select the client AND the specific enquiry too (EnquiryID).

The form I wish to open is the Add/Edit Client form which has a subform of enquiries by the client, so I want to open this Add/Edit form on the correct client (which it does so far) but also on the correct enquiry in the subform.

I hope this is clear.
Ray

 

[JamesMcS]

How about just adding it in to the filter bit of your expression? i.e.

"[ClientID] = " & Me.frmsubClients![ClientID] & " AND [EnquiryID]="&Me.formname![EnquiryID]

 

[imperator]

How about just adding it in to the filter bit of your expression? i.e.

"[ClientID] = " & Me.frmsubClients![ClientID] & " AND [EnquiryID]="&Me.formname![EnquiryID]

Thanks James, unfortunately it didn't work. I presume it is because the [EnquiryID] is on a subform in the form I'm trying to open and cannot be seen by the code above.
I modified it:
DoCmd.OpenForm "frmClient", acNormal, , "[ClientID] = " & Me.frmsubClients![ClientID] & " AND frmClient.sfrmEnquiry![EnquiryID]= " & Me.frmsubClients![EnquiryID]
but still no luck. Any ideas?

I'm a novice with VBA I'm afraid.
Ray

 

[JamesMcS]

No worries. Try taking out the frmclient.sfrmEnquiry bit out, leaving it as my post above.

 

[imperator]

No worries. Try taking out the frmclient.sfrmEnquiry bit out, leaving it as my post above.

Hi James
Done that but still no luck. It prompts me for the EnquiryID. If I type in the number I want, I don't get it. It returns the first enquiry connected to the client.

 

[JamesMcS]

Sorry, I'm an idiot... we need to refer to the enquiryid field in the subform don't we?? Try forms![main form name].form![subform name].form![controlname]

 

[JamesMcS]

This is quite useful when you're referring to objects on different forms.

 

[imperator]

Thanks for the attachment James.

Still not working, when I applied your suggestion, the client details didn't load. Prior to that I got the correct client but the enquiry was simply the first enquiry.

Thanks for your efforts. Any other thoughts would be appreciated. In case it is useful, the Add/edit client and enquiry subform are linked to client and enquiry tables.


Ray

 

[JamesMcS]

Is it possible to cut out any personal data from a copy of the DB and post it so I can have a butcher's? Got a teeny bit of free time whilst running some queries....

 

[imperator]

Thanks for the offer, James.
I'm off this project now and will on field work for the next two months, so I'm going to put the problem on the backburner. I found a post on another forum site which said you cannot filter a subform from an OpenForm method as it only refers to the main form. I did wonder if the filteron property on the subform could be accessed in the code with a With statement.

I have submitted the application so it filters on the selected client, the user can navigate to the correct enquiry from there.

I appreciate your efforts and attention.
Many thanks.