iterate thru access form and update database based on name of control
Posted on 2016-07-25
I have an access form where there are six text boxes on which the user may or may not wish to input values.
The desired behavoir is to iterate thru the text boxes and if the user changed the value from the default of zero, copy that line in the table "Step9" into another table "tblTempTest". The six text boxes are uniquely named, corresponding to the column [Standard Name] in "Step9" I wish to have that line copied once however, when this is executed, it copies the desired line six times presumably once for each text box.
strTable = "tblTempTest"
For Each CtlVar In Me.Controls
If CtlVar.ControlType = acTextBox Then
If CtlVar.Value > 0 Then strSQL = "Insert INTO " & strTable & " Select * FROM Step9 Where [Parent Practice Name]='" & Me.NameXX & "' and Step9.[Standard Name]='" & CtlVar.Name & "' and YQ='2016-03'"