sagarpatil
New Member
- Joined
- Feb 12, 2016
- Messages
- 1
I am wroking on Access Database(.mdb) using DAO recordset, However i am unable to navigate recordset. like Next, Previous, First, Last recordset. I am unable to find issue with my coding. Below is the code for Move Next
Sub MoveNext()
Dim SaveTime As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
'//Database Location
Const DB_LOCATION = "C:\Users\sagpatil\Documents\Sagar\Backup\Project\Freight Employee Database\FEDB.mdb"
'//Open Database
If db Is Nothing Then
Set db = DAO.Workspaces(0).OpenDatabase(DB_LOCATION)
End If
'//Open Table
If rs Is Nothing Then
strSQL = "SELECT * FROM mydb"
Set rs = db.OpenRecordset("mydb")
End If
If Not rs.EOF Then
rs.MoveNext
With rs
UserForm1.TXTECN.Value = rs.Fields(ECNNO) & vbNullString
UserForm1.TXTName.Value = rs.Fields(1) & vbNullString
UserForm1.TXTDOJ.Value = rs.Fields(2) & vbNullString
UserForm1.TXTReportingManager.Value = rs.Fields(3) & vbNullString
UserForm1.TXTDepartment.Value = rs.Fields(4) & vbNullString
UserForm1.TXTDOB.Value = rs.Fields(5) & vbNullString
UserForm1.TXTAddress.Value = rs.Fields(6) & vbNullString
UserForm1.TXTContactNumber.Value = rs.Fields(7) & vbNullString
UserForm1.TXTEmergency_Contact_Person.Value = rs.Fields(8) & vbNullString
UserForm1.TXTEmergency_Contact_Number.Value = rs.Fields(9) & vbNullString
UserForm1.TXTRelationship.Value = rs.Fields(10) & vbNullString
UserForm1.TXTGrade = rs.Fields(11) & vbNullString
UserForm1.ComboBox2.Value = rs.Fields(12) & vbNullString
UserForm1.TXTLan.Value = rs.Fields(13) & vbNullString
UserForm1.TXTEmail.Value = rs.Fields(14) & vbNullString
UserForm1.ComboBox3.Value = rs.Fields(15) & vbNullString
UserForm1.TXTDeskNumber.Value = rs.Fields(16) & vbNullString
UserForm1.TXTLockerNumber.Value = rs.Fields(17) & vbNullString
UserForm1.ComboBox5.Value = rs.Fields(18) & vbNullString
UserForm1.ComboBox6.Value = rs.Fields(19) & vbNullString
UserForm1.ComboBox7.Value = rs.Fields(20) & vbNullString
UserForm1.ComboBox8.Value = rs.Fields(21) & vbNullString
UserForm1.TXTPassportEXPDate.Value = rs.Fields(22) & vbNullString
UserForm1.TXTBillResource.Value = rs.Fields(23) & vbNullString
UserForm1.ComboBox1.Value = rs.Fields(24) & vbNullString
UserForm1.ComboBox4.Value = rs.Fields(25) & vbNullString
UserForm1.TXTResourceStat.Value = rs.Fields(26) & vbNullString
UserForm1.TXTInactiveComments.Value = rs.Fields(27) & vbNullString
End With
Else
rs.MoveFirst
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub
Sub MoveNext()
Dim SaveTime As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
'//Database Location
Const DB_LOCATION = "C:\Users\sagpatil\Documents\Sagar\Backup\Project\Freight Employee Database\FEDB.mdb"
'//Open Database
If db Is Nothing Then
Set db = DAO.Workspaces(0).OpenDatabase(DB_LOCATION)
End If
'//Open Table
If rs Is Nothing Then
strSQL = "SELECT * FROM mydb"
Set rs = db.OpenRecordset("mydb")
End If
If Not rs.EOF Then
rs.MoveNext
With rs
UserForm1.TXTECN.Value = rs.Fields(ECNNO) & vbNullString
UserForm1.TXTName.Value = rs.Fields(1) & vbNullString
UserForm1.TXTDOJ.Value = rs.Fields(2) & vbNullString
UserForm1.TXTReportingManager.Value = rs.Fields(3) & vbNullString
UserForm1.TXTDepartment.Value = rs.Fields(4) & vbNullString
UserForm1.TXTDOB.Value = rs.Fields(5) & vbNullString
UserForm1.TXTAddress.Value = rs.Fields(6) & vbNullString
UserForm1.TXTContactNumber.Value = rs.Fields(7) & vbNullString
UserForm1.TXTEmergency_Contact_Person.Value = rs.Fields(8) & vbNullString
UserForm1.TXTEmergency_Contact_Number.Value = rs.Fields(9) & vbNullString
UserForm1.TXTRelationship.Value = rs.Fields(10) & vbNullString
UserForm1.TXTGrade = rs.Fields(11) & vbNullString
UserForm1.ComboBox2.Value = rs.Fields(12) & vbNullString
UserForm1.TXTLan.Value = rs.Fields(13) & vbNullString
UserForm1.TXTEmail.Value = rs.Fields(14) & vbNullString
UserForm1.ComboBox3.Value = rs.Fields(15) & vbNullString
UserForm1.TXTDeskNumber.Value = rs.Fields(16) & vbNullString
UserForm1.TXTLockerNumber.Value = rs.Fields(17) & vbNullString
UserForm1.ComboBox5.Value = rs.Fields(18) & vbNullString
UserForm1.ComboBox6.Value = rs.Fields(19) & vbNullString
UserForm1.ComboBox7.Value = rs.Fields(20) & vbNullString
UserForm1.ComboBox8.Value = rs.Fields(21) & vbNullString
UserForm1.TXTPassportEXPDate.Value = rs.Fields(22) & vbNullString
UserForm1.TXTBillResource.Value = rs.Fields(23) & vbNullString
UserForm1.ComboBox1.Value = rs.Fields(24) & vbNullString
UserForm1.ComboBox4.Value = rs.Fields(25) & vbNullString
UserForm1.TXTResourceStat.Value = rs.Fields(26) & vbNullString
UserForm1.TXTInactiveComments.Value = rs.Fields(27) & vbNullString
End With
Else
rs.MoveFirst
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub