Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

updating records

Discussion in 'Information Technology' started by CSS, Jul 28, 2006.

  1. CSS

    CSS
    Expand Collapse
    Guest

    I have a situation where I import a text file, including cardholder numbers
    and associated transactions. The problem is that the cardholder number field
    is not populated for all the transaction information. It is only populated
    for the first record associated with the transaction history.

    Example:
    ID cardholder_number transactions
    1 1234567891234567 xxxxxxxxxx
    2 yyyyyyyyyy
    3 iiiiiiiii
    4 2345678912345678 jjjjjjjjjjjjjj
    5 zzzzzzzzzz

    There was a similar posting in November and I tried to use the module logic
    but I could not get it to work. Is there a way to simply use an update query
    to populate the cardholder number field? So, in the example above, the
    second and third records would be updated with the cardholder number from the
    first record. There are many transactions associated with one cardholder
    number.
     
  2. Loading...


  3. Adam P

    Adam P
    Expand Collapse
    Guest

    Maybe try something like: I assume field names like card_number and
    trans, and that the table is always in the proper order so that the
    first line of transactions will have the number/

    Dim prevCardNum as integer

    db = currentdb
    rs = db.openrecordset("Your Table")

    with rs
    .movefirst

    do until .eof
    if isnull(.fields![card_number]) then
    .edit
    .fields![card_number] = prevCardNum
    .update

    Else
    prevCardNum = .fields![card_number]
    end if

    .movenext
    loop
    End with




    CSS wrote:
    > I have a situation where I import a text file, including cardholder numbers
    > and associated transactions. The problem is that the cardholder number field
    > is not populated for all the transaction information. It is only populated
    > for the first record associated with the transaction history.
    >
    > Example:
    > ID cardholder_number transactions
    > 1 1234567891234567 xxxxxxxxxx
    > 2 yyyyyyyyyy
    > 3 iiiiiiiii
    > 4 2345678912345678 jjjjjjjjjjjjjj
    > 5 zzzzzzzzzz
    >
    > There was a similar posting in November and I tried to use the module logic
    > but I could not get it to work. Is there a way to simply use an update query
    > to populate the cardholder number field? So, in the example above, the
    > second and third records would be updated with the cardholder number from the
    > first record. There are many transactions associated with one cardholder
    > number.
     
  4. CSS

    CSS
    Expand Collapse
    Guest

    I received the following runtime error: Object does not support this
    property or method. It looks like it didn't like this: rs =
    db.openrecordset("Unconfirmed")

    The table is in the proper order.
    "Adam P" wrote:

    > Maybe try something like: I assume field names like card_number and
    > trans, and that the table is always in the proper order so that the
    > first line of transactions will have the number/
    >
    > Dim prevCardNum as integer
    >
    > db = currentdb
    > rs = db.openrecordset("Your Table")
    >
    > with rs
    > .movefirst
    >
    > do until .eof
    > if isnull(.fields![card_number]) then
    > .edit
    > .fields![card_number] = prevCardNum
    > .update
    >
    > Else
    > prevCardNum = .fields![card_number]
    > end if
    >
    > .movenext
    > loop
    > End with
    >
    >
    >
    >
    > CSS wrote:
    > > I have a situation where I import a text file, including cardholder numbers
    > > and associated transactions. The problem is that the cardholder number field
    > > is not populated for all the transaction information. It is only populated
    > > for the first record associated with the transaction history.
    > >
    > > Example:
    > > ID cardholder_number transactions
    > > 1 1234567891234567 xxxxxxxxxx
    > > 2 yyyyyyyyyy
    > > 3 iiiiiiiii
    > > 4 2345678912345678 jjjjjjjjjjjjjj
    > > 5 zzzzzzzzzz
    > >
    > > There was a similar posting in November and I tried to use the module logic
    > > but I could not get it to work. Is there a way to simply use an update query
    > > to populate the cardholder number field? So, in the example above, the
    > > second and third records would be updated with the cardholder number from the
    > > first record. There are many transactions associated with one cardholder
    > > number.

    >
    >
     
  5. CSS

    CSS
    Expand Collapse
    Guest

    Actually, this did work. I just needed to make a small change on my end.
    Thanks so much for your help.


    "Adam P" wrote:

    > Maybe try something like: I assume field names like card_number and
    > trans, and that the table is always in the proper order so that the
    > first line of transactions will have the number/
    >
    > Dim prevCardNum as integer
    >
    > db = currentdb
    > rs = db.openrecordset("Your Table")
    >
    > with rs
    > .movefirst
    >
    > do until .eof
    > if isnull(.fields![card_number]) then
    > .edit
    > .fields![card_number] = prevCardNum
    > .update
    >
    > Else
    > prevCardNum = .fields![card_number]
    > end if
    >
    > .movenext
    > loop
    > End with
    >
    >
    >
    >
    > CSS wrote:
    > > I have a situation where I import a text file, including cardholder numbers
    > > and associated transactions. The problem is that the cardholder number field
    > > is not populated for all the transaction information. It is only populated
    > > for the first record associated with the transaction history.
    > >
    > > Example:
    > > ID cardholder_number transactions
    > > 1 1234567891234567 xxxxxxxxxx
    > > 2 yyyyyyyyyy
    > > 3 iiiiiiiii
    > > 4 2345678912345678 jjjjjjjjjjjjjj
    > > 5 zzzzzzzzzz
    > >
    > > There was a similar posting in November and I tried to use the module logic
    > > but I could not get it to work. Is there a way to simply use an update query
    > > to populate the cardholder number field? So, in the example above, the
    > > second and third records would be updated with the cardholder number from the
    > > first record. There are many transactions associated with one cardholder
    > > number.

    >
    >
     

Share This Page