Null ? (Access 97)

Topic

New Reply

I am unable to see the code you are referring to. Please post the code.

Reply | Quote

Replies

Hi All,

Below is code I use to populate a table.

There will be times when 1 or more of the specified fields will be Null….however when the code runs, it won’t accept a Null. I think I need to put a Nz somewhere, but not sure…could someone help me with this please.

Dim DB As Database
Dim rsLog As Recordset
Dim strProEIN As String
Dim strProName As String
Dim strProSSN As String
Dim strType As String
Dim strPPA As String
Dim strProJoin As String

strProEIN = Forms!frmPhyChanges!EINID
strProName = Forms!frmPhyChanges!ProviderName
strProSSN = Forms!frmPhyChanges!SSNID
strType = Forms!frmPhyChanges!Type
strPPA = Forms!frmPhyChanges!ProPlanArea
strProJoin = Forms!frmPhyChanges!Joined

Set DB = CurrentDb()
Set rsLog = DB.OpenRecordset(“tblRepFollowUp”)
With rsLog
.AddNew
.Fields(“ProName”) = strProName
.Fields(“ProEIN”) = strProEIN
.Fields(“ProSSN”) = strProSSN
.Fields(“ProJoin”) = strProJoin
.Fields(“ProType”) = strType
.Fields(“ProPlanArea”) = strPPA

.Update
.Close
End With
Set rsLog = Nothing
Set DB = Nothing

Thanks

Reply | Quote

You can deal with this problem in two ways…
The easy alternative is to add ‘On Error Resume Next’ immediately after all you dim statements.
The other alternative is to check each of your str variables for null within your addnew statements…

Like
With rsLog
.AddNew
If Not Forms!frmPhyChanges!ProviderName.Value & “” = “” Then
.Fields(“ProName”) = strProName
end if
Let me know if it still doesn’t work

Reply | Quote

Roberta,
you can simplify your code as follow:

Dim DB As Database
Dim rsLog As Recordset

Set DB = CurrentDb()
Set rsLog = DB.OpenRecordset("tblRepFollowUp")

With rsLog
  .AddNew
  .Fields("ProName") =  Nz(Forms!frmPhyChanges!ProviderName,"")
  .Fields("ProEIN") =  Nz(Forms!frmPhyChanges!ProviderName,"")
  .Fields("ProSSN") = Nz(Forms!frmPhyChanges!SSNID,"")
  .Fields("ProJoin") = Nz(Forms!frmPhyChanges!Joined,"")
  .Fields("ProType") = Nz(Forms!frmPhyChanges!Type,"")
  .Fields("ProPlanArea") = Nz(Forms!frmPhyChanges!ProPlanArea,"")
  .Update
  .Close
End With

Set rsLog = Nothing
Set DB = Nothing

Replace the “” with the default value you want to enter if the form control is null.

Reply | Quote

Francois,

Thank you very much…just what I was looking for.

Reply | Quote