473,320 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Requery recordset in subform by clicking listbox on mainform

2
Hi guys,

I have following troubles. I did made an access database that worked with macros just fine so far, but I kinda mixed vba with macros and sometimes that makes it hard to combine. So I started writing the macros into vbas. For the most part that wasn't much of a problem, but following function just won't work for me.

I've got a listbox in my mainform and by screen.activecontrol it used to give a selcted value to a subform (macro: GotoRecordset). Then the recordset was requeried also by a macro. I tried different solutions so far I will only write a few down. Most of them didn't give me an error but they won't work neither. The shown recordset of the subform stays with the first record and won't change.

Expand|Select|Wrap|Line Numbers
  1. Dim sub3 As Form
  2. Set sub3 = Forms!mainform!subform.Form
  3. sub3.Visible = True
  4. sub3.RecordsetClone.FindFirst "[GSVB] = " & Me!List126
  5. sub3.Refresh
  6. sub3.Requery
(Here I tried whether refresh will give me a differnt outcome and whether the defintion of sub3 might change anything)

Expand|Select|Wrap|Line Numbers
  1. Forms!mainform!subform.Form.Visible = True
  2. Forms!mainform!subform.Form.Recordset.FindFirst "[GSVB] = " & Me!List126
  3. Forms!mainform!subform.Form.Requery
(Here I changed Me! to the full written form and substituded activecontrol)


Expand|Select|Wrap|Line Numbers
  1. Me!subform.Visible = True
  2. Me!subform.Recordset.FindFirst "[GSVB] = " & Nz(Screen.ActiveControl, 0)
  3. Me!subform.Form.Requery
(That's how it looked in the beginning, except for Recordset used to be RecordsetClone)

I have no idea what's wrong here, since there are no errors either. Still they won't change my subform.
Jun 30 '15 #1
3 2623
jforbes
1,107 Expert 1GB
RecordsetClone will make a copy of a recordset in memory for you to perform actions against. It can be a copy of the current form's Recordset, but it is only a copy. Once you have found the record you are looking for in the Clone, you then need to set that record as the Active record on the Form. A common way to do this is use a Bookmark

This is the example code from the above url. The highlighted piece is the magic that you are looking for:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFindContactName_Click() 
  2.  
  3.      Dim rst As DAO.Recordset 
  4.      Dim strCriteria As String 
  5.  
  6.      strCriteria = "[ContactName] Like '*" & InputBox("Enter the " _ 
  7.      & "first few letters of the name to find") & "*'" 
  8.  
  9.      Set rst = Me.RecordsetClone 
  10.      rst.FindFirst strCriteria 
  11.      If rst.NoMatch Then 
  12.           MsgBox "No entry found.", vbInformation 
  13.      Else 
  14.           Me.Bookmark = rst.Bookmark 
  15.      End If 
  16.  
  17.      Set rst = Nothing 
  18.  
  19. End Sub
Taking the sample code and merging it with yours would give something like this. I don't guarantee that it will work, but it probably will.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFindContactName_Click() 
  2.  
  3.      Dim rst As DAO.Recordset 
  4.      Dim strCriteria As String 
  5.  
  6.      strCriteria = "[GSVB] = " & Me!List126
  7.  
  8.      Set rst = Me.RecordsetClone 
  9.      rst.FindFirst strCriteria 
  10.      If rst.NoMatch Then 
  11.           MsgBox "No entry found.", vbInformation 
  12.      Else 
  13.           Me.Bookmark = rst.Bookmark
  14.      End If 
  15.  
  16.      Set rst = Nothing 
  17.  
  18. End Sub
Jun 30 '15 #2
MrHJL
2
I tried it but it didn't work.

Just to describe the situation again: The listbox is in the mainform and the recordset to be changed is that of a subform. I already have a functional code working that changes the recordset of the mainform from a listbox in the mainform, that is not the issue.

Could you rewrite your suggestion to a form that would do that very task @jforbes?
Jul 3 '15 #3
zmbd
5,501 Expert Mod 4TB
somewhat vague here...

If you are altering the original recordsource for the main and subforms... that is to say, you are changing the SQL that established the recordset for the form or controls regardless if it's a filter or where conditional...

then

If you change the recordsource for the mainform:
you have to force the mainform to requery
then you have to force the subform to requery

Think of the form (or control therein) as being a snap-shot (view) of the current state of the recorsource. If you alter he record set by adding, deleting, or in a multi-user environment when another user changes a record, the current records shown in the form/control do not reflect that change.

Once you have requeried the forms, then you can move to the record of interest.

However, this doesn't sound like a normal parent/child form relationship where the two forms are linked on the related fields. When done this way, going to a record in the parent form will cause the child form to show the related records.

So, what is it you are doing here?
Jul 12 '15 #4

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

Similar topics

2
by: Hey_Moe! | last post by:
I have subform which uses a SQL statement as the record source. One of the criteria (ie. Part of the WHERE condition) is a column value from a ListBox on the MainForm. Two problems exist: 1....
2
by: F. Michael Miller | last post by:
I need to requery a subform from a third form and can't seem to get it to work. frmForm1 has frmAddress as a subform. The button cmdReviseAddress opens the form frmUpdateAddress where all of my...
3
by: ken | last post by:
Hi, I have a main form with a text box and a filter button. I also have a subform in the main form. When I make an entry into the filter text box and click the filter button, I change the query...
3
by: 4004 | last post by:
I have a frmSet (I am a teacher) which has a frmSetQuestions subform, which shows a filtered set of questions. I have a lboTopic which I use in the criteria of the subform recordset to filter...
2
by: cmichaud | last post by:
Previously i had a combo box where i could select a name and then click a button and a subform on that form would requery and give me the information i wanted. However, i have now changed the...
1
by: kimj.dk | last post by:
Hi, I have a form with a subform where the subform uses the value from a combo box on the main form as a query criteria. I don't want the subform to update or get data before the user has selected...
1
by: Parasyke | last post by:
I have a subform within a tabpage that will requery/refresh (?) if I press F9 and it will update the data in the subform, but I have tried various combinations of Me!SubformName.requery...
2
by: mslagle1 | last post by:
Hi all, I am trying to requery a subform "frmOpenWorkOrders" when a value list "status" on my main form "frmWorkOrderMain" is changed. If I change "status" then click on "frmOpenWorkOrders" and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
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 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.