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
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
-
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. -
Hi,
okay thanks I'm going to look for it
++ -
Hello
you can do:
docmd.runsql(sql)
where sql is equal to your query -
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.
++ -
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
++ -
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 [ ] -
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
++ -
Okay, keep us posted.
-
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
++-
ModeratorSquare 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"
-
-
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 -
Thank you very much for your help
I wish you a great holiday season and a good day, and thanks again
++ -
The ' ' does not work under SQL Access!!!
-
I'm not saying it works!!!!
I thought it didn't, sorry ;)
