Full Version: Select Which Record To Store
UtterAccess Discussion Forums > Microsoft® Access > Access Tables + Relationships
mystic226
I have a combobox which shows the client names, and another which shows contact names. I want the contact name combobox to only show records that are related to the client shown in the client name combobox. I have made a query which works based upon a client_number record, but it is prompting me for the client number each time because the table is storing the client name as the actual name rather than the client number. How can I have the client name combobox show the full name of the client but store it as the client_number?
theDBguy
Hi,

Make sure the first column of the combobox is the number and the second column is the name, then in the Column Widths property, set it to 0".

Also, make sure the Bound Column property is set to 1.

Just my 2 cents... 2cents.gif
briangriffey
Let's see a screen-shot of your tables/relationships window...
mike60smart
Hi

I think you need to look in the Archives for "Cascading Combobox's"
mystic226
Thanks DBguy, that helped with the data storage issue. I still have the pop-up asking for the client_number though, so I think something else is wrong. I don't think I can use the cascading comboboxes as mike60smart recommended, as the values is client_number will always be changing and there could be hundreds of records in there. tabappraisals (the table linked to the form with the comboboxes) is getting the records or the clients combobox from the tabclients table using the client_number and client_name fields. It get the records for the contacts combobox from the tabcontacts table using the contact_number and contact_fullname fields. These comboboxes work great independantly, but I would like the contact name box to filter by the client name that is shown. I can make this work using a query and then filtering by a manually entered client_number, but I want that to be automatic.
theDBguy
Hi,

If you could post a zip copy of your db with test data, we could probably help you figure it out.

Just my 2 cents... 2cents.gif
mystic226
I have attached the database as requested. The comboboxes I am having trouble with are on the Appraisals form, the contact box should sort by the client box record. I would also like the "appraisal number" combobox on the Items form to automatically fill with the number from the previous record whenever the add button is pushed. Thanks!
mike60smart
Hi

I have had a quick look at your Structure and I believe you need to take a step back and start
sorting your table design first

You CANNOT create any TRUE relationships due to wrong choices in table design

You are trying to Link Number to Text Data Types
You have Lookup fields in your Tables which are NOT recommended
When you try to Enforce Referential Integrity you will receive error messages informing you that
there is Data in one table which has NO related data in the other

You alos have Repeating Groups with multiple Yes?No fields - these should be records in a separate table and
NOT fields in the table

Can you first of all explain the process of What data you are recording in a bit more detail

We will then be aboe to give you further guidance
mystic226
I have done everything exactly the way my book and course layed out, so now I am confused.

1. The number-to-text data link was just an error that I had not fixed yet, now fixed.
2. How do I avoid the lookup fields in my tables? My book showed that they are required to create the relationships. My access course said this as well...
3. The yes/no fields are related only to each specific item, they are not duplicates of any other fields. I don't like the idea of using option groups as sometimes the names of these items will change. They are related to standard options, etc., why do they need to be in a separate table.

The data will be entered as follows:

1. Client info for customer requesting the appraisal, and all of their contacts (Management, sales, accounts, etc.)
2. Information about a specific appraisal for the client, with the main contact for this appraisal. (will have attachments for this appraisal as well, such as shedule A, etc., not added to form yet)
3. Specific items being appraised for above appraisal with support documents and photos. (There could be literally thousands of items per appraisal, so I would like to keep all of this data on one table)

Once entered, the data will need to be printed in 12 different formats, so every piece of data entered will be used in a report at some point.
mystic226
I picked up a new book last night, and now I see how wrong the other one was. I have removed all of the lookup fields and just set up the relationships using the relationships creator. I am still confused about the yes/no fields, why do they need to be in another table?
mike60smart
Hi

Seeing as I don't understand what your business model is for this database I am just guessing
at a scenario

I take it that a CLIENT will have an Appraisal
and the Appraisal will involve a number of Items ??

To help us understand can you explain your process?

In your tabItems you have the following Repeating Groups

Option1
Option2
OPtion3
Option4
Option5
Option6
Option7
Option8
OPtion9
Option10
Option11
Option12

Status1
Status2
Status3
Status4

Note1
Note2
Note3
Note4
Note5

Let us take the Repeating Group for Notes

If you want to record a Number of different Notes that are related to a specific
Appraisal then the following structure would be used

tblAppraisal
-AppraisalID PK Auto
-AppraisalDate
-(Other fields describing the Appraisal)

tblAppraisalNotes
-AppraisalNotID - PK - Autonumber
-AppraisalID - Number - ForeignKey (Linked to tblAppraisal on PK)
-AppraisalNotes

I may be way of the mark but until we know what you are trying to do then we can't
really give any worthwhile guidance
mystic226
Yes mike60smart, that is the correct scenario for the database. The yes/no cells are all related to specific option checkboxes on the form. For example:

If item #1 has air conditioning and a winch, opt_1 and opt_3 will be true. If the item has physically been seen at the site, status_1 will be true. If the item is an insurance write off, note_3 will be true.

The description for these checkboxes will always be the same on the reports, for example:

If the item is an insurance write off, note_3 will be true, and the reports will have a note that says "This item is listed as a registered insurance write-off".

I am going to add a table that contains all of the notes that could possibly be on the reports, and they will pull the correct note based upon the true/false records for each item.
theDBguy
Hi,

It might also be worthwhile to take a look at this demo by NoahP in the Code Archive:

Multiple Checkbox GUI In A Normalized Structure

Just my 2 cents... 2cents.gif
mike60smart
Hi

Still not understanding your structure

You currently have tabAppraisals linked to tabItems using Appraisal_Number

Appraisal_Number in both of these tables are Text Data Types ???

The Appraisal_Number in tabAppraisal should be set as an Autonumber field
The Appraisal_Number in tabItems should be set as a Number DataType

Currently to obtain the Appraisal_Number to link the 2 tables you are using a Combobox on the Form to select
the Appraisal_Number

Both of these fields would automatically populate with the Correct Unique Number IF you
set up the Relationships correctly
mystic226
I wanted the appraisal number to be the main document tracking number, so it is a number made up of 2 letters from the client name, the year the appraisal is being done, and a unique 3 digit ID number, so it cannot be autonumbered. I have attached the revised database with repaired relationships, and the cascading comboboxes now work. I would still like to have the appraisal_number on the Items form automatically select the previous record when the "add new item" button is pressed, just to save the user from having to remember the appraisal number while doing the item entries. (essentially the same as pressing ctrl +') Even a button next to that control on the form to copy the previous record would work. It also needs the ability to select the appraisal name from a list, as the items form will not always be accessed through the appraisals form. Sometimes a bunch of items will be entered one day, and then more added the next day, so the user would just pick "Items" from the main menu and start doing more entries. If they just had to pick the appraisal number for the first new item and had it automatically fill in for the rest, that would be ideal.
mike60smart
Hi


You said ""just to save the user from having to remember the appraisal number while doing the item entries"

As I said previously, If you construct the tables correctly then you would NOT need to do this as the Number
would populate automatically

As for this statement :-

"appraisal number to be the main document tracking number, so it is a number made up of 2 letters from the
client name, the year the appraisal is being done, and a unique 3 digit ID number, so it cannot be autonumbered"

This number can be generated as a seperate field in the Table BUT it should NOT be used as the linking field between
your tables

You should have an Autonumber field in ALL of your tables that Uniquely identifies EACH Record in the table(s)

The Autonumber Primary Key would then be linked to any related table on the Foreign Key.

When you then set the relationships between the table and you Enforce Referential Integrity this ensures that you MUST have
a Record in the One Side before you can add a Record to the Many side of the Relationship.

If you do not Enforce Referential Integrity this would result in your current situation where you end up with Orphaned Records ie
Date in the Many Side with No Related Record in the ONE side
mystic226
OK, that makes sense, I changed all my tables to have an autonumber field. Now I have a new issue though, my dcount and dsum fields will not work now due to their criteria fields being autonumbers. Can I convert an autonumber to text?

Also, the last reply did not address the main issue I am having, how do I make a record copy from the previous record. I cannot have this field autonumber as it will be the same number each time for every item in the appraisal.
mike60smart
Hi

The problems you are having is as I stated earlier

Your structure is wrong

When you say this :- "Also, the last reply did not address the main issue I am having, how do I make a record copy from the previous record. I cannot have this field autonumber as it will be the same number each time for every item in the appraisal."

When you add an item you DO NOT need to copy the Appraisal_number to another Record

The linking Foreign Key will be the link to the Unique Primary Key - your Autonumber

In this example below - If I have an Appraisal with a Unique Number as shown in your table Appraisals:-

Click to view attachment


when I add an item to the Items table the records will display as follows:-

Click to view attachment

Notice in the Appraisal Items table that the Unique Autonumber 1 is now being Repeated in the AppraisalItems table as the Foreign Key

When I added the Appraisal Number then the Autonumber allocated was 1 - You would enter your Unique Appraisal Number of AB-23-XYZ

When I add items that are related to this Unique AutoNumber in the Form based on the table AppraisalItems then
the Foreign Key (AppraisalID) is automatically entered by Access because I have Enforced Referential integrity between
these 2 tables. So in other words I have a ParentKey or PrimaryKey in table Appraisals and this Parent Key is linked
to the Many side of the relationship on the ChildKey or Foreign Key

1 Appraisal has Many Items
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.