enter code, display description & vice versa

[mitchem1]

Not sure if this belongs in tables or queries forum, but I'll start here.
On an Inventory Item form (based on a query), one of the fields is Equipment Category. Normally I would do a lookup on the Equipment Category Description (from the Equipment Category table), and then store the Equipment Category ID. However the client says that some users know the ID, some know the Description. In other words, when the user types in the ID on the form, I want it to blow in the Description. It they don't know the ID and move on to the Description field, they want the ID to populate when they've selected a Description. Sorry if this is too confusing and Thanks.

 

[wazz]

one possibility:
put two comboboxes on the form. when one is updated, update the other to match with the other info.

it sounds like your tables are normalized - you are not duplicating data - so the two boxes would always have to work in conjunction with each other.

 

[Scooterbug]

Couple of ways off the top of my head:

1. Have two combo boxes, one for the EquiptCatgoryID and one for the Description. Both would have the same Row Source, just make the ID visible on one and the Description visible on the other.

2. If just using a text box, Have a test on the After Update. Check to see if what has been entered has a value or not. If it has a value (an EquiptCatagoryID has been entered) then you know it's an ID. If not, then it's the description. You can tailor your lookup based off of that.

 

[boblarson]

Or use a listbox which shows both fields, give them an option group to sort by ID or Description, and then bind the listbox to the ID field.

 

[Brianwarnock]

I think this should probably be in the FORMS forum. You say that you would normally

do a lookup on the Equipment Category Description (from the Equipment Category table), and then store the Equipment Category ID

, so isn't just a case of reversing the lookup?
ie if the ID is entered lookup the desciption.
I'm not sure what the problem is.

Brian

 

[wazz]

moved to forms forum.

 

[mitchem1]

Couple of ways off the top of my head:

1. Have two combo boxes, one for the EquiptCatgoryID and one for the Description. Both would have the same Row Source, just make the ID visible on one and the Description visible on the other.

2. If just using a text box, Have a test on the After Update. Check to see if what has been entered has a value or not. If it has a value (an EquiptCatagoryID has been entered) then you know it's an ID. If not, then it's the description. You can tailor your lookup based off of that.

Thanks for all the feedback and sorry for not responding till now. Been out for a few days. Scooterbug, I opted for your option 1 as that was the direction I was headed and I don't know VB. I am using the same RowSource for the Equipment Category ID and Equipment Category Description fields (see below), but they do not synch up with each other. i.e., when I choose a category ID, the description does not update and vice versa.


Equipment Category ID combo box (I hide the description)
SELECT tblEquipmentCategory.EquipmentCategoryID, tblEquipmentCategory.EquipmentCategoryDescription FROM tblEquipmentCategory ORDER BY tblEquipmentCategory.EquipmentCategoryID;

Equipment Category Description combo box (I hide the ID)
SELECT tblEquipmentCategory.EquipmentCategoryID, tblEquipmentCategory.EquipmentCategoryDescription FROM tblEquipmentCategory ORDER BY tblEquipmentCategory.EquipmentCategoryDescription;

 

[Scooterbug]

If you want to have the description show up in the combo box after choosing the ID and vice versa...you will need a bit of code. You will have to tell Access what to display when a selection is made.

For each combo box, go into the properties. Under the Events tab, find the On Change event. Click on the little box with the 3 dots. Choose Code Builder. You will see something like this:

Private Sub ComboBoxName_Change()
 
End Sub

Where ComboBoxName is the name of the actual Combo box. Put in the following:

'For the ID Combo box:
me.ComboBoxDesc = me.ComboBoxID
me.ComboBoxDesc.requery
 
 
'for the Desc Combo box:
me.ComboBoxID = me.ComboBoxDesc
me.ComboBoxID.Requery

that will display the ID or Desc in the box that is not used to make the selection. Make sure you substitue the names of the combo boxes in the code with the actual names that are on your form.

 

[mitchem1]

If you want to have the description show up in the combo box after choosing the ID and vice versa...you will need a bit of code. You will have to tell Access what to display when a selection is made.

For each combo box, go into the properties. Under the Events tab, find the On Change event. Click on the little box with the 3 dots. Choose Code Builder. You will see something like this:

Private Sub ComboBoxName_Change()
 
End Sub

Where ComboBoxName is the name of the actual Combo box. Put in the following:

'For the ID Combo box:
me.ComboBoxDesc = me.ComboBoxID
me.ComboBoxDesc.requery
 
 
'for the Desc Combo box:
me.ComboBoxID = me.ComboBoxDesc
me.ComboBoxID.Requery

that will display the ID or Desc in the box that is not used to make the selection. Make sure you substitue the names of the combo boxes in the code with the actual names that are on your form.

Closer but not quite. The name of the combo boxes:

ID combo box = EquipmentCategoryID
Description combo box = EquipmentCategoryDescription

So this is the code I put in, which I think should be right:

--------------------------
Private Sub EquipmentCategoryID_Change()

Me.EquipmentCategoryDescription = Me.EquipmentCategoryID
Me.EquipmentCategoryDescription.Requery

End Sub
--------------------------
Private Sub EquipmentCategoryDescription_Change()

Me.EquipmentCategoryID = Me.EquipmentCategoryDescription
Me.EquipmentCategoryID.Requery

End Sub
--------------------------

However, let's say in the ID combo, I select 254. In the Description combo, it blows in 254 -- not the description that corresponds with 254.

Thanks for your time.

 

[Scooterbug]

You have to set the column widths of the combo boxes. Go into the Properties of the combo boxes. The two that you are concerned about are:

Column Count
Column Width

Both should have a Column Count of 2

For description, column Width should be: 0;1"
and reverse for the ID.

This tells access that both combo boxes have two columns...by setting a width to 0 it will hid that column.

 

[mitchem1]

You have to set the column widths of the combo boxes. Go into the Properties of the combo boxes. The two that you are concerned about are:

Column Count
Column Width

Both should have a Column Count of 2

For description, column Width should be: 0;1"
and reverse for the ID.

This tells access that both combo boxes have two columns...by setting a width to 0 it will hid that column.

I actually do have this part set up correctly, I think. For the ID combo, Column Count = 2. Column Widths = 1";0".
For the Description combo, Column Count = 2. Column Widths = 0", 1". I believe I have this right as when I hit the drop down arrow on the ID combo, all I see are IDs. When I hit the drop down arrow on the Description combo, all I see are descriptions.

 

[Scooterbug]

Hmmm...I tested the code before I posted it....

Anyway you can post a sample database with the form and a bit of test data in the EquiptmentCatagory table?

If you are using A2007, then make sure you save it as a MDB file.

 

[mitchem1]

Here it is. Hope it gets attached. Site seems to be having some technical issues today.
The database will open to the correct form. The fields in question, Equipment Category Code (ID) and Equipment Category Description are three rows down on the left.
Thanks again for all your help.

 

[Scooterbug]

Ok, I took a look...and here's the problem. Both combo boxes need to have the same bound column. In the description, you had the second column bound. In the code, when you tell Access to make the one equal to the other, you were telling it to make the ID field equal to the Decription field. So, for the Description combo, change the Bound Column to 1 and you will be good to go. (It's always good practice to use the ID field as the bound column anyways)

Also, you dont need to store both the ID and the Description in your tblAll. By storing just the ID, you can use that to reference the description.

 

[mitchem1]

Wow, works like a charm. Thanks so much. I really appreciate it.

 

[mitchem1]

Ok, I took a look...and here's the problem. Both combo boxes need to have the same bound column. In the description, you had the second column bound. In the code, when you tell Access to make the one equal to the other, you were telling it to make the ID field equal to the Decription field. So, for the Description combo, change the Bound Column to 1 and you will be good to go. (It's always good practice to use the ID field as the bound column anyways)

Also, you dont need to store both the ID and the Description in your tblAll. By storing just the ID, you can use that to reference the description.

After doing a little testing, I have one more question. If I tab to the Equipment Category Code field, type in a code and then tab to the Description field, the Description doesn't update. However, it I select the code from the drop down list (rather than type it in), the description does update. Is it possible to correct this? If not, no big deal. Thanks.

 

[Scooterbug]

Well, if you are dead set on storing the Description, you need to tweak the code. The new code should be:

Private Sub EquipmentCategoryDescription_AfterUpdate()
Me.EquipmentCategoryID = Me.EquipmentCategoryDescription.Column(0)
Me.EquipmentCategoryID.Requery
End Sub
 
 
Private Sub EquipmentCategoryID_AfterUpdate()
Me.EquipmentCategoryDescription = Me.EquipmentCategoryID.Column(1)
Me.EquipmentCategoryDescription.Requery
End Sub

Also, change the bound column on the description back to 2.

The reason why you shouldn't store the description is what happens when someone decides to change the ID sometime down the road? When the decide that (And trust me....it's not outside the realm of possibility ) you are going to have to go in and find all the records that have that ID number and change the description. Where if you use the ID number to reference the ID number, all you have to do is change the Description in it's Description table.

 

[mitchem1]

I am not dead set on storing the description at all. I just wondered if the description could be displayed when the user types the code as opposed to using the drop down list. If not, no problem.

 

[Scooterbug]

After looking at the db....I forgot to mention to change the control source for the Description combo to EquipmentCategoryID. My bad. Couple that with the first bit of code...and it should work just fine. It wont record the Description, but since you are storing the ID, then it's not a problem.

 

[mitchem1]

It didn't seem to like it when I changed the Control Source of the Description combo to EquipmentCategoryID. When the cursor is in the field before the ID combo (Misc. Location Description), and you tab to the ID combo, it will not let you type in a different code. The field seems to be locked, although the drop down can be used.