UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run Time Error 2146 Application Or Object Defined Error, Access 2013    
 
   
catbeasy
post Dec 22 2017, 02:44 PM
Post#1



Posts: 471
Joined: 14-March 05



I have some code as below that it sticking on one part giving the error 2146 in title..the code is designed to allow for either additions or deletions from argument selected listboxes; so user selects some values from listbox and depending on the argument choice, will either add or delete those values from a table..It grabs values to add from one list box in a form and deletes them from another listbox in the same form via a macro button. The called code from the macro button is in a module.

See bottom of thread for module code..(called code at the very end..)


It is err'ing on passing the control variable to the called module function: fcnChkSpecListEntry(ctlListBox As Control)

so, on this code part:

'verify a selection has been made..

'If fcnChkSpecListEntry(frm.lstSpecAvail) = True Then

If fcnChkSpecListEntry(frm.ctlListBox) = True Then

The 2nd line gives the error, it actually shows the value of the what was selected in the listbox. The rem'd out first line, however, gives and passes the value of the control appropriately as, I'm assuming, I have explicitly stated it as a control object? I was thinking that on the first line, the lstSpecAvail takes on the value of an object(?), which is correct. However the 2nd line, ctlListBox takes on the value of whatever is in the list box, obviously not an object. So how do I pass it as an object? Or do I need to do something else?


The initial calling code is from a form and is:
Call ListBoxAction(Me.lstSpecSel, "DEL", "tbl_template_specialty", "frmTemplateSpecialty")

So, this passes the control via 'me.lstSpecSel', which I assume is passing a control object? Yet when the variable storing it (ctlListBox) gets to evaluating it per the called function, it doesn't seem as if its a control object anymore, but the value of the listbox!

Thanks for any help..



=======================================================================

CODE
Sub ListBoxAction(ctlListBox As Control, strQryAction As String, strTblAffctd As String, strFrm As String)
'below code will add or delete records from argument table (strTblAffctd) based on user selection
'of values from the argument listbox (ctlListBox)

Dim lngTemplateIDAccess As Long
Dim frm As Form
Dim strSQL As String
Dim db As DAO.Database
Dim varItemSel As Variant
Dim strSpecDesc As String
Dim intListBoxID As Integer
Dim strTbl As String
Dim strFldsToInsert As String
Dim lngTemplateIDAcc As Long

Set frm = Forms(strFrm)
lngTemplateIDAcc = fcnGetTemplateIDAccess
Set db = CurrentDb


'set the fields you wish to be inserted into in the below select case statement..
'will be based off the listbox values that are desired to be inserted..
'e.g. if want to add measures, then probably the Case is will be:
'Case is = "lstMeasures" and strFldsToInsert: id_templateproject_access, id_measure..??


Select Case ctlListBox.Name
    Case Is = "lstSpecAvail"
        strFldsToInsert = " (id_templateproject_access, id_specialty)"
End Select


'verify a selection has been made..
'If fcnChkSpecListEntry(frm.lstSpecAvail) = True Then

If fcnChkSpecListEntry(frm.ctlListBox) = True Then


    'code here to populate specialty lists
    For Each varItemSel In ctlListBox.ItemsSelected
        strSpecDesc = strSpecDesc & ctlListBox.Column(1, varItemSel) & vbCrLf
        intListBoxID = ctlListBox.Column(0, varItemSel)
        
        If strQryAction = "ADD" Then
            strSQL = "insert into " & strTblAffctd & strFldsToInsert _
            & "select " & lngTemplateIDAcc & ", " & intListBoxID
        ElseIf strQryAction = "DEL" Then
            'code here for delete
            strSQL = "delete from " & strTblAffctd & " where id_templateproject_access =" & lngTemplateIDAcc & " and id_specialty in (" & intListBoxID & ")"
        End If
        
        db.Execute strSQL, dbSeeChanges
        
     Next varItemSel
        
        
Else
    MsgBox "You have not made a list box selection yet. Please make a selection."
End If

MsgBox strSpecDesc


End Sub


Function fcnChkSpecListEntry(ctlListBox As Control)

Dim varItem As Variant
Dim strCriteria As String

For Each varItem In ctlListBox.ItemsSelected
    strCriteria = strCriteria & "," & ctlListBox.ItemData(varItem)
Next varItem
  
If Len(strCriteria) = 0 Then
    fcnChkSpecListEntry = False
Else
    fcnChkSpecListEntry = True
End If

End Function
Go to the top of the page
 
theDBguy
post Dec 22 2017, 03:20 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You said the following line in your function is causing an error:

If fcnChkSpecListEntry(frm.ctlListBox) = True Then

Your function was declared as follows:

Sub ListBoxAction(ctlListBox As Control, strQryAction As String, strTblAffctd As String, strFrm As String)

So, my question is, in the problem line:

If fcnChkSpecListEntry(frm.ctlListBox) = True Then

What does "frm." means in (frm.ctlListBox)?
Go to the top of the page
 
catbeasy
post Dec 22 2017, 04:05 PM
Post#3



Posts: 471
Joined: 14-March 05



frm refers to the form where the control (listbox) resides..and is set by the original calling sub:

Call ListBoxAction(Me.lstSpecSel, "DEL", "tbl_template_specialty", "frmTemplateSpecialty")

(per called sub: ListBoxAction(ctlListBox As Control, strQryAction As String, strTblAffctd As String, strFrm As String)

So, the idea is to define the listbox and the form that its in.

Here's the line at the top of my code that defines the form, pulling the form name from the calling sub..
Set frm = Forms(strFrm)


So, my thinking is that the line:

fcnChkSpecListEntry(frm.ctlListBox) = True Then

should be something like (form.control): frmTemplateSpecialty.lstSpecSel

which its supposed to pass to this function to ensure that the user selected a value from this listbox:

fcnChkSpecListEntry(ctlListBox As Control)

Note, that, as I mentioned, this does work when I explicitly define the listbox, so using frm.lstSpecSel as the fcnChkSpecListEntry argument in place of frm.cltListBox.

Thanks!
This post has been edited by catbeasy: Dec 22 2017, 04:06 PM
Go to the top of the page
 
pdanes
post Dec 25 2017, 07:49 AM
Post#4



Posts: 81
Joined: 19-June 10



Looks to me like you may be running into a collision between variable names and control names.

You have the expression

CODE
fcnChkSpecListEntry(frm.ctlListBox)


which should refer to a form whose name is contained in the string variable strForm, set by this line:

CODE
Set frm = Forms(strFrm)


and on that form, a control named "ctlListBox". However, you also have that same set of characters, "ctlListBox" defined as the first parameter to the routine ListBoxAction. You can't use the variable named as a parameter (or any variable, for that matter, unless that variable is part of an object's class structure) in the dot notation of an object reference - variable names and control names are entirely different animals.

I think what you need is something like this (warning - untested aircode):

CODE
fcnChkSpecListEntry(frm.Controls(ctlListBox.Name))
Go to the top of the page
 
catbeasy
post Dec 28 2017, 02:04 PM
Post#5



Posts: 471
Joined: 14-March 05



Hi Pdanes, yeah, that pretty much solved it..thanks for your help!
Go to the top of the page
 
pdanes
post Dec 29 2017, 11:46 AM
Post#6



Posts: 81
Joined: 19-June 10



Great - you're welcome. Glad I nailed it.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 09:22 AM