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
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

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

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

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

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
Me.Zip.SetFocus
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
Loop
rs.Close
Set rs = Nothing
db.Close
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 = ""

Me.OfficerNm.SetFocus

End Sub

Please Help

Thanks in Advance

User input from excel form not populating Access Database
 

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.

Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"sam" wrote:

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
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

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

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

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

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
Me.Zip.SetFocus
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
Loop
rs.Close
Set rs = Nothing
db.Close
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 = ""

Me.OfficerNm.SetFocus

End Sub

Please Help

Thanks in Advance

User input from excel form not populating Access Database
 

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

Thanks a lot.

"sam" wrote:

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
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

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

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

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

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
Me.Zip.SetFocus
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
Loop
rs.Close
Set rs = Nothing
db.Close
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 = ""

Me.OfficerNm.SetFocus

End Sub

Please Help

Thanks in Advance

User input from excel form not populating Access Database
 

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

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"sam" wrote:

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

Thanks a lot.

"sam" wrote:

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
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

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

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

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

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
Me.Zip.SetFocus
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
Loop
rs.Close
Set rs = Nothing
db.Close
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 = ""

Me.OfficerNm.SetFocus

End Sub

Please Help

Thanks in Advance