Code stops executing and loops between 1st line and stop

[psbsmms]

I get this problem off and on and I'm hoping someone can tell me what I am doing wrong. I have a form or control that is working fine and then while add more code, usaually something to handle something I didn't anticipate the user doing. My current issue is between two combo boxes. I have one box that they select a store number and on lost focus I set the rowsource of the second combo box to a sql string. it was working fine until a user forgot to put a store number in the 1st box. I went back and had the event look for a null value and exit sub on finding it. Now it doesn't populate the other combo box and when i put a stop in to check the code the debugger loops between the first line and stop and goes no further. As I said this has happebed several times to me on other controls, and I've had to remake the entire form to make it work. I have even sometimes been able to copy the code from one form to the other to save typing and it works. This one is driving me crazy. I am using A2k and XP pro.
The latest code that is giving me the error is below.
Private Sub cmbStore_LostFocus()
Dim strStoreNo As String
Dim intID As Integer
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Stop
If IsNull(cmbStore.Value) Then
MsgBox "You entered and ivalid number for store please try again", vbOKOnly
Exit Sub
End If

strStoreNo = cmbStore.Column(0)
strSQL = "Select [tbljobs].[id], [tbljobs].[JobNo] from [tbljobs] where [StoreNo] = '" & strStoreNo & "';"
Set DB = CurrentDb
Set RS = DB.OpenRecordset(strSQL)

If RS.RecordCount < 1 Then
MsgBox "You have entered a store number that has no listed jobs", vbOKOnly
Exit Sub
Else: cmbJobSelected.RowSource = strSQL
End If

End Sub

 

[RoyVidar]

Don't know, so just guessing and...

So you are not able to press either F8 or F5 to continue the code execution line by line or as a whole, you do not receive any errormessages?

I would suggest trying the code either in this combos after update event, or the other combos on got focus event. One of the things happening when one codes lot of stuff in different events, they may end up triggering each other, creating anomalities.

Implement some errorhandling in the routine

Close and release the object variables

Perhaps try the .ListIndex property of the combo, to test for no selection

But of couse, you could be up against a corruption issue, perhaps try the /decompile option, see Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times for detailed instructions.

Next, is more a matter of style, but I tend to avoid the Exit <something> statements as much as possible, what about:

[tt]...
on error goty myerr
If (me!cmbStore.listindex = -1) Then
MsgBox "You entered and ivalid number for store please try again", vbOKOnly
Else
strStoreNo = me!cmbStore.Column(0)
strSQL = "Select id, JobNo from tbljobs where StoreNo = '" & _
strStoreNo & "';"
Set DB = CurrentDb
Set RS = DB.OpenRecordset(strSQL)

If (RS.RecordCount < 1) Then
MsgBox "You have entered a store number that has no listed jobs", vbOKOnly
Else
me!cmbJobSelected.RowSource = strSQL
End If
RS.close
Set RS = nothing
Set DB = nothing
End If

myexit:
exit sub
myerr:
msgbox err.description
resume myexit
End sub[/tt]

Roy-Vidar

 

[psbsmms]

I can press f8 but it just loops back and forth beteen the stop and first line of code each time it is pressed. Yes there is no error messages. Thanks for the thoughts. I will try the decompile option. I have always had trouble with error handling, will try that option as well. I know you said it was a matter of style, but usaually style is based on experiance with problems. Are there pit falls to using the exit statement withing the routine as opposed to the error handling?

 

[VBslammer]

Sounds like an endless looping problem. I would move the code to a procedure that has a Cancel parameter, such as the [tt]cmbStore_Exit(Cancel As Integer)[/tt] event.

VBSlammer

"You just have to know which screws to turn." - Professor Bob