Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null ? (Access 97)

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

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Null ? (Access 97)

    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
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Lounger
    Join Date
    Jan 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null ? (Access 97)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null ? (Access 97)

    Roberta,
    you can simplify your code as follow:
    <pre>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
    </pre>

    Replace the "" with the default value you want to enter if the form control is null.
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null ? (Access 97)

    Francois,

    Thank you very much...just what I was looking for.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •