Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chris
 
Posts: n/a
Default populating data from excel into access

I have a excel user form that collects data and then drops it into an access
table. However if the user doesn't complete a field or leaves a field blank
then the code comes back with an error.



Dim db As DAO.Database
Set db = DAO.OpenDatabase("accessdatabase.mdb")
' Open the table
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblFOI")

' Write the data
rs.AddNew
rs!area = area_cbo.Text
rs!Officer = officer_txt.Text
rs!tel = tel_txt.Text
rs!Type = type_cbo.Text
rs!firstname = firstname_txt.Text
rs!lastname = lastname_txt.Text
rs!street = street_txt.Text
rs!town = town_txt.Text
rs!county = county_txt.Text
rs!postcode = postcode_txt.Text
rs!apptel = apptel_txt.Text
rs!fax = fax_txt.Text
rs!email = email_txt.Text
rs!Method = method_cbo.Text
rs!request = request_cbo.Text
rs!Description = description_txt.Text
rs!payment = payment_txt.Text
rs!methodreceived = methodreceived_cbo.Text
rs!datetohq = datetohq_txt.Text
rs!target = target_txt.Text
rs!sent = sent_txt.Text
rs!reasons = reasons_txt.Text


' Update the table
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Any Ideas???

  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

Chris wrote:
if the user doesn't complete a field or leaves a field blank
then the code comes back with an error.


Would help to know what the error was.
I am guessing that you were trying to feed a zero length string into a
table field that is set to not allow zero length strings.

If that is the case and the fields have no default value specified then

' leave Null if no value specified
if area_cbo.Text<"" Then rs!area = area_cbo.Text
if officer_txt.Text<"" Then rs!Officer = officer_txt.Text
'etc

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Charting Data from Excel DavidM Charts and Charting in Excel 4 December 30th 04 03:31 PM
How do I display a data table in an Excel 2002 chart? Dr. Mark F. Charts and Charting in Excel 3 December 29th 04 04:04 PM
multiple or changing colours in a data table on an excel chart fo. Kerri Buxton Charts and Charting in Excel 2 December 23rd 04 08:39 PM
Excel 97 chart opened in Excel 2003 - Source Data problem DHunt Charts and Charting in Excel 0 December 6th 04 09:05 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"