UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Use Update Query With Information Coming From Textbox And Combobox, Access 2013    
 
   
NewToAccess2019
post Sep 27 2019, 11:04 AM
Post#1



Posts: 28
Joined: 21-March 19



Hi,

I am trying to use an update query, I am unsure of the syntax. I understand that there are a bunch of ways it can be done, however the book that I have on VBA doesn't cover update query and procedures.

I have been trying trail and error and I have reached a point where I know I am missing only something very trivial.

Please point out what I am missing, or if there is a better way of doing it.

**Here is my code**

CODE
Private Sub Command7_Click()
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
sSQL = "update ratesss set (r3 = '" & Me!textR & "') where (description = '" & Me!TEXTDS & "')"
    
''Temporary query
Set qdf = db.CreateQueryDef("", sSQL)

End Sub


r3 is a column in table ratesss, textR has the rate, textds has the description.
This post has been edited by NewToAccess2019: Sep 27 2019, 11:05 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2019, 11:07 AM
Post#2


UA Admin
Posts: 35,844
Joined: 20-June 02
From: Newcastle, WA


Other than superfluous opening and closing parenthesis, I don't see the problem at first glance.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2019, 11:08 AM
Post#3


UA Admin
Posts: 35,844
Joined: 20-June 02
From: Newcastle, WA


Of course, you have to actually execute the temp query def you created....

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
NewToAccess2019
post Sep 27 2019, 11:20 AM
Post#4



Posts: 28
Joined: 21-March 19



so something like database.execute?
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2019, 11:22 AM
Post#5


UA Admin
Posts: 35,844
Joined: 20-June 02
From: Newcastle, WA


Set db = CurrentDb

so,

db.Execute qdf

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
NewToAccess2019
post Sep 27 2019, 12:14 PM
Post#6



Posts: 28
Joined: 21-March 19



thanks a lot for that tip.

Solution:

CODE
Private Sub Command7_Click()
Dim db As Database
Dim qdf As String

Set db = CurrentDb
qdf = "update Ratesss set R3 = " & Me!textR & " where '(description = " & Me!TEXTDS & ")'"

db.Execute qdf

End Sub
Go to the top of the page
 
NewToAccess2019
post Sep 27 2019, 12:36 PM
Post#7



Posts: 28
Joined: 21-March 19



So something crazy happened.

I used this code:

CODE
Private Sub Command7_Click()
Dim db As Database
'Dim qdf As String
Dim qdf1 As String

Set db = CurrentDb
'qdf = "update Ratesss set R3 = " & Me!textR & " where '(description = " & Me!TEXTDS & ")'"
qdf1 = "update Rolls set Rate = " & Me!textR & " where '(description = " & Me!TEXTDS & ")' and '(date_in >= #9-1-2019#)'"
db.Execute qdf1

End Sub


I tested for qdf before applying qdf1, worked just fine for qdf.
However for qdf1, the query weirdly updated everything after 9-1-2019 to the rate coming from textR and didnt respect the first where condition of matching the description to TEXTDS.
Is the syntax wrong somehow?
This post has been edited by NewToAccess2019: Sep 27 2019, 12:37 PM
Go to the top of the page
 
NewToAccess2019
post Oct 2 2019, 12:23 PM
Post#8



Posts: 28
Joined: 21-March 19



i think something is wrong with the syntax.

it updated the column r3 and didnt match the row value in description column.
Go to the top of the page
 
NewToAccess2019
post Oct 8 2019, 11:31 AM
Post#9



Posts: 28
Joined: 21-March 19



Figured out the correct way of doing it.
CODE
Dim db As Database
Dim qdf As String
Dim qdf1 As String

Set db = CurrentDb
'qdf = "update Rata set R3 = " & Me!textR & " where ([description] = '" & Me!TEXTDS & "')"
qdf1 = "update Rollaa set Rate = " & Me!textR & " where (([description] = '" & Me!TEXTDS & "') and (date_in >= #9-1-2019#))"
db.Execute qdf1
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 03:48 AM