Update query

[Darren]

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!

[Wayne Gillespie]

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

[Wayne Gillespie]

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

[Darren]

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

[Wayne Gillespie]

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

[Darren]

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!

[Darren]

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!

[Darren]

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?

[Wayne Gillespie]

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

[Wayne Gillespie]

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

[Wayne Gillespie]

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

[Darren]

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...

[Darren]

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...

[Wayne Gillespie]

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

[Darren]

Erm...how do you enable the command button?

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

Thanxs lotsa!

[Darren]

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...