UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Requery List Box In Main Form When Clicking Control In 2nd Subform And Stay In The Record In 2nd Subform, Access 2013    
 
   
tserhung
post Apr 16 2018, 04:48 AM
Post#1



Posts: 15
Joined: 4-April 18



Hi Guys,

I've a main form with 2 subforms inside. I need to requery the listbox in main form when clicking the record in 2nd continuous subform and stay in the CLICKED record in 2nd subform.
I've tried the following methods found in the web but failed to stay in the CLICKED record in 2nd subform.
CODE
Private Sub ID_Click()
    Dim strBookmark As String
    strBookmark = Me.Bookmark
    Me.Parent.Parent!lstMatConvert.Requery
    Me.Bookmark = strBookmark
End Sub

CODE
Private Sub ID_Click()
    Dim rs As DAO.Recordset
    Dim pk As String
    pk = Me.ID
    Me.Parent.Parent!lstMatConvert.Requery
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ID]= '" & pk  & "'"
    Me.Bookmark = rs.Bookmark
    Set rs = Nothing
End Sub


Could you guys help me. Thanks.
Go to the top of the page
 
Phil_cattivocara...
post Apr 16 2018, 05:18 AM
Post#2



Posts: 199
Joined: 2-April 18



QUOTE
I need to requery the listbox in main form when clicking the record in 2nd continuous subform and stay in the CLICKED record in 2nd subform.
Why do you need this?
Go to the top of the page
 
Phil_cattivocara...
post Apr 16 2018, 05:29 AM
Post#3



Posts: 199
Joined: 2-April 18



CODE
Me.Parent.Parent!lstMatConvert.Requery

This is the right code but that listbox is "connected" to record displayed in the subforms?
If you want to save a bookmark in a variabile you have to declare it as Variant, not string.
This post has been edited by Phil_cattivocarattere: Apr 16 2018, 05:29 AM
Go to the top of the page
 
tserhung
post Apr 16 2018, 05:30 AM
Post#4



Posts: 15
Joined: 4-April 18



Hi Phil,

I need to see the result in the listbox for checking (some calculation inside) when I update the record in 2nd subform
Go to the top of the page
 
tserhung
post Apr 16 2018, 05:49 AM
Post#5



Posts: 15
Joined: 4-April 18



Phil,

QUOTE
This is the right code

Yes, requery the listbox is successful but it won't stay in the clicked record in 2nd subform.
QUOTE
listbox is "connected" to record displayed in the subforms?

Yes, I've set a criteria in listbox based on the ID control in 2nd subform.
QUOTE
If you want to save a bookmark in a variabile you have to declare it as Variant, not string.

I've changed the declaration to Variant in both method but it's still not working.
Go to the top of the page
 
Phil_cattivocara...
post Apr 16 2018, 05:55 AM
Post#6



Posts: 199
Joined: 2-April 18



If modifying the listbox the content of the subforms change, you cannot use bookmark because recordset are not the same then before.
You have to save in a variable the PK of the second line in the subform then using the recorsetclone.
CODE
rs.FindFirst "[ID]= '" & pk  & "'"
Is ID-PK a text in the second subform?
This post has been edited by Phil_cattivocarattere: Apr 16 2018, 05:57 AM
Go to the top of the page
 
tserhung
post Apr 16 2018, 06:14 AM
Post#7



Posts: 15
Joined: 4-April 18



QUOTE
You have to save in a variable the PK of the second line in the subform then using the recorsetclone.

Yes, you're right. when I debug the code. The ID is not same with PK because it's move the cursor back to the first record in second subform. But I don't know what should I do for the amendment.
QUOTE
Is ID-PK a text in the second subform?

Yes, it is in second subform.
Go to the top of the page
 
Phil_cattivocara...
post Apr 16 2018, 06:28 AM
Post#8



Posts: 199
Joined: 2-April 18



The code you posted in your #1 should work
CODE
Private Sub ID_Click()
     Dim rs As DAO.Recordset
     Dim pk As String 'or Integer or Long. ID is often a number. Verify!
     pk = Me.ID
     Me.Parent.Parent!lstMatConvert.Requery
     Set rs = Me.RecordsetClone
     rs.FindFirst "[ID]= '" & pk  & "'" 'Or rs.FindFirst "[ID] = " & pk ' if ID is numeric.
     Me.Bookmark = rs.Bookmark
     Set rs = Nothing
End Sub

Be sure ID_Click is the right event. Perhaps DoubleClick is better, consider it.
Go to the top of the page
 
zaxbat
post Apr 16 2018, 08:18 AM
Post#9



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


What event fills the subform.

Also, what does your listbox click/select trigger?

I might consider putting a hidden textbox on the main form that the list box uses as it source...so that any time that text box changes the listbox requeries automatically (if everything goes right)

Then in the subform in the on current event I would update that textbox with the needed key for the listbox.

This is wishful thinking, maybe I better go and try it...make sure it works...then I'll be back. I never use listboxes (i just use a small datasheet) so I don't know how they react...just a sec.
This post has been edited by zaxbat: Apr 16 2018, 08:26 AM
Go to the top of the page
 
zaxbat
post Apr 16 2018, 09:18 AM
Post#10



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Ok, yeah....here you go...try this zipped demo.

You can see as you scroll through the list of clients (in one subform) that the details of each client are shown in another subform.

But, now for your listbox, there is a listbox on the main form that shows data from an associated table that updates per each client as you scroll through the first (top) subform.

You do not even need to click.....just scroll and all is automatic...almost no code...no bookmarks...no losing spot in subform....nice...easy...
Attached File(s)
Attached File  UpdateformListBoxfromSubform.zip ( 267.27K )Number of downloads: 13
 
Go to the top of the page
 
tserhung
post Apr 16 2018, 09:19 AM
Post#11



Posts: 15
Joined: 4-April 18



Phil,

This code doesn't works for me. When I debug the code until
CODE
Set rs = Me.RecordsetClone
, it will prompt me Enter Parameter Value of second subform.
Go to the top of the page
 
tserhung
post Apr 16 2018, 09:59 AM
Post#12



Posts: 15
Joined: 4-April 18



zaxbat,

I just tried your attachment, it's works well with 1 main form and 1 subform but it's not working in my case because I've master and child field link between my first subform and second subform (the listbox is in the main form).
Maybe, I'm too greedy to have too many subforms inside.
This post has been edited by tserhung: Apr 16 2018, 10:00 AM
Go to the top of the page
 
zaxbat
post Apr 16 2018, 12:23 PM
Post#13



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Yes, mine is set up the same.....my listbox is on the main form and the datasheet is on a subform. My 2nd subform is linked my first subform and shows the details in textboxes.
So when the user scrolls through the datasheet (on its subform)....both the details textboxes (on their own subform) and the listbox (which is on the main form) update automatically.

If you do not see how it is done....just ask.
This post has been edited by zaxbat: Apr 16 2018, 12:27 PM
Go to the top of the page
 
tserhung
post Apr 17 2018, 05:54 AM
Post#14



Posts: 15
Joined: 4-April 18



Hi Zaxbat,

Let me rearrange my forms structure and try again. I'll use your sample in my database and seek for your advice if I've further questions. Thank you very much.
Go to the top of the page
 
zaxbat
post Apr 17 2018, 06:06 AM
Post#15



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


thumbup.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th October 2018 - 07:32 PM