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.
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.
21 7839
The form isn't considered as open if it's a subform.
So my "more efficient" idea won't work?
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 5,501
Expert Mod 4TB
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.
@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 32,556
Expert Mod 16PB - 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?
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 32,556
Expert Mod 16PB
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).
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 5,501
Expert Mod 4TB 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.
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 32,556
Expert Mod 16PB
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 5,501
Expert Mod 4TB
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.
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 32,556
Expert Mod 16PB
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
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 32,556
Expert Mod 16PB
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.
I get the same error message(Microsoft Access can't fin the field 'Recordset' referred to in your expression) on your line 5.
NeoPa 32,556
Expert Mod 16PB
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.
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).
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |