help convert DAO.Recordset to ADODB.Recordset

[Guest]

I've attempted ADODB.recordset and cant get the code correct. I've been able
to get the following form and table activity to work by using DAO.Recordset.

My table(s) primary and secondary (parent/child) are on IBM Mainframe DB2
structure. My frontend is Microsoft Access 2003 at Service Pack 4, operating
system is Windows 2000 Professional at service pack 4. I've been told that
ADODB was the way to go, but could not get the results to work. The
following code works as DAO.recordset. If there is someone that can convert
this code to use ADODB recordsets and get the same results, I would
appreciate backup help. If ADODB is the way to go, then please help.

This is the code:

Option Compare Database

Private Sub Command32_Save_Click()
On Error GoTo Err_Command32_Save_Click
'This code is revision version: 02/26/05 - 01

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command32_Save_Click:
Exit Sub

Err_Command32_Save_Click:
MsgBox Err.Description
Resume Exit_Command32_Save_Click

End Sub
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()

Forms!Fr_CR_U.SetFocus
Forms!Fr_CR_U.Form!CASE_NUM_YR_P1.SetFocus

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strSecondaryKey As String

'If Current Form Case Number Not Equal Previous Current Form Case
Number, Then
If Me.txt_CASE_NUM <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_SEQ_NUM = 0
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE
End If

'Confirms that Parent Form Has Current Matching Record, if so, then do
Else, If Not Cancel
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "You Must Enter The Case Record First"
Else
'You are adding new record, increment SEQ_NUM field by 2, (DO
NOT CHANGE AMOUNT!)
Me.txt_SEQ_NUM = (Me.unbtxt_TtlRecNum + 2)
Me.txt_CASE_NUM_YR = Me.unbtxt_PREV_CASE_NUM_YR
Me.txt_CASE_NUM = Me.unbtxt_PREV_CASE_NUM
End If

End With

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

'If NewRecord & current form has been modfied, thus setting (DIRTY) flag
"BUT",
'ALL other fields are NULL, then CANCEL UPDATE
If Me.NewRecord And _
Me.Dirty And _
IsNull(Me.txt_VEHICLE_CDE) = True And _
IsNull(Me.txt_OTHER_CDE) = True And _
IsNull(Me.txt_OTHER_NME) = True And _
IsNull(Me.txt_OTHER_ADDR) = True And _
IsNull(Me.txt_FIRM_NME) = True And _
IsNull(Me.txt_OTHER_CITY_NME) = True And _
IsNull(Me.txt_OTHER_STATE_CDE) = True And _
IsNull(Me.txt_OTHER_ZIP_CDE) = True And _
IsNull(Me.txt_UPDATED_DATE) = True Then
Me.Undo
Me.Dirty = False
Cancel = True
ElseIf Me.NewRecord And _
Me.Dirty Then
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE
'Call RecordsInTable Function to Update Record Count
lngCount = RecordsInTable("TST_FR_CASE_OTHERS", "SEQ_NUM")
Else
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_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()
'Set focus and cursor at current form, VEHICLE_CDE field
Me!txt_VEHICLE_CDE.SetFocus

'Recount the records in recordset

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

Me.Refresh

'Special Code to Control/Block Scrolling past BOF/EOF for Users
If Me.CurrentRecord = lngCount Then
Me.Recordset.MoveLast
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.cmd_Next_Rec.Enabled = False
Me.cmd_Prev_Rec.Enabled = True
MsgBox "There Are No More Records to Display For This Case Number!"
& _
" If You Wish To Add More Records, Then Click The Add New Record
Button! "
ElseIf Me.CurrentRecord = 1 Then
Me.Recordset.MoveFirst
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.cmd_Next_Rec.Enabled = True
Me.cmd_Prev_Rec.Enabled = False
Else
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
Me.cmd_Next_Rec.Enabled = True
Me.cmd_Prev_Rec.Enabled = True
End If

If Me.txt_CASE_NUM <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_SEQ_NUM = 0
End If
End Sub

Private Sub Form_Load()

Me!txt_CASE_NUM_YR.DefaultValue = Nz(Me.OpenArgs, "")
Me!txt_CASE_NUM.DefaultValue = Nz(Me.OpenArgs, "")

If Me.txt_CASE_NUM <> Me.unbtxt_PREV_CASE_NUM Then
Me.unbtxt_PREV_SEQ_NUM = 0
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_NUM
End If

End Sub

Private Sub Command36_Add_New_Record_Click()
On Error GoTo Err_Command36_Add_New_Record_Click

Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE

DoCmd.GoToRecord , , acNewRec

Exit_Command36_Add_New_Record_Click:
Exit Sub

Err_Command36_Add_New_Record_Click:
MsgBox Err.Description
Resume Exit_Command36_Add_New_Record_Click

End Sub

Private Sub Form_Open(Cancel As Integer)

Me.unbtxt_PREV_SEQ_NUM = 0

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.txt_CASE_NUM_YR
strFormCase = Me.txt_CASE_NUM

'If strFormYear is Null, then get Case Year from Previous Case Year field
If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_NUM_YR
End If

'If strFormCase is Null, then get Case # from Previous Case Number Field
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 & ";"
If strSQL.Count = 0 Then
MsgBox " No Matching Records Found"
End If

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

Set rst = Nothing

End Function

Private Sub cmd_Prev_Rec_Click()
On Error GoTo Err_cmd_Prev_Rec_Click

'Executed when user wants to navigate to PREVIOUS RECORD in CURRENT
RECORDSET
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE

DoCmd.GoToRecord , , acPrevious

Exit_cmd_Prev_Rec_Click:
Exit Sub

Err_cmd_Prev_Rec_Click:
MsgBox Err.Description
Resume Exit_cmd_Prev_Rec_Click

End Sub
Private Sub cmd_Next_Rec_Click()
On Error GoTo Err_cmd_Next_Rec_Click

'Executed when user wants to navigate to NEXT RECORD in CURRENT RECORDSET
Me.unbtxt_PREV_SEQ_NUM = Me.txt_SEQ_NUM
Me.unbtxt_PREV_CASE_NUM_YR = Me.txt_CASE_NUM_YR
Me.unbtxt_PREV_CASE_NUM = Me.txt_CASE_NUM
Me.unbtxt_PREV_VEHICLE_CDE = Me.txt_VEHICLE_CDE
Me.unbtxt_PREV_OTHER_CDE = Me.txt_OTHER_CDE

'If Me.Recordset.EOF Then
' Me.Recordset.MovePrevious
' MsgBox "This Is The Last Record For This Case Number!"
'End If

DoCmd.GoToRecord , , acNext

Exit_cmd_Next_Rec_Click:
Exit Sub

Err_cmd_Next_Rec_Click:
MsgBox Err.Description
Resume Exit_cmd_Next_Rec_Click

End Sub
Private Sub cmd_Add_Rec_Click()
On Error GoTo Err_cmd_Add_Rec_Click

MsgBox " Adding New Record "

Me.txt_CASE_NUM_YR.DefaultValue = Nz(Me.OpenArgs, "")
Me.txt_CASE_NUM.DefaultValue = Nz(Me.OpenArgs, "")

DoCmd.GoToRecord , , acNewRec

Exit_cmd_Add_Rec_Click:
Exit Sub

Err_cmd_Add_Rec_Click:
MsgBox Err.Description
Resume Exit_cmd_Add_Rec_Click

End Sub

 

[Dirk Goldgar]

I've attempted ADODB.recordset and cant get the code correct. I've
been able to get the following form and table activity to work by
using DAO.Recordset.

My table(s) primary and secondary (parent/child) are on IBM Mainframe
DB2 structure. My frontend is Microsoft Access 2003 at Service Pack
4, operating system is Windows 2000 Professional at service pack 4.
I've been told that ADODB was the way to go, but could not get the
results to work. The following code works as DAO.recordset. If
there is someone that can convert this code to use ADODB recordsets
and get the same results, I would appreciate backup help. If ADODB
is the way to go, then please help.

This is the code:

[snipped]

There's a lot of code there, of which very little involves recordsets in
any way. If you want to convert some part of the code to use ADO, you'd
better specify exactly what part, and what you want the revised code to
do. But what do you actually hope to gain by changing that code from
DAO to ADO? For most purposes, if you are working in an MDB file (and
not an ADP), DAO is at least as efficient as ADO. And that portion of
your code that manipulates the recordset of the form itself *must* use
DAO, because the form's recordset *will be* a DAO recordset if you're
working with an MDB file -- unless you've gone to great lengths to
rebind the form to a separately created ADODB recordset.