Filtered MainForm: Issues w/Subform Query & Edit Form (1 Viewer)

[PSSMargaret]

I am having an issue with a MainForm and SubForm scenario that includes filtering, running a query on the subform data and also opening a separate form to edit the subform records.

Scenario #1

I have a Navigation form that includes a list of clients. When a client is clicked in the list (this format is required by the end user), a form opens filtering the form to details regarding the client that was clicked. The form includes a subform of records pertinent to that client.

The MainForm includes a command button to run a query to limit the records in the subform which works fine and still maintains the filter because it affects only the subform. I have included another command button to open a form to edit a subform record but have not been able to get that code to work as of yet.

I would like to click a record in the subform that needs to be edited, click a command button to open that record in the Edit Record form. I have the below code but am receiving an error “Compile error: Method or data member not found”.

DoCmd.OpenForm "frmClientDetails", , , "[ID]='" & Me.ID & "'"

Or

Scenario #2

Since I haven’t been able to get the above to work, I created another form and in lieu of using a subform, I included the client records in the detail of the MainForm with the Client in the Form Header. It’s still accessed by clicking a client from the Navigation Form which opens the MainForm filtered to that client. With this layout, I have two different issues.

I created a command button and used the wizard to set it up which opens the current record in a form to edit it and that works great, however, when I converted it to VBA it did not convert correctly and I receive this error “Compile error: Syntax error” on the below code.

Docmd.OpenForm "frmEdit", acNormal, "", "[ID]=" [&ID], , acNormal

Also, the form didn't maintain the filter that was applied when I originally accessed the form so I altered the query to include the below parameter in the Client field of the query but it didn’t work either.

[Forms]![frmClientDetails]![Client]

I appreciate any help getting either these scenarios working. Thanks so much.

 

[pbaldy]

For the first, is ID a field in the record source of the form being opened? Is ID the name of a textbox on the form the code is in? The error would point to one or both of those needing to be changed.

 

[PSSMargaret]

The ID is in the record source for the subform and it also in the subform. What would I change them to?

I'm so glad you are the one to respond. I have reviewed your website http://www.baldyweb.com/wherecondition.htm but have been knocking my head against the wall because I haven't been able to get either scenario fully working.

 

[pbaldy]

Can you attach the db here? They need to be the actual field name and actual control name.

 

[PSSMargaret]

I would have to strip a bunch of confidential information out before sending it. In the message above, I used a generic name for the ID. In the actual database, there is the following:

The source table for the Subform includes an ID field named RescID
The source query for the Subform includes the RescID field
The control holding the subform is named frmResc
The subform includes the RescID field as a text box and under the properties for this field it's name is RescID and it's control source is RescID

If we can't get to it soon, I will contemplate stripping the confidential information so I can post it.

 

[PSSMargaret]

Ok, I have stripped it down and have attached it. The issue lies with the Edit Button on the form frmOfficeDetails1. I would like to be able for the User to have their cursor in any field of a record in the subform, click the edit record button and have the form frmRescEdit open to the record selected in the subform.

Thanks for your help.

 

[pbaldy]

Like I said, you needed the actual names. This appears to work:

DoCmd.OpenForm "frmRescEdit", , , "RescID = " & Me.RescID

You had the form name spelled wrong.

 

[PSSMargaret]

It's not working for me. I put the code you just send on the Edit Record button as an OnClick event. I'm getting the same error "Compile error: Method or data member not found". Can you return the database with the code you entered?

 

[pbaldy]

The edit record button isn't in the detail, so it would require you to select a record first. I tweaked the code you already had in the click event of the RescID textbox.

 

[PSSMargaret]

Is there a way to be able to use the Edit Record button even though it's not in the detail section of the form? I hadn't planned to keep the RescID field in the detail section.

 

[pbaldy]

Sure:

DoCmd.OpenForm "frmRescEdit", , , "RescID = " & Me.frmResc.Form.RescID

but like I said, since it isn't in the detail, you have to click somewhere on the desired record, then on the button. Otherwise you get either the first one or the last one with focus.

 

[PSSMargaret]

That's exactly what I was looking for. I think it will be intuitive to the User that they would have to select a record in the subform prior to clicking the Edit Record button. Thank you very much.

 

[pbaldy]

Glad it works for you. My users would be annoyed at having to click twice, but they're...annoying. I'd have a button in the detail, or let them double click in a field in the detail.

 

[PSSMargaret]

It's funny what annoys people. I will show them both ways and let them pick. I wanted to understand how to start an action from the main form to the subform. Good stuff.