Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text box problem (2002)

    I've got a problem with some text box controls on a form. I've read through many, many posts on related issues but I can't seem to resolve it.

    I have several text boxes with VBA functions set as there control source.

    txtItmsIsd control source = fncGetIssued(fncGetManifestID(Forms!frmMain!txtMnf stNmbr))

    Function fncGetIssued(ManifestID As Long) As Long

    On Error GoTo ErrHandler
    'done 1/11

    fncGetIssued = DCount("ID", "tblClientExpected", "ManifestID = " & ManifestID & _
    " AND UCase(Dscrptn) <> 'NOT ISSUED'")

    ExitHandler:
    Exit Function


    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Function

    The following function is used to get the primary of the parent table.

    Function fncGetManifestID(strManifestNmbr As String) As Long

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim intManifestID As Long

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Select ManifestNmbr, ID FROM tblManifest " & _
    " ORDER BY ManifestNmbr", dbOpenDynaSet)

    rst.FindFirst "ManifestNmbr = '" & strManifestNmbr & "'"

    If rst.NoMatch Then
    'Insert new record
    With rst
    .AddNew
    !ManifestNmbr = UCase(strManifestNmbr)
    intManifestID = !ID
    .Update
    End With

    Else
    'Return existing ID
    intManifestID = rst.Fields("ID")

    End If

    fncGetManifestID = intManifestID

    rst.Close

    End Function

    This all works find when the txtMnfstNmbr text box is populated. However, when I first open the form txtMnfstNmbr has no value. The txtItmsIsd text box displays #Error when the form is first opened. I'm assuming this is because txtMnfstNmbr is Null or an empty string, but I can't seem to trap the error. Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text box problem (2002)

    Try changing the declaration of the function from

    Function fncGetIssued(ManifestID As Long) As Long

    to

    Function fncGetIssued(ManifestID As Variant) As Long

    A variable of type Long cannot be Null, but a Variant can.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box problem (2002)

    I changed it to Variant, but no luck, it still returns #Error. Any other ideas?

    Function fncGetIssued(ManifestID As Variant) As Long

    On Error GoTo ErrHandler
    'done 1/11

    fncGetIssued = IIf(IsNull(ManifestID), 0, DCount("ID", "tblClientExpected", "ManifestID = " & ManifestID & _
    " AND UCase(Dscrptn) <> 'NOT ISSUED'"))

    ExitHandler:
    Exit Function


    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text box problem (2002)

    What happens if you change the Control Source of the text box to

    =IIf(IsNull([Forms]![frmMain]![txtMnfstNmbr]), 0, DCount("ID", "tblClientExpected", "ManifestID = " & [Forms]![frmMain]![txtMnfstNmbr] & " AND UCase(Dscrptn) <> 'NOT ISSUED'"))

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box problem (2002)

    I swear I tried this earlier, but it seems to work now. Here's what I did:

    =IIf(IsNull(Forms!frmMain!txtMnfstNmbr),0,fncGetIs sued(fncGetManifestID(Forms!frmMain!txtMnfstNmbr)) )

    Why didn't this work inside the function? Just curious...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text box problem (2002)

    That's hard to say without seeing the database...

Posting Permissions

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