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:

Public Sub btnCopyRecord_Click()

    If Not Me.NewRecord Then
        DoCmd.OpenQuery "Copy Equipment"
        DoCmd.RunCommand acCmdCloseWindow
        DoCmd.OpenForm "Equipment", , , "EquipNumber='" & CopyEq & "'", acFormEdit
        
    Else
        Beep
    End If
End Sub

The query that is being opened:

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 )
SELECT [B]CopyEq() AS NewNum[/B], 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
FROM Equipment
WHERE (((Equipment.EquipNumber)=[forms]![Equipment]![EquipNumber]));

Within that query, "CopyEq() AS NewNum" initiates a module:

Public Function CopyEq() As String
    CopyEq = InputBox("Enter new Equipment Number:", "New Equipment Number", 0)
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.

[Rabbit]

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.

[NeoPa]

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.

[mjoachim]

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.

[mjoachim]

An interesting new development. I discovered that the initial repetitive prompts were due to the query's WHERE criteria:

    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 )
    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
    FROM Equipment
    WHERE (((Equipment.EquipNumber)=[B][forms]![Equipment]![EquipNumber][/B]));

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.

DoCmd.OpenForm "Equipment", , , "EquipNumber='" & [B]CopyEq[/B] & "'", 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.

Public Function EqCopy() As String
    [B]NewNumInput[/B] = InputBox("Enter new Equipment Number:", "New Equipment Number", 0)
    EqCopy = NewNumInput
End Function

If Not Me.NewRecord Then
        DoCmd.OpenQuery "Equip Copy"
        DoCmd.RunCommand acCmdCloseWindow
        DoCmd.OpenForm "Equipment", , , "EquipNumber='" & [B]NewNumInput[/B] & "'", acFormEdit
    Else
        Beep
    End If
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.

[NeoPa]

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.