Form and Subform DAO Recordsets

[Maximus37]

Hi all.

I have this problem, it might be simple to resolv but I just don't see it.

I have recordsets bound to a database through code using DAO; these are publicly defined as follows:

Public db as Database
Public rs1 as DAO.Recordset

and several other recordset defined;

In a Main form I have a sub-form; the Main form is bound through code to the recordset, say rs1 or table1, and the sub-form is not bound through code but through the properties of the sub-form itself, in this case to a Query (Data Source: Query1) which in trun runs every time the main form Field1 changes

Every time I want to add/edit record to/on rs1(Main Form) I also need to allow the user to add/edit records on/to rs2/table2 through the sub-form, but remember this is not bound though code but through properties (Data Source: Query1)

The code works fine for the add/edit of the Main Form, but i'm how do I do add new/edit the sub-forms recordset/table?

The code I have for the Add New is the following:

vBookmark = rsLogHeadAM.Bookmark - rs1 bookmark it
DetailBMark = rsRouteLogAM.Bookmark - rs2 bookmark it
Me.TripNo = "" - set the textbox to nothing
Me.CtrNum = "" - set "
Me.WkEnding = "" - set "
Me.TripNo.SetFocus - set focus to main form's field(textbox)
Call UnlockTrip - since they're locked, unlock them
rsLogHeadAM.AddNew - add new record to main form rs1/table1
-- how do i clear the sub-form for it to allow users to add new records --
and
-- the edit its ok, i just unlock the sub-form
-- how to update (save) the new or edited record on the sub form?
-- when a new record, how do i clear the sub-form?

Thank you all, I really appreciate your help!

EA

 

[willir]

Max35

If if you have the record set defined as a public variable (actually a pointer to the table), then you should be able to access the record set as long as with you are within scope.

I have to wonder why you feel you need to use a public variable for the recordset? You can do something to the recordset from one module that is totally unexpected from another module. Are you trying to walk through a record set to emmulate the way a form walks through a data table? This is fine, but you might want to consolidate all moves records / edits / add / deletes to one central module -- which negates the need for using public variables. Use this central module as "driver" and call out to other sub modules to perform various tasks. Since you are calling out from the central module, you will maintain your position in the record set.

...Moving on to the subform issue.

but i'm how do I do add new/edit the sub-forms recordset/table?

Are you working with the same record set as in the main form? (probably not) Or are you working with a different recordset ? My perception of how you explain what you are doing suggests that the subform is controled by the public record sets, and therefore is less dependent on the main form, and can be manipulated in a similar fashion described for your main form.

Keep in mind that from the main form, you can control the recordset of the subform...
[tt]
Me.YourSubForm.Form.RecordSource = YourSQLStatement
Me.YourSubForm.Requery
[/tt]

And from the subform, you can access variables on the main form...[tt]
Me.Parent.YourFieldOnTheParentForm[/tt]

These and others are powerful tools in manipulating your data.

To each their own, but I personally try to stay away from using public variables unless actually essentual to the application. (For example, I have seen audit routines developed by using public variables.)

Sorry if this is not what you are looking for...
Richard

 

[Maximus37]

Thanx willir.

I'm using about 10 recordset pointers, therefore the usage of public variables making the scope range to the entire app.

And, the sub form is using a query that calls a table's records according to a field in the parent (main) form.
For example, lets say that this is an Orders form(main) with a sub form which contains the Order Details(sub form); but the difference is that the main form is handled through code by a defined and declared variable/pointer to a recordset, this works just fine, the sub form on the other hand, is handled through properties pointing to the query which gets the details (Data source: QueryX); I can move from record to record fine, the sub form will display the appropriate details; but when I try to Add/New record, the main form is ok, I just blank the text boxes and call the AddNew method of the recordset through code, but how about the sub-form? i've tried several methods and properties but I dont get a blank/new record, instead I get #Name? in the text boxes.
What would be the best approach to make the sub form display a blank or new record?

Thanx again,
Max35

 

[willir]

The command line is...

DoCmd.GoToRecord , , acNewRec

 

[Maximus37]

Thanx willir, I really appreciate your help; it solved my problem!