Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-07-25
15
Medium Priority
?
59 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 40

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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

916 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