Change a Particular Recordset

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I have solved the second problem, but still canno change the data in a particular recordset.

Thanks.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top