How can I compare fields with 2 bound columns

[Ssstacey]

Hello..

In one of my tables I have a field with the following characteristics:

Listbox
row source: SELECT Category.CategoryID, Category.Category FROM Category ORDER BY Category.CategoryID;
row source type: table/query
bound column:2
column count: 2

This gives the user the opportunity to see the category as well as the categoryID when choosing, however once selected only the CategoryID (which is a number) is visible.

I would like to put the same option on one of my forms, but Im not succeeding at this. How can I do this? Is this possible?

This leads me to my next question: If I want to load the information from the form and compare it in VBA with the one from the table, is it seen as a number or a string?
I hope it is clear..
Any thoughts on how to deal with this??
Thank u..
Stacey

 

[RuralGuy]

Hi Stacy,

Correct me if I'm wrong but it sounds like you have a Text field in your table with a ListBox as the Lookup for it. That is almost *always* a mistake since it tends to hide what is really in the field and confuse the programmer. Just leave the Lookup as a TextBox. Put your ListBox on a form and use the Wizard to help you define it.

 

[Ssstacey]

Hi Rural guy,
Im not sure I understand what u want me to change. What do u mean by " leave the lookup as textbox" ? if I leave it as a textbox, it doesn't define anything, i think.

What I have is a Number field in my table with a listbox as the lookup. To clarify: I have somewhere a list of categories in a field in a table called category.
In another table (lets call it 'Company' ) I have a field in which I have the option to select the category as I indicated above.
As u mentioned indeed, on the form I have to use the wizard to define the input of the listbox. I have done this, but still the wizard does not put it the way it does in the company table.
Another problem I have is that, even when both of them are excactly the same, does VBA see it as a number or a string?
I hope it's clear.
Thanx
Stacey

 

[RuralGuy]

The answer to your question is why I'm recommending against any "Lookup Fields" in your table. Putting anything but a TextBox as a Lookup does not "define" anything anyway, it only hides what is in the field. If you leave it as a TextBox then you can see what is in the field when you open the table. Are you trying to put a ListBox or a ComboBox on your form?

 

[Ssstacey]

hi RG,

I see your point... How do u suggest I change it?
I would like to use the convenience of the fact that it 'hides' some of the information. is a user select categoryID 1 for examples , it doesn't say anything to them, while if it says food they know what to select. Reason why i cannot only put the names (like 'food' etc.. ) is that everything is linked by the categoryID. So, I need that somewhere.
Im trying to put a combobox on my form.
How can I fix this?
Thanx
Stacey

 

[RuralGuy]

The Wizard should be able to assist you completely in placing a ComboBox on your form that does *exactly* what you desire. That is one of the main functions of a cbo is to allow the user to select from a useful description but actually place some cryptic number in the field. Post back if you can't get the Wizard to properly define the ComboBox you want.

 

[Ssstacey]

Thanx a lot.. i understand now what I was doing wrong..
Greets..
Stacey

 

[RuralGuy]

Good luck on the rest of your project.