Populate text boxes based on 2 combo boxes?

mwisejay

Registered User.
Local time
Today, 07:58
Joined
Apr 5, 2011
Messages
12
Using ACCESS 2007, I have two combo boxes. The first lists all the Tables in the data base. Each table is a contract where I record monthly costs. Based on the table selected from the first combo box the second combo box lists the ReportingPeriod field from the contract table selected in the first combo box.

Based on the selections from the combo boxes, I have 5 text boxes that need to populate the corresponding data to the record selected in combo box 2 from the table in combo box 1.

The following code populates combo box 1 and is placed in the Row Source:
SELECT Name FROM MSysObjects WHERE Type=1 And Flags=0

The following code populates combo box 2 as an AfterUpdate() event:
Private Sub cboCombo1_AfterUpdate()
cboCombo2.RowSource = "SELECT ReportingPeriod FROM " & cboCombo1
cboCombo1.Requery
End Sub

I'm trying to autofill the text boxes unsuccessfully using this code under the requery line of the code above:
MonthOfContract = cboCombo1.Column(1)
ActualLaborHours = cboCombo1.Column(5)
ActualLaborCost = cboCombo1.Column(6)
ActualODC = cboCombo1.Column(8)
ActualTravel = cboCombo1.Column(10)
 
Welcome to the forum.

Structurally speaking you should not have a separate table for each contract. Each contract should be a separate record in 1 table. Like data should be in 1 table.

With respect to your problem, the row source of the combo box must include the fields you want to reference with the cboCombo1.column(x). So you would have to alter this: SELECT Name FROM MSysObjects WHERE Type=1 And Flags=0

But you cannot do that because MSysObjects only has the table names contained in the database.

You need a table to hold the contract info

tblContracts
-pkContractID primary key, autonumber
-ContractNumber
-ContractName
-fkCompanyID foreign key to a table that holds the company names; the contract would be with this company

I assume that you will need a related table to hold the detail information about the contract, but I don't know for sure what you are doing so you will have to supply additional information about what your database is designed to do.
 
jzwp22,

I plan to use the database to record the monthly charges for each contract. That's why I made each contract its own table as each record is a month charged. Since each contract is for a set period of time I already have the months recorded in each record. Wanted to use the Form and combo boxes to select the contract table and then select the date to populate that record into the text boxes so I can enter the monthly charges as they are reported to me.

I haven't even started on reports yet, but I'll need to provide monthly cost reports, cumulative cost reports, etc.

I haven't used ACCESS in awhile and maybe I'm being a bit ambitious before I get up to speed. If you would suggest another method of organizing this information, I'm all ears.
 
I'd go with jzwp22's suggestion about all contracts in one single table. It'll make life a lot easier as you system grows. Alternatively, depending on how your tables are structured you might be able to base your forms and reports on a query where your tables are related by a common field.
 
Having the correct table structure is the most important part of creating a successful relation database application. The key to setting up the tables correctly is by following the rules of normalization. For more on normalization, please check out this site for an overview.

As I mentioned earlier, each contract should be a record in a table. I provided a basic table structure, please modify it to suit your needs

tblContracts
-pkContractID primary key, autonumber
-ContractNumber
-ContractName
-fkCompanyID foreign key to a table that holds the company names; the contract would be with this company


I reference a company table above by the field fkCompanyID, so this is the basic structure for that table

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName

Now you mentioned that you will have many charges associated with each contract, so we'll need a table to hold those and we have to relate each charge back to its respective contract in tblContracts

tblContractCharges
-pkContractChargeID primary key, autonumber
-fkContractID foreign key used to relate the charge back to the specific contract in tblContract
-dteCharge (date of the charge)
-chargeAmount

The above gives the basic structure you will need. What other information do you plan on storing in the database? Employees? Tasks related to a contract? etc.?
 
jzwp22,

Ok, I think I get what you're saying and now I'll have plenty to do the rest of the week to rework my tables into a single table :)

So I should have:

tblContracts: (basically information about the contract. will be used as header information on reports reporting the finances.)
-pkContractID primary key, autonumber
-fkContractNumber forgeign key used to relate the contract to the contract charges in tblContractCharges
-ContractName
-ContractCompany
-ContractCOR

tblContractCharges
-pkContractChargesID primary key, autonumber
-fkContractNumber forgien key used to relate the contract number to the specific contract in tblContracts
-dteCharge (Month ending date)
-LaborCharge
-OtherDirectCharges
-TravelCharges

My concern with this prior had been that this table would contain the ContractNumber multiple times since multiple months are being charged to the same contract number, but that is just fine since ContractNumber is not the primary key. When I create queries and reports, I would rely on sorting and filtering to show just the data of the records I needed correct?

I think I'm steered in the right direction now. Please let me know if this all sound right. I greatly appreciate your assistance!!!
 
My concern with this prior had been that this table would contain the ContractNumber multiple times since multiple months are being charged to the same contract number, but that is just fine since ContractNumber is not the primary key. When I create queries and reports, I would rely on sorting and filtering to show just the data of the records I needed correct?

With respect to forms, you would need a main form (based on the contract table) with a subform based on the charges table. You can hide the control on the subform that holds the foreign key since your user will not need to see it.

Now that you have posted your proposed structure I see another issue. If at each period you have multiple charges (of different types), you have another one to many relationship. What would happen if you want to capture a new charge? You would have to change your table structure and any associated forms, reports and queries (not a good thing). The actual charges should be records not fields in a table

To handle multiple charges of different types the better structure would be as follows:

tblContracts: (basically information about the contract. will be used as header information on reports reporting the finances.)
-pkContractID primary key, autonumber
-fkContractNumber forgeign key used to relate the contract to the contract charges in tblContractCharges
-ContractName
-ContractCompany
-ContractCOR

tblContractCharges
-pkContractChargesID primary key, autonumber
-fkContractNumber forgien key used to relate the contract number to the specific contract in tblContracts
-dteCharge (Month ending date)

You need a table that holds the types of charges that are possible (as records). You can add more in the future as needed without impacting the table structure, forms, queries or reports.

tblChargeTypes (for now this table will hold 3 records: Labor, other direct, Travel)
-pkChargeTypeID primary key, autonumber
-txtChargeType

Now a table to hold the individual charges

tblContractChargeDetail
-pkContractChargeDetailID primary key, autonumber
-fkContractChargesID foreign key relating to tblContractCharges
-fkChargeTypeID foreign key to tblChargeTypes
-ChargeAmt
 
Not quite. You still need to establish a relationship between tblContractCharges and the charge detail table.

Make sure to check the Enforce Referential integrity checkbox for all of your relationships.
 
So I should add an fkContractNumber field in tblChargeType to relate to the fkContractNumber in tblContractCharges?
 
No, tblChargeType just holds the various names of the various charge types. When you enter a record in tblContractChargeDetail with the appropriate type, it becomes linked to the contract record.
 
That's better, but you still need to enforce referential integrity by double clicking the join line between each table and checking the box. (you should see an infinity symbol for the many side of the relationship).
 
I'm starting fresh here so I don't think it was letting me select that option yet because there is no data in the tables yet. I think you've really helped my understanding of how the tables should be constructed and how to relate them to each other. Reading a book can only help until you have questions.

There is more data I want to include and I'm going to attempt to make the addition of the tables and relationships myself and hopefully I've got the hang of it here. The information I'm including is the projected monthly cost that the contractor proposing to spend each month. In later reports, I'll use this information to compare against actual costs incurred to determine whether they are under or over executing compare to their proposed spending plan.

I've attached my update.
 

Attachments

Proposed versus actual are just two types, so there is no need for a duplicate set of tables. You just need to add a field that distinguishes whether the data related to a period is either actual or proposed. So in tblContractCharges, just add a field. I would probably set this field up as a foreign key field that relates to a table that holds 2 records (proposed and actual). This gives you future flexibility and also relieves you of the burden of making sure your users do not misspell the words proposed and actual.
 
Not truely clear on where to go from that. If it makes any difference, I should be the only user of this database and will use it to produce reports at the request of leadership.
 
You may be the only user now, but what happens when you leave the company?

I'll leave it to you to decide what type of field needs to be added.

Once you have the field you can then use a query to get either the proposed data for a period or the actual.
 
So if I add a fkActualVSProposed field to the tblContractCharges (reverting away from the duplicate tables of proposed and actual), would this attachemnt reflect how this would then appear?
 
Sorry, I didn't see the error message that the PDF had to be under 100 KB before I hit sent. I've attached as an accdb file.
 

Attachments

Users who are viewing this thread

Back
Top Bottom