Problem with executable file

I developed an app that works fine when it runs from VB. After compiling it, something odd happens. I've attached a screenshot to illustrate.

On a visible form, I am switching between two paper grades using 2 command buttons (Current Grade and Next Grade). For each grade there is an approval date (selected from a dropdown box) and other basic info associated with the grade (shown at the top of the form). Let's say I'm viewing the current grade. When switching to the next grade, I "dump" the info for the current grade to a hidden form and recall the next grade info (which has been stored on another form). So all I'm doing is passing info back and forth between the visible form and two hidden forms.

No problem when run from VB. But when I run the executable and switch back and forth a few times, I get an error message. I'm using the stored grade ID and approval date to get the appropriate recipes for that grade from an Access database. The error occurs when I query the database using the grade ID and approval date. What seems to happen looking at the error message is that the approval date has become concentated with itself. That is, the query is looking for 01-Sep-0301-Sep-03 instead of 01-Sep-03. This is strange because I'm not doing any manipulations of the approval date in my code. Since the problem is occurring in the executable, are there any ideas as to how I can troubleshoot why this problem is happening?

The cause of "strange things" happening in an exe and not in the IDE is often the fact that the compiled code is faster and some process is not geting a chance to finish. In your case it might be something in your database I/O. Try putting a DoEvents in your code before or after you get the data from the database.

Thanks Martin. Tried putting the DoEvents either before or after the database call and it still gives me the same problem. Here is where the call is made. Your comment on speed of execution sounds logical. Any other suggestion?

VB Code:

Private Sub cboApproved_Click()
      
      DoEvents  '****ADDED
    
    txtEdit.Visible = False
    
    If cboApproved.text = "" Then
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'" 'And [Date approved] = "'" & cboApproved.text & "'"
    Else
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
        strSQL = strSQL & "AND [Date approved]= #" & cboApproved.text & "#" '****this line gives concentated date in exe
    End If
oRS.Open strSQL, oConn
    
    If Not (oRS.BOF And oRS.EOF) Then
        txtComment.text = "" & oRS("Reason for update")
    Else 
        txtComment.text = ""
    End If
    
    
    oRS.Close
    Set oRS = Nothing
 
 '*** Also tried adding DoEvents here ****   
 
LoadRecipe
    
End Sub

What do you see if you add the highlighted line?

VB Code:

If cboApproved.text = "" Then
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'" 'And [Date approved] = "'" & cboApproved.text & "'"
    Else
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
        strSQL = strSQL & "AND [Date approved]= #" & cboApproved.text 
& "#" '****this line gives concentated date in exe
        [HL="#FFFF80"]MsgBox cboApproved.text [/HL]
    End If
oRS.Open strSQL, oConn

stan,

Is it possible to post your project or send it to me to check? Also why are your sql statements almost the same except if the field is empty?

I moved the message box just before the error is invoked. If the date approved is 01-Sep-04 then the msgbox reads 01-Sep-0401-Sep-04 (which invokes an error in the next line). This is only in the compiled version.

VB Code:

If cboApproved.text = "" Then
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'" 'And [Date approved] = "'" & cboApproved.text & "'"
    Else
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
[COLOR=Red]MsgBox cboApproved.text [/COLOR] 
        strSQL = strSQL & "AND [Date approved]= #" & cboApproved.text 
& "#" '****this line gives concentated date in exe
        
    End If
oRS.Open strSQL, oConn

Stan,

That seems impossible unless there is somepossible interruption in your code that changes it.

Quote:

---

Originally Posted by randem

Stan,

That seems impossible unless there is somepossible interruption in your code that changes it.

---

It's a real puzzle. My problem is I don't know how to troubleshoot it since it is only in the executable. Perhaps if I put a line of code in just before the error. Something like "if the number of characters exceed 9 (01-sep-04), then truncate (01-sep-0401-sep-04)". Can you suggest how to do this?

Stan,

By responding to my other post.

Where else are you using cboAppoved? How is the text getting changed?

Quote:

---

Originally Posted by randem

Stan,

By responding to my other post.

---

Sorry... Only saw your other post after sending my last reply. Unfortunately, my app runs off a specific server and would not work on your PC. Also I've since removed the redundant query. As for my last question, could you make a suggestion (I'm hopeless with Trim, etc)?

Thanks

You really should continue to try to figure out what the real problem rather than "fixing" it with a kludge like what you are suggesting.

Quote:

---

Originally Posted by MartinLiss

Where else are you using cboAppoved? How is the text getting changed?

---

It is only getting changed in the cboApproved_Change event (where the above code resides). Everytime I switch between Current and Next Grade, the approved date (stored in a separate form) becomes the new value of the combobox and the change event is automatically invoked (thus setting off the code). Does this make sense?

Stan,

Unfortunately, I cannot make any intelligent suggestions without seeing your code. Obviously something is happening elsewhere in your code and cannot be determined with the snipett you supplied.

Quote:

---

Originally Posted by randem

Stan,

Unfortunately, I cannot make any intelligent suggestions without seeing your code. Obviously something is happening elsewhere in your code and cannot be determined with the snipett you supplied.

---

I could send you the code. It's just that you won't be able to run the app.

Why not just zip it up and attach it. That we we can both look at it.

Quote:

---

Originally Posted by MartinLiss

Why not just zip it up and attach it. That we we can both look at it.

---

It's confidential and shouldn't be posted in the public domain. I could send it to both of you via email if that is OK.

Stan,

Check your PM

Thanks... file sent. Not sure how to get a copy to Martin

Stan

Stan,

Send him a PM or he will get back to you on this thread when he is free.

Stan,

Please resend the file. My emails server bounced it.

Quote:

---

Originally Posted by MartinLiss

You really should continue to try to figure out what the real problem rather than "fixing" it with a kludge like what you are suggesting.

---

Point well taken. Appreciate the feedback. Could you PM me an email address if you would like a copy of the code.

Quote:

---

Originally Posted by randem

Stan,

Please resend the file. My emails server bounced it.

---

File resent... Stan

Temporarily add the highlighted lines. Is it ever true?

VB Code:

Public Sub BackUpNext()
    Dim i, lngRow, lngCol As Long
 
'This code is called when user switches from Next grade to Current grade. It stores the
'frmMain values until the user switches back to Next grade.
 
    'Store current grade code
    frmBackUpNext10.lblNextgrade.Caption = frmMain.cmdNextGrade.Caption
    frmBackUpNext10.lblRunSeq.Caption = frmMain.txtRun.text
    frmBackUpNext10.txtComment.text = frmMain.txtComment.text
    'frmBackupNext10.txtRecipeComment.text = frmMain.txtRecipeComment.text
    frmBackUpNext10.lblDateApprove = frmMain.cboApproved.text
[HL="#FFFF80"]    If Len(frmMain.cboApproved.text) > 9 Then
        MsgBox "Problem"
    End if[/HL]

Martin,

Added your lines of code to the sub and recompiled. When running the executable, the msgbox was not invoked but I still had the problem of an error message where the date approved was in the format "dd-mm-yy-dd-mm-yy".

The last thing I can think of to suggest is this. If that doesn't work you should attempt to scan your program and try to figure out where the combo box is getting the bad value from.

VB Code:

Public Sub [HL="#80FF80"]GetBackUpNext[/HL]()
    Dim i, lngRow, lngCol As Long
 
'Called when user switches from Current grade to Next grade (or deletes current grade).
'This code retrieves the values that were saved in the Sub BackUpNext()and puts them in
'the corresponding grids in frmMain
 
   frmMain.txtRun.text = frmBackUpNext10.lblRunSeq.Caption
   frmMain.txtComment.text = frmBackUpNext10.txtComment.text
   'frmMain.txtRecipeComment.text = frmBackupNext10.txtRecipeComment.text
   frmMain.cboApproved.text = frmBackUpNext10.lblDateApprove.Caption
    [HL="#FFFF80"]If Len(frmMain.cboApproved.text) > 9 or  Len(frmBackUpNext10.lblDateApprove.Caption) > 9Then
        MsgBox "Problem"
    End if[/HL]

Martin,

No luck with that either. But you've given me a good tip on how to troubleshoot. Will not waste your time further and use what you suggest to try and pin-point where/how the problem occurs. Will post if I a) find the solution or b) don't. Many thanks for the feedback. :wave:

One thing to keep in mind and that is that similar to DoEvents, a MsgBox will cause the program logic to pause which if it is a speed problem may mask the problem. To get around that since you are writing to comboboxes and labels anyhow you might want to put some special text like "problem is subxyz" in the combobox/label instead of using the MsgBox.

Good point... I can now see why you objected to my "kludge" solution. Will use your latest advice to troubleshoot. :thumb:

Stan,

Please give me a procedure of what you are doing to achieve this error. You stated I can't run this program on my system. Why can't I run this program on my system?

I haven't really been following this thread, but I can't see how this line:

Quote:

---

Originally Posted by Stan

VB Code:

strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
        strSQL = strSQL & "AND [Date approved]= #" & cboApproved.text & "#" '****

---

could work, as there's no space between the grade ID and the AND operator

Stan,

Do yo realize that the routine

VB Code:

Private Sub cboApproved_Change()

will run each time a character is typed into the combobox as well as when you assign the combobox a value. I really don't think that is what you want. This may not solve your problem but it will prevent others. You should not put this code in that sub. Only put validation code in there.

Stan,

Good catch by anguswalker. Matter of fact I looked at other SQL statements and they are the same. That is why I stress using a Debug.Print StrSQL statement immediately after making your SQL statement so that you can see obvious mistakes such as this.

Another error the same as anguswalker states:

VB Code:

Private Sub HeaderInfo()
    
 'On Error GoTo errHandler
    'Reset batch size
    optFull.Value = True
    lastBatchSize = "Full"
    
    'Remove the grade name specified in the combobox and return only the 13 character _
    'grade code
    strGradeID = Mid$(cboGradeCode.text, 1, 13)
    'strSQL = "SELECT [Comment]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
    If cboApproved.text = "" Then
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'" 'And [Date approved] = "'" & cboApproved.text & "'"
    Else
        strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
        [b]strSQL = strSQL & "AND [Date approved]= #" & cboApproved.text & "#"[/b]
   End If

I have a lot of catching up do. Tried logging on and was not successful till now. Sent a PM to randem and will post my reply here as well.

randem,

You are right about the SQL query. I missed the space in the "AND [Date approved]... statement. Still seemed to work though.

You can't run the program because you need to make a connection to a dedicated server (PI system). But I think we may be close to identifying where the problem lies. First I'll give you an overview of how the app works. When it first starts, it goes into a database file and loads all the grade codes into the cboGradeCode control. When the user selects a grade code for the first time, it goes back into the database and picks up all the Date Approved (cboApproved) data for that grade (plus other info showing at the top of the frmMain) and the recipe for that grade based on the Date Approved showing in the combobox. If the user selects a different date approved, an SQL query is initiated in the cboApproved Change event (will get back to this later).

You will notice on the frmMain two command buttons - Current Grade and Next Grade. Because this is the first grade loaded, the cmdCurrGrade is enabled and has the caption "Current Grade". If the user selects another grade, all the info for the current grade (except the recipe) is dumped to a hidden form and is replaced with info for the next grade based on the date approved. The cmdNextGrade control is enabled and given the caption "Next Grade". The user can now toggle back and forth between the two grade using the command buttons. Each time the user switches grades, the grade code, date approved (and other infor at top of the form) is passed back and forth between hidden forms so that the button selected has the right data for that grade showing. But each time the user switches, the program has to go back into the database for that grade's recipe. All the date approved values for that grade are loaded into the cboApproved. But the cboApproved.text will be the one that was stored in the hidden form. That way the user can get the correct recipe from the database (which is based on Date Approved).

I am putting the SQL query in the cboApproved event and, as you pointed out in an earlier posting, this is not advised. I agree but I'm not sure how to do it differently. I put a message box in the event and saw that the Change event is triggered twice very rapidly when switching between the 2 command buttons. So maybe this is in line with Martin's thinking regarding the difference in speed between the code-run app and the compiled app.

I knew that each time the user clicked on the current or next grade buttons that the cboApproved was updated and it's Change event would be triggered, which would initiate the query. An option would be to put the query in a module and call it from the cmdCurrGrade and cmdNextGrade buttons. But what would I do if I want use the cboApproved to select a new date? If I tried calling the module from the cboApproved Change or Click event I run into the same problem as if I put the query within the event. Any thoughts/suggestions?

Instead of going back and forth to the database and using a hidden form, couldn't you go to the database once and store all the needed data in an array? That array could be an array of a user defined type if necessary.

Stan,

If you want you could pull down some data from the main database and create a smaller database that will work on the local computer and then it will be easier to eliminate the problem for we could run your program and see what you see and help eliminate the problem.

Quote:

---

Originally Posted by MartinLiss

Instead of going back and forth to the database and using a hidden form, couldn't you go to the database once and store all the needed data in an array? That array could be an array of a user defined type if necessary.

---

Good idea. Unfortunately my programming skills are not at that level (yet).

Quote:

---

Originally Posted by randem

Stan,

If you want you could pull down some data from the main database and create a smaller database that will work on the local computer and then it will be easier to eliminate the problem for we could run your program and see what you see and help eliminate the problem.

---

Okay.. I think I can do that (a "fake" database). Give me a bit of time and I'll PM it to you.

Quote:

---

Originally Posted by Stan

Good idea. Unfortunately my programming skills are not at that level (yet).

---

VB Code:

Option Explicit
Private Type RecipeData
    Blah As String
    BlahBlah As Integer
    BlahBlahBlah As Currency
End Type
Private mtypRD() As RecipeData
Private Sub Form_Load()
 
    ' Get recordcount from database
    ' Redim mtypRD() as needed to hold all the data
    ' Fill the array in a loop, pseudocode example
    rs.MoveLast
    intRecCount = rs.RecordCount
    ReDim mtypRD(intRecCount)
    rs.MoveFirst
    
    For lngCounter = 0 To intRecCount - 1
        mtypRD(lngCounter).Blah = rs!Blah
        mtypRD(lngCounter).BlahBlah = rs!BlahBlah
        mtypRD(lngCounter).BlahBlahBlah = rs!BlahBlahBlah
        rs.MoveNext
    Next
 
End Sub

Thanks a lot Marty. I had thought about the possibility of using arrays when I first started the project but had no idea how to go about doing it. What you posted would have been very useful. Unfortunately the project is completed and I haven't the time available to make substantial changes. But this will be most helpful in future projects. Thanks again. :thumb:

BTW - I sent randem a dummy database file so he can run my app. I'll post it here in case you want to run it as well. It should be put in the same directory as the app. Two notes:

1. In frmMain there is a call to PIConnect - rem this out (it tries to connect to a server)
2. We were looking in the wrong place where the error is invoked (cboApproved event). I later found it is actually in the "HeaderInfo" sub in frmMain. It occurs at the following:

VB Code:

strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'"
        strSQL = strSQL & " AND [Date approved]= #" & cboApproved.text & "#"

As a termporary solution so the app can still be used, I inserted the following before these two lines:

VB Code:

If Len(frmMain.cboApproved.text) > 9 Then
            frmMain.cboApproved.text = Right(frmMain.cboApproved.text, 9)
        End If

When I try to run it I get an error message because RotateText.ocx is missing. I can ignore that (I think) but there are also errors involving two missing references, PISDK 1.3 type library and PISDKParse 1.1 type library. If I comment out the global references to them I get to frmMain Form_Load and I run into this.

VB Code:

Private Sub Form_Load()
    Dim intCol As Integer
    Dim intwidthcounter As Integer
    Dim colcounter As Integer
    Dim rowcounter As Integer
    Dim i, j, k As Integer
    Dim optButton As Integer
    Dim CurrentTab As Integer
 
   
    frmMain.Caption = "Manual Entry PM10 - Revised July 4, 2005" 'frmMain.Caption & " (Version " & App.Major & "." & _
        App.Minor & "." & App.Revision & ")"
    
   [HL="#FF8080"]ListIndex = 0[/HL]

How can that compile?

BTW, it's a very common mistake but in Dim i, j, k As Integer only k is actually defined as an Integer and the other two are left as Variants.

Quote:

---

Originally Posted by MartinLiss

BTW, it's a very common mistake but in Dim i, j, k As Integer only k is actually defined as an Integer and the other two are left as Variants.

---

Wasn't aware of that (saw others doing this and thought it was OK). Thanks for the tip.

As for the other problem. Did you rem out the call to PIConnect (uses the PISDKs which are used by dedicated server you don't need). Attached is the RotateText.ocx. Are you saying the program "hangs" when you get to "ListIndex = 0"? Maybe it's related somehow to the error messages you received.

I removed your RotateText.ocx because I assume it's a commercial product. I did rem out the call to PIConnect but that doesn't affect the fact that I don't have those references since there are variables that refer to them. Regarding ListIndex = 0, no it doesn't hang, it's just that that is not valid code and so the program won't run. The ListIndex must be associated with a ListBox or Combobox, eg MyListbox.ListIndex = 0.

As for the PI-SDKs, I just realized it is because the app specifies these under Project>>References. Will have to PM you a revised vbp file. As for the "ListIndex = 0", I rem'd this.

OK, using your new files I can get it to run after doing the following and a few other things.

VB Code:

'    Public srvPI As PISDK.server
    Public strMessage As String
    Public con_lngError As Long
'    Public p_ptPoint As PIPoint
'    Public alAlias As PIAlias
'    Public mod_Level As PISDK.PIModule

Please tell me exactly how to reproduce your problem after I compile.

I'm sorry but ListIndex = 0 still isn't valid. What is it that you are trying to do with that statement?

strStep is referenced in RefreshRecipeGrid but it isn't defined within the scope of that sub. It is defined in LoadRecipe. Did you mean it to be global or should I add a Dim for it in RefreshRecipeGrid? BTW, you have strStep defined as a string, but you use it as a counter. It should be a Long or an Integer (and iof course you should then call it lngStep or intStep).

I've just realized that your problem may be because you have code problems even though you were obviously able to compile your program. I compiled it too, once, but when I tried it again starting with a different sub open the compile failed. If you have invalid code in a compiled program I assume you can get weird results.

Stan,

Each time you call GetBackUpCurrent() or GetBackUpNext() it will trigger cboAppovedDate() because you change the text of cboApprovedDate.

VB Code:

frmMain.cboApproved.text = frmBackUpCurr10.lblDateApprove.Caption
 
and
 
   frmMain.cboApproved.text = frmBackUpCurr10.lblDateApprove.Caption

You will need to move the code to a sub and then call it when you need it. You will need to redo your routine and move it to a module for various forms call these routine and in turn run the code in the cboApprovedDate_Change() routine.

Your problem is procedural.

Quote:

---

Originally Posted by MartinLiss

OK, using your new files I can get it to run after doing the following and a few other things.

VB Code:

'    Public srvPI As PISDK.server
    Public strMessage As String
    Public con_lngError As Long
'    Public p_ptPoint As PIPoint
'    Public alAlias As PIAlias
'    Public mod_Level As PISDK.PIModule

Please tell me exactly how to reproduce your problem after I compile.

---

Using the drop downbox, load one of the 2 grades. Then load the 2nd grade. Now toggle back and forth on the Current Grade and Next Grade buttons.

Quote:

---

Originally Posted by MartinLiss

I'm sorry but ListIndex = 0 still isn't valid. What is it that you are trying to do with that statement?

---

It's not necessary in the code. It should be rem'd out.

Quote:

---

Originally Posted by MartinLiss

strStep is referenced in RefreshRecipeGrid but it isn't defined within the scope of that sub. It is defined in LoadRecipe. Did you mean it to be global or should I add a Dim for it in RefreshRecipeGrid? BTW, you have strStep defined as a string, but you use it as a counter. It should be a Long or an Integer (and iof course you should then call it lngStep or intStep).

---

You are correct. It should be an integer. I've not defined Option Explicit so it is not reallyb necessary to define it. But if it were, it should be global (I have a module where I define the Public variables).

Stan,

I guess you didn't like my comments ... I feel slighted :wave:

Quote:

---

Originally Posted by Stan

I knew that each time the user clicked on the current or next grade buttons that the cboApproved was updated and it's Change event would be triggered, which would initiate the query. An option would be to put the query in a module and call it from the cmdCurrGrade and cmdNextGrade buttons. But what would I do if I want use the cboApproved to select a new date? If I tried calling the module from the cboApproved Change or Click event I run into the same problem as if I put the query within the event. Any thoughts/suggestions?

---

We seem to have come full circle on this. Above is a quote from an earlier posting. Really appreciate you guys sticking with me for so long on this one. My coding skills are modest and I've picked up a lot of useful info unrelated to my problem in this thread. But the problem remains. How can I address the issue in the above quote? Is there a difference between _Change and _Click where they are invoked under different conditions?

Cheers

Stan,

Even if you selected a different date in the cboApprovedDate combo box, the Change event would not fire. So, having the routine in that sub would not amount to much, except trouble.

Here is a program I put together for you to see what I mean. Put a break point on the DoEvents in the Change Event and select an item.

Quote:

---

Originally Posted by randem

Stan,

I guess you didn't like my comments ... I feel slighted :wave:

---

Not sure what I did to offend you... but my apologies anyway :thumb:

Stan,

Well, Did you try the code? You probably have even seen my other post have you?

Yes I did try it. I can see the difference between the _Click and _Change events. Will try to see how I can use this to get around my problem. BTW - didn't have time to respond to your emails (if that's what you miffed about). Will do so now. :thumb:

Quote:

---

Originally Posted by randem

Stan,

Well, Did you try the code? You probably have even seen my other post have you?

---

Ah... I see why you're miffed with me. You probably didn't realize that my Post #55 was in response to your Post #50 (amazing... is this a record for number of postings). Sorry for any confusion. What you pointed out is the crux of my problem I believe.