Customize save menu item in access data entry form

[maxpirate]

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?

[ADezii]

You would probably have to create a New Menu Bar, then Map the functionality through it.

[NeoPa]

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.

[maxpirate]

How do i create a nenu bar all together. Im new to access and i dont have any clue how this should be done

[NeoPa]

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.

[maxpirate]

Ok. How do i make the seek operation in recorset work form dynamic recordset after recordset.reque ry?

After updating the data, i did a recordset.reque ry 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'

[NeoPa]

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

[maxpirate]

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.requer y 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 reinitialisatio n).

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

Private Sub Form_Load()
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\710562\Desktop\NPI\NPI POC\save.mdb;Persist Security Info=False"
conn.Open strg
Dim strsql As String
strsql = "Select ID, employeeid,Name,salary,HRA from Table1"
rst.ActiveConnection = conn
rst.Open strsql, conn, adOpenDynamic, , adCmdText
rst.Index = "ID"                <------------this is the promary key in the table

Text2.Value = rst("Employeeid")
Text1.Value = rst("ID")
Text3.Value = rst("Name")
Text4.Value = rst("Salary")
Text5.Value = rst("HRA")

End Sub


Private Sub Save_Click()
Dim rst1 As New ADODB.Recordset

Dim strsql As String
Dim iRecAff As Integer
Dim Answer As String
Dim MyNote As String

    'Place your text here
    MyNote = "Do you want to save?"

    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Confirmation Message")

    If Answer = vbNo Then
        'Code for No button Press
        MsgBox "You pressed NO!"
    Else
        'Code for Yes button Press
        'MsgBox "You pressed Yes!"

strsql = "Update Table1 set employeeid=" & Text2.Value & ",Name= '" & Text3.Value & "',Salary=" & Text4.Value & ",HRA=" & Text5.Value & " where ID=" & Text1.Value & ";"
'& ID.Value & ""
Set rst1 = conn.Execute(strsql, iRecAff)
rst.Requery
rst.Seek Text1.Value, adSeekFirstEQ
MsgBox "Valued Updated Sucessfully"
End If
End Sub

[ADezii]

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.

[NeoPa]

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.RunComman d(Command:=acCm dSaveRecord)?

That way the .Requery wouldn't be necessary of course.

[ADezii]

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.

[maxpirate]

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

[TheSmileyCoder]

To achieve what your looking for (finding the correct record) use:

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:

Private Sub Form_BeforeUpdate(Cancel as integer)
  'Initiate variables
    Dim intReply As Integer
  
  'Ask user
    intReply=MsgBox("Do you wish to save your changes?", & _
                              vbYesNoCancel,"Save?")

  'Evaluate result
    Select Case intReply
      Case vbYes
        'Save it
        Cancel=False
        Exit Sub
    
      Case vbNo
        'Dont save
        Me.Undo
        Exit Sub
        
      Case vbCancel
        'Return to edit more
        Cancel=True
        Exit Sub

    End Select


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.

[maxpirate]

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.

[MMcCarthy]

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