Update Field

[SeRene]

Hi, I need to update one of the fields, reportdate in 2
tables.
An input box will be provided. User will enter the date
and that particular date must be updated to ALL records in
the two tables. However, there are times whereby one of
the tables will not have any records therefore, the codes
below arent able to update the other table which have
records. How can i edit the codes below to allow the
system to update the table with records even if another
table doesnt have any records?

Thanks!!!
Need help badly on thisssss


Private Sub cmdReportDate__Click()

Dim RptDate As Date
RptDate = InputBox("Enter Report Date:")

Dim db As Database

Dim rs1 As Recordset, rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl1")
Set rs2 = db.OpenRecordset("tbl2")

If rs1.RecordCount <> 0 Then
rs1.MoveFirst
Do
With rs1
.Edit
.Fields("reportdate") = RptDate
.Update
End With
rs1.MoveNext
Loop Until rs1.EOF = True
rs1.MoveFirst
End If


If rs2.RecordCount <> 0 Then
rs2.MoveFirst
Do
With rs2
.Edit
.Fields("reportdate") = RptDate
.Update
End With
rs2.MoveNext
Loop Until rs2.EOF = True
rs2.MoveFirst
End If

 

[Pieter Wijnen]

Dim Db As DAO.Database
Dim Qdef AS DAO.QueryDef
Dim RptDate As Date
Dim strSQL As String

strSQL = "PARAMETERS RptDate DateTime;" & vbCrLf

RptDate = InputBox("Enter Report Date:")

Set Db = CurrentDB()
Set Qdef = Db.CreateQueryDef("")
Qdef.SQL = strSQL & "UPDATE TBL1 SET REPORTDATE=RptDate"
Qdef.Parameters(0) = RptDate
Qdef.Execute

Qdef.SQL = strSQL & "UPDATE TBL2 SET REPORTDATE=RptDate"
Qdef.Parameters(0) = RptDate
Qdef.Execute

Much Quicker too..

HTH
Pieter

 

[SeRene]

Awesome! It works perfectly!
Thank You so Much!!!

Dim Db As DAO.Database
Dim Qdef AS DAO.QueryDef
Dim RptDate As Date
Dim strSQL As String

strSQL = "PARAMETERS RptDate DateTime;" & vbCrLf

RptDate = InputBox("Enter Report Date:")

Set Db = CurrentDB()
Set Qdef = Db.CreateQueryDef("")
Qdef.SQL = strSQL & "UPDATE TBL1 SET REPORTDATE=RptDate"
Qdef.Parameters(0) = RptDate
Qdef.Execute

Qdef.SQL = strSQL & "UPDATE TBL2 SET REPORTDATE=RptDate"
Qdef.Parameters(0) = RptDate
Qdef.Execute

Much Quicker too..

HTH
Pieter




.