473,322 Members | 1,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Function Cycles Multiple Times

mjoachim
Can anyone explain to me why my function loops through 4 times when I expect it to only run through once?

The end goal is to copy a record and assign a new equipment number during the process.

My button click code:
Expand|Select|Wrap|Line Numbers
  1. Public Sub btnCopyRecord_Click()
  2.  
  3.     If Not Me.NewRecord Then
  4.         DoCmd.OpenQuery "Copy Equipment"
  5.         DoCmd.RunCommand acCmdCloseWindow
  6.         DoCmd.OpenForm "Equipment", , , "EquipNumber='" & CopyEq & "'", acFormEdit
  7.  
  8.     Else
  9.         Beep
  10.     End If
  11. End Sub
The query that is being opened:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Equipment ( EquipNumber, [Desc], AddInfo, AdgndTo, Model, EqYear, Specifications, SerialNum, Dept, Class, Type, OperationalCode, OwnershipCode, UtDHour, UtDMiles, UtDMeter, LicenseNum, LicenseExp, DateAssigned, StatusCode, StatusDate, InternalLocWare, VendorNum, UDCBudHours, UDCHourAtAcq, UDCPrevEqID, UDCAppTradeValue, UDCTradedEqNum, JobNum, SubJobNum, CostDist, CostType, ChargeStartDate, AcqDate, AcqMarketValue, AcqAmount, AcqRent, [Rate Type], RateJob, RateSubJob, RateLabor, RateParts, RateTires, RateRent, RateGET, RateFuel, RateOverhead, RateOwnership, RateSupport, [User], EnteredDate, Exported )
  2. SELECT CopyEq() AS NewNum, Equipment.Desc, Equipment.AddInfo, Equipment.AdgndTo, Equipment.Model, Equipment.EqYear, Equipment.Specifications, Equipment.SerialNum, Equipment.Dept, Equipment.Class, Equipment.Type, Equipment.OperationalCode, Equipment.OwnershipCode, Equipment.UtDHour, Equipment.UtDMiles, Equipment.UtDMeter, Equipment.LicenseNum, Equipment.LicenseExp, Equipment.DateAssigned, Equipment.StatusCode, Equipment.StatusDate, Equipment.InternalLocWare, Equipment.VendorNum, Equipment.UDCBudHours, Equipment.UDCHourAtAcq, Equipment.UDCPrevEqID, Equipment.UDCAppTradeValue, Equipment.UDCTradedEqNum, Equipment.JobNum, Equipment.SubJobNum, Equipment.CostDist, Equipment.CostType, Equipment.ChargeStartDate, Equipment.AcqDate, Equipment.AcqMarketValue, Equipment.AcqAmount, Equipment.AcqRent, Equipment.[Rate Type], Equipment.RateJob, Equipment.RateSubJob, Equipment.RateLabor, Equipment.RateParts, Equipment.RateTires, Equipment.RateRent, Equipment.RateGET, Equipment.RateFuel, Equipment.RateOverhead, Equipment.RateOwnership, Equipment.RateSupport, Equipment.User, Equipment.EnteredDate, Equipment.Exported
  3. FROM Equipment
  4. WHERE (((Equipment.EquipNumber)=[forms]![Equipment]![EquipNumber]));
Within that query, "CopyEq() AS NewNum" initiates a module:
Expand|Select|Wrap|Line Numbers
  1. Public Function CopyEq() As String
  2.     CopyEq = InputBox("Enter new Equipment Number:", "New Equipment Number", 0)
  3. End Function
The overall process works and successfully creates a copied record with a new equipment number, but in doing so, it cycles through the function input box several times.
Mar 14 '16 #1
5 1215
Rabbit
12,516 Expert Mod 8TB
What happens if you run just the select portion of the insert query? I'm thinking you have 4 rows in your equipment table with that EquipNumber.
Mar 14 '16 #2
NeoPa
32,556 Expert Mod 16PB
That's bizarre.

I wouldn't expect the function to be called for each record as it doesn't have a reference in it to any field.
Mar 15 '16 #3
Rabbit: If I isolate the select portion, the query prompts for the new equip number, then prompts for all the other unspecified fields, then cycles back to the new equip number prompt 2 more times. When testing this, I actually only have 1 record in my table, so the query shouldn't be trying to replicate multiple records.

To add to the oddity, when I step through the button click code, the input function cycles through 3 times after the query open line. Then once the query is complete and button code advances to OpenForm using the new number, the function prompt initiates again.

I have tried to decompile the database and have deleted and rebuilt the query using different parameter names in an attempt to avoid any hung up ghosts in the system. Neither worked.
Mar 15 '16 #4
An interesting new development. I discovered that the initial repetitive prompts were due to the query's WHERE criteria:
Expand|Select|Wrap|Line Numbers
  1.  
  2.     INSERT INTO Equipment ( EquipNumber, [Desc], AddInfo, AdgndTo, Model, EqYear, Specifications, SerialNum, Dept, Class, Type, OperationalCode, OwnershipCode, UtDHour, UtDMiles, UtDMeter, LicenseNum, LicenseExp, DateAssigned, StatusCode, StatusDate, InternalLocWare, VendorNum, UDCBudHours, UDCHourAtAcq, UDCPrevEqID, UDCAppTradeValue, UDCTradedEqNum, JobNum, SubJobNum, CostDist, CostType, ChargeStartDate, AcqDate, AcqMarketValue, AcqAmount, AcqRent, [Rate Type], RateJob, RateSubJob, RateLabor, RateParts, RateTires, RateRent, RateGET, RateFuel, RateOverhead, RateOwnership, RateSupport, [User], EnteredDate, Exported )
  3.     SELECT CopyEq() AS NewNum, Equipment.Desc, Equipment.AddInfo, Equipment.AdgndTo, Equipment.Model, Equipment.EqYear, Equipment.Specifications, Equipment.SerialNum, Equipment.Dept, Equipment.Class, Equipment.Type, Equipment.OperationalCode, Equipment.OwnershipCode, Equipment.UtDHour, Equipment.UtDMiles, Equipment.UtDMeter, Equipment.LicenseNum, Equipment.LicenseExp, Equipment.DateAssigned, Equipment.StatusCode, Equipment.StatusDate, Equipment.InternalLocWare, Equipment.VendorNum, Equipment.UDCBudHours, Equipment.UDCHourAtAcq, Equipment.UDCPrevEqID, Equipment.UDCAppTradeValue, Equipment.UDCTradedEqNum, Equipment.JobNum, Equipment.SubJobNum, Equipment.CostDist, Equipment.CostType, Equipment.ChargeStartDate, Equipment.AcqDate, Equipment.AcqMarketValue, Equipment.AcqAmount, Equipment.AcqRent, Equipment.[Rate Type], Equipment.RateJob, Equipment.RateSubJob, Equipment.RateLabor, Equipment.RateParts, Equipment.RateTires, Equipment.RateRent, Equipment.RateGET, Equipment.RateFuel, Equipment.RateOverhead, Equipment.RateOwnership, Equipment.RateSupport, Equipment.User, Equipment.EnteredDate, Equipment.Exported
  4.     FROM Equipment
  5.     WHERE (((Equipment.EquipNumber)=[forms]![Equipment]![EquipNumber]));
  6.  
If I put a constant value in place of the form reference, it didn't loop the 2 additional times. Because of that, I added another public function that grabs the form value and passes it to the query in a variable. This works.

The final time that the function was called appear to be due to how I was opening the new form.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Equipment", , , "EquipNumber='" & CopyEq & "'", acFormEdit
The CopyEq reference was calling the function again. To resolve this, I added another variable to the function that can be referenced from the sub.
Expand|Select|Wrap|Line Numbers
  1. Public Function EqCopy() As String
  2.     NewNumInput = InputBox("Enter new Equipment Number:", "New Equipment Number", 0)
  3.     EqCopy = NewNumInput
  4. End Function
Expand|Select|Wrap|Line Numbers
  1. If Not Me.NewRecord Then
  2.         DoCmd.OpenQuery "Equip Copy"
  3.         DoCmd.RunCommand acCmdCloseWindow
  4.         DoCmd.OpenForm "Equipment", , , "EquipNumber='" & NewNumInput & "'", acFormEdit
  5.     Else
  6.         Beep
  7.     End If
  8. End Sub
This works as is, but I feel like I am not utilizing the function in the best way. Please offer up any suggestions as to how I should improve my coding.
Mar 15 '16 #5
NeoPa
32,556 Expert Mod 16PB
I can only repeat that, from the information you share, this shouldn't be happening.

Like the extra call to EqCopy() after the query's already run, I suspect this isn't triggered within the query as stated at all.

More than that I can't say as we only have what's shared. I suggest you look at the query in isolation and determine exactly what's happening where.
Mar 16 '16 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Jonathan Duke | last post by:
I have written a custom session state provider that stores session data in XML in a SQL database , and I was running the SQL profiler to verify that all of my stored procedures were called in the...
22
by: Brett Romero | last post by:
If my UI app uses three DLLs and two of those DLLs reference something named utilities.dll, does the UI app load utilities.dll twice or does the compiler recognize what is going on and load...
0
by: Fabuloussites | last post by:
I have a user control that i need to do some processing in a fuction when it is first loaded. I have tried to add my code to the page_load and page_init fuctions. when i run a debugger, i notice...
2
by: skip | last post by:
I'm trying some stuff with Psyco and am confused about its apparent desire to compile the same function (or method) multiple times). Here's an abstract from a recent run: 12:45:15.99 tag...
6
by: yk | last post by:
Hi, Is it a technique available in html/javascript in order to display same image many many times on a same page? Because of a large page loading I am looking for a way not to have same...
0
by: grbCPPUsr | last post by:
I am new to Python. I would like to use Python for the specialized purpose of dynamic expressions parsing & evaluation in my C++ application. I would like to encapsulate the expressions to be...
5
by: billa856 | last post by:
Hi, My project is in MS Access 2002. In that I want to open one form multiple times. I put one button on my main form. Now whenever I click on that button than form will be open. Now when I...
6
by: eefutah | last post by:
I'm using the Concat function from this thread, and I'm wondering if it's possible to run the function multiple times, so it won't save what's been passed by previous records with the same IOSC? ...
1
matheussousuke
by: matheussousuke | last post by:
In my page: http://sitesdemo.mghospedagem.com/ivam-entregas/3/33209.html I tried disabling a few javascript related to Google Maps, but Chrome Console is still pointing me the same errors, saying...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.