| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User input from excel form not populating Access Database
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel form populating in access 2002-03 but not 2007 | Excel Programming | |||
| Data Not populating in Access database from excel form | Excel Programming | |||
| connect excel user form to access database | Excel Programming | |||
| Excel as Input Form for Another Database | Excel Programming | |||
| Error while populating a combobox from Access database | Excel Programming | |||
Linear Mode
