quick question

[swarv]

Can anybody see what is wrong with the below code:

SQLText1 = "UPDATE tbl_users ([new2008hols]) FROM tblusers SELECT (0) WHERE (tbl_users.user_id=" & Me.Text18 & ")"


Thanks

Martin

 

[MSAccessRookie]

Can anybody see what is wrong with the below code:

SQLText1 = "UPDATE tbl_users ([new2008hols]) FROM tblusers SELECT (0) WHERE (tbl_users.user_id='" & Me.Text18 & "')"


Thanks

Martin

If Me.Text18 is a String, try surrounding it with quotes ['] (See example above)

 

[swarv]

tried that, it returns the value of text18 fine. it just doesn't place the 0 on that record in the new2008hols column.

 

[ByteMyzer]

Hello, swarv,

The structure is incorrect for an UPDATE statement. This should be:

SQLText1 = "UPDATE tbl_users" _
    & " SET tbl_users.new2008hols = 0" _
    & " WHERE tbl_users.user_id = " & Me.Text18 & ")"

...unless user_id is a Text field, in which case, the code should be:

SQLText1 = "UPDATE tbl_users" _
    & " SET tbl_users.new2008hols = 0" _
    & " WHERE tbl_users.user_id = [b]'[/b]" & Me.Text18 & "[b]'[/b])"

 

[swarv]

I tried both of them and still no luck, where could the errror be? its not saying there is an error.
cheers

 

[ByteMyzer]

You only asked what was wrong with the code, and I told you. It is possible that there is no entry in table tbl_users where the value field user_id matches the value in form field Text18, and that could be why no update is taking place.

 

[swarv]

ok cheers, I have checked it and the word Jo is in both places. I will look into it further or try to do it on another field
cheers

 

[ByteMyzer]

Are you actually executing the SQL statement that you set in variable SQLText1? Something like the following:

SQLText1 = "UPDATE tbl_users" _
    & " SET tbl_users.new2008hols = 0" _
    & " WHERE tbl_users.user_id = '" & Me.Text18 & "')"
[b]CurrentDb.Execute SQLText1[/b]

 

[MSAccessRookie]

Hello, swarv,

The structure is incorrect for an UPDATE statement. This should be:

SQLText1 = "UPDATE tbl_users" _
    & " SET tbl_users.new2008hols = 0" _
    & " WHERE tbl_users.user_id = " & Me.Text18 & "[SIZE=4][COLOR=red][B])[/B][/COLOR][/SIZE]"

...unless user_id is a Text field, in which case, the code should be:

SQLText1 = "UPDATE tbl_users" _
    & " SET tbl_users.new2008hols = 0" _
    & " WHERE tbl_users.user_id = [B]'[/B]" & Me.Text18 & "[B]'[SIZE=4][COLOR=red])[/COLOR][/SIZE][/B]"

Bytemyzer is correct about the format being in error (I guess I could not see the Forest through the Trees), but I think that there may be an extra ")" in the code. I do not see an opening "(". Remove the ")" and try again, because a Syntax Error can cause an SQL Statement to fail and not report the failure to the application.

 

[ByteMyzer]

Whoops, you're right. I removed the opening "(", but forgot to remove the closing ")".

swarv should definitely have received an error if he had actually executed the SQL statement from my code, which indicates that he did NOT, in fact, include an Execute statement in his code.

 

[swarv]

cheers guys, great help, now works, just got to do the rest of it now, cheers again