ms access 2000 combo box question

[uscitizen]

to simplify this, say i have a table with a numeric field which we'll call numero. numero uses a combo box lookup table with two columns. one is a number field and the other is a text field. we'll call the fields in combo box numero_1 and text_1. numero_1 is the leftmost field and text_1 is to the right in the combo box lookup table. numero_1 is field that i've bound to the numeric field called numero in the access table.

when the user hits the drop down arrow (s)he sees a two column look-up list. on the left is the numeric column with numero_1's values and on the right is the text column with text_1's values.

and of course, numero_1's values are stored in the the numeric numero field in the ms access table.

i more or less inherited this way of doing this and what i would really like the user to do as (s)he enters this information is to store the value of text_1 column in another text based column which i'd gladly add to the ms access table.

if you've read this far, what is perhaps just as important to know is that the values of numero_1 have no intrinsic value! i didn't create this paradigm, and if i had all i would have done was create a combo box with the values of text_1 for the user to pick from. however......

the ms access table happens to have thousands of rows with values in the numero field --- so it's no mean task to manually convert each to its textual equivalent.

my gut tells me that ms access can be coaxed into handling the awesome conversion.

ideas are most welcome.

 

[neecie]

Hi uscitizen
Just a suggestion. If I understand your question correctly, your problem is having a blank text field where there is something recorded in the number field.

Why not create a stored procedure that looks for a blank text field and fills it with the textual equivalent of the number field? The stored procedure could select both fields into variables, and then update if required.

You wouldn't need to update any records manually and would have the data where you want it.

 

[uscitizen]

being an access newbie, do you mean by stored procedure?

 

[neecie]

A stored procedure is code that resides on and runs from the server. In an adp project, you should see it listed under objects in the database.
Open Access and search for the term in the help file or use SQL server Books on Line if you have access to it.

 

[uscitizen]

how about a macro -

 

[markphsd]

uscitizen,
aside from the interesting though difficult choice in nomenclature, your screen name, your tables.. i think you chose the wrong forum. You might want to check out Access Tables and Relationships, and Access jet Queries, and then Access Forms. This is for ADP specific questions.

Well, to answer your question. I would say what you have is not a table, but a query, or what you should have.

for example, you want to look up customers, in a customers table. You write a select query that holds say 4 fields.

select customerID, cusLastName & ", " & cusFirstName, Zip FROM Customers
WHERE ((cusLastName) is not null)
Order By cusLastName

This will give you a list that will populate your drop down. Set the bound column to 1. That would make the CustomerID field be the look up. You can do a simple lookup wizard to get this done by the way. Just change the field names and the table name to whatever table you want. You don't have to combine the names, I just do that because it's nice.

--------
\part 2
the ms access table happens to have thousands of rows with values in the numero field --- so it's no mean task to manually convert each to its textual equivalent.

Look up append queries. you are right, it isn't a mean task, this would be quite simple, but i'm not sure if that's really what you want to do.


Mark P.

Bleh

 

[chrissie1]

to solve the problem without using queries

just add a field to your table lets say text_1. add a textbox to your form call it text_1 and bind it to text_1.

Put this code in the afterupdate event of the combobox called numero_1

Text_1 = numero_1.column(1)

where column(1) is the second column in your combobox.



Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong