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
(tblPasswordMgm t.Password)

but based on a criteria which is neither the two values. Instead it's
like ([Forms]![frmUpdatePW]![txtusername]=tblPasswordMgm t.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]

Re: Update query

On 21 Jan 2006 06:06:02 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
[color=blue]
>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
>(tblPasswordMg mt.Password)
>
>but based on a criteria which is neither the two values. Instead it's
>like ([Forms]![frmUpdatePW]![txtusername]=tblPasswordMgm t.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![/color]


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.Execu te strSQL, dbFailOnError


Wayne Gillespie
Gosford NSW Australia

[Wayne Gillespie]

Re: Update query

On Sat, 21 Jan 2006 15:48:32 GMT, Wayne Gillespie <bestfit@NOhotm ailSPAM.com.au> wrote:
[color=blue]
>On 21 Jan 2006 06:06:02 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
>[color=green]
>>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
>>(tblPasswordM gmt.Password)
>>
>>but based on a criteria which is neither the two values. Instead it's
>>like ([Forms]![frmUpdatePW]![txtusername]=tblPasswordMgm t.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![/color]
>
>
>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.Exec ute strSQL, dbFailOnError
>
>
>Wayne Gillespie
>Gosford NSW Australia[/color]

Should be

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

CurrentDB.Execu te strSQL, dbFailOnError


Wayne Gillespie
Gosford NSW Australia

[Darren]

Re: Update query

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]

Re: Update query

On 23 Jan 2006 22:24:14 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
[color=blue]
>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[/color]

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.Execu te strSQL, dbFailOnError

End Sub

When the button is clicked, the code will execute.


Wayne Gillespie
Gosford NSW Australia

[Darren]

Re: Update query

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]

Re: Update query

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]

Re: Update query

Say it looks like this..

Private Sub cmdconfirm_Clic k()
On Error GoTo Err_cmdconfirm_ Click

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

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.Execu te strSQL, dbFailOnError

END IF

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

THEN
Dim strSQL As String

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


CurrentDb.Execu te strSQL, dbFailOnError

END IF

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

THEN
Dim strSQL As String

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


CurrentDb.Execu te 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]

Re: Update query

On 24 Jan 2006 00:03:24 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
[color=blue]
>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![/color]

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]

Re: Update query

On 23 Jan 2006 23:56:31 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
[color=blue]
>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![/color]

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]

Re: Update query

On 24 Jan 2006 00:13:33 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
[color=blue]
>Say it looks like this..
>
>Private Sub cmdconfirm_Clic k()
>On Error GoTo Err_cmdconfirm_ Click
>
>IF Me.[txtusername] = Dlookup("UserID ,"tblPasswordMg mt")
>
>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.Exec ute strSQL, dbFailOnError
>
>END IF
>
>IF Me.[txtusername] = Dlookup("UserID ,"tblPasswordPu rchase")
>
>THEN
>Dim strSQL As String
>
>strSQL = "UPDATE tblPasswordPurc hase " _
> & "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
>& " " _
> & "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
>
>
>CurrentDb.Exec ute strSQL, dbFailOnError
>
>END IF
>
>IF Me.[txtusername] = Dlookup("UserID ,"tblPasswordRe ceivi")
>
>THEN
>Dim strSQL As String
>
>strSQL = "UPDATE tblPasswordRece ivi " _
> & "SET [Password]=" & Chr(34) & Me.[txtConfirmPW].Value & Chr(34)
>& " " _
> & "WHERE [UserID]=" & Chr(34) & Me.txtusername & Chr(34) & ";"
>
>
>CurrentDb.Exec ute strSQL, dbFailOnError
>
>END IF
>Exit_cmdconfir m_Click:
> Exit Sub
>
>Err_cmdconfirm _Click:
> MsgBox Err.Description
> Resume Exit_cmdconfirm _Click
>
>End Sub
>
>Will it work if i put the 3 conditions?[/color]

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]

Re: Update query

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]

Re: Update query

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]

Re: Update query

On 26 Jan 2006 08:31:38 -0800, "Darren" <Yeo.darren@gma il.com> wrote:
[color=blue]
>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...[/color]

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]

Re: Update query

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

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

Thanxs lotsa!