Results 1 to 8 of 8

Update certain field in table based on other fileds in the same table

  1. #1
    stalk is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    21

    Update certain field in table based on other fileds in the same table

    Coded this in another programing language but having trouble implementing in Access VBA module. Posted in programming forum but no luck.
    Can some one assist me?

    No error is showing when I run this code, at the same time results are not accurate. It is working and populating only InB_Results and the other fields.

    1) I have to do simple logic based on _ct( all fields). for Ex: if the InA_ct is 0 then InA_result =0 else if InA_ct> 0 then InA_result should be 1 otherwise InA_Result should be 9. This should repeat for all the _Result fields.
    2) For all the _date fields I need to populate the value of date_tested ( a field in the same table). I know it is duplication but need to export to excel in this same format for some other purpose.
    My _Date field should be text where as date=_tested is real date datatype.

    Code:
    Public Const ct = "InA_Ct,InB_Ct,H1_Ct,Hx_Ct, RP_Ct"
    Public Const Dt = "InA_Date,InB_Date,H1_Date,Hx_Date,RP_Date"
    Public Const result = "InA_Result,InB_Result,H1_Result,Hx_Result,RP_Result"
     Sub arr_update()
        Dim objDB As DAO.Database
       Dim mytbl As DAO.Recordset
        Set objDB = CurrentDb()
        Set mytbl = objDB.OpenRecordset("Temp_RESULTS")
         Dim array_ct, array_dt, array_r, i
        Dim result_reported As String
        array_ct = Split(ct, ",")
        array_dt = Split(Dt, ",")
        array_r = Split(result, ",")
        For i = 0 To 4
        While Not mytbl.EOF
        mytbl.Edit
         mytbl.Fields(array_dt(i)).Value = date_tested
        If (mytbl.Fields(array_ct(i)).Value > 0 And mytbl.Fields(array_ct(i)).Value <= 32.99) Then
           mytbl.Fields(array_r(i)).Value = 1 'Positive
        ElseIf (mytbl.Fields(array_ct(i)).Value = 0) Then
           mytbl.Fields(array_r(i)).Value = 0 'Negative
        ElseIf mytbl.Fields(array_ct(i)).Value = Null Then
           mytbl.Fields(array_r(i)).Value = 9 'Not tested
            End If
        mytbl.Update
        mytbl.MoveNext
    Wend
    mytbl.MoveFirst 
    Next I
    Any help is much appreciated.
    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,846
    use a lookup table,
    put in the field value settings, and the result to update

    field1, field2, result
    A, A, A
    A, B, C
    etc

    then use an update query that joins the 2 tables,to update the field.
    you shouldnt have to use code.

  3. #3
    stalk is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    21
    Can you be more clear please?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,786
    This can be done using an update query, no need for code. For each field in the table, set the Update To value of InA_result to
    "if the InA_ct is 0 then InA_result =0 else if InA_ct> 0 then InA_result should be 1 otherwise InA_Result should be 9"
    =IIf(InA_ct=0,0,IIf(InA_ct>0,1,9))
    etc

  5. #5
    stalk is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    21
    But I have to apply this logic for all the 5 fields(InA_Ct,InB_Ct,H1_Ct,Hx_Ct, RP_Ct) may be more fields in future, so thought the code will be faster than an update query.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,786
    In the code above you have hard-coded the field names so any future changes will require development time. You could use a query for this, far easier and just as fast (if not faster). In either case any fields that are added in the future will require manual effort.

    If you want this routine to be available for use for all future enhancements, then it will need to be generic. Write the routine to have a loop which reads all the fields in the table and if it ends in "_ct" then apply your logic. Same for "_date".

  7. #7
    stalk is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    21
    Actually, I tried update query and it's pretty good. One time coding for all the 5 fields and this update query can be used over and over. Thank you so much.

    How do I mark this question as Solved? can someone mark this as solved!! Thank you so much.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,786
    Look under the Thread Tools at the top right.

    Glad you have got it working.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 11-23-2017, 01:03 PM
  2. Replies: 5
    Last Post: 11-01-2012, 08:26 AM
  3. Replies: 1
    Last Post: 08-31-2011, 03:03 PM
  4. Replies: 3
    Last Post: 01-17-2011, 12:48 AM
  5. Replies: 4
    Last Post: 09-03-2009, 01:01 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
  •  
Tech Forums: Microsoft Office Forums