vbaConnect.addControl error

[ThunderForest]

Windows 98
Macola 7.5.103d
Netware 4.11
Netware 3.31 Client
Pervasive SQL 7

New to this forum today. Glad to have found it. After adding a userForm, et al to OE0101, I can no longer execute OE-TRX-Enter Orders (My PC Only), getting 'Error in AddCtls in VBA'. Details show:

VBAConnect.AddControls
Error: 40177
Error Text: Unknown VBA error; error code = 0x800A9CF1.

The error will never close, and you have to end the task. The code, etc. isw okay, as any other PC, except mine, can successully execute the Flex program I added. ActiveX problem? ODBC? Reinstalling Macola, Netware client, Flex, Macola VBA didn't help.

 

[vbajock]

Can you post the code that is associated with the form?

 

[ThunderForest]

Below is the code... but again, this only happens on my PC. The following MACFORM code works just fine on any other PC here. Let me add that before adding the code **on my PC**, I was obviously able to get into **TRX-Enter Orders**. Since then, however, I've never been able to access **TRX-Enter Orders**, getting the aforementioned error. To reiterate, it only happens with my PC. When the O/E screen is a nano-second away from displaying, the error occurs. Compiler problem?
===============
Option Explicit
Public sShipDate As String
Public begShipDate As String

Private Sub DiscPct1_GotFocus()
'Create ERS Objects & other variables
Dim rsIMINVLOC As ERSRecSet
Dim rsIMITMIDX As ERSRecSet
Dim rsEDCTPFL As ERSRecSet
Dim rsEDCITMFL As ERSRecSet
Dim sqlText As String
Dim custVar As String
Dim FoundDup As Integer

Set rsIMINVLOC = New ERSRecSet
Set rsIMITMIDX = New ERSRecSet
Set rsEDCTPFL = New ERSRecSet
Set rsEDCITMFL = New ERSRecSet
Dim rs_Path As String

'Set Macola Path & Connection Strings
rs_Path = "DB=" & macForm.ConnInfo.BtrPath
rsIMINVLOC.ConnectString = "DRIVER={Macola 32-BIT Btrieve (*.dta)}; DFE=BTR;AS=4096;" & rs_Path
rsIMITMIDX.ConnectString = "DRIVER={Macola 32-BIT Btrieve (*.dta)}; DFE=BTR;AS=4096;" & rs_Path
'Determine if Item is set to non-active
rsIMITMIDX.Open ("Select Item_No, Activity_Cd from IMITMIDX_SQL where IMITMIDX_SQL.Item_No = '" & macForm.ItemNo.Text & "'&quot
If rsIMITMIDX.Columns("Activity_Cd&quot.Value = "O" Then
IsItemObsolete = True
Else
IsItemObsolete = False
End If

'Determine QtyAvailable if Obsolete
QtyAvailable = 0
If IsItemObsolete = True Then
rsIMINVLOC.Open ("Select Item_No, Qty_On_Hand,Qty_Allocated, Qty_On_Ord from IMINVLOC_SQL where IMINVLOC_SQL.Item_No = '" & macForm.ItemNo.Text & "' and IMINVLOC_sql.Loc = '" & macForm.Loc.Text & "'&quot
QtyAvailable = rsIMINVLOC.Columns("Qty_On_Hand&quot.Value
If Get_Use_Allocated = True Then
QtyAvailable = QtyAvailable - rsIMINVLOC.Columns("Qty_Allocated&quot.Value
End If
If Get_Use_On_Order = True Then
QtyAvailable = QtyAvailable + rsIMINVLOC.Columns("Qty_On_Ord&quot.Value
End If

'Determine if QtyAvailabe > 0 and allow to continue
If QtyAvailable <= 0 Then
MsgBox &quot;You can not order this item. It has an availablity of zero at this location.&quot;, vbCritical + vbOKOnly, macForm.Company
macForm.Cancel
End If
End If

' See if Customer is an EDI Trading Partner
' First make sure item is not a misc. charge
If Mid(macForm.ItemNo.Text, 1, 2) = &quot;**&quot; Then
Else
rsEDCTPFL.ConnectString = &quot;DRIVER={Macola 32-BIT Btrieve (*.dta)}; DFE=BTR;AS=4096;&quot; & rs_Path
FoundDup = 0
sqlText = &quot;Select ECTP_MAC_CUS_NUM from EDCTP_FL_SQL where EDCTP_FL_SQL.ECTP_MAC_CUS_NUM = '&quot; & macForm.CustNo.Text & &quot;'&quot;
With rsEDCTPFL
.Open sqlText
While Not .EOF
custVar = rsEDCTPFL.Columns(&quot;ECTP_MAC_CUS_NUM&quot.Value
.MoveNext
Wend
If .RowCount > 0 Then
FoundDup = FoundDup + 1
End If
.Close
End With

If FoundDup > 0 Then
FoundDup = 0
rsEDCITMFL.ConnectString = &quot;DRIVER={Macola 32-BIT Btrieve (*.dta)}; DFE=BTR;AS=4096;&quot; & rs_Path
sqlText = &quot;SELECT DISTINCT ECIM_NUM, ECIM_CUS_NUM, ECIM_MAC_ITEM_NUM, ECIM_UPC_NUM FROM EDCITMFL_SQL WHERE (ECIM_CUS_NUM = '&quot; & custVar & &quot;') AND (ECIM_MAC_ITEM_NUM = '&quot; & macForm.ItemNo.Text & &quot;')&quot;
custVar = &quot;&quot;
With rsEDCITMFL
.Open sqlText
While Not .EOF
custVar = rsEDCITMFL.Columns(&quot;ECIM_UPC_NUM&quot.Value
.MoveNext
Wend
If .RowCount > 0 Then
FoundDup = FoundDup + 1
End If
.Close
End With
If FoundDup > 0 Then
If custVar > &quot; &quot; Then
Else
UserForm1.Caption = &quot;EDI Cross Reference WARNING!!!&quot;
UserForm1.Label1.Caption = &quot;No UPC Number in EDI Cross reference for item &quot; & macForm.ItemNo.Text
UserForm1.show
End If
Else
UserForm1.Caption = &quot;EDI Cross Reference WARNING!!!&quot;
UserForm1.Label1.Caption = &quot;Item &quot; & macForm.ItemNo.Text & &quot; is not in EDI Cross Refence !!!&quot;
UserForm1.show
End If
End If
End If

'Clean Up
rsIMITMIDX.Close
rsIMINVLOC.Close
rsEDCTPFL.Close
rsEDCITMFL.Close
Set rsEDCTPFL = Nothing
Set rsIMITMIDX = Nothing
Set rsIMINVLOC = Nothing
Set rsEDCITMFL = Nothing
End Sub

Private Sub ItemNo_GotFocus()
IsItemObsolete = False
QtyAvailable = 0
End Sub

Private Sub OECash_GotFocus()
'Declare Variables
Dim sSql As String
Dim Count As Integer
Dim rspath As String
Dim MsgAns As Integer
Dim rsOEORDLIN As ERSRecSet
Set rsOEORDLIN = New ERSRecSet
Dim sDate As String
Dim MacDate As Double

'Convert Screen Order Date to Macola format YYYYMMDD

If macForm.ShipDate.Text = &quot;A.S.A.P.&quot; Then
sDate = macForm.Date1.Text
Else
sDate = Year(macForm.ShipDate.Text) & Right(&quot;00&quot; & Month(macForm.ShipDate.Text), 2) & Right(&quot;00&quot; & Day(macForm.ShipDate.Text), 2)
MacDate = sDate
End If

'Set Macola Path

rspath = &quot;DB=&quot; & macForm.ConnInfo.BtrPath
rsOEORDLIN.ConnectString = &quot;DRIVER={Macola 32-BIT Btrieve (*.dta)}; DFE=BTR;AS=4096;&quot; & rspath

'Check to see line items exist to modify date, set line dates = to date on header page

sSql = &quot;select ord_no from OEORDLIN_SQL where Ord_No = '&quot; & Right(&quot;00000000000000&quot; & OE0101.macForm.No.Text, 8) & &quot;'&quot;
rsOEORDLIN.Open (sSql)

If begShipDate <> sShipDate Then
If rsOEORDLIN.RowCount > 0 Then
MsgAns = MsgBox(&quot;Change All Line Item Dates to Ship Date?&quot;, vbYesNo, &quot;Date Change&quot
If MsgAns = 6 Then
sSql = &quot;UPDATE OEORDLIN_SQL SET request_dt = &quot; & MacDate & &quot; , promise_dt = &quot; & MacDate & &quot; , req_ship_dt = &quot; & MacDate & &quot; WHERE Ord_No = '&quot; & Right(&quot;00000000000000&quot; & OE0101.macForm.No.Text, 8) & &quot;'&quot;
rsOEORDLIN.Open (sSql)
MsgBox &quot;Date Change Complete!&quot;, vbOKOnly
End If
End If
End If

'Close connection

rsOEORDLIN.Close
Set rsOEORDLIN = Nothing
End Sub

Private Sub Qty_LoseFocus(AllowLoseFocus As Boolean)
Dim rsIMITMIDX As ERSRecSet
Dim rs_Path As String
Dim FoundDup As Integer
Dim sqlText As String
Dim pRatio As Double

Set rsIMITMIDX = New ERSRecSet

pRatio = 0
'Set Macola Path & Connection Strings
rs_Path = &quot;DB=&quot; & macForm.ConnInfo.BtrPath
rsIMITMIDX.ConnectString = &quot;DRIVER={Macola 32-BIT Btrieve (*.dta)}; DFE=BTR;AS=4096;&quot; & rs_Path

'rsIMITMIDX.Open (&quot;Select Item_No, Activity_Cd, Price_Ratio from IMITMIDX_SQL where IMITMIDX_SQL.Item_No = '&quot; & macForm.ItemNo.Text & &quot;'&quot

FoundDup = 0

sqlText = &quot;Select Item_No, Activity_Cd, Price_Ratio from IMITMIDX_SQL where IMITMIDX_SQL.Item_No = '&quot; & macForm.ItemNo.Text & &quot;'&quot;
pRatio = 0
With rsIMITMIDX
.Open sqlText
While Not .EOF
pRatio = rsIMITMIDX.Columns(&quot;Price_Ratio&quot.Value
.MoveNext
Wend
If .RowCount > 0 Then
FoundDup = FoundDup + 1
End If
.Close
End With

If FoundDup > 0 Then
UserForm1.Label1.Caption = &quot;Price Ratio is: &quot; & pRatio & &quot;. If the quantity you entered was eaches, the correct entry would be: &quot; & Round(Qty.Text / pRatio, 2)
UserForm1.show
End If
rsIMITMIDX.Close
Set rsIMITMIDX = Nothing

If Get_Check_On_Ordered = True And IsItemObsolete = True Then
If CDbl(Qty.Text) > QtyAvailable Then
MsgBox &quot;Qty Available for this item is only: &quot; & QtyAvailable & Chr(13) & &quot;Please order this amount or less.&quot;
macForm.Qty.Text = &quot;0.00&quot;
SendKeys (&quot;+({END})&quot
AllowLoseFocus = False
End If
End If
End Sub

Private Sub Qty1_LoseFocus(AllowLoseFocus As Boolean)
If Get_Check_On_Ordered = False And IsItemObsolete = True Then
If CDbl(Qty.Text) > QtyAvailable Then
MsgBox &quot;Qty Available for this item is only: &quot; & QtyAvailable & Chr(13) & &quot;Please order this amount or less.&quot;
macForm.Qty.Text = &quot;0.00&quot;
SendKeys (&quot;+({END})&quot
AllowLoseFocus = False
End If
End If
End Sub

Private Sub ShipDate_GotFocus()
begShipDate = macForm.ShipDate.Text
End Sub

Private Sub ShipDate_LoseFocus(AllowLoseFocus As Boolean)
sShipDate = macForm.ShipDate.Text
End Sub

 

[vbajock]

ok, first thing you have to do is add error handlers to your procedures so you can discover which object has not registered properly. DO this:

At the beginnning each procedure immediately following your dim statements, add the line:

On error goto err_h

At the end of each procedure, directly above the &quot;End Sub&quot; or &quot;End Function: statement, add this code frag

'frag starts here

Exit Sub (or Exit Function) if it is a function

'*****
err_h:
'*****
msgbox str$(err)& &quot; &quot;& Error$ &&quot; has occurred in Procedure X&quot;
stop
Resume

'frag ends here


Put the actual name of the procedure in the place of X. When you run the code, it will take you to the stop statement. Press F5 to execute the next line, (Resume), and it should take you to the offending line. Let me know what it is.

Run the code and you should

 

[ThunderForest]

I added the code. I appreciate your patience, and hate to sound like a broken record. Again, everything runs smooth as silk on any other PC. The code appears to be error-free. On my PC, I don't even get to a point where I can use the F5 key, which I assume would be deBug mode. The crash does not seem to be occurring from within or as a result of the code. It seems to be occurring during whatever process Macola is doing immediately prior to displaying the TRX-Enter Orders screen. It is as if the code just won't compile or be interpreted correctly on this PC.

http://www.thunderforest.bizland.com

 

[ThunderForest]

An additional note...
As I mentioned before, the error is a continuous loop, necessitating an End Task. When I show details the second time it displays, the details are:

VBAConnect.AddControls
Error: -2147220224
Error Text: Index not found. The specified index or name does not exist in the collection.

Showing details the next time it displays is the error noted in my first thread. Showing details again is the index not found, and back and forth.

http://www.thunderforest.bizland.com

 

[Rob7]

You might want to see if the Flex demo code is sitting out in your user login in the VBAPRJ file in the Macola root. This will sometimes give this error because not all of the references are in place and it errors out trying to resolve the object reference.

Rob

 

[chadt]

have you tried just reinstalling the client on the problem machine?

 

[vbajock]

I don't use the ERS objects or the vbaconnect objects much - so I am not really up on the internals of it, other than it is Macola home-grown code. I believe both the users flex program and Macola itself use this object to manage the home grown controls. I'll bet on Rob7's guess. It's got to be something funky like that. If it didn't trigger an error in the error handler code I sent him then the error must be originating from Macola itself as it tries to index in whatever control he added to the existing controls.

His enviro is pretty out of date also. Might be some kind of thing addressed by a patch long,long ago in a Macola galaxy far, far away.

Long shot might be to compare registry entries on a machine that works with his registry doing a search on &quot;vbaconn.exe&quot;.

 

[ThunderForest]

For ChadT, Thanks. I have removed and reinstalled the client several times on this PC, as well as Pervasive, and to no avail.

Rob7, I assume you mean in ..\MACOLA70\VBAPRJ\OE0101.O\.. There's no demo code there, only the OE0101.O file.

VbaJock, thanks for the input too. Perhaps you're on to something here. I do agree about the enviro, but it works for the most part. I'm not sure I understand what you're saying about ERS objects, etc., but if I log into Macola as SUPERVISOR on my PC and try to enter an order, I crash. If I login as SUPERVISOR on another PC, I don't crash. Either PC is using the same ..\MACOLA70\VBAPRJ\OE0101.O\SUPERVISOR\OE0101.O project. Also, registry searches for VBACONN.Exe matched identically on either PC. May be something much deeper. Thanks all.

http://www.thunderforest.bizland.com

 

[chadt]

maybe macola just sucks.

 

[vbajock]

An example of an ERS object is your recordset object. All these objects are stored in the vbaConnection library, which is the one that is returning errors to you. You can use it to connect to btrieve, manage recordsets, etc. They also threw a bunch of stuff in it to manage the macForm object, which is the actual Macola screen that your initial controls are added to. This should have been two or more separate libraries. I program a number of different ERP systems, so I try to use professional strength programming libraries from companies like Microsoft or Borland that apply to all systems so my code will be more portable. The ADO library replaces all of the ERS connection and recordset management, for example. I also have found a lot of the home grown libraries are buggy and unstable. The only thing I usually want out of a Macola screen is an exit point!

 

[Zbob]

Did you ever find a solution?

I am having a similiar problem, Macola will not load the Order entry screen on Windows 2000 machine with flex installed, but will on XP machines.

 

[abaldwin]

When you added your code for the OE0101.O screen, did you just add to the code that was already there or did you start from a fresh project screen.

The demo code that macola puts out is seriously flawed and will cause errors on SOME machines.

when you are signing into your macola from your workstation what sign in are you using?

Andy

Andy Baldwin

 

[MasterPO]

The message you are seeing is usually caused by a screenset error, not necessarily a code error.

I have encountered this problem when the screenset has been altered and not copied to the username (or group) trying to access the screen with Flex code behind it; I have also had corrupted screensets cause this problem; I have also seen this when a user adds a control to the screen, writes some code for the new control, thens removes the control (but not the code) from the screen.

Use the System Manager to copy the screen from a working user's name to your username. This may solve the problem.

 

[abaldwin]

Try the following

Check your references and make sure that the Macola ERS recordset object is referenced.

here is a snippet of your code
If rsIMITMIDX.Columns(&quot;Activity_Cd&quot.Value = &quot;O&quot; Then
IsItemObsolete = True
Else
IsItemObsolete = False
End If


Try removing the .VALUE after the columns
Change your if statements as above.

IF rsIMITMIDX.columsn(&quot;Activity_Cd&quot = &quot;O&quot;

The columns property will return the value of the field in the opened recordset.

Block Remark out all the code in this module except for
Private Sub DiscPct1_GotFocus() <--This sub and its code.

Check to see if the form opens then.

If it does then unremark the next sub and so on until you can find the sub that is causing the error.

Andy


Andy Baldwin

 

[zedsded]

JDonnewald,
MASTERPO is right. This is a screen set error. I experienced this problem during a migration from &quot;same as yours&quot; to 7.6.100 WIN2K. Anyway, your best bet is to Log-in as 'supervisor' in MACOLA=System Manager and copy a &quot;functional&quot; user screenset (OE0101). If you do not use custom screens, you can copy user named MACOLA, this way you're sure that you are copying a DEFAULT Macola screen. Hope this helps.