Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
position to record on subform
- Thread starter GShen
- Start date
- Thread starter
- #2
OK, now, I have a standard, single record displaying customer details form & using the standard command button wizard, I added a combo box designed to list all the companies, select one & then go to that record. This isn't a million miles from what you're trying, except you have the 'companies' listed together in a subform.
If I delve into the details of that unbound combo button (I didn't put it there manually, so I have to cheat!), I see that it creates a query, but this is for the list of all the records, which isn't essential. But then in the After Update event, it has the following code...
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MainRecord] = " & Str(Me![ComboText])
Me.Bookmark = rs.Bookmark
Now I think if you were to alter that third line to something like...
rs.FindFirst "[SubForm]![MainRecord] = " & Str(Me![ComboText])
...and put it into the After Update of your unbound text box, it should work.
If I delve into the details of that unbound combo button (I didn't put it there manually, so I have to cheat!), I see that it creates a query, but this is for the list of all the records, which isn't essential. But then in the After Update event, it has the following code...
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MainRecord] = " & Str(Me![ComboText])
Me.Bookmark = rs.Bookmark
Now I think if you were to alter that third line to something like...
rs.FindFirst "[SubForm]![MainRecord] = " & Str(Me![ComboText])
...and put it into the After Update of your unbound text box, it should work.
- Thread starter
- #4
Najemikon,
You sort of pointed me in a right direction. I got it to the point where I have the bookmark set for the correct item. I am trying to do a DOCMD GOTORECORD ACFORM......
using the bookmark. Can't seem to get correct syntax for the subform. Have you done this before?
DoCmd.GoToRecord acForm, " ", acGoTo, rst.Bookmark
Between the quotes I would normally put the form name but the bookmark I have set is obviously pointing to a record on the subform.
The form name is frmDealDetailNew and the subform name is frmDealItems.
I tried a number of combinations but obviously a period or exclamation or bracket or something is not correct.
Thanks for your feedback.
You sort of pointed me in a right direction. I got it to the point where I have the bookmark set for the correct item. I am trying to do a DOCMD GOTORECORD ACFORM......
using the bookmark. Can't seem to get correct syntax for the subform. Have you done this before?
DoCmd.GoToRecord acForm, " ", acGoTo, rst.Bookmark
Between the quotes I would normally put the form name but the bookmark I have set is obviously pointing to a record on the subform.
The form name is frmDealDetailNew and the subform name is frmDealItems.
I tried a number of combinations but obviously a period or exclamation or bracket or something is not correct.
Thanks for your feedback.
Right direction? Heck, when I thought about it, I followed the wrong road completely!
Forget all that rubbish about recordsets; I take it your list is a subform? The recordset command makes a clone of the table the main form is bound to, so it can't find the record on the subform. That extra bit I added in was a complete load of rubbish! Sorry.
I have though, made some progress, that hopefully will point you in a better direction...
DoCmd.GoToRecord acDataForm, "listform", acGoTo, Str(Me![combo])
This is still called from the After Update event of the unbound combo box (or text box of course).
This doesn't quite work because it isn't pointing at the subform, but if that subform (called 'listform') is open in a seperate window, it will find & highlight the correct record. Freaky. Somehow, you need to make 'acDataForm' point at the subform as part of the main window.
To be honest, I'm not convinced I know why this is working. It doesn't mention a specific field, so 'GoToRecord' must be able to search each one, which is clever so I won't argue too much.
I know this still isn't what you want, but I'm quite pleased the right records were being highlighted in one, albeit useless, way! Surely someone is reading this, laughing his head off at the crudeness of it all, knowing the secret?
Forget all that rubbish about recordsets; I take it your list is a subform? The recordset command makes a clone of the table the main form is bound to, so it can't find the record on the subform. That extra bit I added in was a complete load of rubbish! Sorry.
I have though, made some progress, that hopefully will point you in a better direction...
DoCmd.GoToRecord acDataForm, "listform", acGoTo, Str(Me![combo])
This is still called from the After Update event of the unbound combo box (or text box of course).
This doesn't quite work because it isn't pointing at the subform, but if that subform (called 'listform') is open in a seperate window, it will find & highlight the correct record. Freaky. Somehow, you need to make 'acDataForm' point at the subform as part of the main window.
To be honest, I'm not convinced I know why this is working. It doesn't mention a specific field, so 'GoToRecord' must be able to search each one, which is clever so I won't argue too much.
I know this still isn't what you want, but I'm quite pleased the right records were being highlighted in one, albeit useless, way! Surely someone is reading this, laughing his head off at the crudeness of it all, knowing the secret?
- Thread starter
- #6
Najemikon,
I concur, there has to be a way of doing this easily. I was able to open up the subform as you said and point to it but also as you said,... not what I want to do. I wa able to set a recordset to the subforms recordsetclone. I was also able to do a find of the record because after I executed the find command the bookmark was set to the correct number. Then comes the big problem. I cannot do a DOCMD GOTO RECORD for the subform (Without opening it as a seperate window). There is no way I am going to do this. I am getting ready to scrap this whole thing. Don't you think there should be a DOCMD GOTO RECORD ACSUBFORM somewheres? I look everyday and it never appears (HAHA).
Thanks for all your help. I will look at mskb and see if I can find anything there. Running out of gas... fast.
I concur, there has to be a way of doing this easily. I was able to open up the subform as you said and point to it but also as you said,... not what I want to do. I wa able to set a recordset to the subforms recordsetclone. I was also able to do a find of the record because after I executed the find command the bookmark was set to the correct number. Then comes the big problem. I cannot do a DOCMD GOTO RECORD for the subform (Without opening it as a seperate window). There is no way I am going to do this. I am getting ready to scrap this whole thing. Don't you think there should be a DOCMD GOTO RECORD ACSUBFORM somewheres? I look everyday and it never appears (HAHA).
Thanks for all your help. I will look at mskb and see if I can find anything there. Running out of gas... fast.
Oh this is frustrating! My favourite way of deciphering an error message is to type it exactly as it appears into Google & see what comes back. I've just tried a similar approach with "docmd.gotorecord subform" & found all sorts of stuff to tease!
Loads of results show people on forums like this asking how to create new records on a subform via a button on the mainform. Similar, but not right! Then a couple of MicroSoft pages did the ultimate insult- there seems to be a couple of commands specially for interacting with subforms, literally acGoToLastSubformRecord or something like that. There was also next, previous & first.
Generally, I got the impression this is awkward, but possible. The key seems to be in establishing focus with the subform.setfocus line before you do anything else.
There just has to be a way! If you ever find it, let me know. Even though I've no use for it, I'm determined to know how now. I'm intrigued...
Loads of results show people on forums like this asking how to create new records on a subform via a button on the mainform. Similar, but not right! Then a couple of MicroSoft pages did the ultimate insult- there seems to be a couple of commands specially for interacting with subforms, literally acGoToLastSubformRecord or something like that. There was also next, previous & first.
Generally, I got the impression this is awkward, but possible. The key seems to be in establishing focus with the subform.setfocus line before you do anything else.
There just has to be a way! If you ever find it, let me know. Even though I've no use for it, I'm determined to know how now. I'm intrigued...
- Thread starter
- #8
thanks, you gave me more food for thought with the setfocus for the form. I will try one last time.
If (when???) I get it I will let you know.
I appreciate your help. You have made me feel better that this is not as easy as it sounds. You start to feel stupid after a while.
ttyl,
If (when???) I get it I will let you know.
I appreciate your help. You have made me feel better that this is not as easy as it sounds. You start to feel stupid after a while.
ttyl,
- Thread starter
- #9
Najemikon,
Hold on to your seatbelt. I am so glad you pointed me to finding this thing in Google. I do not know if this is the best or the cleanest or anything but I told 1 line out of each of every suggestion and it WORKS!!!!!!!!!
I will substitute General names so I do not confuse you with my form names and field names.
--------------------------------------------------
MFname = Mainform name
SFname = Subform name
FieldName = Field name on the subform which I wanted to find.
txtSearchField = unbound field on the Mainform which I was using to match against FieldName
--------------------------------------------------
Private Sub txtSearchItem_AfterUpdate()
Dim rst As ADODB.Recordset
Set rst = Forms!MFName!SFName.Form.RecordsetClone
'May need to wait for the recordset to be populated
Do While (rst.State And adStateConnecting) Or _
(rst.State And adStateFetching)
DoEvents
Loop
rst.Find "FieldName = '" & Me.txtSearchField & "'"
If rst.EOF Then
MsgBox ("Field " & Me.txtSearchField & " is not part of this Subform, try again", vbOKOnly
Me.txtSearchField = Null
Me.txtSearchField.SetFocus
Else
Forms!MFName!SFName.SetFocus
Me!SFName.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
I am still pinching myself. YAHOO!!!!!!
Thanks again !!!!!!!!!!!!!!!!!
Hold on to your seatbelt. I am so glad you pointed me to finding this thing in Google. I do not know if this is the best or the cleanest or anything but I told 1 line out of each of every suggestion and it WORKS!!!!!!!!!
I will substitute General names so I do not confuse you with my form names and field names.
--------------------------------------------------
MFname = Mainform name
SFname = Subform name
FieldName = Field name on the subform which I wanted to find.
txtSearchField = unbound field on the Mainform which I was using to match against FieldName
--------------------------------------------------
Private Sub txtSearchItem_AfterUpdate()
Dim rst As ADODB.Recordset
Set rst = Forms!MFName!SFName.Form.RecordsetClone
'May need to wait for the recordset to be populated
Do While (rst.State And adStateConnecting) Or _
(rst.State And adStateFetching)
DoEvents
Loop
rst.Find "FieldName = '" & Me.txtSearchField & "'"
If rst.EOF Then
MsgBox ("Field " & Me.txtSearchField & " is not part of this Subform, try again"
, vbOKOnlyMe.txtSearchField = Null
Me.txtSearchField.SetFocus
Else
Forms!MFName!SFName.SetFocus
Me!SFName.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
I am still pinching myself. YAHOO!!!!!!
Thanks again !!!!!!!!!!!!!!!!!
- Status
Similar threads
- Locked
- Question
