Connection and recordset backend db

[Guest]

I have form that when it unloads, checks for a null value within a table in
the open db. If there are no records, the user is prompted for input. I want
the backend db table (tblSys) to be updated with values identified int he
code. The code I have runs without errors but the data is not updated in the
backend table. I think it might have to do with how my code involving
connections is being done. Help would be appreciated. I am new to recordsets
am really tring to understand more about them. Thank you.

Private Sub Form_Unload(Cancel As Integer)
'Asks user for info then updates info in backend table tblSys
'Table tblSys is a linked backend db to this db
'declare variables and assign address to object variables

Dim cnnUser As adodb.Connection, rsNewData As Recordset

Dim varBlank As Variant, strResponse As String
Dim varCurDir As Variant
Dim dbs As Database
'Dim sVar As String, sValue As String, sDesc As String

'Run function and returns path of active database without
'db name used in next code line
CurrentDBDir

'concantenates db path (CurrentDBDir returned by function) & backend database
varCurDir = CurrentDBDir & "Bike Log_be.mdb"

'Should set connection to backend db
Set cnnUser = Application.CurrentProject.Connection

Set rsNewData = New adodb.Recordset

'open record set in backend db
rsNewData.Open Source:="tblSys", ActiveConnection:=cnnUser, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

'check to see if any records exist in table
varBlank = DMax("[RecNum]", "Statistics")

If IsNull(varBlank) Then
strResponse = InputBox("Enter your first name and first three" _
& vbNewLine & "letters of your last name: (NO SPACES)", "Default
Name")

With rsNewData
.AddNew
![Variable] = "UserName"
![Value] = strResponse
![Description] = "Default user name input at startup"
End With
Else

rstNewData = Nothing
rstNewData.Close
Exit Sub

End If
rstNewData = Nothing
rsNewData.Close

End Sub

 

[David Lloyd]

Bobby:

One suggestion would be to call the Update method to write the record to the
database. For example:

With rsNewData
.AddNew
![Variable] = "UserName"
![Value] = strResponse
![Description] = "Default user name input at startup"
.Update
End With

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have form that when it unloads, checks for a null value within a table in
the open db. If there are no records, the user is prompted for input. I want
the backend db table (tblSys) to be updated with values identified int he
code. The code I have runs without errors but the data is not updated in the
backend table. I think it might have to do with how my code involving
connections is being done. Help would be appreciated. I am new to recordsets
am really tring to understand more about them. Thank you.

Private Sub Form_Unload(Cancel As Integer)
'Asks user for info then updates info in backend table tblSys
'Table tblSys is a linked backend db to this db
'declare variables and assign address to object variables

Dim cnnUser As adodb.Connection, rsNewData As Recordset

Dim varBlank As Variant, strResponse As String
Dim varCurDir As Variant
Dim dbs As Database
'Dim sVar As String, sValue As String, sDesc As String

'Run function and returns path of active database without
'db name used in next code line
CurrentDBDir

'concantenates db path (CurrentDBDir returned by function) & backend
database
varCurDir = CurrentDBDir & "Bike Log_be.mdb"

'Should set connection to backend db
Set cnnUser = Application.CurrentProject.Connection

Set rsNewData = New adodb.Recordset

'open record set in backend db
rsNewData.Open Source:="tblSys", ActiveConnection:=cnnUser, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

'check to see if any records exist in table
varBlank = DMax("[RecNum]", "Statistics")

If IsNull(varBlank) Then
strResponse = InputBox("Enter your first name and first three" _
& vbNewLine & "letters of your last name: (NO SPACES)", "Default
Name")

With rsNewData
.AddNew
![Variable] = "UserName"
![Value] = strResponse
![Description] = "Default user name input at startup"
End With
Else

rstNewData = Nothing
rstNewData.Close
Exit Sub

End If
rstNewData = Nothing
rsNewData.Close

End Sub