473,883 Members | 1,697 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Is it Possible to Reference a SubForm in DoCmd.SearchFor Record?

Seth Schrock
2,965 Recognized Expert Specialist
I was able to figure out the following code, and it works:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  2. DoCmd.OpenForm "frmLoans"
  3. DoCmd.SearchForRecord , , acFirst, "LoanID = " & Me.txtLoanID
  4. Forms!frmLoans!sfrmAppraisals.SetFocus
  5. DoCmd.SearchForRecord , , acFirst, "AppraisalID = " & Forms!frmSearch!txtAppraisalID
  8. End Sub
I thought that I would be able to replace lines 4 & 5 with the following:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 10 '12
21 7921
5,501 Recognized Expert Moderator Expert
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.
Dec 11 '12 #11
Seth Schrock
2,965 Recognized Expert Specialist
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?
Dec 11 '12 #12
32,584 Recognized Expert Moderator MVP
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.
Dec 11 '12 #13
5,501 Recognized Expert Moderator Expert
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 "AppraisalI D = " & 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.YourSubFormN ameHere.Form.Re cordset.FindFir st "AppraisalI D = " & 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 "AppraisalI D = " & 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.sfrmAp praisals.Form.R ecordset.FindFi rst "AppraisalI D = " & 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.Comma nd4 click event you should be able to compile and run it with the desired results.
Dec 11 '12 #14
Seth Schrock
2,965 Recognized Expert Specialist
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.txtAppraisal ID at the end instead of the full reference. So here is what I have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  3. DoCmd.OpenForm "frmLoans"
  4. DoCmd.SearchForRecord , , acFirst, "LoanID = " & Me.txtLoanID
  5. Forms!frmLoans.sfrmAppraisals.Form.Recordset.FindFirst "AppraisalID = " & Me.txtAppraisalID
  7. End Sub
I do like this cleaner code better.
Dec 11 '12 #15
32,584 Recognized Expert Moderator MVP
From Referring to Items on a Sub-Form, you could also shorten it a little further with :
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  2.     DoCmd.OpenForm "frmLoans"
  3.     With Forms!frmLoans
  4.         .Recordset.FindFirst "LoanID = " & Me.txtLoanID
  5.         .sfrmAppraisals!Recordset.FindFirst "AppraisalID = " & Me.txtAppraisalID
  6.     End With
  7. End Sub
Dec 11 '12 #16
Seth Schrock
2,965 Recognized Expert Specialist
So the procedure for the other form would be

Expand|Select|Wrap|Line Numbers
  1. 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).
Dec 11 '12 #17
32,584 Recognized Expert Moderator MVP
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.
Dec 11 '12 #18
Seth Schrock
2,965 Recognized Expert Specialist
I get the same error message(Microso ft Access can't fin the field 'Recordset' referred to in your expression) on your line 5.
Dec 11 '12 #19
32,584 Recognized Expert Moderator MVP
Very strange. What do you get if you try :
Expand|Select|Wrap|Line Numbers
  1. .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.
Dec 11 '12 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Max Harvey | last post by:
Hi, I made up a nice little form which had its own sub form in it. I made a litle VB code so that when I pressed a button it would move form the form (frmConference) to the subform (frmBookingBookingBased), start a new record, and copy some values form the form into some fields in the subform, and then leave the cursor sitting (in focus) at the begining of the subform.
by: Geoff | last post by:
I have a form with a subform. The latter contains a calculated field, , derived from a query. In the footer of this subform is an unbound text box whose control source = SUM() . works fine I want to save the value in into a variable in the main form called . This is bound to Tbl_Products
by: (PeteCresswell) | last post by:
I've got a parent form and three subforms. Two of them work A-OK. For the third one, in the Immediate window: ----------------------------------------- ?Me.subDeal.Name subDeal ?Me.subDeal.SourceObject
by: rhc | last post by:
access 2000 I have a tab control with 2 pages and one subform on each. On parent form P from the form_current() event of subform A I cant seem to refer to subform B. msgbox forms!.form.name works
by: abprules | last post by:
I am trying to tab from my header to my detail section in a form that includes a tabcontrol form. It is not working. Can anybody suggest something. I have tried the following: Add a text box to the header section, make it last in the tab order of that section, and set these properties: Name: txtTransferToDetail Visible: Yes
by: anansi | last post by:
Hi all I have a very simple problem but i am highly confused. firstly, a google web search of "DoCmd.Searchforrecord" returns nothing. i've been starting to get suspicious why no one else is interested in this function but me, but here goes: sID = Me.ShiftID '--- this is the subforms field, ShiftID ParentsID = Me.Parent.ShiftID '---this is shift viewer's field, ShiftID DoCmd.SearchForRecord acDataForm, "shift...
by: Coll | last post by:
I have a form with a subform on it. On my main form, you select an employee id number and the top portion of form fills in with data, and the bottom portion of the form,which is a subform, lists existing records there are for that employee (one to many relationship). How can I set up my main form, so the user can select one of the records on the subform and retain that recordid number for use in opening a new form and displaying the that...
by: Lebbsy | last post by:
After displaying search results, I want to be able to double click the identity number field and then the input form SubmissionDetails becomes the display form for the results of the data matching value of the selected identity number. With my code below I get a data type mismatch error. Please help me....... Private Sub IdentityNumber_Click() On Error GoTo IdentityNumber_Click_Err On Error Resume Next If (Form.Dirty) Then ...
by: Daniel Owino | last post by:
Hi I'm trying to add a new record to a subform called itemsreceived_subform, using a command button. I can't get the clear difference between the subform control, and the subform itself. My code is as follows: Private Sub cmdadd_Click() If Me!Itemsreceived_subform.Form.Dirty Then Me!Itemsreceived_subform.Form.Dirty = False End If Me!.SetFocus ' This is the name of the subform
by: munkee | last post by:
Hi all, I am having some trouble, I am trying to create an advanced search form which will open up as a popup from my main search from. I am using the Allen Browne method of creating the where string however I am setting the recordsource of the subform with a constructed sql string, not filtering. My main form contains a subform which my basic search selects perfectly. However I cant seem to be able to get my advanced search to point...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.