G
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
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