Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    Unanswered: sql syntax in update for docmd

    I am having trouble with the syntax for an update command in sql from a docmd.runsql to whit:

    DoCmd.RunSQL "update Logontable set LogonTable.password = " & appword & " where (([logontable].[username]) = [forms]![logonform]!usernamebox)"

    I have the following:
    table: LogonTable
    fields: password, username (plus others)

    I have set up an inputbox to gain a new password from the user. This variable is 'appword', the inputbox is:

    appword = InputBox(prompt:=strmsg, Title:="Change Password", XPOS:=2500, YPOS:=2000)


    Now when I run the section, it accepts the inputbox, and sets the appword to the input given BUT when I attempt to update the logontable I get asked for a parameter input with the appword input as a prompt for the message.

    I have tried variations on spaces, linebreaks, !s and [] in attempting to find the correct syntax.

    The variable 'appword' is a string ( I have also tried variant) and the password field in the table is text.

    What am I missing?

    Cheers

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: sql syntax in update for docmd

    Originally posted by rodmead
    I am having trouble with the syntax for an update command in sql from a docmd.runsql to whit:

    DoCmd.RunSQL "update Logontable set LogonTable.password = " & appword & " where (([logontable].[username]) = [forms]![logonform]!usernamebox)"

    I have the following:
    table: LogonTable
    fields: password, username (plus others)

    I have set up an inputbox to gain a new password from the user. This variable is 'appword', the inputbox is:

    appword = InputBox(prompt:=strmsg, Title:="Change Password", XPOS:=2500, YPOS:=2000)


    Now when I run the section, it accepts the inputbox, and sets the appword to the input given BUT when I attempt to update the logontable I get asked for a parameter input with the appword input as a prompt for the message.

    I have tried variations on spaces, linebreaks, !s and [] in attempting to find the correct syntax.

    The variable 'appword' is a string ( I have also tried variant) and the password field in the table is text.

    What am I missing?

    Cheers

    Try

    DoCmd.RunSQL "update Logontable set LogonTable.password = '" & appword & "' where (([logontable].[username]) = '" & [forms]![logonform]!usernamebox) & "'"

    Gregg

  3. #3
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53
    Gregg
    no go. I met with a compile error on the last parenth. I have used a similar (same) statement elsewhere:
    DoCmd.RunSQL "UPDATE Logontable SET LogonTable.logonattempts = " & resetattempt & " WHERE (([logontable].[username])=[forms]![LogonForm]!UserNamebox)"

    where the field logon attempts is integer and the resetattempts varable is an integer - this one works so I thought I'd follow the same format BUT...... well here we are.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by rodmead
    Gregg
    no go. I met with a compile error on the last parenth. I have used a similar (same) statement elsewhere:
    DoCmd.RunSQL "UPDATE Logontable SET LogonTable.logonattempts = " & resetattempt & " WHERE (([logontable].[username])=[forms]![LogonForm]!UserNamebox)"

    where the field logon attempts is integer and the resetattempts varable is an integer - this one works so I thought I'd follow the same format BUT...... well here we are.
    Strings are going to have to be surrounded by quotes so compariing to the same query syntax with integer values won't be the same. Are either of these values integer. I was assuming strings (text).

    Have you tried to create the same query in the query design grid and check the syntax there? Sometimes it helps.

    Also, I like to use message boxes to display the SQL string that I am going to use when I have problems. It allows me to see missing spaces, quotes etc.

    Gregg

  5. #5
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    Thumbs up

    Many thanks for pointing me in the right direction.

    After a bit of a system crash on the weekend I've managed to resurrect it and used the following:

    DoCmd.RunSQL "UPDATE LOGONTABLE SET LOGONTABLE.PASSWORD ='" & appword & "'" & " WHERE (([LOGONTABLE].[USERNAME])= [FORMS]![LOGONFORM]!USERNAMEBOX)"

    the code takes the variable appword from an inputbox and inserts the new password given as appword into the correct record where the username in the table is the same as that given by the user into the usernamebox on the logonform(form)

    Those "'" are tricky little blighters aren't they.

    Once again, many thanks.

Posting Permissions

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