473,386 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Update query

Help, i want to run an update query from a form..
and was wonderin..

Can the update query run if i want to update a value manually inputted
from a form (e.g. [Forms]![frmUpdatePW]![txtConfirmPW]) to a table
(tblPasswordMgmt.Password)

but based on a criteria which is neither the two values. Instead it's
like ([Forms]![frmUpdatePW]![txtusername]=tblPasswordMgmt.UserID).

in a nutshell,

UPDATE tblPasswordMgmt
SET (Qn : The command SET is it SET TO or SET FROM?)
WHERE (Qn : Can the command WHERE be specified which has no relevance
to the value that i want to update?)

Can the query "plucked out" the value from the form to update
exclusively to a specific field in the table? and this specific field
must match another value input by the user which matches to another
field in the table before updating the field in the table.

Thanxs Lotsa!

Jan 21 '06 #1
15 3256
On 21 Jan 2006 06:06:02 -0800, "Darren" <Ye********@gmail.com> wrote:
Help, i want to run an update query from a form..
and was wonderin..

Can the update query run if i want to update a value manually inputted
from a form (e.g. [Forms]![frmUpdatePW]![txtConfirmPW]) to a table
(tblPasswordMgmt.Password)

but based on a criteria which is neither the two values. Instead it's
like ([Forms]![frmUpdatePW]![txtusername]=tblPasswordMgmt.UserID).

in a nutshell,

UPDATE tblPasswordMgmt
SET (Qn : The command SET is it SET TO or SET FROM?)
WHERE (Qn : Can the command WHERE be specified which has no relevance
to the value that i want to update?)

Can the query "plucked out" the value from the form to update
exclusively to a specific field in the table? and this specific field
must match another value input by the user which matches to another
field in the table before updating the field in the table.

Thanxs Lotsa!

If the values are strings you need to wrap the control refrerences in quotes. (I use Chr(34)

strSQL ="UPDATE tblPasswordMgmt " _
& "SET [SomeField]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34) & " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"

CurrentDB.Execute strSQL, dbFailOnError
Wayne Gillespie
Gosford NSW Australia
Jan 21 '06 #2
On Sat, 21 Jan 2006 15:48:32 GMT, Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote:
On 21 Jan 2006 06:06:02 -0800, "Darren" <Ye********@gmail.com> wrote:
Help, i want to run an update query from a form..
and was wonderin..

Can the update query run if i want to update a value manually inputted
from a form (e.g. [Forms]![frmUpdatePW]![txtConfirmPW]) to a table
(tblPasswordMgmt.Password)

but based on a criteria which is neither the two values. Instead it's
like ([Forms]![frmUpdatePW]![txtusername]=tblPasswordMgmt.UserID).

in a nutshell,

UPDATE tblPasswordMgmt
SET (Qn : The command SET is it SET TO or SET FROM?)
WHERE (Qn : Can the command WHERE be specified which has no relevance
to the value that i want to update?)

Can the query "plucked out" the value from the form to update
exclusively to a specific field in the table? and this specific field
must match another value input by the user which matches to another
field in the table before updating the field in the table.

Thanxs Lotsa!

If the values are strings you need to wrap the control refrerences in quotes. (I use Chr(34)

strSQL ="UPDATE tblPasswordMgmt " _
& "SET [SomeField]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34) & " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"

CurrentDB.Execute strSQL, dbFailOnError
Wayne Gillespie
Gosford NSW Australia


Should be

strSQL ="UPDATE tblPasswordMgmt " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34) & " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"

CurrentDB.Execute strSQL, dbFailOnError
Wayne Gillespie
Gosford NSW Australia
Jan 22 '06 #3
Hi, should it be in a module or can it be in a macro, with runSQL code:
strSQL ="UPDATE tblPasswordMgmt " _
& "SET [SomeField]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
And if i were to place it in a query, it prompts for an invalid SQL
expression

Jan 24 '06 #4
On 23 Jan 2006 22:24:14 -0800, "Darren" <Ye********@gmail.com> wrote:
Hi, should it be in a module or can it be in a macro, with runSQL code:
strSQL ="UPDATE tblPasswordMgmt " _
& "SET [SomeField]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
And if i were to place it in a query, it prompts for an invalid SQL
expression


If you are running this from a form, I would put the code in the Form's module.

eg If you want to run the update from a command button, The code would be pasted into the Click event of the command
button.

To test it, add a command button to a form (btnUpdate).
In design view click on the command button and in the properties sheet select the OnClick event. Type [Event Procedure]
and click the (...) button at the right of the property. The form's code module will open up with the procedure header
and footer filled in.

Post the code between the header and footer so it looks like -

Sub btnUpdate_Click()
Dim strSQL as String

strSQL ="UPDATE tblPasswordMgmt " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34) & " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"

CurrentDB.Execute strSQL, dbFailOnError

End Sub

When the button is clicked, the code will execute.
Wayne Gillespie
Gosford NSW Australia
Jan 24 '06 #5
Hi, now that i could run this macro...
i haf another problem..becoz i have 3 different level of permissions
for 3 different groups of users, therefore their passwords are found in
3 separate tables. Therefore, i want to update the password but it must
find it in the correct table to update.

How can i do that?

Thanks for that..realli sorry for da trouble!

Jan 24 '06 #6
and if i were to have a macro run before this code, how should i go
about doing dis?

thanxs! You have been a very great help!

Jan 24 '06 #7
Say it looks like this..

Private Sub cmdconfirm_Click()
On Error GoTo Err_cmdconfirm_Click

IF Me.[txtusername] = Dlookup("UserID,"tblPasswordMgmt")

THEN
Dim strSQL As String

strSQL = "UPDATE tblPasswordMgmt " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

END IF

IF Me.[txtusername] = Dlookup("UserID,"tblPasswordPurchase")

THEN
Dim strSQL As String

strSQL = "UPDATE tblPasswordPurchase " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

END IF

IF Me.[txtusername] = Dlookup("UserID,"tblPasswordReceivi")

THEN
Dim strSQL As String

strSQL = "UPDATE tblPasswordReceivi " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

END IF
Exit_cmdconfirm_Click:
Exit Sub

Err_cmdconfirm_Click:
MsgBox Err.Description
Resume Exit_cmdconfirm_Click

End Sub

Will it work if i put the 3 conditions?

Jan 24 '06 #8
On 24 Jan 2006 00:03:24 -0800, "Darren" <Ye********@gmail.com> wrote:
and if i were to have a macro run before this code, how should i go
about doing dis?

thanxs! You have been a very great help!


I never use macros but I think you can add a line in the same procedure like -

DoCmd.RunMacro "MacroName"
Wayne Gillespie
Gosford NSW Australia
Jan 24 '06 #9
On 23 Jan 2006 23:56:31 -0800, "Darren" <Ye********@gmail.com> wrote:
Hi, now that i could run this macro...
i haf another problem..becoz i have 3 different level of permissions
for 3 different groups of users, therefore their passwords are found in
3 separate tables. Therefore, i want to update the password but it must
find it in the correct table to update.

How can i do that?

Thanks for that..realli sorry for da trouble!


It sounds to me like you have a design problem with your basic data structure.
I would suggest that you redesign your table so that all passwords are in the one table. There should be no reason to
have separate tables for each permission level. Combine them into a single table and include fields in the table to
indicate the permission level.
Wayne Gillespie
Gosford NSW Australia
Jan 24 '06 #10
On 24 Jan 2006 00:13:33 -0800, "Darren" <Ye********@gmail.com> wrote:
Say it looks like this..

Private Sub cmdconfirm_Click()
On Error GoTo Err_cmdconfirm_Click

IF Me.[txtusername] = Dlookup("UserID,"tblPasswordMgmt")

THEN
Dim strSQL As String

strSQL = "UPDATE tblPasswordMgmt " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

END IF

IF Me.[txtusername] = Dlookup("UserID,"tblPasswordPurchase")

THEN
Dim strSQL As String

strSQL = "UPDATE tblPasswordPurchase " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

END IF

IF Me.[txtusername] = Dlookup("UserID,"tblPasswordReceivi")

THEN
Dim strSQL As String

strSQL = "UPDATE tblPasswordReceivi " _
& "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
& " " _
& "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

END IF
Exit_cmdconfirm_Click:
Exit Sub

Err_cmdconfirm_Click:
MsgBox Err.Description
Resume Exit_cmdconfirm_Click

End Sub

Will it work if i put the 3 conditions?


This would work however see my other post re your data structure.

You only need to add the line
Dim strSQL as String
once at the top of the routine.

Wayne Gillespie
Gosford NSW Australia
Jan 24 '06 #11
realli thank you for all ya help..owe ya big time man!

but i got one last problem, i can run the above code..and i want it to
prompt to the user that the password was changed successfully, and log
in with the new password on the login form...

the above i would put into a macro..but i want it to run only after the
fileds entered were valid and not null and also with the password
changed..

How can i do that? coz i added the macro below the above code, and it
runs even though the previous steps were not followed (e.g. check
password is valid wif user)..

Thanks a million times...

Jan 26 '06 #12
realli thank you for all ya help..owe ya big time man!

but i got one last problem, i can run the above code..and i want it to
prompt to the user that the password was changed successfully, and log
in with the new password on the login form...

the above i would put into a macro..but i want it to run only after the
fileds entered were valid and not null and also with the password
changed..

How can i do that? coz i added the macro below the above code, and it
runs even though the previous steps were not followed (e.g. check
password is valid wif user)..

Thanks a million times...

Jan 26 '06 #13
On 26 Jan 2006 08:31:38 -0800, "Darren" <Ye********@gmail.com> wrote:
realli thank you for all ya help..owe ya big time man!

but i got one last problem, i can run the above code..and i want it to
prompt to the user that the password was changed successfully, and log
in with the new password on the login form...

the above i would put into a macro..but i want it to run only after the
fileds entered were valid and not null and also with the password
changed..

How can i do that? coz i added the macro below the above code, and it
runs even though the previous steps were not followed (e.g. check
password is valid wif user)..

Thanks a million times...


I would not enable the command button until the required fields have been entered and whatever validity checks have been
done.
Wayne Gillespie
Gosford NSW Australia
Jan 26 '06 #14
Erm...how do you enable the command button?

I appreciate ur tolerance wif me, a access idiot..

Thanxs lotsa!

Jan 30 '06 #15
after finding help on enabling the command button, i found it..

but i realise i can dis-/enable the button with a certain condition,
yikes! i have a headache here...

ya see, i have a macro that runs to validate some stuff (ya know, old
password and username is a fit, new and confirmed password is the same
etc.)

My problem is that can i enable the button ONLY after the macro ran and
made sure that all fields were correct and validated?

i know that there is a true thingy e.g.

Dim x as Integer
x = True
If IsNull(txtconfirmPW) Then x = False
EnableCmdUpdate = x

this works with one condition and in code, but i'm running a macro here
which tests for several conditions to be met..how can it be done?

After enabling the button, then i update the new password...

Jan 30 '06 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
31
by: Lag | last post by:
Having a problem updating my database from a web page, through a submission form. Can anyone help? ----THIS IS MY CODE IN update.php----(user, pass, and database are typed in directly, I...
3
by: eholz1 | last post by:
Hello PHP programmers. I had a brilliant idea on one of my pages that selects some data from my mysql database. I first set the page up to display some info and an image, just one item, with a...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.