Solved

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

Posted on 2016-07-25
15
34 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
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 34

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 57
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
 

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 57
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 57
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 250 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now