By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

how to change subform recordsource in vba

P: n/a
I have a subform that I dont want to have a recordsource initially as it is
locking the table that is created dynamically. I want to connect to the
table after the data has been written by another routine.

I had hoped I could do mytable.recordsource = myquery

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
What I did was to have a subform which was originally based on a given
table - get all the fields, etc. Then I just delete the Recorsource
from the subForm's Recordsource property. Note: you don't need to
delete the field references in the form, like txtName is bound to
fieldName in your table. Just delete the Recordsource.

Then in the Mainform use code like this:

Me.yourSubForm.Form.RecordSource = "Select * From yourTbl Where
someCriteria = 'something'"

Another option is to instead of using a MakeTable Query to use an Insert
Into Query. That gets around locking issues with the MakeTable query.
The only catch is that you have to remove all the old data first. But
that is very easy:

DoCmd.RunSql "Delete * From yourDataTbl"

Then

DoCmd.RunSql "Insert Into yourDataTbl(fld1, fld2,...) Select * From
SourceDataTbl Where someCriteri..."

Just make sure that you have the same number of fields in the Sql
Statements as you do in the Subform and they are the exact same datatype
between the form fields and the fields in the Sql Statement, ie, text
field <-> string field, Date field<->date field in the sql Statement,
Number field<->number field in the sql statement, etc.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #2

P: n/a
I will give that a go tonight, thank you I was pulling my hair out with that
one as this locking issue was stopping the form displaying the correct data
no matter how I attacked the problem ie with querie or table.

Regards
Peter
"Rich P" <rp*****@aol.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
What I did was to have a subform which was originally based on a given
table - get all the fields, etc. Then I just delete the Recorsource
from the subForm's Recordsource property. Note: you don't need to
delete the field references in the form, like txtName is bound to
fieldName in your table. Just delete the Recordsource.

Then in the Mainform use code like this:

Me.yourSubForm.Form.RecordSource = "Select * From yourTbl Where
someCriteria = 'something'"

Another option is to instead of using a MakeTable Query to use an Insert
Into Query. That gets around locking issues with the MakeTable query.
The only catch is that you have to remove all the old data first. But
that is very easy:

DoCmd.RunSql "Delete * From yourDataTbl"

Then

DoCmd.RunSql "Insert Into yourDataTbl(fld1, fld2,...) Select * From
SourceDataTbl Where someCriteri..."

Just make sure that you have the same number of fields in the Sql
Statements as you do in the Subform and they are the exact same datatype
between the form fields and the fields in the Sql Statement, ie, text
field <-> string field, Date field<->date field in the sql Statement,
Number field<->number field in the sql statement, etc.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #3

P: n/a
Create an unbound subform container - its a while ago but I think just
connect it to a recordsource with the wizard but then delete it from
the properties tab.

Then on an event:

Forms!MainFormName![SubformContainerName].SourceObject =
"frmManagementAS2124"
Me.Requery

This works fine for me.

Lincoln King
Sydney Australia
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<cj**********@news7.svr.pol.co.uk>...
I will give that a go tonight, thank you I was pulling my hair out with that
one as this locking issue was stopping the form displaying the correct data
no matter how I attacked the problem ie with querie or table.

Regards
Peter
"Rich P" <rp*****@aol.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
What I did was to have a subform which was originally based on a given
table - get all the fields, etc. Then I just delete the Recorsource
from the subForm's Recordsource property. Note: you don't need to
delete the field references in the form, like txtName is bound to
fieldName in your table. Just delete the Recordsource.

Then in the Mainform use code like this:

Me.yourSubForm.Form.RecordSource = "Select * From yourTbl Where
someCriteria = 'something'"

Another option is to instead of using a MakeTable Query to use an Insert
Into Query. That gets around locking issues with the MakeTable query.
The only catch is that you have to remove all the old data first. But
that is very easy:

DoCmd.RunSql "Delete * From yourDataTbl"

Then

DoCmd.RunSql "Insert Into yourDataTbl(fld1, fld2,...) Select * From
SourceDataTbl Where someCriteri..."

Just make sure that you have the same number of fields in the Sql
Statements as you do in the Subform and they are the exact same datatype
between the form fields and the fields in the Sql Statement, ie, text
field <-> string field, Date field<->date field in the sql Statement,
Number field<->number field in the sql statement, etc.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #4

P: n/a
Thanks guys got the dataset onto the form with no locking or other problems
THANK YOU!

"Jack" <us@yours.com> wrote in message
news:8e**************************@posting.google.c om...
Create an unbound subform container - its a while ago but I think just
connect it to a recordsource with the wizard but then delete it from
the properties tab.

Then on an event:

Forms!MainFormName![SubformContainerName].SourceObject =
"frmManagementAS2124"
Me.Requery

This works fine for me.

Lincoln King
Sydney Australia
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message

news:<cj**********@news7.svr.pol.co.uk>...
I will give that a go tonight, thank you I was pulling my hair out with that one as this locking issue was stopping the form displaying the correct data no matter how I attacked the problem ie with querie or table.

Regards
Peter
"Rich P" <rp*****@aol.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
What I did was to have a subform which was originally based on a given
table - get all the fields, etc. Then I just delete the Recorsource
from the subForm's Recordsource property. Note: you don't need to
delete the field references in the form, like txtName is bound to
fieldName in your table. Just delete the Recordsource.

Then in the Mainform use code like this:

Me.yourSubForm.Form.RecordSource = "Select * From yourTbl Where
someCriteria = 'something'"

Another option is to instead of using a MakeTable Query to use an Insert Into Query. That gets around locking issues with the MakeTable query.
The only catch is that you have to remove all the old data first. But
that is very easy:

DoCmd.RunSql "Delete * From yourDataTbl"

Then

DoCmd.RunSql "Insert Into yourDataTbl(fld1, fld2,...) Select * From
SourceDataTbl Where someCriteri..."

Just make sure that you have the same number of fields in the Sql
Statements as you do in the Subform and they are the exact same datatype between the form fields and the fields in the Sql Statement, ie, text
field <-> string field, Date field<->date field in the sql Statement,
Number field<->number field in the sql statement, etc.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.