VB Input Values to Appropriate Format

Posted on 2004-11-14
Last Modified: 2012-05-05
              -2 combo boxes  where one is user selected for a category and the other is used for choosing multiple subcategories if the user so chooses.

             -2 text boxes where one is for input of frequency (ie say for example you want to schedule something, like painting a building, every so many years, hence frequency) and the other textbox is cost per subcategory.  I need the cost to match the frequency in a datagrid perhaps because of my db.

 Example:   (assuming category is Building, subcats are paint, re-roof, frequency=every 3 yrs
                   cost of paint = 1500.00  and re-roof is 2000.00)

                              2004                2005              2006             2007                  2008        

             Paint          $1500.00                                                  $1500.00
             Re-Roof      $2000.00                                                  $2000.00

Question by:shanemcf
    LVL 12

    Accepted Solution

    Set the table structure up something like this....

    table name :  Maintenance
        fields :  BuildingID           MaintenanceType          Frequency         Date                      Cost
                    1                           Paint                           3                       4/10/2004             1500
                    1                           Re-Roof                      3                       4/10/2004              2000
                    1                           Paint                           3                      4/10/2007              1500
                    1                           Re-Roof                      3                       4/10/2007              2000

    This way you won't be adding fields to your table and your queries will be much more managable.
    All that is needed then is to open the table in code and add the information from the combo boxes to the recordset. Then if you want to display a grid of the information just use the recordset as the record source.


    Author Comment

     if my customer table      =       CustomerID (PK)   type=number
              maintenance table =       BuildingID    (PK)   type=number
                                                   CustomerID (FK)   type=number

       My i-face is to choose from combo boxes for customer and then category, then subcat...

    How do I keep my customerid (pk)  the same as my customerid(fk) ???  There is no prompt for an ID.

    Thanks in advance...
    LVL 12

    Expert Comment

    When you add records to the primary table (PK) add any records to the related table (FK) using the same key for both the PK and the FK.
    You can do this in any sequece using the PK in the related table as a FK. If you get to a many to many relationship, you will need to have a seperate table that joins the tables with only the keys in it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now