INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Modifing Table with Update Query

Modifing Table with Update Query

(OP)
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.

RE: Modifing Table with Update Query

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

CODE --> SQL

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;" 

Duane
Hook'D on Access
MS Access MVP

RE: Modifing Table with Update Query

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.

CODE --> ControlSource

=dLookup("Cal1","tblCOVER") 

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close