Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Automated Update Query (2003)

    Hi

    Our new Access DB contains Accounting Information, which is recorded in tbl_membership_details and referenced by frm_company_membership_details. This Table and Form display the Name of the Account Manager (AM), which is selected from the AM Combo Box. Each year, an Account Manager is assigned certain companies, so each year, when the new Budgeting Information is entered, the Account Manager is again selected and stored in the Current Year's AM Field.

    However, the Account Manager's name for each company also appears on the Main Company Form (frm_company_details). Both use Combo Boxes that reference the same AM Table. As it is possible that these may go out of sync, we would like to ensure that if a value is selected from the Combo Box on frm_company_membership_details it updates the Combo Box on frm_company_details.


    At first I thought that I could create an AfterUpdate Event that would run an Update Query, but to add to the confusion, frm_company_membership_details can have multiple descriptions, i.e., One can have 1 Entry for 03/04 for 1 Campaign with 1 Account Manager, while another can be for 03/04 for another Campaign with a different Account Manger.

    Eg,
    DATE: 03/04, ACCOUNT MANAGER: John Smith, CAMPAIGN: Environment
    DATE: 03/04, ACCOUNT MANAGER: Paul Jones, CAMPAIGN: Membership


    I would therefore like to get an AfterUpdte Event to fire, but I'm not sure if a) what I'm doing is the best way to go around this; and [img]/forums/images/smilies/cool.gif[/img] what the correct syntax should be.

    I'm quite worried that if I get this wrong, I'll stuff up the Database.

    Could someone provide some thoughts on this and see if the following syntax is anywhere near correct?

    Private Sub cboAM_AfterUpdate()
    Dim strWhere As String
    Dim strSQL As String

    If Me.Year = "03/04" AND Description = "Membership"
    Then strSQL = "UPDATE tbl_company_details INNER JOIN tbl_membership_details, " & _
    "ON tbl_company_details.company_id = tbl_membership_details.company_id, " & _
    "SET, " & _
    "WHERE, " & "_(((tbl_company_details.account_manager)=[Forms]![frm_company_membership_details_full]![frm_company_membership_details].[Form]![account_manager])); ("

    I'm sure this is wrong.

    Anyway, just to recap and hopefully make it clearer, I want Field account_manager in Table comany_details to Update to equal the value in Table Membership Details, Field Account Manager WHERE Year = (eg) 03/04 and Description = "Membership"

    Sorry if I've explained this badly.

    Any advice and help would be greatly appreciated.

    Regards

    Brian

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

    Re: Automated Update Query (2003)

    Why do you want to store the same information in two tables? The idea of a relational database is to avoid that. By creating the appropriate join, with enforced relational integrity and cascading updates, you should not have to use any code.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automated Update Query (2003)

    [EDITED]

    Hi Hans

    I just realized why basing the form on a relational query wouldn't work! There are 2,000 records (Companies) in our Database, but of those, only 200 are members and therefore only 200 have invoices raised. Therfore, 90% of the companies on our DB have no financial details.

    The 200 companies that are invoiced have Account Managers assigned to them, the rest simply have 'Owners', that is, the people who requested that they be put on the database.

    That's why we wanted the aforementioned query: for the 200 that do have invoices raised, we want to make sure that the most current Invoice raised is assigned to the same Account Manager that's displayed on the Main Company Form; if someone enters or changes the Account Manager on the Financial Details Form, this should automatically update the Main Company Form.

    Does this make sense? I hope so.

    Thanks (as usual) for your reply. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Automated Update Query (2003)

    i'm afraid I don't understand why that would make a query impossible.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automated Update Query (2003)

    Well, I guess it wouldn't make the query impossible, it would just make it redundant for 1800 Companies. For those 1800 Companies, the Query would return Null values. Therefore, because we want every company to have either an Account Manager or Owner, that wouldn't be ideal.

    To be honest, there should be 2 Tables: Account Managers and Staff. We have about 40 Staff, but of those, only about 12 are Account Managers. Therefore, there should be Two Tables: Account Managers and Staff. The Combo Box on the Main Company Form should reference a Table called Staff, and the Combo Box on the Financial Details Form should reference a Table called Account Manager. It just so happens that every Account Manager's Name appears in the 'Staff' Table b/c they are (naturally) also staff. I hope I'm not making this sound too confusing! Does that clear it up?

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

    Re: Automated Update Query (2003)

    Instead of two tables, you should have one, with a Yes/No field to indicate whether a staff member is an account manager. The row source of the combo box on the main company form would be the staff table, and the row source of the combo box on the financial details form would be a query that selects only those records from the staff table for which the Yes/No field is Yes.

    Having two tables goes against the grain of a relational database.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automated Update Query (2003)

    Hi Hans

    OK, I *KNOW* you are a heck of a lot smarter than I am, so you'll have to forgive my stupidity in asking this, but although I see how using a checkbox eliminates 2 Tables, I don't understand how that solves the problem of 1800 companies being without an 'Owner'.

    If the 'Owner/Account Manager' is simply a Bound Text Box that contains the result of a Query, this will leave 90% of companies with blank fields, as Invoices are only raised for Member Companies.

    As I see it, people need to be able to select the Owner/Account Manager on the Main Company Form, but they also need to be able to select an Account Manager specific to a particular Invoice on the Financial Details Form.

    There can be Many Invoices raised in any year: Membership of our Main Campaign; Membership of another (smaller) campaign (e.g., Environment); Sponsorship of an Event, etc. Each one of these invoices can be 'assigned' to any one of our 12 Account Managers---the Invoice is not always assigned to the same Account Manager as the person selected as the Owner/Account Manager of the overall company.

    I think I must be explaining this badly.

    Sorry. [img]/forums/images/smilies/sad.gif[/img]

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

    Re: Automated Update Query (2003)

    Sorry, I don't understand.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automated Update Query (2003)

    No problem. It's my fault for explaining it badly. I'll try again from the top.

    Our Database is basically a Contact Management System. We store information (addresses, staff, etc) for all the companies we communicate with. Approximately 10% of the companies on our database have agreed to become 'Members' of our Organization. As such, they pay an Annual Subscription Fee in order to be Members. Therefore, we have a Table/Form that contains details of these Membership Fees. As this is an Annual Fee, the Relationship is a One-to-Many (One Company can have Many Annual Subscriptions).

    However, not only do these companies pay a Membership Fee, they may also want to attend an Event we are organizing (eg, an Environmental Event). Therefore, they may in fact be billed twice in one year: Once for Membership and once for the Event.

    Let's say that Coca Cola is on our database, and let's say that they are a Member. Paul may be Coca Cola's Account Manager. Paul is responsible for ensuring that the Invoice for Coca Cola is raised, and he is also responsible for ensuring that it is paid. Therefore, on the Main Company Form, Paul is selected from the 'Account Manager/Owner' Combo Box; he is responsible for all dealings with Coke, and any communication with Coke must be approved by Paul. Similarly, on the 'Membership' Form, when the Annual Membership Invoice is raised for Coke, Paul's name is selected from the 'Account Manager' Combo Box on the Financial Form to show that he is responsible for ensuring that the Invoice is paid.

    However, sometime during the year, we may hold a big Environmental event---so big that we require Sponsors in order to pay for it. As such, the Manager in charge of our Environmental campaign (let's call him Bob) may approach Coca Cola, asking that they help to pay for/sponsor the Event. If Coke agrees, Bob will raise an Invoice, and HIS name is put down as being responsible for ensuring that this particular Invoice is paid, again, selected from the Combo Box on the Financial Form.

    Therefore, we now have 2 Invoices raised for the 03/04 Financial Year: One for Membership, for which Paul is responsible; and one for an Environmental Invoice, for which Bob is responsible.

    As you can see above, Paul's name appears on the Main Company Form as the Overall Account Manager. We wouldn't want the Account Manager to suddenly become Bob, simply because he raised an Invoice for an Event that Coke agreed to sponsor.

    This is why there needs to be two Combo Boxes (one on each Form). And although they mostly cross-over, it is not always the case, as detailed above.

    Does this help to clarify it?

    If it does, I'll go on to explain why we're trying to ensure that they're in-sync.

    Thanks for reading this and for trying to help. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Automated Update Query (2003)

    Now I am completely lost. In the first post in this thread, you stated
    <hr>we would like to ensure that if a value is selected from the Combo Box on frm_company_membership_details it updates the Combo Box on frm_company_details.<hr>
    Now you write
    <hr>As you can see above, Paul's name appears on the Main Company Form as the Overall Account Manager. We wouldn't want the Account Manager to suddenly become Bob, simply because he raised an Invoice for an Event that Coke agreed to sponsor.<hr>
    <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automated Update Query (2003)

    No. You're right. You have followed it perfectly, then.

    We wouldn't want the Main Company Form to update *unless* a Membership Invoice is raised.

    The Fields in the frm_company_membership_details are as follows:
    MembershipID (Autonumber)
    CompanyID (FK)
    Financial Year
    Account Manager
    Budget Date
    Invoice Date
    Invoice Amount
    Description
    etc.

    IF Description = Membership AND Financial Year = 03/04, then the Account Manager Field on the Main Company Form should change.

    If Description OR Financial Year = anything else, then the Account Manager Field should not change when new Financial Information is entered or changed.

    Have you really given up?

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

    Re: Automated Update Query (2003)

    I think it's becoming clearer <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

    Try this:

    Private Sub cboAM_AfterUpdate()
    Dim strSQL As String

    If Me.[Financial Year] = "03/04" And Me.Description = "Membership" Then
    strSQL = "UPDATE [tbl_company_details] SET [Account Manager] = " & Me.[Account Manager] & _
    " WHERE [CompanyID] = " & Me.[CompanyID]
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL
    End If
    End Sub

    Note: I have assumed that [Account Manager] is a number field (some kind of ID); if it is actually a text field (the name), you must surround it with quotes:

    strSQL = "UPDATE [tbl_company_details] SET [Account Manager] = " & Chr(34) & Me.[Account Manager] & Chr(34) & _
    " WHERE [CompanyID] = " & Me.[CompanyID]

    Similar for CompanyID.

  13. #13
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automated Update Query (2003)

    Sorry I was so crap at explaining that.

    Anyway, as usual, it worked.

    You're a real star, Hans.

    Thank you sincerely for perservering and for clarifying the code. I knew I was on the right track, but I also knew I need expertise in getting the syntax right.

    Thanks heaps.

    Have a great day (and week)! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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