SQL "Where" statement.

[mjohnson629]

I have a database that I am working on with a group, and we are having a hard time with some of our VBA code. We have set up a form that is used to update a certain field in a table. In the code we have setup the variables and they are all containing the correct information however in the SQL statement the where statement is not working.

We set it up and it should udate the field that in the row where the UserID's match.

"Where UserID = Competitor.UserID"

When it is in the table like this when we run the update statement it updates every row in the table, but when we have it like this:

"WHERE UserID.Value = Competitor.UserID.Value"

Access asks us which UserID we want to update and we have to put it in manually. Is there a way to fix this so that access will match up the UserID's and update the desired field without us having to enter the ID manually.

Thanks...

 

[supercharge]

WHERE [sqlTable's Name].TableFieldName = Me.UserID

or

the safe way:

WHERE [sqlTable's Name].[TableFieldName] = Me.UserID.value


Post back of its status.

 

[mjohnson629]

OK i tried that but it is still doing the same thing. I'll just post the entire module here. Thanks again for the help. Let me know what needs to be changed.

Private Sub Week1_AfterUpdate()

' Declares variables
Dim UserID As String
Dim Week1 As String
Dim strWeightWeek1SQL As String
Dim IntCount As Integer

IntCount = 1
Do
If Form_ContestUpdater.List31.Selected(IntCount) Then

Week1 = Form_ContestUpdater.Week1.Value

UserID = Form_ContestUpdater.List31.Column(0, IntCount)
strWeightWeek1SQL = "UPDATE Competitor " & _
"SET Competitor.Week1 = Week1.value " & _
"WHERE [Competitor].[UserID] = UserID "

DoCmd.RunSQL (strWeightWeek1SQL)
IntCount = Form_ContestUpdater.List31.ListCount

Else
IntCount = IntCount + 1

End If

Loop While IntCount <> Form_ContestUpdater.List31.ListCount

End Sub

 

[EMP]

If UserID is a numeric field:
"WHERE [Competitor].[UserID] = " & UserID


If UserID is a text field:
"WHERE [Competitor].[UserID] = '" & UserID & "'"

^