Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Need a recommendation on the best way to handle my problem….

    I have a table that contains 80,000 records. Each record contains 1 zip code in the US and the associated city name and state.

    I have a form that is used to capture information about a customer’s zip code coverage area. The objective of this form is to allow the user to view the customer’s current zip code coverage and request to have zip codes added. The user will not know the specific zip codes needed only the city. For example, the end user may want to have Chicago zip codes added to the customer’s coverage area, but not all Chicago zip codes.

    Currently I have a form called frmDirectoryListingAndZips_DataEntry. This form has two subforms. The subforms are: frmActiveFloristZipCodes, which shows all the zip codes currently listed by the customer and frmZip_info, which shows all the zip codes in the US and Canada.

    When the user types in the customer’s ID number, it populates the subform to show the current zip code coverage on the subform. When the user types in a city name it populates the subform showing all the associated zip codes in the specific city.

    My question is how do I get specific zip codes from the subform frmZip_info into the main form and populated into the table that is capturing the data? In addition, the end user may not want all the zip codes associated with a city, they may only want some of the zip codes, and how would I be able to have the end user be able to select specific zip codes tied to the city into my main table?

    I have attached a sample copy of my current database.

    Any help will be much appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    After looking at your sample, it appears to me there are some normalization problems and table structure issues that contribute to the issue of designing a data entry form. It appears you intend to store coverage ZIP codes in table tblActiveFloristZipCode that are linked to the table tblOwnershipCode_ShopStatus, but you are also storing the ZIP codes in a memo field in tblDirectoryListingsAndZips. In addition you are storing the FLORIST_ID field and FLORIST_NAME as well as the Owner_ID in tblActiveFloristZipCode. Storing the ZIP codes in a memo field is a particular problem as they will be difficult to keep consistent. If you want to display them that way, you could use VBA to construct the list.

    As far as the form design is concerned, I don't think I understand who the "end user" is and who is being captured in your table tblDirectoryListingsAndZips. How is the data going to be used once it is captured. If this is to record people who call up and say I want to find a florist, then it seems you would need a separate table to store the zip codes if there are several. One of the first steps recommended in designing a database is toc learly state the problem you are trying to solve and identify the various objects involved it. Perhaps you have already done that, but if not that may help you with your design.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    WendellB has it right. There are serious design issues. In addition to the things he mentioned, I notice the field MBR is 50 characters in one table and 255 in another. Not good. On the main form, the MBR control accepts the enter key as entry in the field. I suspect that there never is a carriage return in the MBR.

    I would suggest you find a colleague to help with the underlying design.

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I wish I had a colleague that could help me. Unfortunately, I do not.

    The objective of the database is to capture new zip codes that a florist would like to add to their current coverage area. The zip codes will then be entered into another system once they are captured from the end user. All the data entered by the end user will be captured in tblDirectoryListingsAndZips.

    The following 3 tables are pulled from another system and are static tables they will not be updated based on the data entry on the main form. The tables are:

    tblActiveFloristZipCode – this table lists all the zip codes a florist currently services.

    tblOwnershipCode_ShopStatus – this table lists the owner id, florist id and florist names.

    tblZip_info – this table lists all the zip codes and city and states in the US and Canada.

    These 3 tables are in the database so that they can provide information to the end user about the current state of the florist, but will not be updated.

    Therefore, the purpose of the frmDirectoryListingsAndZips_DataEntry is to enter the data into the tblDirectoryListingsAndZips. The subform using tblActiveFloristZipCodes purpose is to show the end user the current zip codes covered by the florist, so they do not request a zip code to be added, if it is already in the coverage area. The subform using tblZip_info is to show the end user all the zip codes associated with a specific city. The end user will only know the city name that needs to be added, not the zips associated with the city.

    I am not exactly sure how to get the zip codes from the subform using tblZip_info into the data entry table tblDirectoryListingsAndZips. Maybe a subform is not the best way to make this happen? I know that storing the zip codes in a memo field is probably not the best option because each zip code in the tblZip_info table is a new record.

    The reason the zip codes were stored in a memo field is because this database was original very simple and only required a memo field for zip codes. It has been requested that the end user be able to find zip codes associated with city name and current florist coverage, which has now made the database more complex.

    Somehow the end user needs to be able to type in a city and state, see all the zip codes associated with the particular city and state, then select the specific zip codes that need to be added to their coverage area (because sometimes not all the zips associated with a city will need to be added). The zip codes selected by the end user will then need to be added to the tblDirectoryListingsAndZips. I was not sure if a new record needed to be added to tblDirectoryListingAndZips, since each zip code is a new record? If yes, then how do I get all the other fields in the record to duplicate, except for the new zip code?

    Thanks, Larry. I have now updated the tblDirectoryListingsAndZips MBR control to 255. The difference is likely due to the fact that the other tables in the database are all imported for an outside source.

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    By the way, it would be nice if you updated your profile to show where you are from. I like seeing where posters are located and I suspect others do too.

    In looking further, a couple questions you should ask yourself:

    1) Why are the zip codes being put in the memo field? Are they not already in the tblActiveFloristZipCode table? If so, why not simply display them as you currently do and forget the memo field?

    2) Why is table tblActiveFloristZipCode linked to OwnerID and not MBR? If it should be OwnerID, then why is the user entering MBR? One or the other seems wrong. I'd hazard a guess that a single owner must own more than one florist. This should really be cleaned up.

    3) Why do you switch to new entry every time? Why not have a form that positions to the MBR and shows the entries?

  6. #6
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    1) Why are the zip codes being put in the memo field? Are they not already in the tblActiveFloristZipCode table? If so, why not simply display them as you currently do and forget the memo field?

    The memo field was used in the original database design. The end user would type a list of all the new zip codes that needed to be added into a separate system.

    No the zip codes are not already in the tblActiveFloristZipCode table. This is a static table that is pulled from another system. This table is only used to show the end user what zip codes the florist current has listed. The end user will be requesting new zip codes be added to the other system that maintains all the zip codes. The purpose of this database is to track zip code requests to add or remove zip codes from the other system.


    2) Why is table tblActiveFloristZipCode linked to OwnerID and not MBR? If it should be OwnerID, then why is the user entering MBR? One or the other seems wrong. I'd hazard a guess that a single owner must own more than one florist. This should really be cleaned up.

    These tables are linked by OwnerID because the end user does not know the OwnerID, they only know the MBR. When requesting to add a zip code the end user can not add a zip code that exists on the OwnerID. By linking it by OwnerID it shows all the zips this shop is listed for, whether it was under the MBR they entered or another MBR that is associated with the same OwnerID.


    3) Why do you switch to new entry every time? Why not have a form that positions to the MBR and shows the entries?

    The end user is requesting entries by city, which is why a new entry is requested every time.

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You really need a table that relates the Florist with the zip codes. That would replace the memo field currently trying to be used.

    For example: you could add table ZipsForFlorist with fields ZIPCode and MBR. Then you would replace your subform frmZip_info with a form, not a datasheet. On that subform, add a button (you realize this is only one way to approach the problem) that adds that zip for that florist to table ZipsForFlorist (after first checking that that zip is not already there for the florist). Actually, you should add an index to ZipsForFlorist that has fields ZIPCode and MBR and the index is declared unique. After adding the entry to ZipsForFlorist, you will probably have to requery the subform (a new subform) showing the entries in ZipsForFlorist for this florist.

    I hope this is not all beyond you.

    After I posted the above, I realized that once you have the table ZipsForFlorists, you can build a subform showing it. And you can make the zip a combo box. That combo box can use the same row source as is currently used in the subform frmZip_Info (that is, the zips for a city). The user can then add easily via the combo box new zips. This approach requires less code.

  8. #8
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I agree that there are some major issues with the database design. You should read Mike Hernandez' book "Database Design for Mere Mortals". It will give you a good grounding in that area.

    HOWEVER...you won't learn it all at once in any case so here are a few things I can give you right now that will help.

    First of all...I noticed you use a naming convention for your forms and tables...That is Great. You should also use a naming convention for all of the controls on your forms and reports. txt for Text boxes, cbo for Combo Boxes, sub for SubForms, etc (I don't generally mess with the labels unsless I am going to manipulate them with VBA code) This will make your life so much simpler especially when referring to these items with code. Intellisense will generally help you too. A corralary is: Don't use spaces in your field or object Names. For my next suggestion, I have renamed the controls on your forms with the naming convention. Please be sure to do so prior to the next step.

    Secondly, you can easily move the Zip codes in the SubForm to the memo field in the Main form. Here's how:

    In the Form frmZip_Info in the On Dbl Click Property of the TextBox [txtZip Code] Select [Event Procedure], Click on the Build Button and enter the following Code:

    If Len(Me.Parent.txtZips) > 0 Then
    Me.Parent.txtZips = Me.Parent.txtZips & ";" & Me.txtZIP_CODE
    Else
    Me.Parent.txtZips = Me.txtZIP_CODE
    End If

    Save the Forms

    Now when you double-click on the Zip Code in the SubForm, it will place that value in the Main Form

    Hope that gives you a start on this issue

    Bob Oxford
    Software Wizards, Inc.
    Bob Oxford
    Software Wizards, Inc.

Posting Permissions

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