Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

iterate thru access form and update database based on name of control

Posted on 2016-07-25
15
Medium Priority
?
57 Views
Last Modified: 2016-07-26
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'"
         CurrentDb.Execute strSQL
         End If
         Next CtlVar
0
Comment
Question by:220-221
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 39

Expert Comment

by:PatHartman
ID: 41728158
I don't see where you are using the value of the control in the query.  Please post the table def for both tables along with some sample data.
0
 
LVL 58
ID: 41728167
Rather than loop through all the controls on the form, the syntax your looking for is:

For intK = 1 to 6
      = Me("somebasecontrolname" & intK)
Next intK

Jim.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 41728211
You could put some text into the Tag property of the six textboxes, and cycle through the form controls, running your code for each control with this tag, using code similar to this (though possibly on another event procedure, or a command button):

Private Sub Form_BeforeUpdate(Cancel As Integer)
      
   Dim ctl As Access.Control
   
   For Each ctl In Me.Controls
      Debug.Print "Testing " & ctl.Name
      If ctl.Tag = "Required" Then
         Debug.Print "Control value: " & ctl.Value
         If Nz(ctl.Value) = "" Then
            ctl.BackColor = vbRed
            Cancel = True
         Else
            ctl.BackColor = vbWhite
         End If
      End If
   Next ctl

End Sub

Open in new window

0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:220-221
ID: 41728228
Jim,

What do you mean by "somebasecontrolname"
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 41728233
If you post the database, I could modify my Cycle code to do what you want.
0
 
LVL 58
ID: 41728248
<<What do you mean by "somebasecontrolname">>

 So say you have controls named Text1, Text2, Text3, ....Text6:

= Me("Text" & intK)

 The point being that you can refer to objects using a string, which can be replaced by a variable.

So things like:

 Forms("someformname")("somecontrolname")

can be done like this:

Dim strFormName as string
Dim strControlName as string

strFormName = "someformname"
strControlName = "somecontrolname"

 =Forms(strFormName)(strControlName)

works.

Actually, that syntax is what Access uses internally.  When you do:

Forms![SomeFormName]![SomeControlName]

Access translates it to the ( ) style syntax before working with it.

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41728252
I think I understand your question (think!)
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.  


Not presumably
For Each CtlVar In Me.Controls
...stuff
Next CtlVar


This loop is going to execute once for each textbox on the form.
That does not seem to be what you want.

So you are going to move
CurrentDb.Execute strSQL
Outside the loop.

Now, what do you REALLY want to execute?
Me, I'm not a guy who does SQL for something so simple.
Recordset code is my friend

Dim db as databsse
dim rs as recordset

set db = currentdb ' get a database object going
set rs = db.OpenRecordset("Select * from " & strTable, dbOpenDynaset, dbSeeChanges)
'Open the table to add a record
With rs
    .AddNew 'recordset code for an insert
    If Me.TheFirstControl.Value <> 0 Then
        !TheFirstStep= Me.TheFirstControl.Value
    End if
    If Me.TheSecondControl.Value <> 0 Then
        !TheSecondStep = Me.TheSecondControl.Value
    End if
    If Me.TheThirdControl.Value <> 0 Then
        !TheThirdStep = Me.TheThirdControl.Value
    End if
    If Me.TheFourthControl.Value <> 0 Then
        !TheFourthStep = Me.TheFourthControl.Value
    End if
    If Me.TheFifthControl.Value <> 0 Then
        !TheFifthStep = Me.TheFifthControl.Value
    End if
    If Me.TheSixthControl.Value <> 0 Then
        !TheSixthStep = Me.TheSixthControl.Value
    End if
    .Update 'do the insert
End With

Open in new window


Now if the controlnames are all of a type like SomeNameX, and the fields are of a type SomeFieldX you can use a loop

For x = 1 to 6
    If Me.Controls("SomeField" & x).value <> 0 Then
        rs.Fields("SomeField" & X) = Me.Controls("SomeField" & x).value
    End if
next x
0
 

Author Comment

by:220-221
ID: 41728276
Thanks Jim,

I may have to go back to the drawing board.  I get what you are saying up to a point, how do I name those controls such that it refers back to [Standard Name] and copies the desired row?  I'm clearly not skilled in coding but I follow patters and direction well.   Except for today

For intk = 1 To 6
         strSQL = "Insert INTO tblTempTest Select * FROM Step9 Where [Parent Practice Name]='" & Me.NameXX & "'  and Step9.[Standard Name]= '" & Me("Text" & intk) & "' and YQ='2016-03'"
         CurrentDb.Execute strSQL
         Next intk
0
 
LVL 58
ID: 41728294
<<how do I name those controls such that it refers back to [Standard Name] and copies the desired row?  >>

   You need to tell us a bit about what the setup is in this form and what your trying to accomplish.

Jim.
0
 

Author Comment

by:220-221
ID: 41728313
Please see the attached image.  

The text boxes under the "Desired %" header are named what the labels are.  
So the text box showing 2% is named "Abraxane".
If that textbox does not equal zero,
 I want to copy the line from the table named Step9
that has that corresponding field [Standard Name] ="Abraxane"
and put it in a second table for manipulation.

The code would skip Alimta, Avastin and then do the same for Bendeka - copy the line from Step9, because the value of the text box "Bendeka" is non zero
Image-30.png
0
 

Author Comment

by:220-221
ID: 41728370
I guess what I'll do is deal with the duplicate entries but subsequently running an select query with grouping and throw that into another new table for updating.  May not be elegant but simple at this point.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1000 total points
ID: 41728467
Ok, I did say (think!)
Generally, in the db world, we discuss data as fields (columns) and rows.
Your original code was copying-and-inserting one row (with we don't know how many fields) six times -- and you said that wasn't quite what you needed.

If all the drugs have non-zero values, do you want to copy and insert 6 full, different rows of data from Step9?

if that is the case, then something like this will work
Dim TheWhere as string
Dim OriginalSQL as string
OriginalSQL = "strSQL = "Insert INTO tblTempTest Select * FROM Step9 Where [Parent Practice Name]='" & Me.NameXX & "'  and Step9.["
DIm CtlVar as Variant
For Each CtlVar In Me.Controls
     If CtlVar.ControlType = acTextBox Then
     strSQL ="" 'reset the SQL String!
        Select Case True 'only a true outcome will execute
            Case CtlVar.Name = "Abraxane" And CtlVar.Value <> 0
                TheWhere ="Abraxane] = " & CtlVar.Value & "' and YQ='2016-03'"
            Case CtlVar.Name = "Alimta" And CtlVar.Value <> 0
                TheWhere ="Alimita] = " & CtlVar.Value & "' and YQ='2016-03'"
            Case CtlVar.Name = "Avastin" And CtlVar.Value <> 0
                TheWhere ="Avastin] = " & CtlVar.Value & "' and YQ='2016-03'"
            Case CtlVar.Name = "Bendeka" And CtlVar.Value <> 0
                TheWhere ="Bendeka = " & CtlVar.Value & "' and YQ='2016-03'"
            Case CtlVar.Name = "Abraxane" And CtlVar.Value <> 0
                TheWhere ="Cyramza] = " & CtlVar.Value & "' and YQ='2016-03'"
            Case CtlVar.Name = "Erbitux" And CtlVar.Value <> 0
                TheWhere ="Abraxane] = " & CtlVar.Value & "' and YQ='2016-03'"
            Case Else
                 Goto Skip 'no match for name and non-zero
        End Select
        strSQL = OriginalSQL & TheWhere
        CurrentDb.Execute strSQL 'execute the SQL created in the Select case
    End If
Skip:
'jump to skip when no line needs insertion
Next CtlVar     

Open in new window

0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 41729332
What Nick has given you will work, but as an aside your approaching the database/form design in the wrong way and that's making it tougher to get this done.   For example, what happens when you have another drug?   Your going to be changing your form and your code.

What you should be doing is having it data driven, so you'd be just dealing with different records.   In this case, your form would have six records that it's currently displaying, with three columns for each record:

DrugName
DesiredPct
CurrentPct

Now rather than looking at specific controls with specific names, you could loop through the forms record set, and execute the same SQL statement each time (you'd refer to the same controls, which have different data in them for each record).

This is what Nick was referring too in the first part of his last comment.

Jim.
0
 

Author Comment

by:220-221
ID: 41729354
Both your points are well made and in future upgrades I'll approach it with these methods in mind.  I was able to get around the issue by having two temp tables - one that inserts the row multiple times - one for each text box - followed by a query that groups to remove duplicates and creates a new table for manipulation.  Again, not elegant but functional.   I actually have 20+ items on the form and can add and delete just fine I simplified the form to 6 on discussion to make it easier to digest.  I got so far down my own rabbit hole that to redo was untenable - plus I'm stubborn - but I will use these tips the next time.  BTW the form submission and data return is <1s.    Points to you both - thanks!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41729670
I'm glad that we were helpful, but I don't think we actually solved your problem.
Still, if in the future what we posted helps you get things straightened out, that's good.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question