Help with VBA code accociated combo box

[JSimoncelli]

Hello All,
I have an issue I am trying to resolve, I have a possible method to resolve this issue but I need help with the possible code. Also, if there is a better way to go about solving the issue please let me know.

Background: I am using Access 2007; if you look at the attached Relationship image you can see the database setup. This is a material control/tracking database; the material in question can be CD/DVD’s, documents, hard drives or other types of material.

The tblMaterial table consists of information unique to each piece of material. The tables on the left side are predefined information that is represented in the tblMaterial table as FK’s. The tblSentDetails and tbleReceivedDetails tables represent information of when material is sent or received. Each records in either the sent or received tables can have one or more pieces of materials, for example I can send a CD and five documents in one shipment as well as receive several pieces of material in another. The tblCarrier table consist a list of possible methods of shipment e.g. UPS FedEX ect, and the SentHow and ReceivedHow fields draw from this table. By the same token the Originator, SentTo and ReceivedFrom draw from the tblOrganization table.

Issue: When material records are added or edited I need a way to associate either the received and/or sent details with individual material records.

After a couple of days trying several different possibilities the following is what I came up with, I believe it is simple and efficient.

I created an add/edit form (frmEditAdd bound to tblMaterial table), the form it’s self functions very well and I have no problems with it as a whole. Since the sent details and received details functionally are the same I will concentrate on the received details and duplicate the process for the sent details.

On the EditAdd form (see attached image) the Received From, Received How, and Received Receipt Mailed draw their information from the ReceivedDetails table and are displayed using a sub-form. The process I came up with to solve my issue is an unbound combo box. The row source of the combo box is the attached query qryDetailSelect2 (see attached image). This query draws from the ReceivedDetails table. In the qryDetailSelect2 query the first column is a combination of the ReceivedDate and MailRegNum and is used as a visual reference for the user, the second column is the ReceivedDetailID and the third is again the ReceivedDate field. The reason for the second received date reference is allow me to sort the query with the “newest” record on top, less searching for the user. The combo box is setup to show the first column hiding the other two, column with 1”;0”;0” and column count 3. You can see the result of this part of the process in the frmEditAdd image far right see arrow.

Now here’s what I need help with, the information in the tblReceivedDetails table and subsequent qryDetailSelect2 query is input through another from/process and the user will/should be familiar with the received date and mail registration number. Below is what I want the combo box to do:

With the record selected in the form, the user would use the combo box to display a list of the available Received Details records. Because the query is sorted by newest received date the desired record should be at or near the top. Once selected, I want the ReceivedDetailID of the selected record (see qryDetailSelect2) to be written to the ReceivedDetails (FK) in the tblMaterial table. This will populate the fields in the EditAdd form associated with the ReceivedRetails table. Once saved this should work.

I am looking for the code to do this.

This is the process I came up with to solve my issue, if you feel there is a better way to accomplish this please let me know.

Thanks in advance

John

 

[jzwp22]

I think that your table structure is incorrect. First, the tblSentDetails and the tblReceivedDetails have basically the same fields, so they should be combined into one table with a field designating either sent or received. I would call this table tblTransactions


tblTransactions
-pkTransID primary key, autonumber
-fkTransTypeID foreign key to tblTransTypes
-dteTrans (transaction date)
-Receipt
-MailRegNumber

tblTransactionTypes (2 records: sent, received)
-pkTransTypeID primary key, autonumber
-txtTransTypeName

I would also combine the carrier and organization tables into one (I'll call them companies) & have a field that distinguishes the type (organization or carrier).

tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName
-fkCompanyTypeID foreign key to tblCompany Types

tblCompanyTypes (two records: organization, carrier)
-pkCompanyTypeID primary key, autonumber
-txtCompanyTypeName

Then since you have 2 entities (organization & carrier) involved with each transaction, that describes a one-to-many relationships, so you need a table to handle that

tblTransactionCompanies
-pkTransCoID primary key, autonumber
-fkTransID foreign key to tblTransactions
-fkCompanyID foreign key to tblCompanies (holds both organizations and carriers)


Now for the materials related to a transaction. Since a transaction can have many materials that too describes a one-to-many relationship, so this table.

tblTransactionMaterials
-pkTransMatID primary key, autonumber
-fkTransID foreign key to tblTransactions
-fkMaterialID foreign key to tblMaterial

You would remove the ReceivedDetails and SentDetails fields from tblMaterials

 

[JSimoncelli]

Jz,
Thanks for your advice, I agree with your assessment of the tblSentDetails and tblReceivedDetails and have combined them see Relationship2 image showing related tables. However I did leave the organization and carrier tables separate, I did rename the carrier to mailcarrier. The reason being the mailcarrier describes how/by what method material was sent/received and the organization table is a list of physical places.
I have attached the new relationship (see relationship2 image) and an example of the tblMail and a qryMail. So hopefully this works better.
Ok back to the original request for help with the combo box (see attached frmEditAdd in pervious post).
Again Thanks…
John

 

[jzwp22]

Before we can address the combo box issue, it is critical that the tables are setup properly

You said this:

Each records in either the sent or received tables can have one or more pieces of materials

Are you saying that when there is a shipment or receipt, many different materials can be included?

 

[JSimoncelli]

jz,

Yes, I can receive a USPS Express containing one or more pieces of material (e.g. CD/DVD’s, Documents ECT) in any combination. The same is true if I am sending material. Not to put a monkey wrench into this but, I could receive a given piece of material on one day and send the same piece of material out two months later. So there would be two records related to the material, a received and a sent record.

After some more thought and your question I have created a table tblTransaction to account for the possibility of multiple pieces of material in one mailing record (see Relationship3 image). This allows the tblMail (mailing records) too contain no duplicate records.

So how’s this?

John

 

[jzwp22]

The structure looks better now with the addition of tblTransaction.

With this setup you would typically use a main form based on tblMail with a subform based on tblTransaction. Within that subform, you would use a combo box based on tblMaterials. The combo box would be bound to the materialID field. This allows you to associate several material items to the 1 mail item.