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???

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