Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    Retain and Concat field name from previous iteration with positive value

    SampleId
    A1_Ct A2_ct A3_Ct Up1_Ct Up2_Ct PI1_Ct PI2_Ct Results
    S1 32.37 A1
    s2


    23.43 32.53 A2,PI1
    S3 22.11 44.32 25.23 29.92 28.2 A1,A2,A3,Up1,Up2

    What changes do I need to make for the following code to automatically update the results field which will be empty in the table?
    Code:
    Public Const ct = "A1_Ct, A2_Ct, A3_Ct,Up1_ct, Up2_ct, PI1_Ct,PI2_Ct"
    
    Sub arr_update()
    
    Dim ObjDB as DAO.Database
    Dim mytbl as DAO.Recordset
    set ObjDB =CurrentDb()
    Set mytbl= objDB.OpenRecordset("TP_Test)
    
    Dim Array_Ct
    Dim Results as String
    Dim i as Integer
    array_Ct= Split(ct,",")
    
    For i=0 to 9
    While Not mytbl.EoF
    
    mytbl.Edit
    IF(mytbl.Fields(array_ct(i)).value>0 and mytbl.Fields(Array_Ct(i)).value <=35.99 Then
    results= array_Ct(i)
    End IF
    mytbl.Update
    mytbl.MoveNext
    Wend
    mytbl.MoveFirst
    Next i
    End Sub
    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need arrays in access, the recordset is the array.

    either use a recordset to fill the field name where SampleID= "s1" (or any where statement)
    or use an update query the same way.

  3. #3
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    I have about 54 fields and need to loop through all those fields to check if they have positive values and then update the Results field.
    I want this update to be automatically process when I upload the excel file every day with different sample names.


    Can you send m an example?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can try this --I mocked up your table and included some debugging.
    In your Const you have leading spaces which cause problems leading to errors

    Code:
    Public Const ct = "A1_Ct,A2_Ct,A3_Ct,Up1_ct,Up2_ct,PI1_Ct,PI2_Ct"
    
    
    Sub arr_update()
    
    10        On Error GoTo arr_update_Error
              Dim strHold As String
              Dim ObjDB As DAO.Database
              Dim mytbl As DAO.Recordset
    20        Set ObjDB = CurrentDb()
    30        Set mytbl = ObjDB.OpenRecordset("TP_Test")
    
              Dim Array_Ct
              Dim Results As String
              Dim i As Integer
    40        Array_Ct = Split(ct, ",")
    50        Do While Not mytbl.EOF
    60            For i = 0 To UBound(Array_Ct) - 1
    70                Debug.Print Array_Ct(i) & "  " & mytbl.Fields(Array_Ct(i))
    
    
    80                If (mytbl.Fields(Array_Ct(i)) > 0 And mytbl.Fields(Array_Ct(i)) <= 35.99) Then
    90                    strHold = strHold & Array_Ct(i) & ","
    100                   Debug.Print i & "  " & Array_Ct(i) & "  " & strHold
            
    110               End If
    
                      '
                      '       mytbl.MoveFirst
    120           Next i
    130           Debug.Print "Len strhold " & Len(strHold)
    140           mytbl.Edit
    150     If Len(strHold) > 0 Then
    160               mytbl!result = Mid(strHold, 1, Len(strHold) - 1)
    170               Debug.Print "New record result---> " & Mid(strHold, 1, Len(strHold) - 1)
    180               mytbl.Update
    190     End If
    200           strHold = ""
    210           mytbl.MoveNext
    220       Loop
    230       On Error GoTo 0
    240       Exit Sub
    
    arr_update_Error:
    
    250       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure arr_update, line " & Erl & "."
    
    End Sub
    Hope it's helpful.

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Wow!! Thank you so much Orange. It worked 95%. Found minor issue I am trying to work on.. is
    1) if the first field say A1_ct= 45.0 and the next field say Up2_Ct = 23.45. Then the result is blank it is not picking up Up2_Ct.
    2 If all field values is zero then Result should be Negative .
    3) I realized I should chop off _Ct part for all the Results. For Ex: A1, A2,A3 instead of A1_ct, A2_ct,A3_Ct
    4) Some how the last field(PI2_Ct is not showing in the result even if it is positive.

    I will work on these minor issues an let you know.
    But thank you so much for guiding me in right direction.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    stalk,
    I think we need more info about your requirement.
    Details needed in order to offer more focused assistance.
    If all fields are 0,what exactly should be Result?

    I modified line 60 as follows
    60 For i = 0 To UBound(Array_Ct) 'removed the -1


    And got this result with my mockup

    ID A1_Ct a2_ct a3_ct up1_ct up2_ct PI1_Ct PI2_Ct Result
    2 11.2 36.86 43.17 12.8 12.6 36.9 28.5 A1_Ct,Up1_ct,Up2_ct,PI2_Ct
    3 13.44 23.5 44990 16.9 13 39.43 30.09 A1_Ct,A2_Ct,Up1_ct,Up2_ct,PI2_Ct
    4 -3 6 13 0 0 40.5 0 A2_Ct,A3_Ct
    5 1 0 0 0 13 410 0 A1_Ct,Up2_ct
    6 45 0 0 0 23.45 0 0 Up2_ct
    7 12 0 0 0 0 32 29 A1_Ct,PI1_Ct,PI2_Ct
    8 0 0 0 0 0 0 6 PI2_Ct
    9 0 0 0 0 0 0 0

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Nice code orange.

    I was on hold for a long while (seemed like forever)..... and, well, I modified your code a little. Apologies...
    Code:
    Option Compare Database
    Option Explicit
    
    Public Const ct = "A1_Ct,A2_Ct,A3_Ct,Up1_ct,Up2_ct,PI1_Ct,PI2_Ct"
    Public Const st = "A1,A2,A3,Up1,Up2,PI1,PI2"   '<<- same as array ct, but without the "_ct"
    
    Sub arr_update()
    
    10        On Error GoTo arr_update_Error
               Dim strHold As String
               Dim ObjDB As DAO.Database
               Dim mytbl As DAO.Recordset
               Dim Array_Ct
               Dim Results As String
               Dim i As Integer
    
               Dim Array_St
               Dim MinVal As Double    '<<- variable for min value
               Dim MaxVal As Double    '<<- variable for max value
               Dim AllZero As String   '<<- variable for all zero values
    
    
    20         Set ObjDB = CurrentDb()
    30         Set mytbl = ObjDB.OpenRecordset("TP_Test")
    
    40         Array_Ct = Split(ct, ",")
    50         Array_St = Split(st, ",")
    
    '-----------------------------------------
    60         MinVal = 0
    70         MaxVal = 35.99
    80         AllZero = "- All Zeros -"  '<<- text to display if all values are NULL/Empty or 0
    '-----------------------------------------
    
    90         Do While Not mytbl.EOF
    100            For i = 0 To UBound(Array_Ct)
    110                Debug.Print Array_Ct(i) & "  " & mytbl.Fields(Array_Ct(i))
    
    120                If (mytbl.Fields(Array_Ct(i)) > MinVal And mytbl.Fields(Array_Ct(i)) <= MaxVal) Then
    130                    strHold = strHold & Array_St(i) & ", "   '<<- added a training space to separate value after comma
    140                    Debug.Print i & "  " & Array_St(i) & "  " & strHold
    150                End If
    
    160            Next i
    
    170            Debug.Print "Len strhold " & Len(strHold)
    
    '---------- Update Result field ---------------------------
    180            mytbl.Edit
    190            If Len(strHold) > 0 Then
    200                strHold = Left(strHold, Len(strHold) - 2)   '<<- remove trailing comma & space
    210            Else
    220                strHold = AllZero 
    230            End If
    240            mytbl!result = strHold        
    250            Debug.Print "New record result---> " & strHold
    260            mytbl.Update
    '---------- Update Result field ---------------------------
    
    270            strHold = ""
    280            mytbl.MoveNext
    290        Loop
    
    300        On Error GoTo 0
    
    ' ---- Clean up ----
    310        mytbl.Close
    320        Set mytbl = Nothing
    330        Set ObjDB = Nothing
    
    340        MsgBox "Done"
    
    350        Exit Sub
    
    arr_update_Error:
    
    360        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure arr_update, line " & Erl & "."
    
    End Sub

  8. #8
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you Steve and Orange.
    This is working only if TP_Test has the fixed field names. But my table TP_Test field names change for every run and will only have 6 to 7 '_Ct' fields out of 54 fields. What change should I make to get rid of Run time error: 3265 but keep all the possible 54 field names in Public Const Ct and St declaration and process few fields n tp_test ?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Steve and I are happy to help.
    I you have field names that change run to run then you'll have to address that in the logic of your program. We don't know why or what a _ct field is. 54 fields in a table is quite a lot --not unheard of, but more than typical. You might want to describe your project holistically and see what the business rules are; then revise your structure accordingly, if necessary.
    Many people would create a table(operational) with consistently named fields and bring the incoming data into a temp table with routines to move data from the temp table to the operational table. BUT, readers don't know the whole story of your set up, so any advice is more guess than fact.
    Why 54 fields? Why do field names change? What is the real business?What do the names mean? Lot's to learn and understand before specific advice can be given/tested.
    Good luck.

  10. #10
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Dear Orange,

    You are right. This TP_test is temp table it is just to process the run to run data to update just the result field.
    My master table has 54 fields.
    Business is similar to Blood test plate set up. One type of plate set up has 14 targets( _Ct fields) along with few record identifiers. Second plate set up will have 5 target _Ct fields and soon. I read these machine generated ct values into tp_test and want to automatically calculate the Result field and then update my master table from each run with all these values.
    One more task before updating master table is to output just the tp_test to excel to review because the machine generated format is not readable.
    While explaining to you I understood what I can do to fix this. I should create another operational table from tp_test with all the 54 _Ct fields and then run this array code to update and upload to the master table. Let me try this work flow. Will update you tomorrow.

    Thank you

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff. Sometimes reviewing responses or thinking about options leads to more analysis and clarity.
    Looking forward your update.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What orange said.


    Just curious:
    How many "Blood test plates" are there?

    "My master table has 54 fields." ...... is that because the table has the fields for all of the "Blood test plates"?

  13. #13
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Just for name sake there are 3 plate set up but in reality all the targets ( _Ct fields) are mix and matched while testing.
    Coming to the point : Instead of using the temperory tp_test I used the operational table to run through code which has 54 fields. But still getting item not found error.
    Also I realized using operational table will not work because: before uploading data into master table I would like to write to excel for peer review and want to see only the _Ct fields that we tested along with their results.

    I am still working on changing the code but doing slowly as this is not my top priority of the week. But I got the concept.

    By the way is there an issue with Accessforum. When I type some of the keys have issues or hit hard. I cannot copy paste code, I literally have to type code etc
    Thank you for checking

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please tell us more about this:
    By the way is there an issue with Accessforum. When I type some of the keys have issues or hit hard. I cannot copy paste code, I literally have to type code etc
    Thank you for checking
    As for your code/database:
    You need the complete specification. Trying to work on/develop a database without a plan or a data model can be an extremely long and circuitous, and frustrating journey if you do it by trial and error.

    Suggest you pause and write a description of what you really need. Seems we are addressing this one question at a time.
    Good luck with your project.

  15. #15
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Regarding issues with AccessForum website:
    I use Internet Explorer to ask or reply my questions. Recently I noticed many issues with this site.
    1) Issue with space bar. While typing space bar doesn't work 60% of the time. I have to go back and add space between words
    2) Cannot copy paste my code even with code tags
    3) Have to hit some of the keys hard or twice on my keyboard to type a sentence.
    4) Frequently get IE error and erases all the content I typed. Rarely the auto save function will save my day.

    I don't have issues with my key board anywhere else. Looks\sound strange but true issue I am facing on this site alone.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Concat field values in crosstab query?
    By stalk in forum Queries
    Replies: 5
    Last Post: 05-10-2019, 12:28 PM
  2. Retain previous user entry in form
    By sfspca1 in forum Forms
    Replies: 3
    Last Post: 12-03-2014, 06:07 AM
  3. Make a text box retain its previous value
    By Sidran in forum Access
    Replies: 3
    Last Post: 08-11-2014, 07:17 AM
  4. Last Name, First Name Concat Field
    By brianmcleer in forum Access
    Replies: 2
    Last Post: 06-05-2013, 12:18 PM
  5. Concat field names in Update SQL
    By Deutz in forum Queries
    Replies: 8
    Last Post: 09-21-2011, 05:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums