Hello all. Wondering if I could be pointed in the right direction with my
issue.

I have a spreadsheet with the following range(A1:J53). In colums A thru E I
have formulas which populate the cell depending on data thats entered in
column F(these columns are also hidden to the user. In columns F thru J, is
the spreadsheet that the user will use to fill out and enter data. I got a
code on this site that will export the data on the spreadsheet into an Access
db from a command button. The DB has the two date fields.
The issue I'm having is that whenever I click on my command button, I get a
"run time error 3421 Data type conversion error" and the debugger highlights
the first date field on the spreadsheet. However, when I look at my table,
the data was exported. Why would I be getting a run time error even when the
data is still being exported? Is there a way around the error? I tried
chaning the date fields to text in the db table, but when thats done, the
button works great, however it exports all the records in the logs even the
empty ones (I think its due to having formulas in range A1:E53).Any help is
appreciated. Thanks.


Private Sub cmdSEND_Click()
'exports data from the active worksheet to a table in an access db
'this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("U:\ChangeBack Stats\$$$CHANGEBACKDATA\MY_CB_DB.
mdb")
'open the database
Set rs = db.OpenRecordset("tbl_main_log", 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("LOGDATE") = Range("A" & r).Value
.Fields("lognumber") = Range("B" & r).Value
.Fields("logid") = Range("c" & r).Value
.Fields("employeeid") = Range("d" & r).Value
.Fields("cbdate") = Range("e" & r).Value
.Fields("order_rx_number") = Range("f" & r).Value
.Fields("medication") = Range("g" & r).Value
.Fields("conflict") = Range("h" & r).Value
.Fields("saved") = Range("i" & r).Value
.Fields("if_no_why") = Range("j" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Range("a1:j53").ClearContents
MsgBox "Thank You For Your ChangeBack Logs! =)", vbOKOnly
ActiveWorkbook.Close True
Application.Quit

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

Try db.open not db.openrecordset
Set rs = db.Open("tbl_main_log", dbOpenTable)


"AccessUser777 via OfficeKB.com" wrote:

Hello all. Wondering if I could be pointed in the right direction with my
issue.

I have a spreadsheet with the following range(A1:J53). In colums A thru E I
have formulas which populate the cell depending on data thats entered in
column F(these columns are also hidden to the user. In columns F thru J, is
the spreadsheet that the user will use to fill out and enter data. I got a
code on this site that will export the data on the spreadsheet into an Access
db from a command button. The DB has the two date fields.
The issue I'm having is that whenever I click on my command button, I get a
"run time error 3421 Data type conversion error" and the debugger highlights
the first date field on the spreadsheet. However, when I look at my table,
the data was exported. Why would I be getting a run time error even when the
data is still being exported? Is there a way around the error? I tried
chaning the date fields to text in the db table, but when thats done, the
button works great, however it exports all the records in the logs even the
empty ones (I think its due to having formulas in range A1:E53).Any help is
appreciated. Thanks.


Private Sub cmdSEND_Click()
'exports data from the active worksheet to a table in an access db
'this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("U:\ChangeBack Stats\$$$CHANGEBACKDATA\MY_CB_DB.
mdb")
'open the database
Set rs = db.OpenRecordset("tbl_main_log", 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("LOGDATE") = Range("A" & r).Value
.Fields("lognumber") = Range("B" & r).Value
.Fields("logid") = Range("c" & r).Value
.Fields("employeeid") = Range("d" & r).Value
.Fields("cbdate") = Range("e" & r).Value
.Fields("order_rx_number") = Range("f" & r).Value
.Fields("medication") = Range("g" & r).Value
.Fields("conflict") = Range("h" & r).Value
.Fields("saved") = Range("i" & r).Value
.Fields("if_no_why") = Range("j" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Range("a1:j53").ClearContents
MsgBox "Thank You For Your ChangeBack Logs! =)", vbOKOnly
ActiveWorkbook.Close True
Application.Quit

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

.

Mike wrote:
Try db.open not db.openrecordset
Set rs = db.Open("tbl_main_log", dbOpenTable)

Hello all. Wondering if I could be pointed in the right direction with my
issue.
[quoted text clipped - 52 lines]
ActiveWorkbook.Close True
Application.Quit

Mike, thanks for your response. I got it to work by changing the following
line from
Do While Len(Range("A" & r).Formula) 0 to
Do While Len(Range("A" & r).Cells.Value) 0

so now its only looking for any row with values vs an empty cell that
contained formulas

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1