Update 2

[Ragah21]

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

 

[Zion7]

Try This,

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

 

[TheAceMan1]

How are ya Ragah21 . . .

If [blue]B[/blue] is a date then it appears your attempting to extract a 4 digit year ([purple]I could be wrong[/purple]).

If I'm correct and [blue]B[/blue] is formatted for [blue]4 digit year[/blue], try this:

[blue]strSQL = "UPDATE Requests" & _
         " SET Requests.A=" & "[purple][b][i]Year([/i][/b][/purple][B][purple][b][i])[/i][/b][/purple] & '_' & [RequestID]" & _
       & " WHERE ((Requests.[A] Is Null) AND " & _
                  (Requests.[B] Is Not Null));"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Zion7]

Good point Ace but, I'm really getting tossed on the
syntax, eg. mine's way out of whack!

But, are you sure about yours? Ragah said A was Text.
I'm assuming we need qualifiers, and since most
VBA funcions are legal in SQL statements, no need for concatenation, thus...


strSQL = "UPDATE Requests" & _
" SET Requests.A='Year() & '_' & [RequestID]'" & _
& " WHERE ((Requests.[A] Is Null) AND " & _
(Requests. Is Not Null));"

But, I'm really not convinced either way???

(...You may very well be right on?)

 

[TheAceMan1]

Howdy Zion7 . . .

[blue]I'm really getting tossed on the
syntax, eg. mine's way out of whack! . . .[/blue]

No! . . . not at all [blue]Zion7[/blue].

Bear in mind that [blue][/blue] is a date!

I just believe the ambiguity lies here! . . . despite our code!

I did say . . . [blue]I could be wrong! . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

So [blue]what happen to Ragah21?[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Zion7]

well, I finally got off my lazy b*** and tried it.

strSQL = "UPDATE Requests" & _
" SET Requests.A=Year() & '_' & [RequestID]" & _
& " WHERE ((Requests.[A] Is Null) AND " & _
(Requests. Is Not Null));"


So yes Aceman, I believe you're correct on both accounts.

 

[Ragah21]

Thanks all, this what it seems to be working:

But I have to go to next record and comeback for the feild to get 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=" & "Year([Submit_Date]) & '_' & [RequestID]" _
& " WHERE (((Requests.Submit_Date) Is Not Null));"
db.Execute strSQL
End Sub

 

[TheAceMan1]

Ragah21 . . .

We've only been dealing with getting your SQL proper.

[ol][li]Are you attempting to update A in the current record?[/li]
[li]According to your latest where clause . . .

[blue]   WHERE (((Requests.Submit_Date) Is Not Null))[/blue]

. . . your going to update all previously saved records where Submit_Date Is Not Null. Is this truly the results your looking for?[/li][/ol]

In the interim add the line [blue]Me.YearlyReqstID.Requery[/blue] after [blue]db.Execute strSQL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Ragah21]

It should update as I exite the field on current record!

When I added Me.YearlyReqstID.Requery it gave me a run time error on Requery.....

 

[Zion7]

Ragah, Ace & I are probably unsure where & what recordsource
you're updating (parent, child, control?).

So, if main form, Me.recordsource.Requery
or Me.refresh or Me.Recalc maybe Me.Repaint
If subform Me.sfrmName.Form.Requery

it appears you tried to requery a control.
I think only listboxes & comboboxes are requeriable?
(is that a word?).

 

[Ragah21]

Thanks Zion, TheAceMan.

Both of you have been a great help.

Final Code:
Private Sub Submit_Date_Exit(Cancel As Integer)

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

If IsNull(Me.[Submit_Date]) Then
Me.YearlyReqstID = ""
End If

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

Me.Refresh
db.Execute strSQL

End Sub