Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Question (XP SP-1)

    Hello,

    The attached picture shows a form that a user wants me to create. My question focuses on the Opportunity Markets area. My solution would be to create 37 tables (one for each Opportunity Market), each table containing 4 fields (MKTID, Active (yes/no), Market_Name, and Revenue). I would then tie each table to the other tables (client, campaign, agency, etc).

    Is there a better way? The user wants each Opp Market displayed on the form.

    Thanks
    Attached Images Attached Images

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

    Re: Form Question (XP SP-1)

    I would certainly not create 37 tables. That would make analyzing the collected data very hard. I would put the records in one table, and present it in a continuous subform.

    Is there going to be just one record for each market, or will multiple users fill in the form, so that each user will need to fill in a record for each market?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Thank you, Hans. I'm glad I followed my hunch.

    There can be multiple records for each market because AE #1 might have White Co. (client) with revenue....AE #2 might have Green co. with revenue....all for the Charlotte market.

    The fields in the opportunity market table are: CampaignID Active Opp_Mkt Revenue

    As you can see, I've tied this table to the campaign table.

    Thank you.

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

    Re: Form Question (XP SP-1)

    I would create a structure like this:

    1. A table tblMarkets with two fields:

    - MarketID (AutoNumber, primary key)
    - MarketName (Text, length 20)

    Enter the market names (Albuquerque, ...) in this table, one per record.

    2. A table tblMarketOpportunities with four fields:

    CampaignID (Number?)
    MarketID (Number, Long Integer)
    Active (Yes/No)
    Revenue (Currency?)

    Primary key on the combination of CampaignID and MarketID.
    There is a relationship between tblMarkets and tblMarketOpportunities on MarketID, with referential integrity enforeced, and with cascading deletes.

    3. A query qryMarketOpportunities based on tblMarkets and tblMarketOpportunities (linked on MarketID). It returns all fields from tblMarketOpportunities, plus MarketName from tblMarkets, and is sorted (ascending) on the latter.

    4. A continuous form sbfMarketOpportunities based on qryMarketOpportunities. Set the AllowAdditions and AllowDeletions properties to No. Put sbfMarketOpportunities as a subform on the main form, linked by CampaignID.

    5. Write code to insert new records into tblMarketOpportunities when a new CampaignID is created: one record for each MarketID from tblMarkets, each with the same new CampaignID. You can either create an Append query and execute it, or use DAO or ADO to append the records.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Hans,

    Thank you. I am putting the structure in place. Writing the code will be a stretch for me, but I like stretching. Anyway, you'll probably see some more posts along the way after I put this all in place.

    Thanks again for all of your help.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Please take a look at this jpg of qryMarketOpportunities and of my relationships. When I run the query, am I supposed to see the list of all of the markets? If so, I do not.
    Attached Images Attached Images

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

    Re: Form Question (XP SP-1)

    No, this query will display only existing records in TBL_MarketOpportunities, together with the market name. At the moment, you probably have none.

    You can temporarily see all markets by double clicking the line joining the tables, and selecting the option to include ALL records from TBL_Markets. Don't save this, though.

    The idea is that when you create a new CampaignID, you will use code to create a record for this CampaignID and for each MarketID (step 5 in my proposed setup). If you already have existing CampaignID's, you would need to run the code for those too. The records will then turn up in the query.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    OK, got it. Here are relationships I set up, based on your suggestions.
    Attached Images Attached Images

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

    Re: Form Question (XP SP-1)

    Looks good. Post back if you need more assistence, with the code for instance.

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Hans,

    OK. I think I see where you're taking me on this. I've done steps 1-4 and spent a good portion of Friday trying to figure out how to begin writing the code for this, but I'm lost. If you could get me started, we'll see how far I can run with the ball.

    Thanks for all the help.

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

    Re: Form Question (XP SP-1)

    Make a backup copy of your database before trying the following, so that you can test without messing up things.

    I assume you have a form on which campaigns are edited and new campaigns are entered. Let's say that this form is named frm_Campaign, and that the control containing the ID is named simply CampaignID.

    Create a new query based on TBL_Market. Add MarketID to the query grid, and a calculated column:

    CampaignID: [Forms]![frm_Campaign]![CampaignID]

    Change the query into an Append Query through the Query menu, and specify TBL_MarketOpportunities as the table to append the records to. Access should automatically fill in the field names to append to. Save this query as qryAppendCampaign.

    For the moment, we will call this query from a command button on the form. Open the form in design view and put a command button on it. Set its Caption to "Create records" or something like that, and set its Name property to cmdAppend. Then create an On Click event procedure:

    Private Sub cmdAppend_Click()
    If IsNull(Me.CampaignID) Then
    MsgBox "No valid CampaignID", vbCritical
    Exit Sub
    End If
    DoCmd.OpenQuery "qryAppendCampaign"
    Me.sbfMarketOpportunities.Requery
    End Sub

    Close and save the form. Then open it in form view, and try the button. You should get a warning that you are about to run an action query, then that you are about to append 37 (or whatever) records; confirm both times.

    If everything works OK, you can suppress the warnings by inserting DoCmd.SetWarnings False before, and DoCmd.SetWarnings True after the DoCmd.OpenQuery instruction.

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Edited by HansV to remove superfluous white space that made up more than 90 % of attached image.

    Hans,

    I have created the query as stated (see attached). As soon as I created it and changed it to an append query, I tried to run it using the red exclamation point in the query window. I get an error message that Access can't append all the records...In the details section of the warning box it says that couldn't append the 39 rows due to key violations, etc. (I'm sure you've seen this message before). Also, when I try to run it an Enter a Parameter Value box appears.

    Should I not try to run it at this time and will these problems disappear as I move forward, or is the calculated field not pointing to the campaignID in the form properly? I checked the spelling and syntax of the query 4 times.

    As always, thank you.
    Attached Images Attached Images

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

    Re: Form Question (XP SP-1)

    1. With the form open, activate the database window, and open the query in design view. Then switch to datasheet view. Both fields should be populated, MarketID with a different value in each record, and CampaignID with the same value (that from the form) in each record. Is that correct?
    2. What does the parameter window prompt you for?

  14. #14
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Hans,

    Holy Moly!!!! It worked. I wasn't sure where we were going to end up with all this. WOW.

    Thank you for taking the time to take a step by step approach and do some educating.

  15. #15
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Question (XP SP-1)

    Hans, when I type in a new campaign in the campaign field of the main campaign form and click the command button, nothing happens. I have to go to another record and come back to the current (new) record and click the button before the markets appear. I know it has something to do with requerying or refreshing but I don't know how to fix it. Any ideas??

    BTW, I typed in those two DoCmd.Setwarning statements and all is well.

Page 1 of 2 12 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
  •