Update

[Max]

I have a field called A, when somone update the B field, I would like the A
field get updated.

Note:
A is text, B is Date, RequestID is Autonumber


I wrote the below code but nothing is happening when i update the Submite
Date field.


Private Sub B_AfterUpdate()

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb()

strSQL = "UPDATE Requests" _
& " SET Requests.A=" & "Right(,4) & '_' & [RequestID]" _
& " WHERE (((Requests.A) Is Null) AND ((Requests.) Is Not Null))"
db.Execute strSQL

End Sub

 

[tina]

each "section" of the SQL statement has to be enclosed in double quotes.
also, i'm guessing you're wanting to take the four-digit year from the date
field. try the following, as

strSQL = "UPDATE Requests" _
& " SET Requests.A='" & Right(,4) & "'_'" & [RequestID] _
& "' WHERE Requests.A Is Null AND Requests. Is Not Null", _
dbFailOnError

hth

 

[Max]

The code below is working, but have to go to next record and comeback for
the field to be updated:

Private Sub Submit_Date_Exit(Cancel As Integer)

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb()

strSQL = "UPDATE Requests" _
& " SET Requests.YearlyReqstID=" & "Right([Submit_Date],4) & '_' &
[RequestID]" _
& " WHERE (((Requests.Submit_Date) Is Not Null));"
db.Execute strSQL
End Sub

each "section" of the SQL statement has to be enclosed in double quotes.
also, i'm guessing you're wanting to take the four-digit year from the date
field. try the following, as

strSQL = "UPDATE Requests" _
& " SET Requests.A='" & Right(,4) & "'_'" & [RequestID] _
& "' WHERE Requests.A Is Null AND Requests. Is Not Null", _
dbFailOnError

hth

I have a field called A, when somone update the B field, I would like the A
field get updated.

Note:
A is text, B is Date, RequestID is Autonumber


I wrote the below code but nothing is happening when i update the Submite
Date field.


Private Sub B_AfterUpdate()

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb()

strSQL = "UPDATE Requests" _
& " SET Requests.A=" & "Right(,4) & '_' & [RequestID]" _
& " WHERE (((Requests.A) Is Null) AND ((Requests.) Is Not Null))"
db.Execute strSQL

End Sub

 

[tina]

you're running this code in a form, right? if YearlyReqstID and Submit_Date
are in the same table, and the form is bound to that table, why are you
running an Update query to update the YearlyReqstID field? can't you just
set the value of YearlyReqstID directly? or are you updating that field in
ALL the records in the table at one time?

hth

The code below is working, but have to go to next record and comeback for
the field to be updated:

Private Sub Submit_Date_Exit(Cancel As Integer)

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb()

strSQL = "UPDATE Requests" _
& " SET Requests.YearlyReqstID=" & "Right([Submit_Date],4) & '_' &
[RequestID]" _
& " WHERE (((Requests.Submit_Date) Is Not Null));"
db.Execute strSQL
End Sub

each "section" of the SQL statement has to be enclosed in double quotes.
also, i'm guessing you're wanting to take the four-digit year from the date
field. try the following, as

strSQL = "UPDATE Requests" _
& " SET Requests.A='" & Right(,4) & "'_'" & [RequestID] _
& "' WHERE Requests.A Is Null AND Requests. Is Not Null", _
dbFailOnError

hth

I have a field called A, when somone update the B field, I would like

the
A

field get updated.

Note:
A is text, B is Date, RequestID is Autonumber


I wrote the below code but nothing is happening when i update the Submite
Date field.


Private Sub B_AfterUpdate()

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb()

strSQL = "UPDATE Requests" _
& " SET Requests.A=" & "Right(,4) & '_' & [RequestID]" _
& " WHERE (((Requests.A) Is Null) AND ((Requests.) Is Not Null))"
db.Execute strSQL

End Sub