Well..another problem..
A form has a List Box on it that takes data from a table called Housing Ass Name and stores in a table called Reigate/Banstead. Now i have a query that looks for certain Housing Ass Name field (eg RHT or MG, etc), in the query I have a field called HousingAssociation with this in the criteria [Reigate Banstead]![HousingAssociationName] therefore asking the person to put in Housing Ass Name that is stored, such as RHT, but the problem is MSAccess stores this value as a number not as RHT, therefore to bring back the correct data the person has to put in the corresponding number (in this case 6) to bring back the correct data. My question is...is there anyway i can change how MSAccess stores the value or can the person still put in RHT but the query understands that its number six. I am trying to make it easy for the person...not having to remember or lookup the number value of the Housing Ass Name.
Hope that makes sense...once again any help would be appreciated
Thanks :D

It sounds like you are using a Lookup Field in your table (http://www.mvps.org/access/lookupfields.htm)when it should be a ComboBox on your form.

I tried it with a ComboBox also plus the ListBox in my Form which is based on a table called Reigate_Banstead, (not in the table, the tables are only holding the data required and the results) ......but MSAccess still stores the value in the Form Table (Reigate_Banstead) as a number not RHT etc... so in the query the operator still has to put in the Number value of the Housing Ass Name ..eg RHT = 6.

You can have the ComboBox display the TextValue but use the Numerical value. Is your table using Lookup Fields?

I used the Wizard for the ComboBox or ListBox and it asks you from which table do you want to get the information from...eg HousingAssNames and than it asked if you want it to remember the value or store it..i said store it in the Table that the form is based on Reigate_Banstead. The ComboBox shows the Text value in the Form when you look at each record for clients...but when you go to the Table that is holding the information Reigate_Banstead and look in the column for that field HousingAssociation it shows a number value.. eg..in form value is shown as RHT but in the Table it is shown as 6.

So what is wrong with a numerical value? That is probably a ForeignKey field and done properly.

The problem is i am working with people that need things to be simple :rolleyes:...and for this query to work correcting at the moment...they need to remember what Numerical value is for each and every HousingAssName which there is upto 20 of them....so i am trying to fix it so they can enter the text value, eg: RHT, (which they know by heart) instead of the Numerical value 6. So I need to know how i can get MSAccess to understand that RHT means 6 or whatever Numerical Value is assigned to HousingAssName by the ComboBox.

I would think you would run your query from a form.

Do you mean base the query on the Form...thus use the fields that are on the form and not from the table that holds the data that the Form generates... Only problem with that is the ComboBox doesnt give you the option to use form unless you a field that holds the values that you want..which i dont... as i am using the ComboBox on the form to give the person a choice and than store that choice in the Form's table...and the ComboBox gets its information from the HousingAssNames Table....
Not easy to explain all this long distance :eek: The query i am using runs from a Command Button that is on the Form...but its uses Data from the Reigate_Banstead table.

If the field is a Text field then you could just change the bound column on the ComboBox.

Sorry...not to sure what you mean by change the bound column .. do you mean...click on the ComboBox on the form in Design mode and bring up the Properties Box for the ComboBox... I see the Bound Column but it has 1 in it ...can i change that so it refers to a field that has text in it....:(

That is the column of your RowSource that will be stored in the ControlSource of the cbo.

:D:p:D:p:D;) got it....thanks heaps...

You're welcome. Glad I could help.