Saving data from unbound forms using ADO connections to SQL

  • Thread starter Thread starter Gail
  • Start date Start date
G

Gail

I am developing an application using unbound forms to
enter, view and edit records in various tables. The save
routine is a function called from a stand-alone module.
Elements to build the connection string and criteria
filters are embedded on each form. The primary table is a
customer master, keyed to the unique customer number. The
customer items table is 1:Many where one customer number
can have multiple items. There is a unique key of the item
number used to reference this table. Tables are stored in
SQL 7.0 on an NT4.0 server, and I'm using Access 2000 on
an NT4.0 machine as the project front-end.

In a nutshell, I started having problems saving the items
table yesterday. The function processes and reports the
record is saved, but nothing is added to the table. So I
went back to the master form and tried to enter another
new customer (which was working fine through yesterday.)
Now, I am getting an error that multiple ole actions
caused an error and no work was done. The connection
string was changed from Provider=SQLOLEDB; to
Provider=SQLOLEDB.1;. All tables exist in the same SQL
database. It's almost as if the connection has become
corrupt or something. It does not appear that the
connection is closed at any point in the code. Record sets
are, but not the connection. Yet it is rebuilt every time
the function (or any function in the module) is called.

When this started, I was calling the items form from the
master form. In case that was causing the problem, I tried
working from the items form directly this morning with the
same result. I do check to see if the form calling the
next one needs to be saved before moving on to the next
form.

Any suggestions? I am rather new at this, and have gotten
most of my information from the book "Access 2002 Bible"
by Cary Prague and Michael Irwin. I found it fairly easy
to understand and follow. The code I'm using for the
unbound forms is taken directly from the book.

Gail
 
Ok, here's thesave function. I tried updating an existing
record, and it was updated correctly. It does not give me
any errors for the new record, but there is nothing
written to the table.

Public Function uf_SaveRecord(frm As Form)

Dim strConnection As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim ctl As Control
Dim vartemp As Variant
Dim strCriteria As String
Dim Response As Integer

On Error GoTo ErrorHandler

'Check to see if data has been changed
If Not frm.FlagEdited Then
MsgBox "Nothing to save"
GoTo Done
End If

'Open connection
cnn.Open frm.Controls("xProvider") & frm.Controls
("xDataSource")

'Open recordset to determine type of key field and
setup Criteria
rst.Open "Select * From " & frm.Controls
("xRecordset"), cnn, adOpenStatic
Select Case rst(frm.Controls("xKey").Value).type
Case adChar, adVarWChar, adLongVarWChar
strCriteria = frm.Controls("xKey") & " = " &
Chr(39) & frm.Controls(frm.Controls("xKey")).Value & Chr
(39)
Case adDate
strCriteria = frm.Controls("xKey") & " = "
& "#" & frm.Controls(frm.Controls("xKey")).Value & "#"
Case Else 'assume numeric
strCriteria = frm.Controls("xKey") & " = " &
Str(frm.Controls(frm.Controls("xKey")).Value)
End Select
rst.Close

'Determine if this is a new record or a changed record
rst.Open "Select * From " & frm.Controls("xRecordset")
& " WHERE " & strCriteria, cnn, adOpenKeyset,
adLockOptimistic
If rst.RecordCount = 0 Or rst.RecordCount = -1 Then

'Create new record
rst.AddNew
'Iterate through controls on form that match
fields in recordset
For Each ctl In frm
'if error the field is not on the form
On Error Resume Next
Err = 0
vartemp = rst.Fields(ctl.Name).Name
If Err = 0 Then
On Error GoTo ErrorHandler
'if control enabled then
' if it is not an auto increment field
' if data is not null or an empty
string
If ctl.Enabled Then
If Not rst.Fields(ctl.Name).Properties
("IsAutoIncrement") Then
If Not IsNull(ctl.Value) And Not
ctl.Value = "" Then
vartemp = ctl.Value
rst(ctl.Name).Value = vartemp
End If
End If
End If
End If
Next
'Set the update counter
rst("UpdateCtr") = 1
'Update the recordset
rst.Update
rst.Close
Else
'Check to see if this record was already updated
by another user
If rst("UpdateCtr") <> frm.Controls
("UpdateCtr").Value Then
Response = MsgBox("This record was already
updated by another user." & vbCrLf & _
"Do you want to overwrite the other
user's changes?", _
vbInformation + vbYesNo, "Data already
changed")
If Response = vbNo Then
rst.Close
Exit Function
End If
End If
'Change record
'Iterate through controls on form that match
fields in recordset
For Each ctl In frm
'if error the field is not on the form
On Error Resume Next
Err = 0
vartemp = rst.Fields(ctl.Name).Name
If Err = 0 Then
On Error GoTo ErrorHandler
'if control enabled then
' if it is not an auto increment field
' if data is not null or an empty
string
If ctl.Enabled Then
If Not rst.Fields(ctl.Name).Properties
("IsAutoIncrement") Then
If Not IsNull(ctl.Value) And Not
ctl.Value = "" Then
vartemp = ctl.Value
rst(ctl.Name).Value = vartemp
End If
End If
End If
End If
Next
'Increment the Update Counter
rst("UpdateCtr") = rst("UpdateCtr") + 1
'Update the recordset
rst.Update
rst.Close

End If
'reset edited flag on form
frm.FlagEdited = False
MsgBox "Record Saved"
GoTo Done

ErrorHandler:
MsgBox Err.Description
Done:

End Function
 
Back
Top