473,324 Members | 2,581 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,324 software developers and data experts.

how to change subform recordsource in vba

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
4 54826
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: robert.waters | last post by:
Hello, Say that there is a database for client information; there is a main form that displays the client's name, and this form has one or more subforms that display different types of...
1
by: napstar | last post by:
I have a subform and I'm trying to change its recordsource but i keep getting this error : Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist. ...
2
by: David Haskins | last post by:
I have a fairly complex interface screen (form) that is comprised of several subforms that perform different, but related activities. I am designing a search/filter form that should be able to...
3
by: Simon | last post by:
Dear reader, The syntax for the VBA code to change the RecordSource of a Master Report is: Me.RecordSource = "TableOrQueryName"
3
by: Simon van Beek | last post by:
Dear reader, How to change the RecordSource for a subReport. For forms the syntaxes is:
7
by: tnjarrett | last post by:
Hello, I have a MS Access continuous subform that was using a query as the recordsource. I changed it to use a recordset instead because when the query was used, the changes to the subform values...
2
stonward
by: stonward | last post by:
I'm trying to change the recordsource of a subform using a simple button click: by default, the subform's source is one query, when a button is clicked it changes to another query (the same query,...
57
thelonelyghost
by: thelonelyghost | last post by:
Just searched google and this website but I couldn't come up with an answer to this. Basic Information Software: Microsoft Access 2000 OS: Windows XP Professional SP3 Problem: Error 3008...
7
reginaldmerritt
by: reginaldmerritt | last post by:
Has anyone had this problem before, my search on-line would suggest it not common but I'm sure you fine folk will be able to help. This is the code I have used to change the recordsource on a...
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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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...

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.