ADODB.Recordset Add Record problem

  • Thread starter Robert Nusz via AccessMonster.com
  • Start date
R

Robert Nusz via AccessMonster.com

I have Microsoft Access 2003 on my computer. Service Pack 4, Operating
system is Windows 2000 at Service Pack 4. My database is stored on IBM
Mainframe computer in DB2. I have IBM DB2 Content Manager also installed
on my computer.

I have Read, Write, Save, and Delete rights to my table that is named
TST_FR_CASE_OTHERS. Field names in this table are:

CASE_NUM_YR
CASE_NUM
SEQ_NUM
VEHICLE_CDE
OTHER_CDE
OTHER_NME
OTHER_ADDR_TXT
FIRM_NME
OTHER_CITY_NME
OTHER_STATE_CDE
OTHER_ZIP_CDE
UPDATED_DATE

Primary Key field is combination of: CASE_NUM_YR, CASE_NUM, and SEQ_NUM.

I have a Ms/Access form that I execute, I pass it the CASE_NUM_YR value and
the CASE_NUM value. It executes a SQL Query that does the following:
Open Form = FR_CR_U
View = Form
Where Condition = ([TST_FR_CASE_RECORDS]![CASE_NUM_YR]=[Forms]!
[Fr_Search_By_Case]![unbtxt_SEARCH_CASE_YR] And [TST_FR_CASE_RECORDS]!
[CASE_NUM]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_CASE_NUM])
Data Mode = Edit
Window Mode = Normal

Form FR_CR_U then opens reflecting the data in the form. This is a
(form/subform) form. The parent form reflects the primary table record
(only 1), with the subform reflecting possible multiple recors from table
named above TST_FR_CASE_OTHERS.

This form allow the user to scroll forward and backward through the data,
make changes to the existing records without any problems, but I need to
allow user to "ADD" records to the same cluster of data. I opted to use
the ADODB.Recordset control. Code follows:

Option Compare Database

Private Sub Command33_Undo_Changes_Click()
On Error GoTo Err_Command33_Undo_Changes_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command33_Undo_Changes_Click:
Exit Sub

Err_Command33_Undo_Changes_Click:
MsgBox Err.Description
Resume Exit_Command33_Undo_Changes_Click

End Sub

Private Sub Command63_Return_Main_Page_Click()

Me.Parent.SetFocus
'Me.Parent!CASE_NUM_YR_P1.SetFocus

End Sub

Private Sub Form_AfterUpdate()

Me.Refresh

Me.CASE_NUM_YR_OTHER = Me.unbtxt_PREV_CASE_YR
Me.CASE_NUM_OTHER = Me.unbtxt_PREV_CASE_NUM

End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

'Only On-Apply Filter, then

If Me.CASE_NUM_OTHER <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
End If

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.CASE_NUM_OTHER <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE
End If

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "You Must Enter The Case Record First"
Else
Me.txt_SEQ_NUM = (unbtxt_TtlRecNum + 2)
Me.CASE_NUM_YR_OTHER = Me.unbtxt_PREV_CASE_YR
Me.CASE_NUM_OTHER = Me.unbtxt_PREV_CASE_NUM
End If

End With

'Me.Refresh

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord And _
Me.Dirty And _
IsNull(Me.VEHICLE_CDE) = True And _
IsNull(Me.OTHER_CDE) = True And _
IsNull(Me.OTHER_NME) = True And _
IsNull(Me.FIRM_NME) = True And _
IsNull(Me.OTHER_ADDR_TXT) = True And _
IsNull(Me.OTHER_CITY_NME) = True And _
IsNull(Me.OTHER_STATE_CDE) = True And _
IsNull(Me.OTHER_ZIP_CDE) = True Then
Me.Undo
Me.Dirty = False
Cancel = True
ElseIf Me.NewRecord And _
Me.Dirty Then
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE
MsgBox " Wrote The Record "
Else
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE

End If

End Sub

Private Sub Form_Current()

Me.txt_VEHICLE_CDE.SetFocus

Dim lngCount As String
lngCount = RecordsInTable("TST_FR_CASE_OTHERS", "SEQ_NUM")

Me.Refresh

'the following code should prevent the user from scrolling past
'BOF (Beginning-of-file) and EOF (End-of-File)
If Me.CurrentRecord = lngCount Then
Me.Recordset.MoveLast
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.Command86_View_Next_Record.Enabled = False
Me.Command85_View_Previous_Record.Enabled = True
MsgBox "There Are No More Records To Display For This Case Number.
" & _
" If You Wish To Add More Records To This Case, Click The Add
New Record Button"

ElseIf Me.CurrentRecord = 1 Then
Me.Recordset.MoveFirst
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.Command86_View_Next_Record.Enabled = True
Me.Command85_View_Previous_Record.Enabled = False

Else
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.Command86_View_Next_Record.Enabled = True
Me.Command85_View_Previous_Record.Enabled = True
End If
' End of Special code to prevent BOF/EOF Scrolling

End Sub

Private Sub Form_Load()

'Declare and instantiate an ADODB Recordset
Dim strADODBTableYr As String, strADODBTableCase As String,
strADODBTable As String

Dim rstADODB As ADODB.Recordset
strADODBTable = "TST_FR_CASE_OTHERS"
strADODBTableYr = strADODBTable & ".CASE_NUM_YR"
strADODBTableCase = strADODBTable & ".CASE_NUM"

strFormYear = Me.CASE_NUM_YR
strFormCase = Me.CASE_NUM

If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_YR
End If

If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If

Set rstADODB = New ADODB.Recordset

'Establish Connection Cursor Type, Cursor Location & Record Lock Type
here
rstADODB.ActiveConnection = CurrentProject.Connection
rstADODB.CursorType = adOpenDynamic 'Allow Immediate
Access to Recordset
rstADODB.CursorLocation = adUseClient ' or adUseServer
rstADODB.LockType = adLockOptimistic

'Create select Recordset here
'rstADODB.Open "SELECT * From " & strADODBTable & ", Options:=adCmdText
rstADODB.Open "SELECT * From TST_FR_CASE_OTHERS WHERE CASE_NUM_YR = " &
strFormYear & _
" And CASE_NUM = " & strFormCase & " Order By SEQ_NUM;"

'set the forms Recordset to the Recordset just created
Set Me.Recordset = rstADODB
Me.Recordset.MoveFirst
Me.Bookmark = Me.Recordset.Bookmark

Me.CASE_NUM_YR_OTHER.DefaultValue = Nz(Me.OpenArgs, "")
Me.CASE_NUM_OTHER.DefaultValue = Nz(Me.OpenArgs, "")

Set rstADODB = Nothing

End Sub

Private Sub Command81_Return_Click()
On Error GoTo Err_Command81_Return_Click

DoCmd.Close

Exit_Command81_Return_Click:
Exit Sub

Err_Command81_Return_Click:
MsgBox Err.Description
Resume Exit_Command81_Return_Click

End Sub

Private Sub Command85_View_Previous_Record_Click()

unbtxt_PREV_VEHICLE_CDE = txt_VEHICLE_CDE
unbtxt_PREV_OTHER_CDE = txt_OTHER_CDE

'Move to the Previous Record in the Recordset
Me.Recordset.MovePrevious

'If at BOF, then move to Next Record in Recordset
If Me.Recordset.BOF Then
Me.Recordset.MoveFirst
MsgBox "This Is The First Record For This Case"
End If

'Set the Bookmark of the Form to be the Bookmark of the Recordset
Me.Bookmark = Me.Recordset.Bookmark

End Sub

Private Sub Command86_View_Next_Record_Click()

unbtxt_PREV_VEHICLE_CDE = txt_VEHICLE_CDE
unbtxt_PREV_OTHER_CDE = txt_OTHER_CDE

'Move to the Next Record in the Recordset
Me.Recordset.MoveNext

'If at EOF, then move to Previous Record in Recordset
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "This Is The Last Record For This Case"
End If

'Set the Bookmark of the Form to be the Bookmark of the Recordset
Me.Bookmark = Me.Recordset.Bookmark

End Sub

Private Sub Command88_Delete_Record_Click()
On Error GoTo Err_Command88_Delete_Record_Click

'Ask User To Confirm Delete Of This Record

intAnswer = MsgBox("Are You Sure, Delete This Record??", _
vbYesNo + vbQuestion, _
"Delete Current REcord?")

'If intAnswer = Yes, Then Delete the Record and Move to Next Record in
Recordset
If intAnswer = vbYes Then
Me.Recordset.Delete
Call cmdNext_Click
Me.Refresh
End If

Exit_Command88_Delete_Record_Click:
Exit Sub

Err_Command88_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Command88_Delete_Record_Click

End Sub

Function RecordsInTable(Tablename As String, Fieldname As String) As Long
Dim strSQL As String, strTableField As String
Dim rst As DAO.Recordset

strTableField = Tablename & "." & Fieldname
strTableYr = Tablename & ".CASE_NUM_YR"
strTableCase = Tablename & ".CASE_NUM"

strFormYear = Me.CASE_NUM_YR
strFormCase = Me.CASE_NUM

If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_YR
End If

If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If

strSQL = "SELECT Count(" & strTableField & ") AS [Count] From " &
Tablename & _
" WHERE " & strTableYr & " = " & strFormYear & _
" And " & strTableCase & " = " & strFormCase & ";"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
RecordsInTable = rst!Count

Set rst = Nothing

End Function

Private Sub cmd_AddNewRecord_Click()
On Error GoTo Err_cmd_AddNewRecord_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmd_AddNewRecord_Click:
Exit Sub

Err_cmd_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_AddNewRecord_Click

End Sub
Private Sub cmd_SaveNewRecord_Click()
On Error GoTo Err_cmd_SaveNewRecord_Click

If Me.NewRecord And _
Me.Dirty And _
IsNull(Me.VEHICLE_CDE) = True And _
IsNull(Me.OTHER_CDE) = True And _
IsNull(Me.OTHER_NME) = True And _
IsNull(Me.FIRM_NME) = True And _
IsNull(Me.OTHER_ADDR_TXT) = True And _
IsNull(Me.OTHER_CITY_NME) = True And _
IsNull(Me.OTHER_STATE_CDE) = True And _
IsNull(Me.OTHER_ZIP_CDE) = True Then
MsgBox " A Valid Record Is Required, Please Correct Record
& Try Again!!!"
Me.Undo
Me.Dirty = False
Cancel = True
Else
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish Connection Cursor Type, Cursor Location & Record Lock
Type here
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic 'Allow Immediate Access to
Recordset
rst.CursorLocation = adUseClient ' or adUseServer
rst.LockType = adLockOptimistic
'Create select Recordset here
rst.Open "SELECT * From TST_FR_CASE_OTHERS WHERE CASE_NUM_YR =
Me.CASE_NUM_YR.OTHER" & _
" And CASE_NUM = Me.CASE_NUM_OTHER Order By SEQ_NUM;"
'set the forms Recordset to the Recordset just created
Set Me.Recordset = rstADODB
Me.Recordset.AddNew
Me.Recordset("CASE_NUM_YR") = Me.CASE_NUM_YR_OTHER()
Me.Recordset("CASE_NUM") = Me.CASE_NUM_OTHER()
Me.Recordset("SEQ_NUM") = Me.SEQ_NUM()
Me.Recordset("VEHICLE_CDE") = Me.txt_VEHICLE_CDE()
Me.Recordset("OTHER_CDE") = Me.txt_OTHER_CDE()
Me.Recordset("OTHER_NME") = Me.txt_OTHER_NME()
Me.Recordset("FIRM_NME") = Me.txt_FIRM_NME()
Me.Recordset("OTHER_CITY_NME") = Me.txt_OTHER_CITY_NME()
Me.Recordset("OTHER_STATE_CDE") = Me.txt_OTHER_STATE_CDE()
Me.Recordset("OTHER_ZIP_CDE") = Me.txt_OTHER_ZIP_CDE()
Me.Recordset("UPDATED_DATE") = Me.txt_UPDATED_DATE()
Me.Recordset.Update
Me.Bookmark = Me.Recordset.Bookmark
End If

Exit_cmd_SaveNewRecord_Click:
Exit Sub

Err_cmd_SaveNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_SaveNewRecord_Click

End Sub

Record passes edits, but fails to write to the table. Can someone assist
me in telling me what I did incorrectly.

Thanks,

Robert
 
M

Michel Walsh

Hi,

It is useless, and time consuming, to open a recordset just to append a
new record in some table. Instead of opening a recordset just for that, try:

DoCmd.RunSQL "INSERT INTO tableName(listOfFields) VALUES(
listOfValues); "


You can also use CurrentDb.Execute or CurrentProject.Connection.Execute, but
with those two, you would not be able to use FORMS!formName!ControlName
syntax, so, you will have to supply the delimiter. With DoCmd, you can use
the FORMS syntax:

" ... VALUES( FORMS!FormName!Case_num_yr_other,
FORMS!FormName!Seq_Num, ... ) "


Sure, that will appear as a record added by another user (your VBA code is
not the user in front of the keyboard), and you may have to requery the form
source.

In the other than, I don't understand why you use ( ) after the
control name, almost in the last lines of your code ... or are they
function?

Jet does not support dynamic recordsets. A dynamic recordset is a
recordset that will REMOVE the record from the set if, after a modification,
the modified recordset does not pass the WHERE clause. Illustration:
dynamic recordset with SELECT * FROM prices WHERE cost<=10; Next, assume
you change a record, with a value for its cost now at a value >10. After the
update, this dynamic record won't be accessible from the dynamic recordset.
That is the Dynamic behavior. If you want the DAO behavior, use KeySet: the
records that will be part of the recordsets are determined at creation time
of the recordset (and NOT through a continuous follow-up as in Dynamic
behavior). In fact, if you check the recordset after its creation, it will
be reported as a Keyset, not as Dynamic, even if you ask for a Dynamic one
(graceful, errorless, "demotion" from dynamic to keyset). I will also avoid
ClientSide mode, with Jet, since Access does not really support disconnected
modes, anyhow, that could hurt the performance without any benefit of being
disconnected.



Hoping it may help,
Vanderghast, Access MVP



Robert Nusz via AccessMonster.com said:
I have Microsoft Access 2003 on my computer. Service Pack 4, Operating
system is Windows 2000 at Service Pack 4. My database is stored on IBM
Mainframe computer in DB2. I have IBM DB2 Content Manager also installed
on my computer.

I have Read, Write, Save, and Delete rights to my table that is named
TST_FR_CASE_OTHERS. Field names in this table are:

CASE_NUM_YR
CASE_NUM
SEQ_NUM
VEHICLE_CDE
OTHER_CDE
OTHER_NME
OTHER_ADDR_TXT
FIRM_NME
OTHER_CITY_NME
OTHER_STATE_CDE
OTHER_ZIP_CDE
UPDATED_DATE

Primary Key field is combination of: CASE_NUM_YR, CASE_NUM, and SEQ_NUM.

I have a Ms/Access form that I execute, I pass it the CASE_NUM_YR value
and
the CASE_NUM value. It executes a SQL Query that does the following:
Open Form = FR_CR_U
View = Form
Where Condition = ([TST_FR_CASE_RECORDS]![CASE_NUM_YR]=[Forms]!
[Fr_Search_By_Case]![unbtxt_SEARCH_CASE_YR] And [TST_FR_CASE_RECORDS]!
[CASE_NUM]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_CASE_NUM])
Data Mode = Edit
Window Mode = Normal

Form FR_CR_U then opens reflecting the data in the form. This is a
(form/subform) form. The parent form reflects the primary table record
(only 1), with the subform reflecting possible multiple recors from table
named above TST_FR_CASE_OTHERS.

This form allow the user to scroll forward and backward through the data,
make changes to the existing records without any problems, but I need to
allow user to "ADD" records to the same cluster of data. I opted to use
the ADODB.Recordset control. Code follows:

Option Compare Database

Private Sub Command33_Undo_Changes_Click()
On Error GoTo Err_Command33_Undo_Changes_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command33_Undo_Changes_Click:
Exit Sub

Err_Command33_Undo_Changes_Click:
MsgBox Err.Description
Resume Exit_Command33_Undo_Changes_Click

End Sub

Private Sub Command63_Return_Main_Page_Click()

Me.Parent.SetFocus
'Me.Parent!CASE_NUM_YR_P1.SetFocus

End Sub

Private Sub Form_AfterUpdate()

Me.Refresh

Me.CASE_NUM_YR_OTHER = Me.unbtxt_PREV_CASE_YR
Me.CASE_NUM_OTHER = Me.unbtxt_PREV_CASE_NUM

End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

'Only On-Apply Filter, then

If Me.CASE_NUM_OTHER <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
End If

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.CASE_NUM_OTHER <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE
End If

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "You Must Enter The Case Record First"
Else
Me.txt_SEQ_NUM = (unbtxt_TtlRecNum + 2)
Me.CASE_NUM_YR_OTHER = Me.unbtxt_PREV_CASE_YR
Me.CASE_NUM_OTHER = Me.unbtxt_PREV_CASE_NUM
End If

End With

'Me.Refresh

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord And _
Me.Dirty And _
IsNull(Me.VEHICLE_CDE) = True And _
IsNull(Me.OTHER_CDE) = True And _
IsNull(Me.OTHER_NME) = True And _
IsNull(Me.FIRM_NME) = True And _
IsNull(Me.OTHER_ADDR_TXT) = True And _
IsNull(Me.OTHER_CITY_NME) = True And _
IsNull(Me.OTHER_STATE_CDE) = True And _
IsNull(Me.OTHER_ZIP_CDE) = True Then
Me.Undo
Me.Dirty = False
Cancel = True
ElseIf Me.NewRecord And _
Me.Dirty Then
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE
MsgBox " Wrote The Record "
Else
Me.unbtxt_PREV_CASE_YR = Me.CASE_NUM_YR_OTHER
Me.unbtxt_PREV_CASE_NUM = Me.CASE_NUM_OTHER
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE

End If

End Sub

Private Sub Form_Current()

Me.txt_VEHICLE_CDE.SetFocus

Dim lngCount As String
lngCount = RecordsInTable("TST_FR_CASE_OTHERS", "SEQ_NUM")

Me.Refresh

'the following code should prevent the user from scrolling past
'BOF (Beginning-of-file) and EOF (End-of-File)
If Me.CurrentRecord = lngCount Then
Me.Recordset.MoveLast
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.Command86_View_Next_Record.Enabled = False
Me.Command85_View_Previous_Record.Enabled = True
MsgBox "There Are No More Records To Display For This Case Number.
" & _
" If You Wish To Add More Records To This Case, Click The Add
New Record Button"

ElseIf Me.CurrentRecord = 1 Then
Me.Recordset.MoveFirst
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.Command86_View_Next_Record.Enabled = True
Me.Command85_View_Previous_Record.Enabled = False

Else
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.Command86_View_Next_Record.Enabled = True
Me.Command85_View_Previous_Record.Enabled = True
End If
' End of Special code to prevent BOF/EOF Scrolling

End Sub

Private Sub Form_Load()

'Declare and instantiate an ADODB Recordset
Dim strADODBTableYr As String, strADODBTableCase As String,
strADODBTable As String

Dim rstADODB As ADODB.Recordset
strADODBTable = "TST_FR_CASE_OTHERS"
strADODBTableYr = strADODBTable & ".CASE_NUM_YR"
strADODBTableCase = strADODBTable & ".CASE_NUM"

strFormYear = Me.CASE_NUM_YR
strFormCase = Me.CASE_NUM

If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_YR
End If

If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If

Set rstADODB = New ADODB.Recordset

'Establish Connection Cursor Type, Cursor Location & Record Lock Type
here
rstADODB.ActiveConnection = CurrentProject.Connection
rstADODB.CursorType = adOpenDynamic 'Allow Immediate
Access to Recordset
rstADODB.CursorLocation = adUseClient ' or adUseServer
rstADODB.LockType = adLockOptimistic

'Create select Recordset here
'rstADODB.Open "SELECT * From " & strADODBTable & ", Options:=adCmdText
rstADODB.Open "SELECT * From TST_FR_CASE_OTHERS WHERE CASE_NUM_YR = " &
strFormYear & _
" And CASE_NUM = " & strFormCase & " Order By SEQ_NUM;"

'set the forms Recordset to the Recordset just created
Set Me.Recordset = rstADODB
Me.Recordset.MoveFirst
Me.Bookmark = Me.Recordset.Bookmark

Me.CASE_NUM_YR_OTHER.DefaultValue = Nz(Me.OpenArgs, "")
Me.CASE_NUM_OTHER.DefaultValue = Nz(Me.OpenArgs, "")

Set rstADODB = Nothing

End Sub

Private Sub Command81_Return_Click()
On Error GoTo Err_Command81_Return_Click

DoCmd.Close

Exit_Command81_Return_Click:
Exit Sub

Err_Command81_Return_Click:
MsgBox Err.Description
Resume Exit_Command81_Return_Click

End Sub

Private Sub Command85_View_Previous_Record_Click()

unbtxt_PREV_VEHICLE_CDE = txt_VEHICLE_CDE
unbtxt_PREV_OTHER_CDE = txt_OTHER_CDE

'Move to the Previous Record in the Recordset
Me.Recordset.MovePrevious

'If at BOF, then move to Next Record in Recordset
If Me.Recordset.BOF Then
Me.Recordset.MoveFirst
MsgBox "This Is The First Record For This Case"
End If

'Set the Bookmark of the Form to be the Bookmark of the Recordset
Me.Bookmark = Me.Recordset.Bookmark

End Sub

Private Sub Command86_View_Next_Record_Click()

unbtxt_PREV_VEHICLE_CDE = txt_VEHICLE_CDE
unbtxt_PREV_OTHER_CDE = txt_OTHER_CDE

'Move to the Next Record in the Recordset
Me.Recordset.MoveNext

'If at EOF, then move to Previous Record in Recordset
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "This Is The Last Record For This Case"
End If

'Set the Bookmark of the Form to be the Bookmark of the Recordset
Me.Bookmark = Me.Recordset.Bookmark

End Sub

Private Sub Command88_Delete_Record_Click()
On Error GoTo Err_Command88_Delete_Record_Click

'Ask User To Confirm Delete Of This Record

intAnswer = MsgBox("Are You Sure, Delete This Record??", _
vbYesNo + vbQuestion, _
"Delete Current REcord?")

'If intAnswer = Yes, Then Delete the Record and Move to Next Record in
Recordset
If intAnswer = vbYes Then
Me.Recordset.Delete
Call cmdNext_Click
Me.Refresh
End If

Exit_Command88_Delete_Record_Click:
Exit Sub

Err_Command88_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Command88_Delete_Record_Click

End Sub

Function RecordsInTable(Tablename As String, Fieldname As String) As Long
Dim strSQL As String, strTableField As String
Dim rst As DAO.Recordset

strTableField = Tablename & "." & Fieldname
strTableYr = Tablename & ".CASE_NUM_YR"
strTableCase = Tablename & ".CASE_NUM"

strFormYear = Me.CASE_NUM_YR
strFormCase = Me.CASE_NUM

If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_YR
End If

If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If

strSQL = "SELECT Count(" & strTableField & ") AS [Count] From " &
Tablename & _
" WHERE " & strTableYr & " = " & strFormYear & _
" And " & strTableCase & " = " & strFormCase & ";"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
RecordsInTable = rst!Count

Set rst = Nothing

End Function

Private Sub cmd_AddNewRecord_Click()
On Error GoTo Err_cmd_AddNewRecord_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmd_AddNewRecord_Click:
Exit Sub

Err_cmd_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_AddNewRecord_Click

End Sub
Private Sub cmd_SaveNewRecord_Click()
On Error GoTo Err_cmd_SaveNewRecord_Click

If Me.NewRecord And _
Me.Dirty And _
IsNull(Me.VEHICLE_CDE) = True And _
IsNull(Me.OTHER_CDE) = True And _
IsNull(Me.OTHER_NME) = True And _
IsNull(Me.FIRM_NME) = True And _
IsNull(Me.OTHER_ADDR_TXT) = True And _
IsNull(Me.OTHER_CITY_NME) = True And _
IsNull(Me.OTHER_STATE_CDE) = True And _
IsNull(Me.OTHER_ZIP_CDE) = True Then
MsgBox " A Valid Record Is Required, Please Correct Record
& Try Again!!!"
Me.Undo
Me.Dirty = False
Cancel = True
Else
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish Connection Cursor Type, Cursor Location & Record Lock
Type here
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic 'Allow Immediate Access to
Recordset
rst.CursorLocation = adUseClient ' or adUseServer
rst.LockType = adLockOptimistic
'Create select Recordset here
rst.Open "SELECT * From TST_FR_CASE_OTHERS WHERE CASE_NUM_YR =
Me.CASE_NUM_YR.OTHER" & _
" And CASE_NUM = Me.CASE_NUM_OTHER Order By SEQ_NUM;"
'set the forms Recordset to the Recordset just created
Set Me.Recordset = rstADODB
Me.Recordset.AddNew
Me.Recordset("CASE_NUM_YR") = Me.CASE_NUM_YR_OTHER()
Me.Recordset("CASE_NUM") = Me.CASE_NUM_OTHER()
Me.Recordset("SEQ_NUM") = Me.SEQ_NUM()
Me.Recordset("VEHICLE_CDE") = Me.txt_VEHICLE_CDE()
Me.Recordset("OTHER_CDE") = Me.txt_OTHER_CDE()
Me.Recordset("OTHER_NME") = Me.txt_OTHER_NME()
Me.Recordset("FIRM_NME") = Me.txt_FIRM_NME()
Me.Recordset("OTHER_CITY_NME") = Me.txt_OTHER_CITY_NME()
Me.Recordset("OTHER_STATE_CDE") = Me.txt_OTHER_STATE_CDE()
Me.Recordset("OTHER_ZIP_CDE") = Me.txt_OTHER_ZIP_CDE()
Me.Recordset("UPDATED_DATE") = Me.txt_UPDATED_DATE()
Me.Recordset.Update
Me.Bookmark = Me.Recordset.Bookmark
End If

Exit_cmd_SaveNewRecord_Click:
Exit Sub

Err_cmd_SaveNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_SaveNewRecord_Click

End Sub

Record passes edits, but fails to write to the table. Can someone assist
me in telling me what I did incorrectly.

Thanks,

Robert
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add New Records to secondard Table with Subform 0
help convert DAO.Recordset to ADODB.Recordset 1
DoCmd.GoToREcord error! 0
Code is failing on empyt recordset 3
Delete code competing with BeforeUpdate validation 2
Filtering a multiple item form 1
Write New table record failing with error 424 1
Add new record if nothing found with filter 2

Top