Using Update Query in VB

Solved
Bonjour,
I have been using Access for just a week and I'm working on a small project where I've been facing a difficulty for the past two days. I have a "loan" table (with three fields: loan_id, user_id, third_party_id, amount, and date) and a form that allows me to modify these various fields.

Initially, I wanted to create a search dropdown list that displays various fields for the user to choose one, which returns the loan_id, and I managed to achieve that.

After that, I have four other fields on my form that allow me to enter new values for user_id, third_party_id, amount, and date, and I want the user to simply click the modify button for the fields to be automatically updated, and that's where I'm stuck.
Here are my different attempts:
1)
Dim req As String
Dim rs As Recordset
Dim mabase As Database
Set mabase = CurrentDb()
' Execute the query
req = "Update LOAN Set LOAN.THIRD_PARTY_ID = '" & txtThirdPartyName & "', LOAN.USER_ID = '" & ValueNumUser & "', LOAN.LOAN_AMOUNT = '" & txtAmountHeld & "', LOAN.LOAN_DATE = '" & txtDateHeld & "' Where LOAN.LOAN_ID = '" & txtNumHeld & "'"
Set rs = mabase.OpenRecordset(req)

2)
Dim sdl As String
Dim rs As DAO.Recordset
sql = "Update LOAN Set LOAN.THIRD_PARTY_ID = '" & txtThirdPartyName & "', LOAN.USER_ID = '" & ValueNumUser & "', LOAN.LOAN_AMOUNT = '" & txtAmountHeld & "', LOAN.LOAN_DATE = '" & txtDateHeld & "' Where LOAN.LOAN_ID = '" & txtNumHeld & "';"
Set rs = CurrentDb.OpenRecordset(sql)

3)
Set db = CurrentDb
Set req = db.OpenRecordset("LOAN")
req.Edit
req("THIRD_PARTY_ID") = txtThirdPartyName
req("USER_ID") = ValueNumUser
req("LOAN_AMOUNT") = txtAmountHeld
req("LOAN_DATE") = txtDateHeld
req.Update
req.Close

With this last method, I can perform an update, but on the first line of my table I can't direct it to the loan_id returned by the search list.

Best regards, thank you for your help
jph
Configuration: Windows XP Firefox 3.5.6

13 answers

  • Votes
  • Date
  • Descending date
  1. Hello

    I don't know Access, but I can guarantee that your update queries do not return a recordset (so there's little chance that OpenRecordset will manage to execute an update).
    There must be a command like ExecuteCommand to execute your update queries.
    1. Hello

      you can do:

      docmd.runsql(sql)
      where sql is equal to your query
      1. Hi,
        okay I'll try this around noon because I'm not on my PC right now and I don't have access to my file. I'll keep you posted and thanks again.

        ++
        1. Hello, I'm just testing with this and here's what I get
          Dim sdl As String
          sql = "Update LOAN Set LOAN.IDENTIFIANT_TIERS = '" & txtNomTier & "', LOAN.IDENTIFIANT_UTILISATEUR = '" & ValeurNumUtilisateur & "', LOAN.MONTANT_LOAN = '" & txtMontantAvoir & "', LOAN.DATE_LOAN = '" & txtDateAvoir & "' Where LOAN.IDENTIFIANT_LOAN = '" & txtNumAvoir & "';"
          DoCmd.RunSQL (sql)

          and it gave me an error which is:
          incompatible data type in the criteria expression

          Sorry, but I don't understand, can someone help me please thank you

          ++
          1. Moderator
            Hello,

            surely a numeric field that you put in quotes, whereas they are reserved for strings...

            UserNumValue?
            txtAmountAsset?

            --

            A+ Blux
             "Fools dare anything. It's even how you recognize them." 
        2. Already your dim isn't good:
          dim sql as string
          sql = "Update LOAN Set LOAN.IDENTIFIER_PARTY = [txtPartyName], LOAN.IDENTIFIER_USER = ValueNumUser, LOAN.LOAN_AMOUNT = txtAmountAvailability, LOAN.LOAN_DATE = txtDateAvailability Where LOAN.IDENTIFIER_LOAN = txtNumAvailability"

          try this out

          I don't understand why you're concatenating everything, you're executing your query from VB so I assume you're executing it from a button located on the form so there's no need to concatenate, maybe you'll need to put them between [ ]
          1. Read,
            yes sorry I should have specified that everything in txt...... are fields present on my form and ValeurNumUtilisateur is a global variable containing my user ID because to access this form you must authenticate and I keep the number in this variable, blux.

            I will try this syntax right away thanks moiced59
            ++
            1. Re then it’s great it works thanks a lot dim sql as string
              sql = "Update LOAN Set LOAN.IDENTIFIER_PARTY = [txtPartyName], LOAN.IDENTIFIER_USER = ValueNumUser, LOAN.LOAN_AMOUNT = txtAmountToHave, LOAN.LOAN_DATE = txtDateToHave Where LOAN.IDENTIFIER_LOAN = txtNumToHave"

              but just one last thing could you explain to me or give me the address of a tutorial that explains how it works exactly why we have to put brackets or nothing or quotes and in which case too ^^ and also how to remove the alert message that appears: "You are about to update a row(s). As soon as you click yes, you will no longer be able to ...... "

              thank you again
              ++
              1. Moderator
                Square brackets [] are used when referencing a name that has a space inside, to prevent Access from thinking it's a separator...

                [field 1] and not field 1

                Strings should be enclosed in quotes (the single apostrophe, key 4 on the keyboard). Numeric values do not require this, and for dates, you must place # at the beginning and end...

                SELECT * FROM [table 1] WHERE field1 = 'toto' AND [next field] = 12 AND field_date = #01/01/2010#;


                --

                See you, Blux           
                 "Fools dare everything. That's how you recognize them"
            2. Here is the translation: so brackets: reference to a form field
              nothing: reference to a variable
              "": concatenates raw text example select * from person where name = pierre !!! where name = "pierre"
              remove alert message:
              at the beginning of the vb code:
              docmd.setwarnings false
              at the end of the code
              docmd.setwarnings true

              there you go
              1. Thank you very much for your help
                I wish you a great holiday season and a good day, and thanks again
                ++
                1. The ' ' does not work under SQL Access!!!
                  1. Moderator
                    since which version?

                    --

                    A+ Blux
                     "Les cons, ça ose tout. C'est même à ça qu'on les reconnaît"