access docmd.searchforrecord with subform

I'm trying to use docmd.searchforrecord to run in another form, which is a subform. (Note: I'm not in the parent form).
I keep getting an error message that the object is not open.
What's the correct syntax to refer to a subform in a docmd.searchforrecord statement?
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
How are you searching for the record?  If you are searching by a key field value (this would be best), use this syntax (modified from the standard version to work with a subform):

Private Sub cboSelectOrder_AfterUpdate()
'Created by Helen Feddema 7-Mar-2012
'Last modified 7-Mar-2012

On Error GoTo ErrorHandler

   Dim strSearch As String
   Dim frmSub As Access.Form
   strSearch = "[OrderID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Set frmSub = Me![subOrdersPerCustomer].Form
   frmSub.Recordset.FindFirst strSearch
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Helen FeddemaCommented:
This code would normally run from an unbound combo box in the form header.
etech0Author Commented:
Isn't that much more complicated than DoCmd.SearchForRecord?
Or does that not work for subforms?
(I'm using 2010)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

etech0Author Commented:
Let me explain the workflow to you.
I have a main form, with a subform. On the main form, I have a "New Task" button. The button opens a separate form where I can enter a new task. The New Task form's recordsource is the same as the subform's. When I close the New Task form, I want the summary form to navigate to that newly created record, for more editing/etc.
I'm therefore trying to use docmd.searchforrecord to navigate to that record, using the ID field of the recordsource.
Jeffrey CoachmanMIS LiasonCommented:
So is your question?
"I *Must* use searchforrecord specifically?"

Or is your real question simply:
"What is the best way to do this?"

Here is one simple technique...

etech0Author Commented:
What is the best way to do this?
Jeffrey CoachmanMIS LiasonCommented:
The Upload system here seems not to be working currently so I will post the code with some comments:

Presuming Customers and Tasks (Parent/Child, linked on CustomerID)
Mainform:  frmCustomersMain
subform:  frmTasksSub
Pop-up form:  frmTasks

On the main form's "New Task" button:
'Open the popup form and store the CustID from the main form in the Open Args
    DoCmd.OpenForm "frmTasks", , , , , , Me.CustomerID

On the Open event of the pop-up "Task" Form:
'If the Open Argsis not 0 (empty)
'Create a new record
'insert the Open Arg in the CustomerID field
'Move to the task Name field
    If Me.OpenArgs <> 0 Then
        DoCmd.GoToRecord , , acNewRec
        Me.txtCustomerID = Me.OpenArgs
    End If

On the Close event of the pop-up "Task" Form:
'If the Mainform is open, requery the subform and go to the newly created record.
    If CurrentProject.AllForms("frmCustomersMain").IsLoaded = True Then
        ' Find the record that matches the control.
        Dim rs As Object
        Set rs = Forms![frmCustomersMain]![frmTasksSub].Form.Recordset.Clone
        rs.FindFirst "[TaskID] = " & Str(Nz(Me.OpenArgs, 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

I am sure you can adapt this to work in your Database

etech0Author Commented:
Thanks for your response.
The code breaks on this line, with a "Not a valid bookmark" error.

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
Not sure without seeing the entire code, and knowing the details of your DB...
(The form design, Datatypes, Object Names...etc)

This is why I always ask for a sample db...

All I can say, is that this worked fine for me.
etech0Author Commented:
Here is the code I used. My main form is called CatWebWork2F, and the sub is called CatWebWork2SummaryF. I'm running this code from another form (not a sub).

' Find the record that matches the control.
Dim rs As Object

Set rs = Forms![CatWebWork2F]![CatWebWork2SummaryF].Form.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me.OpenArgs, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
Make sure you are referencing the form names, not the subform "Object"
etech0Author Commented:
How can I do that?
Jeffrey CoachmanMIS LiasonCommented:
can you just post a sample of your DB that illustrates this issue.

There are just to many things that this could be...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

Jeffrey CoachmanMIS LiasonCommented:
This works fine for me...
etech0Author Commented:
Interesting - your code seems to be the same as mine.
I'll get to work setting up a sample db to upload.
etech0Author Commented:
etech0Author Commented:
(It's a very watered down version of my db - I hope everything works.)
Jeffrey CoachmanMIS LiasonCommented:
What happened to step 13?
<Post the explicit steps to replicate the issue.>
etech0Author Commented:
Open the form called CatWebWork2F. Click the New Task button on top. This will open the New Task form. Fill it with bogus data, and then click Save and Edit. It's supposed to navigate the subform (CatWebWork2SummaryF) on CatWebWork2F to the new record.
Jeffrey CoachmanMIS LiasonCommented:
I open that form and I immediately get an "Invalid use of Null" error...
Did you see step 14.?
<14. Test the database before posting.>

Even if I move past this, and click the button for a new task, ...When I create a new task, I get the "Not a valid bookmark error" when I click on either "Save" button.

1. I note that on your Save & New button, you actually have code to "Save" the record, ...the Save & Edit button does not...

2. In the New Task Button, I don't see where you are setting the ID in the Open Args, like I illustrate in my sample?

...There may be more issues, but my sample illustrates that what you originally asked for is possible.

You will have to review my sample thoroughly and apply the same logic to your application.

etech0Author Commented:
The Invalid use of Null came up because the db selects data based on your windows username. I can take that out, but it doesn't really matter for our purposes.

There is code to save in both buttons.

I'll take a look at the openargs.
etech0Author Commented:
Where does OpenArgs get its information from?
Jeffrey CoachmanMIS LiasonCommented:
Again, this is all in my sample....

Open Args get its info from whatever you pass to it.

In your case, probably the "ID" (or whatever your key field is) my case it was "CustomerID"
    DoCmd.OpenForm "frmTasks", , , , , , Me.CustomerID
etech0Author Commented:
The main form is not opening now. It's already opened, and I want the code to run from the New Task form before it closes, so that when you get back to the main form it's at the current record.
Is that what your code does? I looks like it's trying to open the main form.
Jeffrey CoachmanMIS LiasonCommented:
Your post is confusing...

Here is the logic behind my sample...

You open the main form (that contains the subform)
Instead of creating the child record in the subform, you open up a Popup form to enter the child record.
(You pass the ID field from the main form to the popup form via the Open Args)
When the Popup closes, the subform now will displays the newly created child record.

This is what My sample does.
Please confirm that my sample is functioning in this way.

Then explain the difference between my sample and your database.
etech0Author Commented:
Why am I passing the ID field from the main form to the popup form?

The way I have it now, the popup form's datasource is the same as the subform's. In the popup form, a new record is created. We don't have its ID before the popup form is opened.
Jeffrey CoachmanMIS LiasonCommented:
<Why am I passing the ID field from the main form to the popup form?>
If you want to enter child records in a Popup form, the two forms need to be "linked"  on the common field.
(In the same way as the main and subform are linked through the Master and Child link properties)
Passing the ID field from the main form to the popup form creates this "link"
It also populates the correct ID for the child record.

<We don't have its ID before the popup form is opened.>
I think you are confusing the Primary key in the Popup form (or subform) and the *Foreign* key which is from the main form

Again, note my sample...where this all works flawlessly...
Parent Table Name: tblCustomers
CustID (Primary Key)

Child Table Name: tblTasks
TaskID (Primary Key)
CustID (Foreign Key, to the Customer table)

So here the Primary key in the task record is not really known before the new Task record is created, but the *Foreign* key is known. (whatever the Primary key is in the Main form)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
etech0Author Commented:
My main form does not have a record source - it's simply a shell.
etech0Author Commented:
Thanks for your help! I ended up moving the code to a public sub on the subform, and calling it from the new task form. For some odd reason, it cleared up the issue.
Jeffrey CoachmanMIS LiasonCommented:
OK, you can accept your own post as the solution
etech0Author Commented:
That's okay. First of all, you put a lot of work into this. Second of all, I used some of what you posted in order to find the solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.