Results 1 to 8 of 8
  1. #1
    edson is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Oct 2014
    Posts
    57

    UPDATE, SET field AND field WHERE AND WHERE

    Hello you all. I was wondering what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.

    Here is my challenge:
    My TableI has the columns A, B, C and D which are populated, for example, as follows:
    TableI
    A B C D
    1 2


    2 6 4 3
    1 7 5 9
    1 2
    2 5 8 5
    etc.

    I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.
    So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.
    For instance, assuming C=& and D=%, the desired result should be as follows:
    TableI
    A B C D
    1 2 & %
    2 6 4 3
    1 7 5 9
    1 2 & %
    2 5 8 5
    I did not find any examples in the net including multiple criteria so I hope you can help me on that one. Here is what I wrote unsuccesfully:

    Private Sub BtSalvarFrmII_Click()
    CurrentDb.execute "UPDATE TableI"
    Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2
    Docmd.save
    Docmd.close
    End Sub

    What would be the correct syntax? Thank you.
    Edson

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is data duplicated between tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    edson is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Oct 2014
    Posts
    57
    Quote Originally Posted by June7 View Post
    What is data duplicated between tables?
    Oooops! My mistake. Sorry about that. Just fixed and refrased my example. Please, keep in mind I used object and field nicknames to avoid language barrier.
    -----------------------------------------
    Hello you all. I was wondering what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.

    Here is my challenge:
    My TableI has the columns A, B, C and D which are populated, for example, as follows:
    TableI
    A B C D
    1 2
    2 6 4 3
    1 7 5 9
    1 2
    2 5 8 5
    etc.

    I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields E and F. When saving data entry thru the save button of the FormII, fields E and F will be naturally saved on the TableII. Well, I also want E and F values updated into Table I as well, but only when field A=1 and B=2.
    So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it (with the value of the field E into FieldC and the value of the FieldF into FieldD) in every record WHERE A=1 AND B=2.
    For instance, assuming E=& and F=%, the desired result should be as follows:
    TableI
    A B C D
    1 2 & %
    2 6 4 3
    1 7 5 9
    1 2 & %
    2 5 8 5
    I did not find any examples in the net including multiple criteria so I hope you can help me on that one. Here is what I wrote unsuccesfully:

    Private Sub BtSalvarFrmII_Click()
    CurrentDb.execute "UPDATE TableI"
    Set FieldC = Forms!FrmII!FieldE.value AND Set FieldD = Forms!FrmII!FieldF.value WHERE FieldA = 1 AND FieldB = 2
    Docmd.save
    Docmd.close
    End Sub

    What would be the correct syntax? Thank you.
    Edson

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I meant to ask: WHY is data duplicated between tables?

    The syntax would be like:

    CurrentDB.Execute "UPDATE Table 1 Set FieldC=" & Forms!FrmII!FieldE & ", FieldD=" & Forms!FrmII!FieldF & " WHERE FieldA = 1 AND FieldB = 2"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    edson is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Oct 2014
    Posts
    57
    Thank you June 7. I have adapted the code to the actual field names, however, even though it goes thru the compilation and it assumes correct values, I get the error message 'Run Time Error 3061. Too Few parameters. Expected 2'.

    Here is the code:

    CurrentDb.execute "UPDATE TblCadastro Set NFISCAL=" & Forms!FrmNFLancNoCadastro!NFNUM & ", NFISCALDT=" & Forms!FrmNFLancNoCadastro!NFDT & ", NFVALOR=" & Forms!FrmNFLancNoCadastro!NFVLR & " WHERE LICNUM=
    Forms!FrmNFLancNoCadastro!NFCONTRAPARTIDA AND LICEMPNUM = Forms!FrmNFLancNoCadastro!NFEMPENHO"

    NFISCALDT and NFDT are date type. NFVALOR and NFVLR are Currency type. All the others are text type.

    What should I do to fix it? Thank you.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Text and Date fields need delimiters for the parameters.

    Variables cannot be within quote marks. Reference to controls is a variable. Concatenate variables.

    Try the Me alias for form prefix.

    CurrentDb.Execute "UPDATE TblCadastro Set NFISCAL=" & Me!NFNUM & ", NFISCALDT=#" & Me!NFDT & "#, NFVALOR=" & Me!NFVLR & " WHERE LICNUM='" & Me!NFCONTRAPARTIDA & "' AND LICEMPNUM = '" & Me!NFEMPENHO & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    edson is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Oct 2014
    Posts
    57
    Thank you June 7 for your kind explanation. Sorry to bother you again. In fact the error message is gone. New code went fine through the compilation. It assumes the right values... but does NOT updates TblCadastro!!!
    Geeeeeee!!! How come is that possible?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't know enough about your data and form structure.

    I expect records are not meeting the filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-30-2013, 12:11 PM
  2. Replies: 6
    Last Post: 04-26-2013, 10:07 AM
  3. Replies: 3
    Last Post: 02-13-2013, 10:15 AM
  4. Replies: 12
    Last Post: 03-17-2012, 04:46 AM
  5. Replies: 1
    Last Post: 08-31-2011, 04:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums