 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / January 2006Update 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! >Help, i want to run an update query from a form.. >and was wonderin.. [quoted text clipped - 19 lines] > >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 >>Help, i want to run an update query from a form.. >>and was wonderin.. [quoted text clipped - 30 lines] >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 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 >Hi, should it be in a module or can it be in a macro, with runSQL code: > [quoted text clipped - 5 lines] >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 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! >Hi, now that i could run this macro... >i haf another problem..becoz i have 3 different level of permissions [quoted text clipped - 5 lines] > >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 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! >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 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? >Say it looks like this.. > [quoted text clipped - 52 lines] > >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 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... 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... >realli thank you for all ya help..owe ya big time man! > [quoted text clipped - 11 lines] > >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 Erm...how do you enable the command button? I appreciate ur tolerance wif me, a access idiot.. Thanxs lotsa! 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... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.