null values with ado

[stuartam]

Hi, i have a module like this:

Public Function Savea(VBText157 As String, VBDigitised_by As String, VBTeam As String, VBPRefNumber As String)

' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "TBL_Main_test", cn, adOpenStatic, adLockOptimistic ' open it
.AddNew ' prepare to add a new record
.Fields("Type_DB") = VBText157
.Fields("User_ID") = VBDigitised_by
.Fields("team") = VBTeam
.Fields("Pack_Ref_Number") = VBPRefNumber
.Update ' update the table
.Close ' close the recordset connection
End With

End Function

which i call with a button on my form:

Private Sub Command257_Click()
    Call Savea(Text157, Digitised_by, team, PRefNumber)
End Sub

but when i leave one of the field blank i get an error, this is because its trying to save something when there is nothing in the field to save.

so how can i get it to save just the fields that are populated in my form, i dont rearly want to have to setup default values as this will just waste space in saving null events

best regards

 

[crosmill]

You can take two aproaches, either test for null values and only update the field if it is not null or replace null values with a zero lenth string "".

Hopes this helps

 

[stuartam]

thanks for the quick reply, how do i test for null values, would i write it into the module or into the call procedure.

sorry to be a pain but im not very experienced in vba

best regards

 

[crosmill]

No probs!!

the IsNull() function will return a True or False value (case sensitive).

so..

If IsNull(var1) = "True" then.....

 

[MarkK]

Change this...
Public Function Savea(VBText157 As String, VBDigitised_by As String, VBTeam As String, VBPRefNumber As String)
to this...
Public Function Savea(VBText157 As Variant, VBDigitised_by As Variant, VBTeam As Variant, VBPRefNumber As Variant)
and see what happens. The table should take the nulls, but your function declaration won't let them in.