SubForm Record Source

[kirkm]

How can I set the record source of my Subform? Part of the problem is I can't figure what the subform name is.


I have tried various things e.g.

Private Sub Form_Current()
Form_Form6.Child.RecordSource = SQL
End Sub

But this errors with method or data member not found.

 

[vba_php]

Kirk,

I'm sort of positive you can't set the recordsource like the way you are doing by trying to do it on the CURRENT event of the form. I could be wrong though, because I've never tried to do what you're trying to do. Why can't you just set the recordsource by opening the main form in design view, clicking on the subform's control, going to its property sheet and changing it?? Additionally, subforms have 2 "names" associated with them => the name of the actual form *object* that's associated with its presentation when viewed on a main form, and the name of it's "control" or "container", which is the same concept as controls like combo boxes, textboxes and listboxes. To find the name of your subform *control*, go to the properties sheet after clicking on it when in design view, and under the *other* tab, see the value next to "name".

 

[Uncle Gizmo]

Try:-

Private Sub Form_Current()
Form_Form6.Child.Form.RecordSource = SQL
End Sub

Sent from Newbury UK

 

[isladogs]

Alternatively, if you are on the main form, you can shorten the code by using the Me operator

Private Sub Form_Current()
Me.SubformControlName.Form.RecordSource = SQL
End Sub

You may find this reference useful for bookmarking http://access.mvps.org/Access/forms/frm0031.htm

 

[kirkm]

Thanks for the replies. I'm not have any success though. Adam the "Name" in Properties-Other-Name has space in it so that won't work in the example from Colin. And Uncle Gizmo's code gives the same error as Msg 1.


>Why can't you just set the recordsource by opening the main form in design view, clicking on the subform's control, going to its property sheet and changing it
Perhaps it's possible but I can't figure out how to set the criteria. Here's the query I want to use. How would this be format for putting in the property sheet and would it update the date automatically ?

SQL = "SELECT NewSingles.TheDate, NewSingles.ThisWeek, NewSingles.LastWeek, NewSingles.WeeksOn, NewSingles.Artist, NewSingles.Title, NewSingles.Label, NewSingles.Number, NewSingles.Field9, NewSingles.Done, NewSingles.[Date In], NewSingles.ExistingPrefix "
SQL = SQL & "FROM NewSingles WHERE (((NewSingles.TheDate) = #" & Me!TheDate & "#)) ORDER BY NewSingles.ThisWeek;"

 

[vba_php]

Kirk,

I made a sample for you. You can copy this example and use the same process to assign the recordsource of your choice, your SQL string, on the form_current event like you want. Tony knows for sure, that you *can* indeed do this. As far as using me!TheDate as criteria, please specify to us *where* that date field is at. is it a field on your main form or subform? the syntax and reference-pointing code needed is different based on where it's located. As a matter of fact, matters of that sort are very convoluted in access, and everybody seems to have a different opinion on what works and what doesn't. I don't even think Microsoft knows! Colin's post hinted at that.

 

[isladogs]

Always best to avoid spaces in control or field names.
However if you enclose the control name in [] it should work.

If not, add the line Debug.Print SQL immediately before the code in form current. Then copy what is shown in the immediate window into the query designer and see if it works/gives you the expected result

 

[kirkm]

Adam, Me!TheDate is obtained from the main forms RecordSource (which is SELECT DISTINCT [qryEachWeek].[TheDate] FROM qryEachWeek ORDER BY [qryEachWeek].[TheDate];
). I'm just about to look at your sample, thank you but to reply to Colin... that space Access put in, not me. The code in Form_current is the sql string plus the line that tries to assign the Record Source. Enclosing it in square brackets) has worked. Yes!! I just had to alter the date to US format.

Private Sub Form_Current()
Dim SQL As String
SQL = "SELECT NewSingles.TheDate, NewSingles.ThisWeek, NewSingles.LastWeek, NewSingles.WeeksOn, NewSingles.Artist, NewSingles.Title, NewSingles.Label, NewSingles.Number, NewSingles.Field9, NewSingles.Done, NewSingles.[Date In], NewSingles.ExistingPrefix "
SQL = SQL & "FROM NewSingles WHERE (((NewSingles.TheDate) = #" & nztous(Me!TheDate) & "#)) ORDER BY NewSingles.ThisWeek;"

Me.[qryEachWeek SubForm].Form.RecordSource = SQL

End Sub

Function nztous(d)
    nztous = Month(d) & "/" & Day(d) & "/" & Year(d)
End Function

I notice the Font used in the subform is not the Font all it's controls are set to. Is there a Global Font for the SubForm ? (I can't see one).
Thank you both and just about to save all that and look at Adams zip file.

 

[isladogs]

Congratulations.
Took me a couple of seconds to work out what nztous meant
What time is it in NZ now?

 

[Gasman]

Adam, Me!TheDate is obtained from the main forms RecordSource (which is SELECT DISTINCT [qryEachWeek].[TheDate] FROM qryEachWeek ORDER BY [qryEachWeek].[TheDate];
). I'm just about to look at your sample, thank you but to reply to Colin... that space Access put in, not me. The code in Form_current is the sql string plus the line that tries to assign the Record Source. Enclosing it in square brackets) has worked. Yes!! I just had to alter the date to US format.

Private Sub Form_Current()
Dim SQL As String
SQL = "SELECT NewSingles.TheDate, NewSingles.ThisWeek, NewSingles.LastWeek, NewSingles.WeeksOn, NewSingles.Artist, NewSingles.Title, NewSingles.Label, NewSingles.Number, NewSingles.Field9, NewSingles.Done, NewSingles.[Date In], NewSingles.ExistingPrefix "
SQL = SQL & "FROM NewSingles WHERE (((NewSingles.TheDate) = #" & nztous(Me!TheDate) & "#)) ORDER BY NewSingles.ThisWeek;"

Me.[qryEachWeek SubForm].Form.RecordSource = SQL

End Sub

Function nztous(d)
    nztous = Month(d) & "/" & Day(d) & "/" & Year(d)
End Function

I notice the Font used in the subform is not the Font all it's controls are set to. Is there a Global Font for the SubForm ? (I can't see one).
Thank you both and just about to save all that and look at Adams zip file.

Shouldn't you be defining d as Date in your function?

 

[kirkm]

Adam, all very interesting! You recordsource for F2_SUB never changes, is that right?
And you have two Forms. That's differnt from 1 form with a subform. Any significance ? I do see what you're doing (those modules are not part of it?).

I have not used Requery, should I ? It seems to work without it. And my Recordsouce is set in the Form On Current events to get a new date from each record.

And I found something VERY worthwhilke. The Name is only shown in Properties-Other depending on where you click. I always thought you clicked on the square betwteen the 2 rulers. That REMOVES the name (no wonder I was going around in circles). I see now the name is only there when it has an Orange outline.


Colin its 11:02am here now.


Hi Gasman, I did not use As Date (although would have it it hadn't worked) because aren't "/" strings? Wouldn't that muck up a Date ?


I don't understand Form Header which you have something in. Is it just like a label? How did you put Table1 ansd Table2 there ?

 

[Gasman]

I have not used Requery, should I ? It seems to work without it. And my Recordsouce is set in the Form On Current events to get a new date from each record.

Hi Gasman, I did not use As Date (although would have it it hadn't worked) because aren't "/" strings? Wouldn't that muck up a Date ?

If you change recordsource there is no need for a requery.

You are passing in a date and returning a string in your function?

 

[isladogs]

Kirkm
When you click on the orange outline, you are selecting the subform control aka container so you see the name of that control in the properties sheet.
But if you click anywhere inside the subform you are selecting the subform itself. As a result the properties sheet now refers to the subform itself

 

[vba_php]

Adam, all very interesting! You recordsource for F2_SUB never changes, is that right?

if you look in the property sheet for the subform, you'll notice there's no value in there. if you change the RS in code, that value listed in the prop sheet also never changes, but the subform itself *does* reflect the changes made through code when you view the sub in access's interface.

And you have two Forms. That's differnt from 1 form with a subform. Any significance ? I do see what you're doing (those modules are not part of it?).

see the attached image, Kirk. I don't know what method you used to create your sub:

1. dragging and dropping it from the db window object to the main form while its in design view.....OR:
2. creating a subform control through the interface's control wizard.

if you did #2, what you will find is that it doesn't matter if you choose *use existing tables or queries* or *use an existing form* as the option for creation....when the creation process is over, an actual form *object* appears before you in the db window.

I have not used Requery, should I ?

per Gasman, it's apparently not needed. I just tend to go overboard making sure things work. It's the same thing as a software platform creator providing developers multiple resources that do the same thing. For instance, I develop a lot in PHP, and it has 2 identical functions in it that do exactly the same thing, which is query out mysql data from server db's. they are:

mysqli()
pdo()

The Name is only shown in Properties-Other depending on where you click. I always thought you clicked on the square betwteen the 2 rulers.

if you click on the *orange square in the upper left you're talking about, the prop sheet you see will not show *name* on the *other* tab because you're looking at the properties of the actual form object, *not* the subform control. pretty confusing, eh? That's microsoft for you! those nuances are exactly the reason why I love open-source genius so much. They care a lot about their users, and in general, their stuff works wonderfully! By the way, I would check with the experts around here to make sure everything I've said is really accurate.

 

[kirkm]

Gasman I added As Date to the function, it didn't seem to change the result, still works, so I'll leave it.
It's certainly a real fight with Access, but I know what I want to achieve. Sorry i just don't get object and control. My brain doesn't follow... I have a main Form and a Subform and can follow that. I create the sub by dragging the subform onto the main form.. but then got some kind of wizard that asked if I wanted table or query and then which fields.
Anyway now it's gets worse. I need a 2nd subform and as I click on a row in [qryEachWeek SubForm] I want to set the record source of this new subform. However [qryEachWeek SubForm] has no On Current event. Can I make one ?

 

[vba_php]

Anyway now it's gets worse. I need a 2nd subform and as I click on a row in [qryEachWeek SubForm] I want to set the record source of this new subform. However [qryEachWeek SubForm] has no On Current event. Can I make one ?

don't worry about it man. we'll stick with you to get it solved. have you considered uploading your file here so these guys can look at it? you might get some better results, and even more advise on that in terms of how could set things up better. wanna try that?

by the way, the difference between *object* and *control* is simply that *objects* are the highest-level entities that access deals with. they are basically what drives the application and allows people like yourself to create marketable products. *controls* are entities that reside on forms. they are also present on reports. they *are* necessary to build marketable products, but are not as important in the process as the actual objects are, because without objects there would be no need for controls. same is true for building web applications in almost any language. both are necessary for things to work. there's plenty more to say, but that's enough for the purposes of this post.

 

[kirkm]

Further to above, I've now got the 2nd subform working but I had to use the On Enter event.
So it only works once.
How can i pick up an event in [qryEachWeek SubForm] to get every record as it's clicked?
Thanks... just need this sorted to start using this for real work.

 

[kirkm]

Thanks Adam, I would upload it, but it's a mess and way too large. But I've just had a brainwave, instaed of getting the event fired from a click event, I can add a command button and click that. Maybe... I'll try it...

 

[vba_php]

How can i pick up an event in [qryEachWeek SubForm] to get every record as it's clicked?

nobody else is getting involved it doesn't seem like. not sure what you mean by that Kirk. *get every record as it clicked*?

 

[isladogs]

You are again confusing the subform control and the subform itself

All subforms have Current events -they are just forms embedded in another forms. They do not have an Enter event

However the container holding the subform control does not have a current event. All the control has is an enter and exit event

In case you missed it, see post #13