Can'tupdate second table in VBA SQL routine

[SteveJtoo]

Hi, Hope someone can help me. I am trying to update a 2nd table 'WhatsNext" after entering a date in a field "ReadDate" (mmm-yy format), in table "Books".
There is a slight variation as I must reformat the date for another filed on the form "MyDate". I can see the date go into in "MyDate" upon exiting the field "ReadDate" but when I try to Update the table/field "WhatsNext.MyDate" from the field "Me.MyDate" on the form, I get a pop up box asking me to enter the parameter value for 'MyDate" Here is my code

Private Sub ReadDate_Exit(Cancel As Integer)


' ***THESE NEXT TWO ROUTINES ARE STRICTLY FOR USE IN THE Read by Month Report******
If IsNull(Me.ReadDate) Then 'If ReadDate is Null,
Me.RightDate = ""           'Set RightDate Null or opening form will error out.
Else                'compute the value if ReadDate and put here in date format
Me.RightDate = DateValue("1" & "-" & Left([ReadDate], 3) & "-" & "20" & Right([ReadDate], 2))
End If

If IsNull(Me.RightDate.Value) Then ' If RightDate is empty
Me.Mydate = ""                     ' Set MyDate to Null
Else                               ' Set MyDate Field to the value of RightDate
Me.Mydate = Me.RightDate.Value     ' This was the only way I could get a date for
End If

     DoCmd.SetWarnings False
     
     DoCmd.RunSQL "UPDATE WhatsNext  " & _
    "SET WhatsNext.Mydate = Me.Mydate " & _
    "WHERE WhatsNext.AuthorID = Books.AuthorId"
    
    DoCmd.SetWarnings True

End Sub

Sorry for the long explanation. Thanks.

 

[pbaldy]

You have to concatenate the form reference into the string. Is MyDate a date field, or text?

 

[SteveJtoo]

MyDate is a Date field. They all are ReadDate Etc. Maybe different formats.

 

[pbaldy]

For a date field try:

"SET WhatsNext.Mydate = #" & Me.Mydate & "# " & _

This may also help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[SteveJtoo]

That code produces this error: Syntax error in date in query expression '#'.

By the way thanks for your help on this. I'm really stuck.

 

[SteveJtoo]

Oh, I also tried "SET WhatsNext.Mydate =" & " #" & Me.Mydate & "# " & _
and also "SET WhatsNext.Mydate =" " & " #" & Me.Mydate & "# " & _
both produced syntax errors.

 

[pbaldy]

Did you try the debugging technique in that link? If so, what's the SQL?

 

[SteveJtoo]

I didn't understand how to do it. Anyway, I got it to go through whit this code;

DoCmd.RunSQL "UPDATE WhatsNext " & _
"SET WhatsNext.Mydate = Mydate " & _
"WHERE WhatsNext.AuthorID = AuthorId"

BUT the message says "You are about to update 88 rows". Why it says it will update 88 instead of 1 ?? I said yes and it went through but didn't update anything. I did use MsgBox to verify that the fields did have values. Now what?

 

[pbaldy]

Are there 88 records in the table? Your WHERE clause doesn't look right. Can you post the db here?

 

[SteveJtoo]

Ok. I think I uploaded it.

 

[pbaldy]

Ok. I think I uploaded it.

Strike one!

 

[SteveJtoo]

Try Again.

 

[pbaldy]

Like I said, you need to concatenate the date variable and use the delimiter (you're setting the date to itself). Also you need to concatenate the criteria. This works:

  DoCmd.RunSQL "UPDATE WhatsNext " & _
               "SET WhatsNext.Mydate = #" & Mydate & "#" & _
               "WHERE WhatsNext.AuthorID=" & [AuthorID]

 

[SteveJtoo]

It works!!!! Thank you soooo much.

I only do this for myself and obviously I'm not very good. I just started on Access 2010 from 2000. Not even sure what your last message means but now I do. Thanks again.

 

[pbaldy]

Happy to help!