Null Values Run time error message

[GirlBlonde]

Hi I have a code i am working on, i am just saving some data to a table and some of the fields maybe blank so i am getting a message 'run time error 3421 Data type conversion error. when i go into the code it says that there are null values. there will be some null values at times in this particular field so how do i get around this problem? i have used this code on many occassions and never come up with this issue. if someone could lend a hand i am not to cluey on VB. My code is below. Not that it probably makes a difference but it's the staff number field.thanks

Dim MyDb As Database, MySet As Recordset
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("Tbl_FBTData", DB_OPEN_DYNASET)
Dim frmname As String

frmname = "frm_drivers"


If Me.TotDriverPercentage < 1 Then 'if not 100% bring up message and go from there

intAnswer = MsgBox(" Your total driver percentage does not equal 100%!", vbRetryCancel, "Please recheck your figures!")

End If

If intAnswer = 2 Then 'cancel go to field total pers km driver 1

DoCmd.GoToControl "bus1"

Else 'ok save data

If Me.I1 = 1 Then

MySet.AddNew

MySet!Date = Date
MySet!VehicleRego = Forms!Frm_Drivers!VehicleRego
MySet!OdometerAprDateFirstUsed = Forms!Frm_Drivers!odometer1
MySet!Odometer31MarDisposalDate = Forms!Frm_Drivers!odometer2
MySet!TotDriverPercentage = Forms!Frm_Drivers!TotDriverPercentage
'bus pers
MySet!TotalKmTravelled = Forms!Frm_Drivers!totals1
MySet!NumDrivers = Forms!Frm_Drivers!number
MySet!grandtotalbuskm = Forms!Frm_Drivers!grandtotalbuskm
MySet!grandtotalperskm = Forms!Frm_Drivers!grandtotalperskm


'Driver 1
MySet!DriverSurname = Forms!Frm_Drivers!Cbodrivers17
MySet!DriverFirstname = Forms!Frm_Drivers!text1
MySet!StaffNo = Forms!Frm_Drivers!No1
MySet!TotBusKmTrav = Forms!Frm_Drivers!Bus1
MySet!TotPrivKmTrav = Forms!Frm_Drivers!Per1

MySet.Update
End If



Energy & persistance conquer all things!

 

[dhookom]

You can try modify your code like:

If Not IsNull(Forms!Frm_Drivers!No1) Then
   MySet!StaffNo = Forms!Frm_Drivers!No1  
End If

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[GirlBlonde]

hi

thanks for that thats helpful little tip for future coding, but it still gives me an error data type conversion. i need it to allow null values to go through the code keeps showing me "" which is nulls. is there something i can add like where null save anyway?

Energy & persistance conquer all things!

 

[PHV]

What about this ?
MySet!StaffNo = Nz(Forms!Frm_Drivers!No1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GirlBlonde]

hi

i'm still getting data type conversion error still showing me the "" when i go into the code error ??

Energy & persistance conquer all things!

 

[GirlBlonde]

hi

just wanted to let you know i sorted the problem out using the below code thanks for all your help it was very helpful.

If Forms!Frm_Drivers!No1.Value = "" Then
MySet!StaffNo = Null
Else
MySet!StaffNo = Forms!Frm_Drivers!No1
End If

Energy & persistance conquer all things!