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

How to use ADO to insert/update/select data into a form with subform(Datasheet)

P: 1
Dear all,
I have a question about ADO in the subform. I know how to use ADO to insert/update/select data into the sigin form, but wehn I try to do it in the form with subform((Datasheet). I don't have any idea how to insert/update/select the recordset data into the subform(Datasheet), especially insert/update. Would someone can give me a idea or sample code to me? Appreciate your help.


OS: windows XP +SP2+ Access 2003+SP2
Database: MS SQL Server 2000+SP4
Oct 8 '07 #1
Share this Question
Share on Google+
11 Replies


nico5038
Expert 2.5K+
P: 3,072
The mainform's properties like recordsource can be referenced with the "Me.recordsource"
For the subform on such a mainform you need: "Me!<subformname>.Form.recordsource"

Nic;o)
Oct 8 '07 #2

P: 40
Hi Nico,

I'm having trouble putting this together.

This is what I've got:
Expand|Select|Wrap|Line Numbers
  1. Me!subFrmTasks.Form.RecordSource = str
And I get this error: Runtime 40036, "Application-defined or object-defined error".

I also tried with using "Me." instead of "Me!", and I got same error code, "Method 'Form' of object '_SubForm' failed"

Any ideas?
Oct 11 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Hmm, make sure that "str" contains a valid SQL Select string and also verify that the name of the subform (See under "Other" tab in properties window) is the name used after the Me!

Nic;o)
Oct 11 '07 #4

P: 40
I've checked both and they are correct, not sure where else to look, I'll keep searching.

R
Oct 11 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Perhaps database corruption is the problem.
Try to create a new blank database and use File/Get externaldata/Import to create a new "fresh" copy of the database.

Nic;o)
Oct 11 '07 #6

P: 40
OK, so I'm not getting anywhere. This is the last thing I have to do on this database before I leave the company!

I've made a new database, reimporting all the tables, queries, forms and modules necessary. Here is what my whole code looks lik, on the On Open event for the main form:

Expand|Select|Wrap|Line Numbers
  1. str = "SELECT tblTasks.TaskID, tblTasks.ProjectID, tblTasks.NameID, tblTasks.NameID2, tblTasks.TaskTypeID, tblTasks.Notes, tblTasks.LanguageID, tblTasks.WordCount, tblTasks.Email, tblTasks.AssignDateMonth, tblTasks.AssignDateDay, tblTasks.AssignDateYear, tblTasks.DeadlineMonth, tblTasks.DeadlineDay, tblTasks.DeadlineYear, tblTasks.TaskStatusID, tblTasks.NameID2, tblTasks.ProjectManagerID, tblTasks.DateCreated, tblTasks.PostponedFrom, tblName.PersonEmail, tblName_1.PersonEmail, tblTasks.AssignTime, tblTasks.DeadlineTime, tblTasks.StatusID, tblName.NameTypeID, tblName_1.NameTypeID, tblTasks.EmailSent, tblTasks.UnderstandsSource, tblTasks.TechnicalIssues, tblTasks.StyleGuide, tblTasks.PercentComplete, tblTasks.UpdateFlag, tblTasks.VENotes FROM tblName AS tblName_1 RIGHT JOIN (tblTasks LEFT JOIN tblName ON tblTasks.NameID = tblName.NameID) ON tblName_1.NameID = tblTasks.NameID2 ORDER BY tblTasks.LanguageID;"
  2. Me!subfrmTasksOneLine.Form.RecordSource = str
  3. Me!subFrmTasks.Form.RecordSource = str
I've checked the subform names, and it's fine. I've made sure that the subform names are accurate, and that the sql code is functional. The Master-Child relatioship is set on the forms themselves (not in code). The subforms are set in tabs, I don't know if that affects anything. I'm getting two distinct problems.

On the line: Me!subfrmTasksOneLine.Form.RecordSource = str, I get nothing, the subform comes up without a recordset (the unpopular #Name?). This is my first time setting a subform recordset through code, so it could be I'm missing something fundamental.

On the next line: Me!subFrmTasks.Form.RecordSource = str, I get the same error as before, "Application-defined or object-defined error". The only difference between these two subforms is that they are put in a tab; subFrmTasks is in the hidden tab. I don't know if that could affect things.
Oct 12 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Hmm, looks like the event sequence isn't working OK.
You could try to move the code into the OnCurrent event of the form, that way we're sure that the subforms are loaded.

I'm puzzled why you are setting the recordsource here, as I see no variable part in the query. Why not have the subforms with a "fixed" recordsource ?

Nic;o)
Oct 12 '07 #8

P: 40
The main form is a project form that opens up from a separate form. Depending on what the Project ID is, these subforms are based off of a different recordsource. I actually didn't post the two recordsource strings, to keep the posting simple.
Oct 12 '07 #9

nico5038
Expert 2.5K+
P: 3,072
Did the move to the OnCurrent event work ?

Nic;o)
Oct 12 '07 #10

P: 40
No, unfortunately it didn't. I don't really know what else to do. I tried the OnCurrent for the main form, now I'm trying it on the subform.
Did the move to the OnCurrent event work ?

Nic;o)
Oct 12 '07 #11

nico5038
Expert 2.5K+
P: 3,072
Hmm, ran into this sample where they try to implement transaction processing, but the subform assignment is like we do, but two Set statements are used to assign the recordsource:
http://www.tek-tips.com/viewthread.c...1257388&page=7
one for activating the rst (by running a stored procedure) and one to assign the recordset to the subform.

Is that the same way you used for the login form ?

Nic;o)
Oct 12 '07 #12

Post your reply

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