Results 1 to 9 of 9
  1. #1
    Coasterman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    8

    Talking Pass form name as argumentto a Function

    I am modifying a combobox notInList Function I came across

    The comboboxes are cascading so I also need to write the value of the co-dependent combo to the underlying table at the same time so they remain in sync.

    I have the code working when I reference the form (and control) by name but as I would like to be able to re-use the function in other forms it would be very handy if I could pass the form name as an argument

    my current working line of code in the function is

    Code:
    rst(strLinked) = Forms!frm_CombinedData.cboSubCat_FK
    and the sub from the notinList event is



    Code:
    Private Sub finalCat_FK_NotInList(NewData As String, Response As Integer)
    Response = AddNewToList(NewData, "tbl_finalCat", "finalcat", "Final Catergories", "subcat_FK")
    End Sub
    I thought as first I should pass the argument as a String but either thats wrong (or I have the syntax wrong) I also tried As Access.form but again no joy?

    I perhaps should mention this is a small database I am writing for my sole use so hopefully not to much risk of the users (Me) adding too much new nonsense to the underlying lists

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Coasterman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    8
    Hi

    In my attempt to keep the post shorter I have perhaps miscommunicated what I am having trouble with.

    The Function I am modifying is this

    Code:
    Public Function AddNewToList(NewData As String, stTable As String, _                                   stFieldName As String, strPlural As String, strLinked As String, _
                                       Optional strNewForm As String) As Integer
    On Error GoTo err_proc
        'Adds a new record to a drop down box list
        'If form name passed, then open this form to the newly created record
    
    
        'Declare variables
        Dim rst As DAO.Recordset
        Dim IntNewID As Long
        Dim strPKField As String
        Dim strMessage As String
      
        
      
        
        ' Display message box asking if user wants to add the new item
        strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                     "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                     "(Please check the entry before proceeding)."
    
    
        If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
            Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
            rst.AddNew
                rst(stFieldName) = NewData                'Add new data from combo box
                rst(strLinked) = Forms!frm_CombinedData.cboSubCat_FK  'How do I pass the name of the current form as an argument??
                strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
            rst.Update
            rst.Move 0, rst.LastModified
            IntNewID = rst(strPKField)
    
    
            'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
            If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog
      
            AddNewToList = acDataErrAdded                'Set response 'Data added'
        Else
            AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
        End If
        
    exit_proc:
    I added
    Code:
    rst(strLinked) = Forms!frm_CombinedData.cboSubCat_FK
    to write the current ID value of the combobox one level above to the FK of the table proving the Row Source values for the cboBox I am calling the NotInList event from.

    strLinked variable = the current ID value in "cbosubcat_FK" and is 2nd of 3 cascading comboxes. All comboboxes are bound controls in my form.

    Everything is working but I would like the option to re-use the function from other forms where I have other cascading combo boxes. Obviously I need to change the reference to the Form and cboControl name and was hoping to do this by passing an argument (well two actually the Form name and the cboxControl Name ) from the NotInList event of which ever combobox I am working with.

    I could probably have dispensed with any mention of the fact this part of a NotinList function but wasn't sure whether there were differences with the arguments permitted for a NotinList event compared to say 'onCurrent' - I don't suppose there is but wanted it known this was the event type I was calling the Function from just in case it made any difference?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you pass forms and controls as object parameters

    Code:
    function myFunction(frm as form, ctl as Control)
    
    debug.print frm.name
    debug.print ctl.name
    
    end function
    in fact you probably only need to pass the control since you can determine the form as the control parent

    Code:
    function myFunction2(ctl as Control)
    
    debug.print ctl.parent.name
    debug.print ctl.name
    
    end function

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Unless you want to modify or otherwise access the form, I would pass its name rather than the whole object, as in

    Public Function DoStuff (frmName As String, whatever As Whatever) As Whatever

    and call as DoStuff(Me.Name, whatever)

    The "whatevers" are because I don't know what else might be passed, or what, if anything, the function is supposed to return. If it is not supposed to return anything it may as well be a sub.
    EDIT - maybe

    Public Function DoStuff (frmName As String, ctlName As String) As Whatever
    DoStuff(Me.Name, Me.cboName.Name)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Coasterman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    8
    So I passed the Form and Control name as Strings as suggested and can now debug.print to see both are returning what I need.

    I am just now having difficulty with the final syntax to replace what was

    Code:
    rst(strLinked) = Forms!frm_CombinedData.cboSubCat_FK
    Have tried

    rst(strLinked) = Forms!myform.myContol
    rst(strLinked) = Forms.myform.myControl
    rst(strLinked) = Form.myfrom.myControl

    am getting Form myform not found or type mismatch error

    Have tried to find other example functions where form names are passed but haven't hit on any examples so far

    I have attached a mock up to demostrate how the form is intended to behave
    Attached Files Attached Files

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming the parameters are myform and myContol then it would be

    rst(strLinked) = Forms(myform).controls(myContol)


  8. #8
    Coasterman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    8
    Thank you so much

    This is what is so fustrating about learning - you spend hours trying to force your own logic on something - fail - and then see the answer and think 'oh of course!'

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see what the issue is/was with

    rst(strLinked) = Forms(myform).controls(myContol) or
    Forms!frm_CombinedData.cboSubCat_FK

    as they are just different ways to do the same thing. The former method is slower, according to M$. Either should work AFAIK, with the caveat that the Controls() syntax requires the references to be within quotes unless maybe they are string variables.
    Form myform not found
    That certainly would be the case with "Form.myfrom.myControl " because "Form" is not a valid reference to the Forms collection.
    Maybe your form wasn't open or there was just a teeny spelling mistake? I'm assuming you did use the actual form and control names and not just myForm and myControl - unless those are the real names.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 31
    Last Post: 08-10-2017, 01:26 PM
  2. Pass DAO.Recordset into public function
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 07-07-2014, 12:31 AM
  3. Pass recordset field into function
    By Ruegen in forum Programming
    Replies: 6
    Last Post: 04-13-2014, 11:04 PM
  4. How to Pass ListBox to Function?
    By RedGoneWILD in forum Programming
    Replies: 4
    Last Post: 03-05-2013, 12:13 PM
  5. pass a variable to the MsgBox function
    By 3dmgirl in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 07:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums