Modifing Table with Update Query

[RonMcIntire]

All:

My problem seems simple but it is in two parts.

PART #1
I have a data entry form with an underlying table called tblCOVER
This table has 1 record with two fields: "Year", "Cal1"
With an UPDATE query in an After Update Event, I update tblCOVER.Cal1 from a table of 100 values with fields "CalYear", "Cal41", in the table named tblCALENDAR. Data is picked up for a corresponding year between the two tables.
Both tables are keyed on the Year.

I thought my SQL worked but while the Cal1 value changes sometimes, most of the time it does not.

Here is my SQL code:
CalSQL = "UPDATE tblCalendar "
CalSQL = CalSQL & "INNER JOIN [tblCOVER] ON tblCalendar.CalYear = [tblCOVER].Year "
CalSQL = CalSQL & "SET [tblCOVER].Cal1 = [tblCalendar].[Cal41], "
CalSQL = CalSQL & "WHERE ((([tblCOVER].YearID)=1));"

DoCmd.RunSQL CalSQL

What am I doing wrong? Is anything obvious?

PART #2
Once tblCOVER is changed, I want to display the Cal1 value on my form by calling Cal1, but it displays the wrong information. I can't even initialize Cal1 with a null.

As I see it, when the Year field on the form is changed, the After Update event is triggered and the SQL code updates tblCOVER. Could it be that the updating of Cal1 on the form occurs after the form is updated, thus the change does not occur? Is an On Current event appropriate here?

My attempts at displaying Cal1 from the Expression Builder, "= [tblCOVER]![Cal1]", end in "#Name?"

QUESTION
What is the best way to display Cal1 on my form?

Help would be appreciated.

 

[dhookom]

First part of you question, get rid of the extra comma:

CalSQL = "UPDATE tblCalendar "
 CalSQL = CalSQL & "INNER JOIN [tblCOVER] ON tblCalendar.CalYear = [tblCOVER].Year "
 CalSQL = CalSQL & "SET [tblCOVER].Cal1 = [tblCalendar].[Cal41][highlight #FCE94F], [/highlight] "
 CalSQL = CalSQL & "WHERE [tblCOVER].YearID=1;"

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Second part of question: you can't just use a table and field name as a control source. Try this expression which could return any Cal1 value from the table. If you want to reference a specific record, you will need to add a WHERE argument.

=dLookup("Cal1","tblCOVER")

Duane
Hook'D on Access
MS Access MVP