Adding an Effective Date to All Records (1 Viewer)

[milkman2500]

Hi,

I'm trying to run a saved import through VBA, add a new column called "Effective_Date", then update this field for all records to the effective date indicated by the user.

It currently works for all records except 1 is always ommitted. Any help is appreciated.

Private Sub cmd_upload_staffing_report_Click()
DoCmd.RunSavedImportExport ("Import-AccessUploadStaffingReport")
 
Dim strField As String
Dim curDatabase As Object
Dim tblTempEmployees As Object
Dim fldEffectiveDate As Object
Dim inputResponse As Date
Dim rstEmployees As Recordset
 
'Creates new column "Effective_Date" in table t_temp_employees
Set curDatabase = CurrentDb
Set tblTempEmployees = curDatabase.TableDefs("t_temp_employees")
strField = "Effective_Date"
Set fldEffectiveDate = tblTempEmployees.CreateField(strField, dbDate)
tblTempEmployees.Fields.Append fldEffectiveDate
 
'Updates all records in the new "Effective_Date" field to the effective date
'indicated by the user through the input box
Set rstEmployees = curDatabase.OpenRecordset("t_temp_employees")
inputResponse = InputBox("What is the effective date of the report?")
With rstEmployees
Do While Not .EOF
    .Edit
    !Effective_Date = inputResponse
    .Update
    .MoveNext
    Loop
    .Close
End With
 
End Sub

 

[Rx_]

I will bite.... Which 1 doesn't update? e.g. the first, last, one in between? Can you give an example with 5 records?

With rstEmployees
if Not .EOF and .BOF then ' in case of empty recordset
 .MoveFirst
   Do While Not .EOF
    .Edit
    !Effective_Date = inputResponse
    .Update
    .MoveNext
       debug.print rstEmployees.recourdcount   
    Loop
   .Close
End If 
End With