Is it Possible to Reference a SubForm in DoCmd.SearchForRecord?

[Seth Schrock]

I was able to figure out the following code, and it works:

Private Sub Command4_Click()
DoCmd.OpenForm "frmLoans"
DoCmd.SearchForRecord , , acFirst, "LoanID = " & Me.txtLoanID
Forms!frmLoans!sfrmAppraisals.SetFocus
DoCmd.SearchForRecord , , acFirst, "AppraisalID = " & Forms!frmSearch!txtAppraisalID
 
 
End Sub

I thought that I would be able to replace lines 4 & 5 with the following:

DoCmd.SearchForRecord acDataForm, "sfrmAppraisals", acFirst, "AppraisalsID = " & Me.txtAppraisalID

but it said that sfrmAppraisals wasn't open on the error messages. So I thought that I would try

DoCmd.SearchForRecord acDataForm, "frmLoans!sfrmAppraisals.Form", acFirst, "AppraisalsID = " & Me.txtAppraisalID
 

Same Problem. Is there a way to do this?

It is really no big deal, but I thought it would be a little more efficient using only one line of code instead of two. The form opens on the slow side anyway so I'm trying to eliminate any unnecessary steps in the code.

[Rabbit]

The form isn't considered as open if it's a subform.

[Seth Schrock]

So my "more efficient" idea won't work?

[Rabbit]

Probably not. It's the same if you try to Close the form. It will tell you the form is not open as opposed to "closing" the subform.

[zmbd]

If you are using either DAO or ADO recordset then you should be able to use one of these:

In the click_event of a command button on the PARENT form:


If [AppraisalID] is datatype text/string:
Me.YourSubFormNameHere.Form.Recordset.FindFirst "AppraisalID = '" & Forms!frmSearch!txtAppraisalID &"'"

If [AppraisalID] is datatype numeric:
Me.YourSubFormNameHere.Form.Recordset.FindFirst "AppraisalID = " & Forms!frmSearch!txtAppraisalID

You can also use the movefirst, movelast, etc...

other than using your field name and the "YourSubFormNameHere" these are taken directly from one of my production databases.

[Seth Schrock]

@Z My problem is that the code will be triggered from an event on a totally different form than the parent or child form.

As I said before, I do have code that works that doesn't seem too bulky (to me) so I can live with that.

[NeoPa]

DoCmd.SearchForRecord acDataForm, "sfrmAppraisals", acFirst, "AppraisalsID = " & Me.txtAppraisalID

Looking at your code, and checking the online Help page as I don't use Access 2007, it seems clear that you have everything pretty well OK except that the Help page doesn't explain clearly what the parameters are exactly, and what you can expect to use. Reading between the lines it seems that acDataForm is expecting a form from the Forms() collection. A collection which subforms are never added to of course. A bit of a naff interface to introduce after this many years of experience IMHO. It would appear that you can still use the procedure by doing things the awkward way, but one of the alternatives already suggested may be an easier route. Who needs clumsy code? Who needs to be forced into using clumsy code by the designers of the system?

[Seth Schrock]

I tried leaving out the acDataForm so that it would go to its default (acActiveDataObject or something like that) and it still didn't work.

So are you meaning that you think my code is clumsy (the code that I was planning on using is in the first block of code in the OP if that wasn't clear)?

[NeoPa]

The clumsiness I'm referring to is the same clumsiness you recognised when you decided to try to do it in a single call. That was showing good instincts. The fact that Access doesn't support tidier code when you try to use it is certainly not a reflection on you.

In the circumstances, your original code seems to be about the best available (without changing your approach to use a different call).

[Seth Schrock]

Okay. I glad that I have finally came up with some code on my own that is actually a good way to do it. I am always scared that I'm doing something in a poor way when I try something new, especially when it looks bigger than it needs to be. I always like to check to make sure that I'm not doing things the hard way because I know how easy it is to really mess things up by not doing them the proper way. There are reasons for the rules, but I don't always know what the rules are. This gives me some hope that I am starting to really learn from the experts here.

[zmbd]

Replace the "me" reference with the name of the parent form then place the code in the correct event... Mine was in the click event of a button... could vary well have placed in in the on change or lost focus events.

[Seth Schrock]

I'm confused. The "me" reference isn't referring to a control on the parent form. It is on the search form. So if I change it from Me.txtLoanID (which is the same as Forms!frmSearch!txtLoanID) to Forms!frmLoans!txtLoanID, won't it come up with an error of unable to find control?

[NeoPa]

I'm not sure exactly what you're suggesting there Z (You're not very clear where the "Me" is that you suggest replacing.), but the second parameter to SearchForRecord is called ObjectName and is not an object reference, but only the name of (in this case) a Form from the Forms() collection. Not appropriate in the case of a subform.

[zmbd]

Here's what I'm Getting from you seth:

- Form1
- Form2
- Form2Sub1

-- Form2 is parent to Form2Sub1

Form1 - Will on some event call on Form2Sub1 to move to a record.
This series of actions appears to be in Form1.Command4 click event wherein the order of events appears to be:
1) Form2 is opened.
2) You now tell Form2 to find some record based on the Form1.txtLoanID "LoanID = " & Me.txtLoanID
3) You now want Form2Sub1 to sync to the value either in form1 or Form2 using a control named txtAppraisalID "AppraisalID = " & Forms!frmSearch!txtAppraisalID

You attempted (3) as though Form2Sub1 is an open form in the collection... it is not as explained in the other posts within the thread.

Let’s go back to here:

If [AppraisalID] is datatype numeric:
Me.YourSubFormNameHere.Form.Recordset.FindFirst "AppraisalID = " & Forms!frmSearch!txtAppraisalID

This was, of course, provided as a template/example not a plug-n-play. Thus, if you place this code in the click event of the control on Form1 then it'll more than likely error - or perhaps call up a small Gremlin - I have several to give away free to a good home ;D

SO: You can't use the "Me" reference.... that'll return Form1 so you need to specify Form2
AND: let’s just replace the 'your name here" with our example Form2Sub1
NOW: you have something like this:
Form2.Form2Sub1.Form.Recordset.FindFirst "AppraisalID = " & Forms!frmSearch!txtAppraisalID

And wither it needs a dot or a bang between "From2Sub1" and "Form" is a matter of semantics in that, in this case, VBA recognizes that you are referring the Form2Sub1 form properties and want to play with the record-set so it doesn't seem to care. Now I have ran into a few cases where it had to be one or the other and the new navigation control has a few references where that matters.

Now we really need to use your form names so the final is (from my best guess based on your posted code):

frmLoans.sfrmAppraisals.Form.Recordset.FindFirst "AppraisalID = " & Forms!frmSearch!txtAppraisalID
Now I have not plugged this into the VBA editor; however, the original posted in #5 is based upon code I currently use in one of my production databases – for over 5 years now; thus, unless the form name are incorrect, if you place this final code in your frmSearch.Command4 click event you should be able to compile and run it with the desired results.

[Seth Schrock]

Ah, now I understand what you are talking about. I had dismissed that post since you said "In the click_event of a command button on the PARENT form:" and that didn't fit. And you have got the form names perfectly. The only thing that I had to change on your code was to add Forms! to the beginning and it worked perfectly. I was also able to use the Me.txtAppraisalID at the end instead of the full reference. So here is what I have:

Private Sub Command4_Click()
 
DoCmd.OpenForm "frmLoans"
DoCmd.SearchForRecord , , acFirst, "LoanID = " & Me.txtLoanID
Forms!frmLoans.sfrmAppraisals.Form.Recordset.FindFirst "AppraisalID = " & Me.txtAppraisalID
 
End Sub

I do like this cleaner code better.

[NeoPa]

From Referring to Items on a Sub-Form, you could also shorten it a little further with :

Forms!frmLoans.sfrmAppraisals!Recordset.FindFirst "AppraisalID = " & Me.txtAppraisalID

This is example of appreciating where dot and bang make most sense.

I would also suggest that if you are going to use this new procedure for the subform (and there's really no reason why you wouldn't), then it would makes sense to use that same procedure for the other form too - to provide consistentcy.

Private Sub Command4_Click()
    DoCmd.OpenForm "frmLoans"
    With Forms!frmLoans
        .Recordset.FindFirst "LoanID = " & Me.txtLoanID
        .sfrmAppraisals!Recordset.FindFirst "AppraisalID = " & Me.txtAppraisalID
    End With
End Sub

[Seth Schrock]

So the procedure for the other form would be

Forms!frmLoans.Recordset.FindFirst "LoanID =" & Me.txtLoanID

I get an error saying "can't find the field 'Recordset'" when I take the .Form out from before it. I tried . instead of !, but that broke it as well (as you probably already knew).

[NeoPa]

I think I was editing and improving my post as you were testing that Seth. Can you try the code as it is now (in post #16) and tell me what errors you get, if any. You need to be clear which lines you're talking about when reporting that, of course.

[Seth Schrock]

I get the same error message(Microsoft Access can't fin the field 'Recordset' referred to in your expression) on your line 5.

[NeoPa]

Very strange. What do you get if you try :

.sfrmAppraisals.Form.Recordset.FindFirst "AppraisalID = " & Me.txtAppraisalID

Maybe the ! shortcut only works for controls on the form. I've never had to use the .Form. syntax before.

[Seth Schrock]

That works great. That is also the same as what I had before only in With/End With form (which I like as it is neater).

[NeoPa]

A new thread - Discussion: Dot Versus Bang (Again) - has been created to handle the discussion that developed from looking for the best answers for this question.