Function Module looping problem

[Bruce Lawrence]

I'm running Access 97 and my modules are looping if someone puts an
invalid value in.

The setup:
3 macros - get_clock_num, verify_clocknum, append_to_history
3 functions. each in their own module - get_clocks(), verify_clocks(),
append()

Each macro runs a function from in its corresponding module.
Here is my code.

From the "gatheremp_num" module... ________________________________________
Public Loader, Packer As String

Function get_clocks()

Packer = ""
Loader = ""

Packer = InputBox("Input packer")
Loader = InputBox("Input loader")

DoCmd.RunMacro "verify_clocknum"

End Function
_______________________________________

From the "verify" module... _______________________________________
'Verify if the Loader and Packer are clocked in using the
"look_up_empid" query

Function verify_clocks()
Dim packname, loadname As String
Dim packcount, loadcount As String
Dim lookup_emp_id As QueryDef
MsgBox "Step0"

'count the number of times each employee shows up. If null, ask for
names again
packcount = DCount("[clocknum]", "look_up_empid", "[clocknum]= '" &
Packer & "'")
loadcount = DCount("[clocknum]", "look_up_empid", "[clocknum]= '" &
Loader & "'")

If packcount = 0 Then
MsgBox "step1"
MsgBox "One or more employees not clocked in. Rescan badges"
DoCmd.RunMacro "get_clock_num"
Else
MsgBox "Step2"
GoTo Verify_Loader
End If

Verify_Loader:
MsgBox "step3"
If loadcount = 1 Then
MsgBox "step4"
GoTo Display_Results
Else
MsgBox "Step5"
MsgBox "One or more employees not clocked in. Rescan badges"
DoCmd.RunMacro "get_clock_num"
End If

Display_Results:

'Lookup employee names from loader and packer input boxes
packname = DLookup("[empname]", "look_up_empid", "[clocknum]= '" &
Packer & "'")
loadname = DLookup("[empname]", "look_up_empid", "[clocknum]= '" &
Loader & "'")

'display employee names
MsgBox ("Packer is " & packname & " " & Packer & Chr(13) & "Loader is "
& loadname & " " & Loader)

DoCmd.RunMacro "append_to_history"

End Function
_________________________________________

From the "append_history" module...

____________________________________________

Function append()

Dim SQLstr, SQLstr2, packname, loadname, order, machno As String
Dim curdate As Date
Dim look_up_empid, getorder As QueryDef
Dim history, outputtable As TableDef

' gets variables from queries
packname = DLookup("[empname]", "look_up_empid", "[clocknum]= '" &
Packer & "'")
loadname = DLookup("[empname]", "look_up_empid", "[clocknum]= '" &
Loader & "'")
order = DFirst("[ordno]", "getorder")
machno = DFirst("[mach num]", "getorder")

'declares append query to put values into history table

SQLstr = "INSERT INTO history (clockno,empname,ordno,curdate,machno)
VALUES" & _
"('" & Packer & "','" & packname & "','" & order & "',#" &
Now() & "#,'" & machno & "')"

SQLstr2 = "INSERT INTO history (clockno, empname,ordno,curdate,machno)
VALUES" & _
"('" & Loader & "','" & loadname & "','" & order & "',#" &
Now() & "#,'" & machno & "')"

'executes queries
MsgBox "step6"
DoCmd.RunSQL SQLstr
MsgBox "step7"
DoCmd.RunSQL SQLstr2

End Function
_________________________________________
I know this is alot of code and I apologize for that but I'm really
stuck and it makes no sense to me.

This code runs perfectly if I put valid 2 numbers into Packer and
Loader. Here is the steps that run when 2 good numbers are entered.
Step 0,2,3,4,6,7

Here is what happens if I put a bad number and good number.
Step 0,1
It asks again so I put in 2 good numbers
Step 0,2,3,4,6,7,3,4,6,7

Why the loop? Its displaying the good numbers twice and also appends
them into the table twice.

[Bruce Lawrence]

Here is what happens if I put in a good number and then a bad number
(the reverse of the above example)

Step 0,2,3,5
Tells me one is bad and to redo it. I then put 2 good numbers in.
Step 0,2,3,4
It shows me the 2 employees
Step 6,7
It shows me the 2 employees again!
Step 6,7

Its like the "End Function" isn't getting applied in the append module.
Almost as if it remembers where the input error was and after 2 good
numbers are in there, it has to go back to the error spot and redo it.

[Bruce Lawrence]

bump*

[redryderridesagain]

Bruce,
I lost track of your code after finding that get_clock calls
verify_clock and verify_clock calls get_clock, in other words
unintended recursion which might cause extra actions as it unwinds. You
might also try writing w/o gotos which can also cause unexpected
actions and make it difficult to explain code to others.

Good Luck!

[Bruce Lawrence]

First, thanks for taking some time to review my code. I admit its not
structured the best but it was my first stab at VB with access.

To sum the code up as simple as I can.... There is an SQL passthrough
query called "lookup_emp_id" that taps into an SQL server that provides
a list of employee names and their clock numbers. Its defined on the
query tab.

get_clocks asks for 2 employee numbers. It then goes to verify_clock
which takes the 2 numbers they entered from step one and looks them up
in the SQL pass through query. If it finds each record clocknum 1
time, it goes to append. Otherwise if one of those are wrong, it tells
them someone isnt clocked in and goes back to get_clocks for 2 numbers
again. It continues to do this until dcount on both = 1.

The problem is that if you put 1 of those numbers in wrong, it doesn't
find them. Infact if you enter a wrong number in 2 times it loops 2
times. You'll notice that after step7 is called, it should end the
function but it doesn't. It goes back to the code where it found a
wrong number.

[Bruce Lawrence]

Just to let you all know, we fixed the problem.

The issue was that I was calling a function B from within function A
and when the error occured in B, it was going back to the beginning of
A which in turn called B all over again and hence the "loop".

What I did was created an extra function that gathers the clock numbers
and does not call any other functions. Hard to explain but sleep well
knowing my problem is resolved :)

Thanks to everyone that took their time out to look at this.