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

[cooperkuo]

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

[nico5038]

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)

[RobinAG]

Hi Nico,

I'm having trouble putting this together.

This is what I've got:

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?

[nico5038]

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)

[RobinAG]

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

R

[nico5038]

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)

[RobinAG]

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:

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;"
Me!subfrmTasksOneLine.Form.RecordSource = str
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.

[nico5038]

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)

[RobinAG]

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.

[nico5038]

Did the move to the OnCurrent event work ?

Nic;o)

[RobinAG]

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)

[nico5038]

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)