Change a Particular Recordset

[Hashiru]

Hello all

I have a set of code in Excel module and I want the code to transfer data from Excel 2010 to Access 2010 for a particular record. However, my code is only changing record 1. Here is what I have so far.


Public Sub Transfer()
Dim SampleNBr As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rc As Integer
Dim WB As Workbook
Dim WS As Worksheet
Dim fd As FileDialog
Dim xfilepath As Variant
Dim NbrOfSamples As Variant
Dim RecordNbr As Integer


NbrOfSamples = InputBox("Enter the Number of Samples")

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Browse for the Datasource

With fd

.Title = "Browse for the Database"
If .Show = True Then
' Assign the file to a variable xfilepath.
xfilepath = fd.SelectedItems.Item(1)

Else
MsgBox "You clicked Canncel in the file dialog box.", , "You have canceled the transfer process"
Exit Sub
End If
End With



'//Open Database
If db Is Nothing Then
Set db = DAO.Workspaces(0).OpenDatabase(xfilepath)
End If


'//Open Table
If rs Is Nothing Then
Set rs = db.OpenRecordset("_wrkshts", dbOpenDynaset)
End If

'//Create a new record
Set WB = Workbooks("REVIEW.xlsm")
Set WS = WB.Worksheets("0R")

rs.MoveFirst
SampleNBr = WS.Cells(6, 2)
'NbrOfSamples = rs.recordCount
For rc = 1 To NbrOfSamples

' If rs!_ID = SampleNBr Then Chang this line of code after code can determine number samples
If rc = SampleNBr Then

With rs
.Edit
![date_endorsement] = WS.Cells(7, 3)
![date_endorsement_correct] = WS.Cells(7, 4)
![date_form_prepared] = WS.Cells(8, 3)
![date_form_prepared_correct] = WS.Cells(8, 4)
![duedate_last_cmpl_correct] = WS.Cells(9, 4)
.Update
End With
'If rs!_ID = SampleNBr Then Replace this line after a code can determent the sample number without input box values
If rc = SampleNBr Then
Exit Sub
End If
End If
Next rc


'End With

'//Make sure we have closed the database


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


End Sub

Requirement:
(1) I what rs!_ID to point out to the correct record number (recordset)
(2) Use Access to determine the number of record rather than using Inputbox


Can anyone help me please.

 

[Hashiru]

I have solved the second problem, but still canno change the data in a particular recordset.

Thanks.

 

[Hashiru]

I am now a able to solve the first question

With rs
If .recordCount <> 0 Then 'Ensure that there are actually records to work with
'The next 2 line will determine the number of returned records
rs.MoveLast 'This is required otherwise you may not get the right count
iCount = rs.recordCount 'Determine the number of returned records

Do While Not .BOF
If rs!ID = SampleNBr Then
'Do something with the recordset/Your Code Goes Here
.Edit
!Date= WS.Cells(4, 1)
.Update
.MovePrevious
Loop
End If
End With