By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

Customize save menu item in access data entry form

P: 37
I have data entry form in access and a save button which checks for validations and throws errors. I want to map this function to the save menu item available in the menubar. How do i do it?
Jan 7 '10 #1

✓ answered by TheSmileyCoder

To achieve what your looking for (finding the correct record) use:
Expand|Select|Wrap|Line Numbers
  1. Me.Form.Recordset.FindFirst ("KEY_Item=" & lngKEY)
Thats assuming you have a primary key called "KEY_Item", and that you have saved your variable for the primary key into a long variable called lngKEY while your doing the save.

However that Database must be a MONSTER to maintain.

What I do, is to tie an event into the forms BeforeUpdate event.

Example:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as integer)
  2.   'Initiate variables
  3.     Dim intReply As Integer
  4.  
  5.   'Ask user
  6.     intReply=MsgBox("Do you wish to save your changes?", & _
  7.                               vbYesNoCancel,"Save?")
  8.  
  9.   'Evaluate result
  10.     Select Case intReply
  11.       Case vbYes
  12.         'Save it
  13.         Cancel=False
  14.         Exit Sub
  15.  
  16.       Case vbNo
  17.         'Dont save
  18.         Me.Undo
  19.         Exit Sub
  20.  
  21.       Case vbCancel
  22.         'Return to edit more
  23.         Cancel=True
  24.         Exit Sub
  25.  
  26.     End Select
  27.  
  28.  
  29. End Sub
The Form's BeforeUpdate fires right as Access is about to write new info the tables. Cancelling it will halt the procedure and return {control to} you. Doing a Me.Undo will undo the changes the user made.

Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,637
You would probably have to create a New Menu Bar, then Map the functionality through it.
Jan 7 '10 #2

NeoPa
Expert Mod 15k+
P: 31,489
Specifically, you cannot. The save option in the menu has a different job to do. It saves design changes (not data changes).

As ADezii suggests though, you can do something similar.
Jan 7 '10 #3

P: 37
How do i create a nenu bar all together. Im new to access and i dont have any clue how this should be done
Jan 8 '10 #4

NeoPa
Expert Mod 15k+
P: 31,489
I've never done it in Access, so I'll leave ADezii to come up with something. If you hear nothing from him after 24 hours then bump this thread and I'll look into it more deeply for you. I've done similar work in excel so I should be able to find my way without too much struggle.
Jan 8 '10 #5

P: 37
Ok. How do i make the seek operation in recorset work form dynamic recordset after recordset.requery?

After updating the data, i did a recordset.requery to sync the data btw the table and the recordset. but then it reinitialized the recordset object and when i press the previous button when in third record it says 'already at first record'
Jan 11 '10 #6

NeoPa
Expert Mod 15k+
P: 31,489
I'm completely confused :S

Is this the same question, or a completely new one? Either way I cannot follow what you are talking about. Perhaps we could leave the mind-reading for our stage act & you could explain your question fully :D
Jan 11 '10 #7

P: 37
Sorry,

I retrieve values from a table and show it on a access form. The form has a previous, next and save buttons. the next and previous are working fine, but whenever i save the updated values into the database i call the requery method on the recordset object so that the values in the access table and recorset are in sync.
but after call that requery method. the recorset is getting reinitialised.
Example
Suppose if i click save after updating the third record in the table. the value is updated as required and because of recorset.requery the values are in sync in two sources. but when i press previous button, i get a message that the recordset is already at the first record(because of reinitialisation).

For fixing this i tried to to recordset.seek for the updated record. but i got a run time error saying ''current provider does not support the necessary interface for index functionality"
how do i fix it.
my code is
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim strg As String
  3. strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\710562\Desktop\NPI\NPI POC\save.mdb;Persist Security Info=False"
  4. conn.Open strg
  5. Dim strsql As String
  6. strsql = "Select ID, employeeid,Name,salary,HRA from Table1"
  7. rst.ActiveConnection = conn
  8. rst.Open strsql, conn, adOpenDynamic, , adCmdText
  9. rst.Index = "ID"                <------------this is the promary key in the table
  10.  
  11. Text2.Value = rst("Employeeid")
  12. Text1.Value = rst("ID")
  13. Text3.Value = rst("Name")
  14. Text4.Value = rst("Salary")
  15. Text5.Value = rst("HRA")
  16.  
  17. End Sub
  18.  
  19.  
  20. Private Sub Save_Click()
  21. Dim rst1 As New ADODB.Recordset
  22.  
  23. Dim strsql As String
  24. Dim iRecAff As Integer
  25. Dim Answer As String
  26. Dim MyNote As String
  27.  
  28.     'Place your text here
  29.     MyNote = "Do you want to save?"
  30.  
  31.     'Display MessageBox
  32.     Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Confirmation Message")
  33.  
  34.     If Answer = vbNo Then
  35.         'Code for No button Press
  36.         MsgBox "You pressed NO!"
  37.     Else
  38.         'Code for Yes button Press
  39.         'MsgBox "You pressed Yes!"
  40.  
  41. strsql = "Update Table1 set employeeid=" & Text2.Value & ",Name= '" & Text3.Value & "',Salary=" & Text4.Value & ",HRA=" & Text5.Value & " where ID=" & Text1.Value & ";"
  42. '& ID.Value & ""
  43. Set rst1 = conn.Execute(strsql, iRecAff)
  44. rst.Requery
  45. rst.Seek Text1.Value, adSeekFirstEQ
  46. MsgBox "Valued Updated Sucessfully"
  47. End If
  48. End Sub
Jan 11 '10 #8

ADezii
Expert 5K+
P: 8,637
You will probably have to Requery the Form in order to reflect any Additions, Deletions, and/or Modifications to the underlying Record Source. I'm assuming, of course, that your Form is Bound to Table1, but if this is the case and the Controls are Bound to the individual Fields, then you would not need much of the code at all.

P.S. - I'll get back to you on how to create a Menu Bar and Map the functionality that you requested to one of its Options, but it probably will not be until tonight or even tomorrow.
Jan 11 '10 #9

NeoPa
Expert Mod 15k+
P: 31,489
This may seem an obvious question, but why are you saving the data using a SQL query instead of using the form itself with Call DoCmd.RunCommand(Command:=acCmdSaveRecord)?

That way the .Requery wouldn't be necessary of course.
Jan 11 '10 #10

ADezii
Expert 5K+
P: 8,637
Creating a Menu Bar with Sub-Menus in Access is by no means intuitive but hopefully I'll point you in the right direction:
  1. Create a Public Function, let's call it fTestFunction(), that contains all the functionality that you wish to utilize.
  2. View ==> Toolbars ==> Customize
  3. Toolbars Tab ==> New ==> Name the Toolbar (let's call it My Toolbar).
  4. Click My Toolbar ==> Properties.
  5. Type = Menu Bar ==> select Docking and other Options ==> Close.
  6. Select My Toolbar.
  7. Commands Tab ==> Categories = New Menu.
  8. Drag-N-Drop New Menu from the Commands Pane to the New Toolbar in the Drop Down.
  9. Populate the Sub-Menus.
  10. Right Click any Menu/Sub-Menu Option ==> Properties to set Properties.
  11. Set the On Action Property of your Save Menu to =fTestFunction().
Yes, it is confusing, but it is the only Method that I know and after you play with it for awhile, it will become second nature.
Jan 12 '10 #11

P: 37
Adezil, thanks for the steps to create the menu, i have been able to follow the steps and create a menu

Neopa,
Sorry for not using code tags, i didnt know

My requirement is such that there is already an access application built and im trying to enhance it.. The forms in the application are such that they are directly linked to the underlying tables. The problem is whenever we change any data in any of the data entry. forms It directly updates the tables without asking a save confirmation(Do you want to save the changes? Yes Or No).

That is why i decided to decouple the tables and the forms by using recordset. But the problem now is there are twenty such forms having 50 or so data fields associated with each of them.

My approach will be like building everything from scratch and i don't wish to go ahead with that because of time constraints.

Thanks to both of u for your support
Jan 12 '10 #12

TheSmileyCoder
Expert Mod 100+
P: 2,321
To achieve what your looking for (finding the correct record) use:
Expand|Select|Wrap|Line Numbers
  1. Me.Form.Recordset.FindFirst ("KEY_Item=" & lngKEY)
Thats assuming you have a primary key called "KEY_Item", and that you have saved your variable for the primary key into a long variable called lngKEY while your doing the save.

However that Database must be a MONSTER to maintain.

What I do, is to tie an event into the forms BeforeUpdate event.

Example:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as integer)
  2.   'Initiate variables
  3.     Dim intReply As Integer
  4.  
  5.   'Ask user
  6.     intReply=MsgBox("Do you wish to save your changes?", & _
  7.                               vbYesNoCancel,"Save?")
  8.  
  9.   'Evaluate result
  10.     Select Case intReply
  11.       Case vbYes
  12.         'Save it
  13.         Cancel=False
  14.         Exit Sub
  15.  
  16.       Case vbNo
  17.         'Dont save
  18.         Me.Undo
  19.         Exit Sub
  20.  
  21.       Case vbCancel
  22.         'Return to edit more
  23.         Cancel=True
  24.         Exit Sub
  25.  
  26.     End Select
  27.  
  28.  
  29. End Sub
The Form's BeforeUpdate fires right as Access is about to write new info the tables. Cancelling it will halt the procedure and return {control to} you. Doing a Me.Undo will undo the changes the user made.
Jan 12 '10 #13

P: 37
Smileyone, the code is working fine.Thanks a ton for that.Never knew this was that simple.the event fires only when the form is closed . But what i want is when i press save in the menu strip the same confirmation should be asked.
Jan 13 '10 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
maxpirate

You have to start a new thread every time you have a new question. Having multiple questions in the same thread is confusing for everybody.

I am splitting off your latest post into its own thread.

Admin
Jan 13 '10 #15

TheSmileyCoder
Expert Mod 100+
P: 2,321
Is it changes to form design you wish to save(and confirm) or is it data changes you wish to save(and confirm).

As far as I know, the code provided will run however you initiate the DATA save (by closing the form, moving to another record, or pressing save.)

If you wish to have a button I believe the following code will work:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Save_Click()
  2.   On Error Resume Next
  3.   If Me.Dirty then
  4.     Me.Dirty=False
  5.   End If
  6. End Sub
This will force the form to save.

As for saving form changes at runtime, I don't know how its done, but I sometimes open reports in hidden design mode, modify them by code, and close em again, saving. Then finally open them in preview mode. To save the report use the command:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acReport, "myRep",acSaveYes
Jan 13 '10 #16

P: 37
Not for the design changes.. The data changes..

Whenever i update a record in the form and press the "save" icon in the menubar i wish to get confirmation for the data changes.
Jan 13 '10 #17

TheSmileyCoder
Expert Mod 100+
P: 2,321
I have never used the toolbar to save my records. However when I tried it now, the form's BeforeUpdate still runs(And asks for save confirmation), so Im not sure what your issue is. Have you tested the code I provided while using the toolbar menu item to save?
Jan 13 '10 #18

P: 37
I found out the same,
pressing the save icon does not persist the record. that icon is only used for saving design changes. so the only way to save a record is through the confirmation alert which fires when beforeupdate event is called

Thanks for help.
If you wish, kindly reply for my other issue
Jan 13 '10 #19

NeoPa
Expert Mod 15k+
P: 31,489
My thanks to TheSmileyOne for fleshing out the point I was trying to make in post #10. I wasn't around yesterday much as I was out playing football in the warm British weather (brrrr). I'm very pleased to see so much activity since I last posted though. Nothing here for me to do :)

That's how simple it is supposed to be Max. Obviously, it takes experience or a little help sometimes to see how Access is making it easy for you.
Jan 13 '10 #20

P: 37
Yea. I agree.. Thanks a lot for helping guys. You have simplified my work. I'm running on tight schedule, thats why i keep posting stuff. Will be continuing for some while till the schedule eases out.
Jan 15 '10 #21

Post your reply

Sign in to post your reply or Sign up for a free account.