Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2002
    Posts
    10

    Unanswered: Running Code per Record Line

    I have a form which outputs the results of a query. I wish to fill an unbound field with the results of a calculation (must be code). However, when I run the form, the unbound field in every record is filled with the results of the calculation for the first record...

    This is a (very) simplified example of what I'm after...

    ABC 100 200 Result: ABC300
    DEF 200 250 Result: DEF450
    XYZ 100 100 Result: XYZ200


    This is what I get...

    ABC 100 200 Result: ABC300
    DEF 200 250 Result: ABC300
    XYZ 100 100 Result: ABC300

    It is as if the code is only run once.

    Any ideas?
    ACDickson

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    Unbound controls behave differently comparing with bound ones. If you use unbound controls, then it is your job to populate them with data.

    In your case you may use form's current event to populate unbound controls with data.

    Igor

  3. #3
    Join Date
    Mar 2002
    Posts
    10
    I am using the current event property and my code populates the unbound field.

    But, it is the FORM's current event propert not the record's, this is why it is calculating the contents of the unbound field just once per form, not per record as I would like.
    ACDickson

  4. #4
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey ac,

    Can you be little more specific with what your code looks like. When running queries you should be able to calculate each record and then apply those values to fields within a form. You may want to set up your query in code and then call it from your On Current event??? I am confused as to why your having problems.

    Kal

  5. #5
    Join Date
    Mar 2002
    Posts
    10
    I have a field called Rank which places a sequential number next to each record. GPs is a numerical value. The code below (Rank2) is aimed at dealing with ties in GPs. However, for it to work, the code must run on each record...


    Function Utility_Rank2()

    Dim x As Double
    Dim y As Double

    With CodeContextObject

    If .[Rank] = 1 Then
    x = .[GPs]
    End If

    y = .[GPs]

    If y < x Then
    .[Rank2] = .[Rank] + 1
    Else
    .[Rank2] = .[Rank]
    End If

    x = .[GPs]

    End With

    End Function


    If anyone knows an alternative way of doing this then I'd be glad to hear it.
    ACDickson

  6. #6
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey,

    Have you considered creating a query with the function IIF????

  7. #7
    Join Date
    Mar 2002
    Posts
    10
    As my original submission states, the calculation must be coded as other code (that my calculation relies upon) is only calculated on the form.

    If you know of a way to take raw data in a table, count the number of times certain information appears, sort (the count totals) in decending order and then rank them (including ties) - all in one query, then I would be ecstatic to hear how - I've been trying to do this (off and on) for about three years...
    ACDickson

  8. #8
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Wow, you sure can get testy!!! Although I would not normally help somebody who responds like that here is something that might help you.

    You can use different features of SQL to obtain those kind of results and then manipulate it with a report.

    Example:

    SELECT TOP n GroupField, Count(GroupField) AS FieldName
    FROM SomeTable
    GROUP BY GroupField
    ORDER BY Count(GroupField) DESC;

    The TOP will allow you to narrow your search if necessary, otherwise omit. Basically, this statement will count the number of records with a certain value and group them then sort the count descending (and include ties). Once you obtain those values create a report, (or form in your case) and create a row counter that in turn will give the values for ranking. Like (1, 2, 3, etc..)

    I hope you finally can achieve what you need.

    Later, Kal

  9. #9
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    hey ac,

    One more thing I just figured out. You can rank the records by applying another query to the one you just created. Just start a new query from the one listed previously. Change its alias name to something like NewName and Add your two fields (GroupField, FieldName) and then create a third column will a name like rank, then finally create a sub query within rank that references the first query.

    Like this:

    SELECT GroupField, FieldName, (SELECT Count(*) FROM FirstQuery WHERE FieldName > NewName.FieldName) + 1 AS Rank
    FROM FirstQuery AS NewName
    ORDER BY NewName.FieldName;

    This will still keep your descending counts while ranking them 1-whatever. Now that answers your whole question without having to create a report. Just apply this new query to your form and your good to go.

    Later Kal

  10. #10
    Join Date
    Mar 2002
    Posts
    10
    First of all, my apologies to Kal. I didn't mean to appear testy, it was the end of a long day (5:30pm UK time), and I just wanted to get the info down.

    I've tried using an alias query before, but it always runs very, very slowly (and it didn't handle ties).

    I'm a SQL rookie, but I added the SQL and it works great! It's still a little slow on some bigger queries, but I'll work on streamlining them.

    Many thanks for the help.

    Adrian.
    ACDickson

  11. #11
    Join Date
    May 2003
    Location
    Pgh
    Posts
    8
    Hey Kal, I have a simliar problem but not really for sure how to use the SQL statement that you provided for my problem. Can you take a look at:
    http://dbforums.com/showthread.php?threadid=791499
    http://dbforums.com/showthread.php?threadid=790665

    Thanks


    Originally posted by kpalmer
    hey ac,

    One more thing I just figured out. You can rank the records by applying another query to the one you just created. Just start a new query from the one listed previously. Change its alias name to something like NewName and Add your two fields (GroupField, FieldName) and then create a third column will a name like rank, then finally create a sub query within rank that references the first query.

    Like this:

    SELECT GroupField, FieldName, (SELECT Count(*) FROM FirstQuery WHERE FieldName > NewName.FieldName) + 1 AS Rank
    FROM FirstQuery AS NewName
    ORDER BY NewName.FieldName;

    This will still keep your descending counts while ranking them 1-whatever. Now that answers your whole question without having to create a report. Just apply this new query to your form and your good to go.

    Later Kal

Posting Permissions

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