How to Update database table 'insert/delete' from Excel data form?

B

bobghw

New member
Joined
Mar 30, 2017
Messages
3
Reaction score
0
Points
0
Is it possible to update a database using excel data form?

I can create the database connection and display the table data in the spreadsheet and when I click on form it displays a form and allows me to scroll through the data.
But when I click on new and enter details nothing happens and the new record is not inserted into the database.
 
Here's a simple database using a UserForm approach:

Code: 
Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Customers")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub
 

Attachments

  • Simple Database.xlsm
    19.9 KB · Views: 40
Logit said:
Here's a simple database using a UserForm approach:

Code: 
Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Customers")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub



Thanks for the reply was hoping for something that didn't involve code.
Click to expand...
 
removed original post after re-reading.
apparently you are already using the built in data form.
 
Last edited:
I believe you will need to use code regardless which method you use.

Don't let the VBA code hinder your progress. It really is not that hard to learn.

Members of this and other Forums will be willing to assist.
 
Thanks for your reply.

I am looking at using InfoPath designer (part of office pro) if that doesn't work then I will try VBA.
 
You must log in or register to reply here.
Back
Top