Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Private Function (A2k)

    Sorry I know I've posted three times today, but it's ages since I've been able to sit with the database.

    I'm getting "Argument Not Optional" when including "PartExists" on a command buttons click event.
    I've referenced and checked the Command buttons Tag value is correct,
    but I don't think I have referenced the command buttons correctly.

    <pre>Private Sub cmdOQP_Click()
    Me.Tag = "OQP"
    PartExists '"Argument Not Optional"
    Me.LstGraphics.AddItem "OQP;O/S Qtr Panel"
    MsgBox Me.Tag ' this shows the correct string value "OQP", So I know the Tag is correct
    End Sub</pre>


    <pre>Private Function PartExists(cancel As Integer, frm As Form)
    Dim PartCheck As Integer
    Dim strWhere As String
    Dim ctl As Control

    strWhere = "EstimateNo = Forms!frmEstimateDetails!EstimateNo And _
    Supp = Forms!frmEstimateDetails!Supp"

    PartCheck = DCount("*", "tblEstimateDetails", "Code=" & Chr(34) & Me.Tag & Chr(34) & _
    " And " & strWhere)

    'For Each ctl In frm.Controls
    Select Case ctl
    Case acCommandButton
    ' No need to check for duplicates if code is "UN"
    If Me.Tag = "UN" Then Exit Function
    ' Check for duplicates
    If PartCheck > 0 Then
    MsgBox "This Part " & Me.Tag & " Already Exists For This Estimate", vbCritical, "Duplicate Part"
    cancel = True
    End If
    End Select
    End Function</pre>


  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Private Function (A2k)

    Hi Dave... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I'm really confused with what you're trying to do with this code... but I believe your very first problem is that the function requires two arguments to be sent over when it's called and you're not sending any? ....and the idea of using a function is so that you can return a value and you're not doing that either... ?

    What are you trying to do with this code? Is that function in the form module (the form with the cmd button) or a separate module?... Has it worked for your purposes in the past?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Private Function (A2k)

    "Argument not optional" means exactly that. If you call the function PartExists as it is written, you have to pass both a Cancel value and a form object into the function, since those arguments are declared in the function using the Optional keyword. Cancel doesn't seeem to have any purpose in this function, since nothing is being done with it in cmdOOP_Click. As written, you're passing the values ByRef, so setting Cancel = True in PartExists will make that value available in the calling routine, but it won't do anything in PartExists.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Private Function (A2k)

    In another instace of the database, I have the following code which checks to see if a part
    already exists for an item, EstimateNo & Supp.:

    <pre>Private Sub cboCode_BeforeUpdate(cancel As Integer)
    Dim PartCheck As Integer
    Dim strWhere As String
    ' No need to check for duplicates if code is "UN"
    If Me.cboCode = "UN" Then Exit Sub
    ' Check for duplicates
    strWhere = "EstimateNo = Forms!frmEstimateDetails!EstimateNo And _
    Supp = Forms!frmEstimateDetails!Supp"

    PartCheck = DCount("*", "tblParts", "Code=" & Chr(34) & Me!cboCode & Chr(34) & _
    " And " & strWhere)

    If PartCheck > 0 Then
    MsgBox "This Part " & [cboCode] & " Already Exists For This Estimate", vbCritical, "Duplicate Part"
    cancel = True
    End If
    End Sub</pre>


    Seeing as this works in one instance (One control, Other Form),
    I needed to create a private function for another form.
    The code is from the forms module.
    The reason to use it is there will be many command buttons which all need to
    call this function before allowing the part (Code) to be transfered.
    In short, When the Command button is clicked, the Tag value is set,
    (Me.Tag = "OQP") and the "PartExists" function is called to check if the part already exists for this record.

    My problem was how to reference the command buttons, ie "Dim Ctl as Control" etc

    I know what I want it to do, but the syntax stumps me.

    I have no problem with form design, I'm sure many have seen examples of my creations.
    Admittidly I try to make Access jump through hoops of fire, but am still amazed with its capabilities.

    My only downfall, which I am still learning slowly, is VBA.
    One day, maybe I'll get there.

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

    Re: Private Function (A2k)

    Dave,

    I would do it like this: the function PartExists takes the code for the part (e.g. "OQP") as argument, and returns a boolean: True if the part already occurs for the current EstimateNo and Supp, False otherwise.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Function PartExists(strPart As String) As Boolean
    Dim PartCheck As Integer
    Dim strWhere As String

    ' Default return value
    PartExists = False

    ' No need to check for duplicates if code is "UN"
    If strPart = "UN" Then Exit Function

    strWhere = "EstimateNo = Forms!frmEstimateDetails!EstimateNo And " & _
    "Supp = Forms!frmEstimateDetails!Supp"

    PartCheck = DCount("*", "tblEstimateDetails", _
    "Code=" & Chr(34) & strPart & Chr(34) & " And " & strWhere)

    ' Check for duplicates
    If PartCheck > 0 Then
    MsgBox "This Part " & strPart & " Already Exists For This Estimate", _
    vbCritical, "Duplicate Part"
    PartExists = True
    End If
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    In the On Click code for the command button, pass the code to the function, and execute AddItem only if the function returns False:

    Private Sub cmdOQP_Click()
    If PartExists("OQP") = False Then
    Me.LstGraphics.AddItem "OQP;O/S Qtr Panel"
    End If
    End Sub

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Private Function (A2k)

    Amazing.

    Thanks for the help once more. I really was off the track by the look of things wasn't I ?
    With a few minor tweaks this will do just fine.

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

Posting Permissions

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