Binding a form's recordset to VB-Code - how about updates/inserts?

[oddbec]

Hi!

I have bound a form's recordset to a recordset i've created through some VB code.

However, the form has "allow inserts" and "allow updates" properties set, and I wonder how I'm supposed to go about implementing these functions.

I'm not an experienced Microsoft Access user, and I'm not that famliar with working with Forms etc.

Does anyone have an pointers or tutorials that go about explaining this?

Thanks in advance.

Best regards,

Beck.

 

[oddbec]

Maybe I should rephrase things:

I have this function defined:


Public Function DoQueryReturnRecordset(TAGNum As String) As ADODB.Recordset

Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim i As Integer

Set DoQueryReturnRecordset = New ADODB.Recordset

cnn.ConnectionString = "ODBC;Driver=OMNIS;DSN=XXXXX;Description=CXXXXX;UID=XXXXXX;Trusted_Connection=Yes;DATABASE=XXXXX"

cnn.Open

strSQL = "SELECT LOTSASTUFF FROM (Table1 INNER JOIN Table2 ON Table1.code = Table2.Kv_code) INNER JOIN Table3 ON Table1.TagNum = Table3.TAGNum where Table3.TAGNum = '" & TAGNum & "'"

DoQueryReturnRecordset.CursorLocation = adUseClient

DoQueryReturnRecordset.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

Set DoQueryReturnRecordset.ActiveConnection = Nothing

cnn.Close
Set cnn = Nothing

End Function


and in the Parent Form_Load-event I have code very similar to this:

SubForm.Recordset = DoQueryReturnRecordSet(someID)
and then Bind the data.

The form is editable, insertable and when I try to add new data to this form it says it can't do it.

I've read somewhere that pass through queries don't work, but this isn't exactly a pass through query, instead it's a recordset.

What am I missing? Do I need some more functions set up to some other event handlers? How am I suppsed to define an insert-event and update-event related to changes / inserts in this form?

Hope to receive some tips.

 

[VilaRestal]

The recordset is static and read only
DoQueryReturnRecordset.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
It's not surprising you can't add to it

 

[VilaRestal]

Why not just set the form's recordsource to
"SELECT LOTSASTUFF FROM (Table1 INNER JOIN Table2 ON Table1.code = Table2.Kv_code) INNER JOIN Table3 ON Table1.TagNum = Table3.TAGNum where Table3.TAGNum = '" & TAGNum & "'"
? Rather than create a recordset from that query and assign the recordset to the form?

 

[oddbec]

Thanks, I've tried changing the recordset-query, but to no avail. It seems as if Access doesn't know how to create the update/delete statements when I'm doing it this way, could this be correct?

I do this programatically because I'm not able to query the underlying SQL Server using the special functions we have added to it in order to filter data before Access receives them.

 

[Galaxiom]

Even if you have changed the recordset properties it will still be a disconnected recordset because you have killed the ActiveConnection in the function.

The connection object variable would need to be declared at the top of the module so it persisted after the function ended and definitely not closed by the function if you want the recordset connection to remain active.

When you do close the connection you really should Close it formally before setting the object variable to Nothing. Otherwise you would be leaving the server end to time out instead of negotiating a closure.

 

[Galaxiom]

You would also need to Set the recordset of the form to the recordset created by the function. (Not just with equals).

I don't see how your code could work at all otherwise. Perhaps it is not your actual code and you have actually done this. Note however that this wastes people's time finding typos instead of the real problems so it is better to post what you really use.

Also please post your code in a code box.

 

[oddbec]

Yeah, I didn't cut'n paste the code, but i had i.e. "Set" the recordset of the form, as you mentioned.

I've tried not closing the connections and retaining the variables in the more global scope, however I'm not nearer a solution. I think I have to redo it some way so that I'm only displaying info, and then I have to set up separate add/delete/edit buttons to the repeated form.

Thanks for your input anyway.