473,320 Members | 1,845 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,320 software developers and data experts.

Customize save menu item in access data entry form

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.

20 3963
ADezii
8,834 Expert 8TB
You would probably have to create a New Menu Bar, then Map the functionality through it.
Jan 7 '10 #2
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
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
2,322 Expert Mod 2GB
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
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
14,534 Expert Mod 8TB
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
2,322 Expert Mod 2GB
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
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
2,322 Expert Mod 2GB
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
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

4
by: Andras Gilicz | last post by:
Hi VB fans I'm working on a relatively large project in VB6 with about a dozen forms, including graphs, labels, text boxes, etc. The software itself is actually a flow simulator with more or...
2
by: zapazap | last post by:
Dear Snake Charming Gurus, (Was: http://mail.python.org/pipermail/python-list/2004-January/204454.html) First, a thank you to Tim Golden, Thomas Heller, and Mark Hammond for your earlier help...
1
by: deko | last post by:
I've found that the "Remove Filter/Sort" selection in the Shortcut menu (displayed on right click) produces ugly, untrappable errors, even though "Allow Filters" is set to No in the subform. ...
2
by: JMCN | last post by:
hi i'm having a problem with the customize a shortcut menu. i read the ms office assistance: customize a shortcut menu/delete a command or add to a shortcut menu and followed the simple...
2
by: Paul Brady | last post by:
I have non-computer skilled users entering data into a form. There are certain ranges of values which, if they enter them, make no sense in the application, but I can't test them until they try to...
4
by: Glenn M | last post by:
I have a shared XML file on a server . i also have one xslt file that performs a simple transform on in to view the data. now i want to have another page that lets users modify the shared xml...
3
by: Bob Quintal | last post by:
In access 2003, a toolbar has a dropdown to allow customizing. In the toolbar properties->options is a checkbox labelled Allow Customizing. Unchecking this doesn't prevent me from customizing the...
5
by: giandeo | last post by:
Hello Experts. Could you find a solution for this problem please! I have the following tables in Access Database Table Name: origin Fields Names: country, countrycode Table Name: make...
7
by: Neil | last post by:
Was working in A2003 and noticed that the Save Record item on the Records menu was not available when the record was not dirty. In A2000, Save Record was always available. (This is a problem for me...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.