ADO AddNew problem

[andraandjeff]

Hi everyone,

I've got an ADO AddNew update query in VBA. Some of the fields may be
empty and I want to know if there is an easier way than writing If
statements around each field to test for null.

Here's my code

.AddNew ' create a new record
' add values to each field in the record
.Fields("fkRSCWeekID") = Range("AA" & r).Value
.Fields("fkPrimeID") = Range("R" & r).Value
.Fields("fkTypeID") = Range("S" & r).Value

For example, right now if the first field (fkRSCWeekID) is blank it
throws a Type Mismatch error. Can anyone please advise how I can run
the update regardless of whether the field is populated or not?

Thanks in advance,

Jeff

[Brian Puffer]

Jeff,

You should probably set up default values on your table fields. Both
access and sql server support default values. Other than that, I am
not sure there is an easy way. Even using the IIF() function can be
tedious if you have many fields with the potential to be null.

Hope this helps,

Brian
an**********@gmail.com wrote:

Hi everyone,

I've got an ADO AddNew update query in VBA. Some of the fields may be
empty and I want to know if there is an easier way than writing If
statements around each field to test for null.

Here's my code

.AddNew ' create a new record
' add values to each field in the record
.Fields("fkRSCWeekID") = Range("AA" & r).Value
.Fields("fkPrimeID") = Range("R" & r).Value
.Fields("fkTypeID") = Range("S" & r).Value

For example, right now if the first field (fkRSCWeekID) is blank it
throws a Type Mismatch error. Can anyone please advise how I can run
the update regardless of whether the field is populated or not?

Thanks in advance,

Jeff

[chris.nebinger]

.AddNew ' create a new record
' add values to each field in the record
.Fields("fkRSCWeekID") = NZ(Range("AA" & r).Value , "")
.Fields("fkPrimeID") = NZ(Range("R" & r).Value , "")
.Fields("fkTypeID") = NZ(Range("S" & r).Value, "")

Try that and see if it works for you.
Chris Nebinger
an**********@gmail.com wrote:

Hi everyone,

I've got an ADO AddNew update query in VBA. Some of the fields may be
empty and I want to know if there is an easier way than writing If
statements around each field to test for null.

Here's my code

.AddNew ' create a new record
' add values to each field in the record
.Fields("fkRSCWeekID") = Range("AA" & r).Value
.Fields("fkPrimeID") = Range("R" & r).Value
.Fields("fkTypeID") = Range("S" & r).Value

For example, right now if the first field (fkRSCWeekID) is blank it
throws a Type Mismatch error. Can anyone please advise how I can run
the update regardless of whether the field is populated or not?

Thanks in advance,

Jeff