Page 1 of 4 123 ... LastLast
Results 1 to 15 of 53
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update with code (2000)

    I have some code that is behind the After Update event of the field (on a form) called Client Number. See below
    The file that updates the fields is refreshed on a monthly basis.
    In order to get the most current data for the fields the Client Number field has to be overwritten. Is there any way I can (on a mthly basis when the file is refreshed) run some code to update these fields instead of having to overwrite the client number field record by record. They haven't asked for this yet but I know they are going to. Thanks for your help and patience.



    Private Sub Client_Number_AfterUpdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Handler

    'Only do this if user is on new record
    'If Me.NewRecord Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset _
    ("SELECT tblAllActive_Client_Name, Renew_Date, Members, Broker, Rep FROM [qrysum2] WHERE [tblAllActive_Client_Number]='" & Me![Client_Number] & "'")


    'Did we get a match?
    If rst.RecordCount = 0 Then
    'No, we don't
    MsgBox "There is no matching record.", vbInformation
    Exit Sub
    Else
    'Yes we do
    'Copy some fields
    Client_Name = rst!tblAllActive_Client_Name
    Renewal_Date = rst!Renew_Date
    Member_Cnt = rst!Members
    SFGroup_Broker_Name = rst!Broker
    SFRep_Last = rst!Rep
    End If


    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

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

    Re: Update with code (2000)

    Do you mean that you want to update existing records instead of modifying a single new record?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update with code (2000)

    I hate to ask this, but WHY are you updating a Client Number? Surely it doesn't change monthly. If it does, then how are you identifying your clients uniquely?
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with code (2000)

    Right now when they put in a client number and hit the tab key, certain fields are populated if there is a client number match to a file that I will be getting monthly (same records with changes, additions etc.) I was thinking I could update the records (just the fields that have already been populated previously from this file) to the updated fields in this excel file. I was thinking of an update query but I wasn't sure how to set it up. I looked up an update query but I didn't see anything about overwriting certain fields from another table. Is it possible to do this with an update query? I don't want to overwrite the client number field - just match it to the client number field in this monthly file that I will be getting and overwrite those fields that I designated in the code . I just wanted these existing records to be updated with what is in the mthly file if there is a client number match.

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

    Re: Update with code (2000)

    You should be able to use an update query for this:
    - Create a new query in design view.
    - Add the main table and the table or query with updates (qrysum2 ?)
    - Join them on Client_Number vs tblAllActive_Client_Number.
    - Add the fields from the main table that need to be updated: Client_Name, Renewal_Date, Member_Cnt, SFGroup_Broker_Name, SFRep_Last.
    - Select Query | Update Query to change the query into an update query.
    - In the Update to line, enter the names of the fields containing the updated values in the appropriate columns. Replace qrysum2 with the name of the table/query with the updated values.

    [qrysum2].[tblAllActive_Client_Name]
    [qrysum2].[Renew_Date]
    [qrysum2].[Members]
    [qrysum2].[Broker]
    [qrysum2].[Rep]

    - You can now either run the query (Query | Run) or save it for later use.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with code (2000)

    I got an error message when I tried to run the query - Operation must use an updatable query. I am using the table that I want to update and the query qrysum2 where the fields are updated from. I think I set my query up correctly.

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

    Re: Update with code (2000)

    Is there a unique key (e.g. the primary key) on Client_Number in the destination table?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with code (2000)

    No - I have a SFID. The field is indexed. This is the SQL
    UPDATE qrysum2 INNER JOIN tblSF ON qrysum2.tblAllActive_Client_Number = tblSF.Client_Number SET tblSF.Client_Name = [qrysum2].[tblAllActive_Client_Name], tblSF.Renewal_Date = [qrysum2].[Renew_Date], tblSF.Member_Cnt = [qrysum2].[Members], tblSF.SFGroup_Broker_Name = [qrysum2].[Broker], tblSF.SFRep_Last = [qrySum2].[Rep];

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

    Re: Update with code (2000)

    In order for the query to be updateable, there MUST be a unique index on Client_Number in tblSF. If there isn't, Access doesn't know which record it is going to update.
    Try the following:
    - Open tblSF in design view.
    - Activate the Indexes window (View | Indexes).
    - If there is already an index on Client_Number, set its Unique property to Yes.
    - If there isn't, create a new index - enter Client_Number in both the Index Name and Field Name columns, and set the Unique property to Yes.
    - Close and save the table.

    If you get an error message, please note what it says and report it in a reply.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with code (2000)

    The reason I didn't make Client Number a primary key is because sometimes a record may not have a client number. The database is going to be tracking clients that have client numbers and prospective clients that do not have client numbers yet. If I change the unique property to Yes, will they still be able to enter a record that does not have a client number?

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

    Re: Update with code (2000)

    Yes, if you set a unique index (but not the primary key) on Client_Number, you will be able to enter records without client number. They will be ignored by the update query.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with code (2000)

    I made the changes but I get the same error - operation must use an updateable query

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

    Re: Update with code (2000)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.
    We only need to see the two tables and the query, and you can remove most of the records from the table. We don't need other tables or queries, forms, reports, etc.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update with code (2000)

    <hr>update the records (just the fields that have already been populated previously from this file) to the updated fields in this excel file<hr>
    What Excel file are you talking about? Is one of your recordsets built on a linked Excel file? If so, that takes this out of the realm of a pure Access question.
    Charlotte

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with code (2000)

    I give up - this problem is probably due to the network. Why does it let me update the fields using the code that I posted and yet not let me do the same thing using an update query. I would post a stripped down version but I don't think there is anything wrong with the update query and it probably works when not using the network. I will test it on my C drive.

Page 1 of 4 123 ... LastLast

Posting Permissions

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