Run-Time error 438 "OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD" 2

[TNN]

I am using a class module as a datasource for ADO. Then am creating binding collections to bind text boxes to records.
All has been going well until with one binding collection I get the above error message. Error occurs on this line of code being the first text box I attempt to bind:

objBindF4Screen.Add txtGROSS_MTD, "text", "GROSS_MTD"

Can anyone suggest where to look for the problem???? I did all my other binding collections in the same manner and they work ok. The only difference is the SQL in the stored procedure.

Thank You Anyone
TNN, Tom
TNPAYROLL@AOL.COM





TOM

http://WWW.TNPAYROLL@AOL.COM

 

[swilliams]

Do you have an Add method of the class module?? If you don't, create one. If you do, post the code so that I can take a look.

Simon

 

[TNN]

Hi Simon,
I don't know what the "ADD method of the class module" is and don't know how to create one. I do have a GetDataMember case as follows:
Case "SELECT FNAME,MIDDLE,LNAME,BASSPERS1.EMPNO,GROSS_MTD,GROSS_QTD,GROSS_YTD,SALRED_MTD," _ & "SALRED_QTD,SALRED_YTD,SREDPD_MTD,SREDPD_QTD,SREDPD_YTD,CAFE_MTD,CAFE_QTD,CAFE_YTD," _ & "NEGCHK_MTD,NEGCHK_QTD,NEGCHK_YTD,NEGRED_MTD,NEGRED_QTD,NEGRED_YTD,RTIPS_MTD,RTIPS_QTD," _ & "RTIPS_YTD,OCOMP_MTD,OCOMP_QTD,OCOMP_YTD,MISTTL_MTD,MISTTL_QTD,MISTTL_YTD," _ & "ADVEIC_MTD,ADVEIC_QTD,ADVEIC_YTD,FICA_MTD,FICA_QTD,FICA_YTD,HI_MTD,HI_QTD,HI_YTD," _ & "FIT_MTD,FIT_QTD,FIT_YTD,SIT_MTD,SIT_QTD,SIT_YTD,COUNTY_MTD,COUNTY_QTD,COUNTY_YTD," _ & "MUNI_MTD,MUNI_QTD,MUNI_YTD,UNEM_MTD,UNEM_QTD,UNEM_YTD,SDI_MTD,SDI_QTD, SDI_YTD," _ & "MISC_MTD,MISC_QTD,MISC_YTD,NET_MTD,NET_QTD,NET_YTD" _
& "FROM BASSPERS1 INNER JOIN BASSTAX" _
& "ON BASSPERS1.EMPNO = BASSTAX.EMPNO" _
& "WHERE BASSPERS1.EMPNO = strEmployee_Number"

Set Data = adoRsF4Screen

In the class module I am using the ADO Open statement and referring to a SQL stored procedure. The SQL is per above.

Thank You
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[swilliams]

It looks like you have a class module on which you base the object objBindF4Screen. (ie Dim objBindF4Screen As xxxxx)

If you try and do objBindF4Screen.Add, then the class module must have an Add method/property - similar to doing Label1.Text; a label does not have a Text property and so this will give an error similar to the one you are getting.

Simon

 

[TNN]

Simon,
Your assumptions are correct.
Your saying that the class module must have an Add method/property. Please excuse my ignorence but I have no idea how to go about adding a method or property to a class module. I looked in MSDN library but couldn,t find anything in this regard.
Can you help or point me to some help?
Your patience is appreciated.
Thank You
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[oneshadow]

Hi Tom,
Adding method/function/property to a class module is similar to write your own function.

ie in your class module you write a procedure like this:

Public Sub AddValue(txtBox as Object, sValue as string)
txtBox = sValue
End Sub

If we call it from a form :
Call objBindF4Screen.AddValue(Text1, "Blue&quot
It will put a 'Blue' string in Text1.

My suggestion is the same as Simon's. If you haven't an Add method, try to create one just like the sample above.

Good Luck

 

[TNN]

Hi Oneshadow & Simon,
No offense guys but I think your not understanding.

I tried the create method and it is a no go.

I have three other binding collections working without any method being created.

objBindF4Screen.Add txtGROSS_MTD, "text", "GROSS_MTD"

In the above created binding collection, objBindF4Screen, the .Add method comes up with intellisense--The "text" represents the text property of the text box txtGROSS_MTD--The "GROSS_MTD" represents the field name in the database. The value of the field name is then inserted into the text box txtGROSS_MTD. I have three other binding collections, on other forms, that work perfectly this way and only this one is giving me this error message.
TNN, Tom
TNPAYROLL@AOL.COM




TOM

http://WWW.TNPAYROLL@AOL.COM

 

[swilliams]

Can you post more code - it is hard to troubleshoot something without seeing what is going wrong!!

It would help me if you posted the complete code for a binding that works and how you call the binding and the complete code for the binding that does not work and how you call this (you may have answered the last part of this in previous posts - eg your last post)

Simon

 

[TNN]

Hi Simon,
******Bindingcollection that works********

Private Sub Form_Load()

'Set objSource as object to represent class module.
'This goes and opens all recordsets in class module and
'sets up cross reference that allows this procedure to
'reference any recordset in datasource class module and
'create binding objects with reference to datasource class
'module.
Set objSource = New clsDataSource
Set objBindF1Screen = New BindingCollection
objBindF1Screen.DataMember = "SELECT * FROM BASSPERS
WHERE EMPNO=strEmployee_Number"
Set objBindF1Screen.DataSource = objSource

objBindF1Screen.Add txtFNAME, "text", "FNAME"
objBindF1Screen.Add txtLNAME, "text", "LNAME"
objBindF1Screen.Add txtMIDDLE, "text", "MIDDLE"
objBindF1Screen.Add txtEMPNO, "text", "EMPNO"
'etc.

******Bindingcollection that DOES NOT work******

Private Sub Form_Load()

'Set objSource as object to represent class module.
'This goes and opens all recordsets in class module and
'sets up cross reference that allows this procedure to
'reference any recordset in datasource class module and
'createbinding objects with reference to datasource class
'module.
Set objSource = New clsDataSource
Set objBindF4Screen = New BindingCollection
objBindF4Screen.DataMember = "SELECT FNAME,MIDDLE,LNAME,BASSPERS1.EMPNO,GROSS_MTD,GROSS_QTD,GROSS_YTD,SALRED_MTD," _ & "SALRED_QTD,SALRED_YTD,SREDPD_MTD,SREDPD_QTD,SREDPD_YTD,CAFE_MTD,CAFE_QTD,CAFE_YTD," _ & "NEGCHK_MTD,NEGCHK_QTD,NEGCHK_YTD,NEGRED_MTD,NEGRED_QTD,NEGRED_YTD,RTIPS_MTD,RTIPS_QTD," _ & "RTIPS_YTD,OCOMP_MTD,OCOMP_QTD,OCOMP_YTD,MISTTL_MTD,MISTTL_QTD,MISTTL_YTD," _ & "ADVEIC_MTD,ADVEIC_QTD,ADVEIC_YTD,FICA_MTD,FICA_QTD,FICA_YTD,HI_MTD,HI_QTD,HI_YTD," _ & "FIT_MTD,FIT_QTD,FIT_YTD,SIT_MTD,SIT_QTD,SIT_YTD,COUNTY_MTD,COUNTY_QTD,COUNTY_YTD," _ & "MUNI_MTD,MUNI_QTD,MUNI_YTD,UNEM_MTD,UNEM_QTD,UNEM_YTD,SDI_MTD,SDI_QTD, SDI_YTD," _ & "MISC_MTD,MISC_QTD,MISC_YTD,NET_MTD,NET_QTD,NET_YTD" _
& "FROM BASSPERS1 INNER JOIN BASSTAX" _
& "ON BASSPERS1.EMPNO = BASSTAX.EMPNO" _
& "WHERE BASSPERS1.EMPNO = strEmployee_Number"
Set objBindF4Screen.DataSource = objSource

objBindF4Screen.Add txtGROSS_MTD, "text", "GROSS_MTD"
objBindF4Screen.Add txtGROSS_QTD, "text", "GROSS_QTD"
objBindF4Screen.Add txtGROSS_YTD, "text", "GROSS_YTD"
'etc.

************Class module GetDataMember*************

Public Sub Class_GetDataMember(DataMember As String, Data As Object)

Select Case DataMember
Case "SELECT EMPNO,DEPT,WRATE,MTD_AMT,QTD_AMT,YTD_AMT," _
& " WAGENAME,BASSEARN.WAGENO" _
& " FROM BASSEARN INNER JOIN" _
& " BASSWAGE ON BASSEARN.WAGENO = BASSWAGE.WAGENO" _
& " WHERE EMPNO = strEmployee_Number"
Set Data = adoRsF3Screen
Case "SELECT * FROM BASSPERS WHERE
EMPNO=strEmployee_Number"
Set Data = adoRsF1Screen
Case "SELECTWAGENAME,WAGENO,NEGWAGE,OTHCOMP,RESETRATE,"_
& "VACATION,TIPPEDWAGE,SAL_HOUR,REPTTIPS,CAFEPLAN " _
& "FROM BASSWAGE"
Set Data = adoRsF3ScreenFindWage
Case "SELECT * FROM BASSEARN WHERE EMPNO=1066"
Set Data = adoRsF3ScreenAddWage
Case "SELECT DEDNAME,BASSDEDS.DEDNO,DEPT,TYPE,AMOUNT," _
& " MTD_AMT,QTD_AMT,YTD_AMT" _
& " FROM BASSDEDS INNER JOIN" _
& " BASSDDED ON BASSDEDS.DEDNO = BASSDDED.DEDNO" _
& " WHERE EMPNO = strEmployee_Number"
Set Data = adoRsF2Screen
Case Case "SELECT FNAME,MIDDLE,LNAME,BASSPERS1.EMPNO,GROSS_MTD,GROSS_QTD,GROSS_YTD,SALRED_MTD," _ & "SALRED_QTD,SALRED_YTD,SREDPD_MTD,SREDPD_QTD,SREDPD_YTD,CAFE_MTD,CAFE_QTD,CAFE_YTD," _ & "NEGCHK_MTD,NEGCHK_QTD,NEGCHK_YTD,NEGRED_MTD,NEGRED_QTD,NEGRED_YTD,RTIPS_MTD,RTIPS_QTD," _ & "RTIPS_YTD,OCOMP_MTD,OCOMP_QTD,OCOMP_YTD,MISTTL_MTD,MISTTL_QTD,MISTTL_YTD," _ & "ADVEIC_MTD,ADVEIC_QTD,ADVEIC_YTD,FICA_MTD,FICA_QTD,FICA_YTD,HI_MTD,HI_QTD,HI_YTD," _ & "FIT_MTD,FIT_QTD,FIT_YTD,SIT_MTD,SIT_QTD,SIT_YTD,COUNTY_MTD,COUNTY_QTD,COUNTY_YTD," _ & "MUNI_MTD,MUNI_QTD,MUNI_YTD,UNEM_MTD,UNEM_QTD,UNEM_YTD,SDI_MTD,SDI_QTD, SDI_YTD," _ & "MISC_MTD,MISC_QTD,MISC_YTD,NET_MTD,NET_QTD,NET_YTD" _
& "FROM BASSPERS1 INNER JOIN BASSTAX" _
& "ON BASSPERS1.EMPNO = BASSTAX.EMPNO" _
& "WHERE BASSPERS1.EMPNO = strEmployee_Number"
Set Data = adoRsF4Screen

End Select

End Sub

******Class module initialize************

Private Sub Class_Initialize()
Dim MyProperty As Property
Dim see As Boolean
Dim strSQLF1 As String
Dim strSQLF2 As String
Dim strSQLF3 As String
Dim strSQLF4 As String
'Instantiate here. Declared in General Declarations this module.
Set cmd = New ADODB.Command
Set adoRsF1Screen = New ADODB.Recordset
Set adoRsF2Screen = New ADODB.Recordset
Set adoRsF2ScreenFindDed = New ADODB.Recordset
Set adoRsF3Screen = New ADODB.Recordset
Set adoRsF3ScreenAddWage = New ADODB.Recordset
Set adoRsF3ScreenFindWage = New ADODB.Recordset
Set adoRsF4Screen = New ADODB.Recordset
Set adoConnection = New ADODB.Connection
Set adoConnection2 = New ADODB.Connection
'Load string variable and use string in open method of adoConnection.
strConnect = "Provider=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\BEMPLOYE.MDB"
adoConnection.Open (strConnect)
adoConnection2.Open (strConnect)
'Strings to hold EXECUTE statements for stored SQL procedures to be used
'in Recordset open statements.
strSQLF1 = "EXECUTE DisplayF1 " & (strEmployee_Number)
strSQLF2 = "EXECUTE DisplayF2 " & (strEmployee_Number)
strSQLF3 = "EXECUTE DisplayF3 " & (strEmployee_Number)
strSQLF4 = "EXECUTE DisplayF4 " & (strEmployee_Number)

'Open recordset
'This recordset used to load text boxes onto F1Screen with employee information.
'Used in Form Load event of F1Screen.
adoRsF1Screen.CursorType = adOpenStatic
adoRsF1Screen.LockType = adLockOptimistic
adoRsF1Screen.Open Source:=strSQLF1, ActiveConnection:=adoConnection, _
Options:=adCmdText



'Open recordset
'This recordset used to load text boxes onto F2Screen with employee information.
'Used in Form Load event of F2Screen.
adoRsF2Screen.CursorLocation = adUseClient
adoRsF2Screen.CursorType = adOpenStatic
adoRsF2Screen.LockType = adLockOptimistic
adoRsF2Screen.Open Source:=strSQLF2, ActiveConnection:=adoConnection, _
Options:=adCmdText

'adoConnection.CursorLocation = adUseClient
'adoRsF2Screen.CursorType = adOpenStatic
'adoRsF2Screen.LockType = adLockOptimistic
'cmd.ActiveConnection = adoConnection
'cmd.CommandText = strSQLF2
'cmd.CommandType = adCmdText
'adoRsF2Screen.CursorLocation = adUseClient
'Set adoRsF2Screen = cmd.Execute






'Open recordset.
'This recordset used in SelectWage procedure to search BASSDDED table, with Find Method,
'for the wage description(DEDNAME) beginning with a letter that the user keys.
adoRsF2ScreenFindDed.CursorType = adOpenStatic
adoRsF2ScreenFindDed.LockType = adLockOptimistic
adoRsF2ScreenFindDed.Open Source:="SELECT * FROM BASSDDED", _
ActiveConnection:=adoConnection, _
Options:=adCmdText


'Open recordset.
'Used to load text into text boxes on F3Screen in form load event.
adoRsF3Screen.CursorLocation = adUseClient
adoRsF3Screen.CursorType = adOpenStatic
adoRsF3Screen.LockType = adLockOptimistic
adoRsF3Screen.Open Source:=strSQLF3, ActiveConnection:=adoConnection, _
Options:=adCmdText


'Open recordset.
'This recordset used in SelectWage procedure to search BASSWAGE table, with Find Method,
'for the wage description(WAGENAME) beginning with a letter that the user keys.
adoRsF3ScreenFindWage.CursorType = adOpenStatic
adoRsF3ScreenFindWage.LockType = adLockOptimistic
adoRsF3ScreenFindWage.Open Source:="SELECT WAGENAME,WAGENO,NEGWAGE,OTHCOMP,RESETRATE," _
& "VACATION,TIPPEDWAGE,SAL_HOUR,REPTTIPS,CAFEPLAN " _
& "FROM BASSWAGE", ActiveConnection:=adoConnection, _
Options:=adCmdText


'Open recordset.
'Use this recordset to add new wage to BASSWAGE table.
adoRsF3ScreenAddWage.CursorType = adOpenStatic
adoRsF3ScreenAddWage.LockType = adLockOptimistic
adoRsF3ScreenAddWage.Open Source:="SELECT * FROM BASSWAGE", _
ActiveConnection:=adoConnection, _
Options:=adCmdText


'Open recordset.
'Use this recordset to add new wage to BASSWAGE table.
adoRsF4Screen.CursorType = adOpenStatic
adoRsF4Screen.LockType = adLockOptimistic
adoRsF4Screen.Open Source:=strSQLF4, _
ActiveConnection:=adoConnection, _
Options:=adCmdText

End Sub

The End
Thanks TNN, Tom
TNPAYROLL@AOL.COM




TOM

http://WWW.TNPAYROLL@AOL.COM

 

[swilliams]

In the form that does not work, check that the sql statement that you use in the line
objBindF4Screen.DataMember = ...
is correct (put this sql statement directly into an Access query and see if it runs without error.

It looks to me as if it will not, as there needs to be a space between the last field selected and the word FROM

Simon

 

[TNN]

Hi Simon,
I pasted SQL from objBindF4Screen.DataMember into Access and statement works fine without any space after last field.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

Hi Simon,
FOUND MY PROBLEM BUT DON'T UNDERSTAND IT, WEIRD!!!
The problem is my text boxes. Get this. It looks like vb in code is looking at my text box as an object rather than a control. In the form load event when I do a txtbox. the intellisense comes up with the following selections: count, item,lbound, ubound. Now when I press F4 on the form to bring up the properties for the textbox all the properties for a textbox come up.
I replaced a text box with a new one and the binding works ok.
I dropped those text boxes on the form from a dataenvironment and then edited out the dataenvironment in the textbox properties to use the binding object instead. Maybe that caused the problem. There is nothing in the textbox properties different from the new txtbox I replaced it with.
Any light shedded is appreciated.
Thank you for all your attention. Nice talking with you.
TNN, Tom
TNPAYROLL@AOL.COM


TOM

http://WWW.TNPAYROLL@AOL.COM

 

[swilliams]

When you get the intellisense options Count, Item, LBound and UBound it means that you have a Control Array - ie more than one text box of the same name, with different Index properties.

This being said / found, your code would probably have worked before if you replaced txtBox (or whatever the name of the control was) with txtBox(0), assuming that it had an index value of 0.

Simon

 

[Snaggs]

Actually if you look at the objects you have defined, you most likely have a collection named as a textbox. You probably had something selected that you didn't think was selected and you changed the name of it. From the Properties window, find the textbox by its name, that is giving you problems and look right after it to see what type of class it is. I think you'll find it's not a TextBox class. You'll have to hunt down the error from there and figure out what got its name changed. Snaggs
tribesaddict@swbell.net

 

[TNN]

Hi Snaggs, Simon and everyone that tried to help.

I have to fess up to my stupidity. Your right Snaggs. I was failing to include the index of the text box in the binding statement:

objBindF4Screen.Add txtGROSS_MTD(0), "text", "GROSS_MTD"

Thereby not having the correct name of the text box entered. As it is I do not need the indexes and have removed them. All works well.

Thank You for your input and sorry to waste your time on a dumb mistake.
TNN, Tom
TNPAYROLL@AOL.COM




TOM

http://WWW.TNPAYROLL@AOL.COM