Solved

access docmd.searchforrecord with subform

Posted on 2012-03-12
31
1,344 Views
Last Modified: 2012-06-21
Hi!
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?
0
Comment
Question by:etech0
  • 17
  • 12
  • 2
31 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37710028
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
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37710040
This code would normally run from an unbound combo box in the form header.
0
 
LVL 10

Author Comment

by:etech0
ID: 37710042
Isn't that much more complicated than DoCmd.SearchForRecord?
Or does that not work for subforms?
(I'm using 2010)
0
 
LVL 10

Author Comment

by:etech0
ID: 37710062
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37710500
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...

JeffCoachman
0
 
LVL 10

Author Comment

by:etech0
ID: 37710522
What is the best way to do this?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37710582
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
        Me.TaskName.SetFocus
    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
        Forms![frmCustomersMain]![frmTasksSub].Form.Requery
        ' 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

JeffCoachman
0
 
LVL 10

Author Comment

by:etech0
ID: 37715204
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37715351
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.
0
 
LVL 10

Author Comment

by:etech0
ID: 37715446
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).

Forms![CatWebWork2F]![CatWebWork2SummaryF].Form.Requery
' 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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37715703
Make sure you are referencing the form names, not the subform "Object"
0
 
LVL 10

Author Comment

by:etech0
ID: 37715714
How can I do that?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37715944
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, ...post 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.


JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37716246
This works fine for me...
Access--RefreshSubFormFromPopupF.mdb
0
 
LVL 10

Author Comment

by:etech0
ID: 37724656
Interesting - your code seems to be the same as mine.
I'll get to work setting up a sample db to upload.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Author Comment

by:etech0
ID: 37724746
0
 
LVL 10

Author Comment

by:etech0
ID: 37724750
(It's a very watered down version of my db - I hope everything works.)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37724800
What happened to step 13?
<Post the explicit steps to replicate the issue.>
0
 
LVL 10

Author Comment

by:etech0
ID: 37724815
Sorry.
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37725178
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.


JeffCoachman
0
 
LVL 10

Author Comment

by:etech0
ID: 37725351
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.
0
 
LVL 10

Author Comment

by:etech0
ID: 37725365
Where does OpenArgs get its information from?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37725544
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)

...in my case it was "CustomerID"
    DoCmd.OpenForm "frmTasks", , , , , , Me.CustomerID
0
 
LVL 10

Author Comment

by:etech0
ID: 37725568
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37725893
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.
0
 
LVL 10

Author Comment

by:etech0
ID: 37726034
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37726553
<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
Fields:
CustID (Primary Key)
CustName
CustAddress

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

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)

Jeff
0
 
LVL 10

Author Comment

by:etech0
ID: 37726721
My main form does not have a record source - it's simply a shell.
0
 
LVL 10

Author Closing Comment

by:etech0
ID: 37737958
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37740686
OK, you can accept your own post as the solution
0
 
LVL 10

Author Comment

by:etech0
ID: 37742263
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now