Assigning a created ado recordset to a sub forms recordsource

[Chris Strug]

Hi,

I'm not even sure if this is possible so bear with me but...

I have a form for data input. The form contains a sub form showing linked
records to the record from the main form.

What I'd like to do is create a recordset in ADO and assign it as the
recordsource of the sub form. However I'm having a few problems....

Is what I'm trying to achieve possible and if so where am I going wrong?

E.g..

Private Sub Form_Open(Cancel As Integer)
dim rstAddress as ADODB.recordset
Set rstAddress = New ADODB.Recordset

With rstAddress
With .Fields
.Append "Address_ID", adInteger
.Append "Address_1", adVarChar, 40
.Append "Address_2", adVarChar, 40
.Append "Address_3", adVarChar, 40
.Append "Address_4", adVarChar, 40
.Append "Address_Postcode", adVarChar, 40
End With
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open
End With

Set Me!subfrm.Form.Recordset = rstAddress
End Sub

The recordset creates fine but when I try to bind it to the form as its
recordsource (the last line in the sub) I get the error: "The Object your
entered is not a valid recordset property".

I'm using an Access 2000 (with service packs) ADP project linked to SQLS
2000.

Any and all advice is gratefully received.

Kind thanks

Chris Strug

 

[Allen Browne]

Chris, I doubt that will work very well.

If you have anything in the LinkMasterFields/LinkChildFields, Access will
reassign the matching records to the subform as soon as you move record in
the main form. As a result the assignment of the Recordset is lost whenever
the main form changes record.

 

[Chris Strug]

Hi Allen,

Thanks for coming back to me so quick.

If I could explain my problem, could you perhaps give me a few ideas as to
how to approach it?

My application is an Access ADP linked to SQLS 2000 in which the vast
majority of the forms are unbound.

I have an unbound form which is for data entry ("bookings"). Each booking
is assigned a reference when it is added to its table. Each booking has a
number of addresses held in a booking table.

My problem is that I need someway to store the booking addresses at the
client until the job is actually added to the Server and I have the booking
reference. At this point I can add the addresses to their table as I have
the reference to link the addresses to their job.

I have thought about using a transaction table on the server but I need
someway to identify the addresses as belonging to their new booking. Because
of concurrency issues I can't just grab the next number of the temporary
table and at this point I'm stuck.

I've considered just using an autonumber (identifier actually but anyway) in
the temp table and storing the ID added addresses in an array. When the
booking is created I can then loop through the array, identifying each
temporary address for that job and adding it to the address table proper
with the job reference.

However this seems like a terribly convoluted way to doing things.

Any suggestions are gratefully received.

I hope I've made this clear enough!

Thanks again

Chris.

 

[Allen Browne]

If you are just adding new records (not modifying existing ones), then I see
no problem with just using a local table with an autonumber for the main
record, and another related local table for the related records.

When it comes time to commit them, you can write the main record, get the
@@IDENTITY from SQL Server, and then use that number in place of the Access
AutoNumber for the foreign key field in the INSERT statement that writes the
related records.

 

[Chris Strug]

Hi Allen,

Thanks for your advice.

Storing them in a local transaction table would make sense I agree -
unfortunately the application is an ADP: No local storage!

I suppose I could look at developing it in an MDB but I really don't want to
loose the functionality of the ADP that I've grown used to!

I was thinking of simply having a table on the server which holds addresses
before they are committed. For concurrency I could, when creating the first
address, generate a group ID and use that to identify all the addresses
belonging to that booking. When the main record is committed its then a
simple job of a SELECT INTO statement from the transaction table based on
the group ID.

It's still a bit messy though and there's still a (small) risk due to
concurrency concerns...

Any thoughts?

Thanks again

Chris.

If you are just adding new records (not modifying existing ones), then I see
no problem with just using a local table with an autonumber for the main
record, and another related local table for the related records.

When it comes time to commit them, you can write the main record, get the
@@IDENTITY from SQL Server, and then use that number in place of the Access
AutoNumber for the foreign key field in the INSERT statement that writes the
related records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
news:[email protected]...

<snip>

 

[Allen Browne]

Fair enough.

I don't use ADPs, so can't really give you a better suggestion. If anyone
else can that would be welcome.

 

[Dirk Goldgar]

Hi Allen,

Thanks for your advice.

Storing them in a local transaction table would make sense I agree -
unfortunately the application is an ADP: No local storage!

I suppose I could look at developing it in an MDB but I really don't
want to loose the functionality of the ADP that I've grown used to!

Could you create your own local "work" MDB file for this purpose?