User input from excel form not populating Access Database



Hi All,

I have a user form designed in Excel. I am trying it to connect it to Access
Database, so that Once users fill in the excel form and click "Submit" it
directly populates the access database.

Here is my Code for Submit_Click() event:

Private Sub Submit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for Officer Name
If Trim(Me.OfficerNm.Value) = "" Then
MsgBox "Please enter your Name"
Exit Sub
End If

'check for Address
If Trim(Me.Add.Value) = "" Then
MsgBox "Please enter Address"
Exit Sub
End If

'check for City Name
If Trim(Me.Cty.Value) = "" Then
MsgBox "Please enter City Name"
Exit Sub
End If

'check for State Name
If Trim(Me.St.Value) = "" Then
MsgBox "Please enter State Name"
Exit Sub
End If

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
MsgBox "Please enter Zip Code"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.OfficerNm.Value
ws.Cells(iRow, 3).Value = Me.AcctNo.Value
ws.Cells(iRow, 4).Value = Me.OffcPhNo.Value

ws.Cells(iRow, 5).Value = Me.CntcPersonName.Value
ws.Cells(iRow, 6).Value = Me.ContactPersonPhNo.Value

ws.Cells(iRow, 11).Value = Me.Add.Value
ws.Cells(iRow, 12).Value = Me.Cty.Value
ws.Cells(iRow, 13).Value = Me.St.Value
ws.Cells(iRow, 14).Value = Me.Zip.Value

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB")
' open the database
Set rs = db.OpenRecordset("Demo_Table", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Officer Name") = Range("A" & r).Value
.Fields("Officer Phone #") = Range("D" & r).Value
.Fields("Contact Person Name") = Range("E" & r).Value
.Fields("Contact Person Phone #") = Range("F" & r).Value
.Fields("Address") = Range("K" & r).Value
.Fields("City") = Range("L" & r).Value
.Fields("State") = Range("M" & r).Value
.Fields("Zip Code") = Range("N" & r).Value

.Update ' stores the new record
End With
r = r + 1 ' next row
Set rs = Nothing
Set db = Nothing

'clear the data from form
Me.OfficerNm.Value = ""
Me.OffcPhNo.Value = ""
Me.CntcPersonName.Value = ""
Me.ContactPersonPhNo.Value = ""

Me.Add.Value = ""
Me.Cty.Value = ""
Me.St.Value = ""
Me.Zip.Value = ""


End Sub

Please Help

Thanks in Advance


Hummm, everything looks right. Where does it fail? If the data is
non-confidential, and if you want to send the two files, I'll take a look.

(e-mail address removed)


Hey Ryan, I have sent you my files
Can you please look at it and let me kown whats the issue.

Thanks a lot.


Resolved. Check your email. Just a couple tiny coding errors and needed to
set a reference to ADO in Excel.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question