Results 1 to 5 of 5

Thread: Please Help me.

  1. #1
    Join Date
    Dec 2003
    Posts
    28

    Unanswered: Please Help me.

    I really need to modify a field specified in another field... that simple. Someone please let me know how this can be done. Thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Re: Please Help me.

    Originally posted by PainGypsy
    I really need to modify a field specified in another field... that simple. Someone please let me know how this can be done. Thanks.
    In other words, some field contains the name of the field that is to be modified? In other words, there exists a table...
    Code:
       name_of_field_to_update   change_to_value
       -----------------------   ---------------
       balance_due                    245368.00
       total_fines                       111.22
       balance_due                      4330.89
       ...
    The number of possible values in the "name_of_field..." field will of course be limited, and this suggests an algorithm that could be used:
    Code:
      "SELECT DISTINCT name_of_field_to_update" ...
      for each field-name returned (call it "f"):
        build and execute an SQL string:
          "UPDATE ... SET " & f & " = change_to_value WHERE name_of_field_to_update = '" & f & "'"
    
    So you construct:  (for example)
      UPDATE .. SET balance_due = change_to_value WHERE name_of_field_to_update = 'balance_due' 
      UPDATE .. SET total_fines = change_to_value WHERE name_of_field_to_update = 'total_fines' 
    ...
    Thus you would build and execute, first, a query that updates all the balance_due columns, all at once. Then a second query would update the total_fines columns, also all-at-once. Q.E.D.

    Obviously the SQL-string I have constructed might be much too simple. (It is, in fact: I haven't talked about "id" columns in any way whatever.) But you get the idea: that you construct SQL strings based on data in the table.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Dec 2003
    Posts
    28

    Thank you so dearly and from the bottom of my heart!

    I have considered a way similar to that, but I want the field_to_be_changed to be a drop down list from the field names of a table and that table may change and I don't want to have to add code for each added field. So what I need is a way to say (field_to_be_changed) = new value

    I greatly appreciate your solution and it is a good one. I posted this issue before and got no responses at all.

    Is there some function to put the contents of a field into the left of an equation or can you do this with recordsets such as rstRecordset.fields(field_to_be_changed) = new value ???

    Thanks again so much for your reply!

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Re: Thank you so dearly and from the bottom of my heart!


    I have considered a way similar to that, but I want the field_to_be_changed to be a drop down list from the field names of a table and that table may change and I don't want to have to add code for each added field. So what I need is a way to say (field_to_be_changed) = new value..
    Kindly notice that you wouldn't have to "add new code for each field." If you store the exact name of the field to be changed (whether you derive it from a combo-box or whatever), then the logic I described earlier will work in principle no matter what the field name is.

    Carefully re-read the post. The logic queries the database to obtain the list of field-names on the fly, then it constructs a query for each field-name found.

    You do have to be careful when designing such a program's user-interface: you probably don't want an id field to be eligible for example. And you have to be mindful of data-types and so on. So my reply was only intended to be an illustration, but it does loosely describe the concept of a workable design that will not require code modifications for each new field.

    HTH.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Dec 2003
    Posts
    28
    Ahhhhh I see Thank you for beating it into my head. I think I got it, I have mostly been using VB code to do the data work. I don't know sql very well. But If I understand correctly, you build a set of queries on the fly and then update the one that applies.

    I truly appreciate your help. I hope I can implement it.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •