Problem trying to do an update query in VBA

[Chuck Arbogast]

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

 

[guido via AccessMonster.com]

I'll start with your first problem.
Is "Schedule.Date" your table.field? If so, VBA will not recognize that.
(side note: if you name a field "date" in Access, always refer to it as [Date]
in VBA because date is a reserved word and access won't know what you are
referring to). To get a field value in Access VBA do the following:

Dim rst as DAO.Recordset

Set rst = CurrentDB.OpenRecordset("Schedule")
rst.MoveFirst
Week = WeekNumber(rst![Date])
rst.Close

 

[Chuck Arbogast]

Thanks for the help!
I'm sure that is my problem. Based on your answer would I have to do the
same for each table I want to see in my code?
For example, would it be :
Dim rstSchedule, rstSchools As DAO.Recordset

Set rstSchedule = CurrentDB.OpenRecordset("Schedule")
Set rstSchool = CurrentDB.OpenRecordset("School")
rstSchedule.MoveFirst
rstSchool.MoveFirst
Then do what I need to with each field for both tables
Then Close the recordset

Please let me know if that is correct. I won't be able to look at my
problem today but hope to work on it tomorrow.

Thanks again,
Chuck