Add different info than what is on the drop down list

[MelissaMuz]

Hi I'm wondering if there is a way to add text that is not included in the drop down list (from my other table) without first adding it to my other table. I ask as we have one off parts which would not be needed to be used again. I think the answer is obvious but I thought I would ask before giving up!

 

[micks55]

Hi, Firstly, I'm no specialist so this might be rubbish.

If the combo gets it's data from a table and you are actually storing the ID for a record in that table then I think you can't avoid the source table getting cluttered with a load of one-offs.

However, if the Row Source of the combo comes from a table or a value list and you are storing the text you see in the combo (ie: the combo shows Red/Blue/Green and you are storing the actual word Red/Blue/Green in a text field then maybe changing the Limit To List property of the combo to No would solve the problem.

I'm sure better brains than mine will respond later. Mike

 

[MelissaMuz]

Hi, Firstly, I'm no specialist so this might be rubbish.

If the combo gets it's data from a table and you are actually storing the ID for a record in that table then I think you can't avoid the source table getting cluttered with a load of one-offs.

However, if the Row Source of the combo comes from a table or a value list and you are storing the text you see in the combo (ie: the combo shows Red/Blue/Green and you are storing the actual word Red/Blue/Green in a text field then maybe changing the Limit To List property of the combo to No would solve the problem.

I'm sure better brains than mine will respond later. Mike

Ah that makes sense, thank you, I will have a look at this. I am just starting out with Access so any help is great at the moment!

 

[Gasman]

Hi I'm wondering if there is a way to add text that is not included in the drop down list (from my other table) without first adding it to my other table. I ask as we have one off parts which would not be needed to be used again. I think the answer is obvious but I thought I would ask before giving up!

You say that now, but things will likely change.
Adding another record in a table is more flexible and if it gets used once every few years or only once, then no great deal.
You could even add a flag field in the table to say whether active or not, and set them to inactive so they do not clutter up the combo with daily used entries?

 

[arnelgp]

another "trick" would be do add another Field (Discontinued, Yes/No) to your item table.
to remove the item from the list just tick the ones you don't need from the item table.
then on the Row Source of your combo, just select only those items who's discontinued field is not Set.

example:

Select ItemID, [Item Name] From ItemTable Where (Discontinued = False)

 

[micks55]

Also.
Personally, I would not store an ID anyway. I would expect part numbers to be unique so adding a unique ID is overkill. By storing the part number in full you will know what was fitted at the time of the repair.
As Gasman and arnelgp say, your Parts table could/should have a "Discontinued" yes/no field for when that part is no longer available or has been superseded by a new part.

 

[Gasman]

Also.
Personally, I would not store an ID anyway. I would expect part numbers to be unique so adding a unique ID is overkill. By storing the part number in full you will know what was fitted at the time of the repair.
As Gasman and arnelgp say, your Parts table could/should have a "Discontinued" yes/no field for when that part is no longer available or has been superseded by a new part.

No, I would always use an Autonumber.
More efficient I believe, and just in case the part number changes for any reason whatsoever.

 

[MelissaMuz]

You say that now, but things will likely change.
Adding another record in a table is more flexible and if it gets used once every few years or only once, then no great deal.
You could even add a flag field in the table to say whether active or not, and set them to inactive so they do not clutter up the combo with daily used entries?

another "trick" would be do add another Field (Discontinued, Yes/No) to your item table.
to remove the item from the list just tick the ones you don't need from the item table.
then on the Row Source of your combo, just select only those items who's discontinued field is not Set.

example:

Select ItemID, [Item Name] From ItemTable Where (Discontinued = False)

Thanks both for the advice, I will definitely try these

 

[MelissaMuz]

Also.
Personally, I would not store an ID anyway. I would expect part numbers to be unique so adding a unique ID is overkill. By storing the part number in full you will know what was fitted at the time of the repair.
As Gasman and arnelgp say, your Parts table could/should have a "Discontinued" yes/no field for when that part is no longer available or has been superseded by a new part.

No, I would always user an Autonumber.
More efficient I believe, and just in case the part number changes for any reason whatsoever.

I have been wondering about this too, but every video I have watched on Access says to use it on pretty much every table and the Primary key needs to be "meaningless"... Still not sure as I haven't had to use the Autonumber IDs much!

 

[Gasman]

I have been wondering about this too, but every video I have watched on Access says to use it on pretty much every table and the Primary key needs to be "meaningless"

That is my view as well. Applies not just to Access, but to any database design. Then again, that is just my view as I mentioned.

 

[CJ_London]

@MelissaMuz
If it is literally a one off project and there is nothing in the projects table that is required other than the name then another alternative is to have a separate field to record the project number - you can include a project record with a name like 'one off project' and if selected, the other field becomes available.

Access says to use it on pretty much every table and the Primary key needs to be "meaningless"

I would interpret that as 'should not be assigned any meaning' other than as a unique identifier for the record. So should not be something like ProjectNumber which has given it a meaning.

@micks55

Personally, I would not store an ID anyway. I would expect part numbers to be unique so adding a unique ID is overkill. By storing the part number in full you will know what was fitted at the time of the repair.

If your part number is text and say typically 8 characters long, your app will suffer from poor performance once you have a large number of records. An autonumber (a long) takes 4 bytes, strings require 2 bytes per character plus 2 bytes so an 8 character code will take 18 bytes - 4.5 times bigger. Indexing reads 4000 bytes at a time so will capture 1000 longs, or 220 strings - so simplistically strings will be nearly 5 times slower. This won't be apparent until you have 220 records although even then, with modern computers the time difference will not be that noticeable, but get into the 10's of thousands and you will start to see a degradation in performance.

And don't forget the child records will also need indexing - so if you have a parent record with 10 child records, for autonumbers, the parent record takes up 8 bytes (4 for the record and 4 for the index) and the child records 80 bytes - a total of 88 bytes, whilst your string equivalent will require 36 bytes for the parent record and 360 bytes for the child records - which takes up more space on the disk

So by all means stick with your string PK if you don't expect to hold many records (I do use them for things like listing abbreviations as PK and fullname for US states, counties in the UK and the like) . But don't dismiss autonumbers as overkill.

 

[Pat Hartman]

Hi I'm wondering if there is a way to add text that is not included in the drop down list (from my other table) without first adding it to my other table.

The point of using a combo or listbox is to prevent random data entry. The number of times you will assign a particular value is irrelevant. If it is a valid option, it belongs in the RowSource table of the combo or listbox. PERIOD.

@arnelgp alluded to using a discontinued code in the table. This is not a "trick". It is a valid and common solution. Using the ActiveFlg allows you to deactivate an item so that although it was valid in the past, you can no longer add it to new items going forward. This leaves your data and your historical reporting intact.

Take a look at this mini-app I add to all my applications. It manages an infinite number of simple lists (including the concept of Active/Inactive) and even can be hooked up to some basic security so that users can add items or change the text. I do not allow users to delete items because it is too dangerous. If you want to allow deletes, you can change the AllowDeletes property to yes but I recommend that if you do that, you add validation to ensure that the lookup value has not been used anywhere.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

Still not sure as I haven't had to use the Autonumber IDs much!

Then you are probably not creating correct relationships

 

[MelissaMuz]

Then you are probably not creating correct relationships

I would love to learn how! My db seems to be working well so far, but I have lots to learn with Access.