472,805 Members | 966 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Function Module looping problem

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(),

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"
MsgBox "Step2"
GoTo Verify_Loader
End If

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


'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)
"('" & Packer & "','" & packname & "','" & order & "',#" &
Now() & "#,'" & machno & "')"

SQLstr2 = "INSERT INTO history (clockno, empname,ordno,curdate,machno)
"('" & 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.

Nov 13 '05 #1
5 1734
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.

Nov 13 '05 #2

Nov 13 '05 #3
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!

Nov 13 '05 #4
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.

Nov 13 '05 #5
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.

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Randell D. | last post by:
Folks, I feel like pulling my hair out - I tried unsuccessfully over the past few days to install ImagMagick but because of version conflicts and missing libraries I had to give up. I originally...
by: Marc Shapiro | last post by:
I am relatively new to python (I have used it on and off for a few small projects over the last few years) so I imagine that what I am trying to do has already been done, but practical experience,...
by: Alex | last post by:
Hi all, I've found a module that I think will help me combine fields properly, but I'm unsure how to add or use it with Access 2000. Below is the module I'd like to add: ...
by: deko | last post by:
I have a function with a number of long loops. While the function is running, I want to be able to click a Stop button and exit the function as quickly as possible. The abbreviated code looks...
by: didier.doussaud | last post by:
I have a stange side effect in my project : in my project I need to write "gobal" to use global symbol : .... import math .... def f() : global math # necessary ?????? else next line...
by: downwitch | last post by:
Hi all, Wondering if there is a way to determine that a given module is a class module or not. In looping through the AllModules collection, it would be useful for me to do different things...
by: DanielJohnson | last post by:
how to use the combination function in python ? For example 9 choose 2 (written as 9C2) = 9!/7!*2!=36 Please help, I couldnt find the function through help.
by: xkenneth | last post by:
Looking to do something similair. I'm working with alot of timestamps and if they're within a couple seconds I need them to be indexed and removed from a list. Is there any possible way to index...
by: RandomElle | last post by:
Hi there I'm hoping someone can help me out with the use of the Eval function. I am using Access2003 under WinXP Pro. I can successfully use the Eval function and get it to call any function with...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.