Beyond Excel: VBA and Database Manipulation

Jul 22 2010   6:59PM GMT

Check Entry – Post_Click Event



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
tutorial
vba
There are two times when you want to check entries:
 
  1. When the user changes something and 
  2. When they want to post entries to the database (aka add, change or delete data). 

Today’s post covers the second situation.  The second situation requires a “Post” button.  We give the user a “Post” button with this command:

Create_Easy_Button “Post”, “Sheet1.Post_Click”, 105, 10

The code for that routine is here:  Code for Easy Button
The “Sheet1.” is the worksheet with the “Post” button on it, AND with the Post_Click routine in it.  It’s possible that your “Data” worksheet isn’t Sheet1.  To determine which sheet it is:
 
  1. Copy the code below to your “Data” worksheet
  2. Use Alt-F8 to bring up the Macros’ list. 
  3. Note the Sheet# name infront of Post_Click.  That’s the Macro name you’ll need.
 
105 is the number of pixels to the right for the upper left corner of your button.  10 is the number of pixels down.
 
If you’ve been following along from the beginning, you have a workable “Update” spreadsheet.  But we have more to do.  We have to wire up the “When the user changes something” event (See bullet #1 at top) and we need to control how the cursor moves from cell to cell so it automatically skips over protected (non-entry) cells.  Stay tuned.  More to come.  Here is today’s code:
 

Sub Post_Click()

 

   Wrapper for Post and Load_Data_Logs tied to “Update Logs” button

 

     Date   Init Modification

   05/22/09 CWH  Initial Programming

   

    On Error Resume Next

   

    Settings “Disable”      ‘Disable events, screen updates & Calc.s

    Worksheet_Activate

    Dim bResult As Boolean

    bResult = Post(sConnect, _

                   Me.Name, _

                   “”, sFields, _

                   “”, sData, _

                   sTable)

    If bResult = Success Then _

        Load_Data False     ‘False means no prompting

    Settings “Clear”        ‘Enable events

   

    On Error GoTo 0

 

End Sub

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: