Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Unanswered: Can't figure out update queries

    Below is what I put on the Access newsgroup but it didn't get a reply. So, I thought I would try here. I'm sure I'm missing something simple but I have never used an update query before. Please be gentle.

    Hello,
    I am new to Access and I'm creating a database that calculate a rating
    each week from football games played. The ratings are stored in a table
    called Ratings, the School information is located in the Schools table and
    the Schedule/Results are in a table called Schedules. Everything is tied
    together by a SchoolID number. The ratings are calculated by a custom
    function I created that requires the Schools previous rating, Opponents
    previous rating, School Score, Opponent Score, and week played. I have that
    function working properly. What I am having an issue with is the update of
    the table based on the results. Below is my VBA to try to do this based on
    some criteria. Right now I'm just trying to get it to update the table
    correctly. The Ratings table has fields called InitialRating, Week1, Week2,
    etc. Basically what I want to do is to check to see which week it is by
    using a custom function called WeekNumber(DateGamePlayed) which works
    correctly. Then check to see if the they played a game or not. Then set a
    variable to the correct value. After I determining the correct value for
    each week then I run the Update Query. After I update the table I want to
    view the table so I run a predefined query in read only view to view the
    ratings each week.

    Here is the code. Following each problem area is some comments of the
    problems I'm having in parenthesis.

    Private Sub ViewRatings_Click()
    Dim Week, Week1, Week2, Week3 As Integer

    Week = WeekNumber(Schedules.Date) (Problem: it says a compile error:
    variable(Schedules) not defined)

    If Week = 1 Then
    If Schools_1.School = "BYE" Then (I set Week to equal 1 and then this
    Problem: it says a compile error: variable(Schools_1) not defined)
    Week1 = Ratings.InitialRating
    Else
    Week1 = 11
    End If
    ElseIf Week = 2 Then
    If Schools_1.School = "BYE" Then
    Week2 = Ratings.Week1
    Else
    Week2 = 22
    End If
    Else
    Week3 = 3
    End If

    DoCmd.RunSQL "UPDATE(Ratings INNER JOIN Schools ON Ratings.SchoolID =
    Schools.SchoolID) " & _
    "INNER JOIN (Ratings AS Ratings_1 INNER JOIN (Schools AS Schools_1 " & _
    "INNER JOIN Schedules ON Schools_1.SchoolID = Schedules.OpponentID) " &
    _
    "ON Ratings_1.SchoolID = Schools_1.SchoolID) ON Schools.SchoolID =
    Schedules.SchoolID " & _
    "SET Ratings.Week1 = Week1, Ratings.Week2 = Week2, Ratings.Week3 =
    Week3;"

    DoCmd.OpenQuery "qryViewRating", acViewNormal, acReadOnly
    End Sub

    If I set the Week = 1, like in the second issue, and then taking out the
    check to see if game was a BYE or not I get this Run-time error '3079'
    Specified field 'Week1' could refer to more than one table listed in the
    FROM clause of your SQL statement. This is funny because it doesn't have a
    FROM clause in it. Schools_1.School is set up in the relationship to
    identify the opponent from the Schedules table.

    I hope this isn't too much info but I didn't want to give too little and not
    make any sense. It probably doesn't make sense anyway but I thought I would
    try these newsgroups. I'm sure I'm not doing something that I need to do
    but the UPDATE query works if I manually put in values for the SET section.
    I will be signing out until the morning so no reply until then.

    Thanks for anyone who will be able to help me.

    Chuck

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Bits at a time....

    First off:
    Week = WeekNumber(Schedules.Date) (Problem: it says a compile error: variable(Schedules) not defined)
    I assume Schedules as in Schedules.Date, is the name of your Form and therefore I have to assume you are calling this code from that very same Form. If so then use this format:

    Week = WeekNumber(Me.Date)

    If this code is called from any other Form module other than the Schedules Form then use this method (the Schedules Form MUST be Open):

    Week = WeekNumber(Forms![Schedules].Date)

    The VBA compiler doesn't know what Schedules is so you must be more definative. You need to tell it what Schedules stands for, a variable, an object, a Constant, whatever...

    Still get the error?

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    While I continued to look at your code....

    If Week = 1 Then
    If Schools_1.School = "BYE" Then (I set Week to equal 1 and then this
    Problem: it says a compile error: variable(Schools_1) not defined)
    Week1 = Ratings.InitialRating
    Else
    I get the feeling that you are trying to access data directly from your tables. If so..you need to be specific about what record(s) you want to deal with and should perhaps really be doing all this through a RecordSet based on those records.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Jan 2006
    Posts
    11
    First of all, Thanks for the reply.

    You are correct. All of these are referring to the tables themselves and not forms. What I want to do is for the user to click on the button and it updates the table with the latest data and then opens a query to view the results. I'm not sure what you mean by a recordset. Please explain.

    Thanks Again,
    Chuck

  5. #5
    Join Date
    Jan 2006
    Posts
    11
    Still haven't figured this out. Any more help would be appreciated!

    Thanks,
    Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •