Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003 (Trying to go to a specific record)

    I am trying to run code that either creates a new record or fills in specific fields in an existing record. The first time the code is run, there will always be a new record created. The problem I have is that when I am trying to fill in fields in a previous record, I cannot get to that record. I have tried moving previous, setting a bookmark, moving last - nothing works.
    There must be an answer to this- but I am stumped! I am attaching the code in a word document.
    Thank you for any help!!!

    desparate deutsch
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access 2003 (Trying to go to a specific record)

    You must add code to go to the record you want to edit. To do so, you must have a unique key in the target table - this can be one field or a combination of fields. It is not clear from your attachment what this key might be - perhaps the combination of OrderNo, PartNo and AttyID, but I'm not sure. When you have found the correct record, you must place it in edit mode (rstTarget.Edit, apply the changes and update it.

    So please let me know what can be used as unique key for the target table.

    BTW, a design with fields PartLocDescr1 through PartLocDescr30 does not seem right to me. I would create a separate table linked to tblWaiverMerge with one PartLocDescr field and up to 30 records per record in tblWaiverMerge, one for each description.

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (Trying to go to a specific record)

    Thank you Hans
    I am trying to create a flat table for merge purposesto e-mail. Therefore the large number of fields named PartLocDescr1..n The Source table has separate records for the various parts etc. It comes from SQL Server originally. We are trying to copy records for the same atty, orderno, orderdate to a flat file.
    There is no unique key when it comes to me from SQL, the only thing I can do is to add an autonumber into the target table.

    Deutsch

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access 2003 (Trying to go to a specific record)

    It doesn't need to be a key (index) in the table, but you do need a field or combination of fields that can be used to identify the record you want to update.

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (Trying to go to a specific record)

    OK, Hans

    I am attaching a zip file that has a Word doc showing some sample data.
    Scenario 3:Same atty, same orderdate diff partno: the combination of partno, location and description is to be written into the next partlocdescr1..n field in the target.

    Scenario 2:If order date is different, new record.
    Scenario 1:If atty is different, new record.

    As I mentioned before, scenarios 1 and 2 work fine, 3 is the one giving me trouble.
    When a record is added via code, data filled in and then updated, where is the record pointer at that point?
    I get the impression that it is nowhere, since all I really need to do is go the last record added and fill in some other data. I am obviously missing a link here.

    Thank you so much for your patience and help.

    deutsch
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access 2003 (Trying to go to a specific record)

    If I understand correctly, all this verbiage means that the combination of OrderNo, AttyID and OrderDate is the unique key.

    Change

    Set rstTarget = db.OpenRecordset("tblWaiverMerge", dbOpenTable)

    to

    Set rstTarget = db.OpenRecordset("tblWaiverMerge", dbOpenDynaset)

    This enables us to use FindFirst. Below the line

    Else 'scenario 3

    insert these lines:

    rstTarget.FindFirst "OrderNo = " & strOrdNo & " AND AttyID = " & Chr(34) & strAtty & Chr(34) & _
    " AND OrdDate =#" & Format(dtmOrdDate, "mm/dd/yyyy") & "#"
    rstTarget.Edit

    You need only one line

    rstTarget.Update

    immediately above the line

    End If ' for partno condition

    Remove all the lines rstTarget.Update you have now. I think that should do it.

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (Trying to go to a specific record)

    Thank you so much Hans!
    I had to work on the code to make it work the way I wanted it; but you pointed me in the right direction. Thank you for helping me with your patience and knowledge!

    deutsch

Posting Permissions

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