Problem with transferring date

[nominoo]

I'm using a code to transfer data from a user form in excel to an access data base.

with rs
.addnew
.field ("status") =combobox1.value

All data tranfers when an entry is made in the combo/text box however a user may leave them blank. This then throws up a runtime error, can anybody help please.

Regards

nominoo

[Stewart Ross]

Hi Nominoo, and Welcome to Bytes!

What run-time error are you getting? Is it an Excel run-time error or an Access one?

It could be that your Access table's Status field is set not to allow null values (values which indicate that an entry has not been made yet). If this was the case, you could try using the Nz function to return a non-null value to your Status field. If Status is a text field (you don't say what type it is) then you could use Nz as follows:

.field("status") = Nz(Combobox1)

(The .value property does not normally have to be stated explicitly, as it is the default property returned when you refer to a control.)

If your Status field is a number, then Nz can be used to return 0 if a null is encountered. like this:

.field("status") = Nz(Combobox1, 0)

If it turns out that the null value in your combo box is not the issue here it would really help to know what the run-time error code and text is and which application is reporting it.

-Stewart

[nominoo]

Hi Stweart

Thanks for reply, I am really new to doing this kind of thing and I'm really struggling at the mo.

I have set up a user form in excel, which has a number of combo boxes and textboxes which allows a user to input data. Some of the text boxes link to formulas in cells on the spreadsheet and show there values(it has just dawned on me that this maybe the problem).

The user doesn't have to complete all of the boxes so there maybe combo boxes text boxes left blank.

I then have a command button which is pressed to submit the data to an access database. If all combo boxes and textboxes have an entry in them the data is tranferred no problems, however if they don't have an entry I am receiving this error.

run-time error '-2147352571 (80020005)' type mis match (in excel)
Here is a condensed version it:

Private Sub CommandButton2_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\filepath.mdb"
Set rs = New ADODB.Recordset
rs.Open "Main", cn, adOpenKeyset, adLockOptimistic

With rs


.AddNew

.Fields("Status") = ComboBox1.Value
.Fields("RRR") = ComboBox46.Value
.Fields("RRS") = ComboBox52.Value
.Fields("SRR") = ComboBox47.Value
.Fields("SRS") = ComboBox53.Value
.Fields("WSR") = ComboBox48.Value
.Fields("WSS") = ComboBox108.Value
.Fields("WPR") = ComboBox110.Value
.Fields("WPS") = ComboBox112.Value
.Fields("WER") = ComboBox49.Value
.Fields("WES") = ComboBox54.Value

.Update
End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing



MsgBox ("Your data has been sent")
UserForm_Initialize
End Sub

[Stewart Ross]

I'd certainly try the Nz approach to see if this resolves the problem.

You would also benefit from setting a break point at your .Addnew statement and stepping through one line at a time to see if it fails at one particular blank combo, and to be able to test values using the immediate window.

There is an introductory article (one of a series) in our Insights section entitled Debugging in VBA which I have linked here for you, as you may well find it useful to know how to perform simple debugging steps.

-Stewart