Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Requery unbound subform when RecordSource's SQL has changed doesn't work

Posted on 2006-07-23
14
Medium Priority
?
601 Views
Last Modified: 2012-06-21
hello experts,
what the hell am i doing wrong?  why won't this unbound subform requery it's Recordsource and show the child records of the 1st subform?
i tried resetting the recordsource to it's query
i tried resetting the recordsource to it's recorsource
i tried requerying the subform....

everything works above the DoEvents.  in other words, you don't have to help me with the first part of the code, i'm just copying it entirely.  the query IS being modified.

it's just that the subform isn't requerying and changing to show the current recordset being returned by the query.

it's after the DoEvents that isn't working.

this is an Unbound subform (called sfmTrackingGroupMilestones) that is supposed to show the records in the child table of the table that the recordsource of the Bound subform (named sfmTrackingGroup)
both subforms are on a tab control on the main form called frmProjectMain.

it is showing NewRecord, no matter what, until the main form is closed, then when the main form reopens, the Unbound subform shows what the code set the query to.

thanks,
mac

*******  begin code  ***************

Public Sub gsbRequerySubform()
    Dim cmd As New ADODB.Command
    Dim cat As New ADOX.Catalog
    Dim strQuery As String
    Dim strSQL As String
    Dim lngI As Long

    strQuery = "qryTrackingGroupMilestones_subform"
        glngI = Nz(Forms!frmProjectMain.Form!sfmTrackingGroup.Form!txtID, 0)
        If glngI <> 0 Then
            gstrSQL = "SELECT * FROM [MyTable] WHERE ([ID] = " & glngI & ");"
            Set cat.ActiveConnection = CurrentProject.Connection
            Set cmd = cat.Views(strQuery).Command
            cmd.CommandText = gstrSQL
            Set cat.Views(strQuery).Command = cmd

DoEvents

' doesn't work
            Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.RecordSource = "qryTrackingGroupMilestones_subform"
           
' doesn't work
            Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.RecordSource = Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.RecordSource
           
' doesn't work
            Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.Requery
           
        End If
   
    Set cmd = Nothing
    Set cat = Nothing

End Sub
***********  end code  ************

gsbRequerySubform is being called from the main form's Current event and the Bound subform's current event.
again, i know that the query is being modified correctly, it's the Requery of the Unbound subform that's not working on-the-fly.


0
Comment
Question by:MacRena
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 17163684
Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.RecordSource = "qryTrackingGroupMilestones_subform"
                                   ^--- remove first .Form

Like:

 Forms!frmProjectMain!sfmTrackingGroupMilestones.Form.RecordSource = "qryTrackingGroupMilestones_subform"

Mike
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 17163686
Do this for all three cases.
0
 
LVL 4

Author Comment

by:MacRena
ID: 17163693
nope, no change
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 400 total points
ID: 17163717
Removing the first .Form is required.  The reason it doesn't work after you have removed the first .Form, it means there are some other problems.

There are two additional area you could take a look at:

The name of the control (container) having sfmTrackingGroupMilestones in its object source property.  Make sure its name also reads sfmTrackingGroupMilestones not something like Child23.

The next we will look at SQL qryTrackingGroupMilestones_subform has at the time of insertion to RecordSource.

Mike
0
 
LVL 4

Author Comment

by:MacRena
ID: 17163785
1)  good thought, eghtebas, but the name of the subform control on the tab is sfmTrackingGroupMilestones and the sourceobject is also sfmTrackingGroupMilestones

2)  ok, i think i understand you to mean that you want to be sure that the SQL property of the query is being set before the subform's requery.

i put STOPs in the code to assure this is being done.

do you have an email address that i can zip this up and send it to you (it's pretty small)?
i'll increase the points for sure!

thanks,
mac
0
 
LVL 4

Author Comment

by:MacRena
ID: 17163871
Mike,
i see you have your address in your profile, so i'll send it along and see if you have the time to review it.
thanks so much!!!,
mac
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 17163882
I have to leave shortly thus will not be work on it.

Not knowing how your query is build, I wanted to make sure it runs okay.

Consider doing this:

Dim strSql as string
strSql =currentdb.querydefs("qryTrackingGroupMilestones_subform").sql

Forms!frmProjectMain!sfmTrackingGroupMilestones.Form.RecordSource = strSql

mike
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 17163890
Also

Dim strSql as string
strSql =currentdb.querydefs("qryTrackingGroupMilestones_subform").sql

Debug.Print strSql   '<-- make a new test query to make sur it runs okay just before you insert it to the record source

Forms!frmProjectMain!sfmTrackingGroupMilestones.Form.RecordSource = strSql

mike
0
 
LVL 4

Author Comment

by:MacRena
ID: 17163941
oh, sorry - it's already been sent.  you can ignore it.

there is no DAO reference selected - i'm trying for an "ADO only" solution.

i'm confident that the query IS being modified at the correct time (before the requery) because i've put STOPs in and gone to look at the query.

mac
0
 
LVL 85
ID: 17163994
So you have an unbound subform, and user interaction on that unbound subform will be used to populate another Bound subform on the Main form (which contains both the bound and unbound subform)?

If so, you'd have to do something like this in the Unbound subform, in whatever you event you want to use to fill the bound subform:

Me.Parent.Name_Of_Your_Bound_Subform_Control.Form.Recordsource = "blah blah"

FWIW, I've always had better luck just setting the .Recordsource directly to an SQL string, rather than opening and query and chaning the SQL there ... but you may have other reasons to do it this way.

BTW: It's a violation of the EE member agreement to use private Email to solve a problem (http://www.experts-exchange.com/help.jsp#hi99). There's an area here you can upload files for EE use:

http://www.ee-stuff.com/accessLogin.php?returnURL=%2FExpert%2Findex.php%3F

0
 
LVL 4

Author Comment

by:MacRena
ID: 17164367
LSMConsulting,
1) RE: "So..." - i believe you have that backwards.  the Bound subform sets the recordsource for the Unbound subform, but i don't think that matters.  your point is that my code should set:
Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.RecordSource = "blah"
and i think i've got that correct.

2) RE: "FWIW" - i've tried setting the SQL string directly into the recordsource of the unbound subform with the same failure.  i think it's just not requerying!

3) RE: "violation" - i'm sorry that i didn't know of the "upload files" site.  i'll post it there.  i was here mostly in the late 90's and all that hadn't been developed yet.  i'll have to make it a point to re-read that agreement.  eghtebas has his email address posted in his profile for, i assumed, just this purpose, as i have mine in my profile, both here and other help sites.

thanks,
mac
0
 
LVL 4

Author Comment

by:MacRena
ID: 17164389
oops, old code
change
Forms!frmProjectMain.Form!sfmTrackingGroupMilestones.Form.RecordSource = "blah"

to
Forms!frmProjectMain!sfmTrackingGroupMilestones.Form.RecordSource = "blah"
0
 
LVL 4

Author Comment

by:MacRena
ID: 17164492
ok, Mike.
thanks for the look-over.  i'm sure you're right and i have a problem somewhere else.
if i just can't solve it, i'll post another question.  this one got too conflagulated!
thanks very much,
mac
0
 
LVL 85
ID: 17164513
mac,

Glad you got your troubles straight. Lots of members have their email posted in their profile, but the Admins can get on your case pretty hard if you use email too much (especially lately). Sorry if you took offense, I inteneded this as a simple reminder.

The ee-stuff site just came online, makes it a bit easier to handle these types of things ...
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question