1 table 2 columns 1 form

[tstolk]

Hi all!

First of all sorry for my terrible English!

I have a very simple question (I think). I have a table with 2 columns in it, filled with city names and their id's. For example:

city - id
rotterdam - rd
amsterdam - ad
new york - ny
london - ld

I have a form with this city-table as a dropdown menu to fill in the control. What i now want is, when I select the city, a different text field named ID must be filled in with the corresponsding city-id.
So, how do I fill in 1 field when a edit the 2nd field?

Thnx in advance!!

 

[KenHigg]

Hi all!

First of all sorry for my terrible English!

I have a very simple question (I think). I have a table with 2 columns in it, filled with city names and their id's. For example:

city - id
rotterdam - rd
amsterdam - ad
new york - ny
london - ld

I have a form with this city-table as a dropdown menu to fill in the control. What i now want is, when I select the city, a different text field named ID must be filled in with the corresponsding city-id.
So, how do I fill in 1 field when a edit the 2nd field?

Thnx in advance!!

First off, a combo box will have a rowsource, which is supplies the data you select from, and a recordsource, which is the fld in the forms underlying recordsource (or table), where you want to store the data you select. So after you have these set up what ever you select from the list will be stored in the appropriate fld. If you have two or more flds populating the list, like city and id, one of the columns will be set up as the bound column. Say you may elect to set up the first col as the bound col. In this case the city is the first col in the list box, this is the data that will be saves in the forms table.

Hope this makes sense...

 

[tstolk]

Thnx Ken! But I think you did not understand my question well, or I don't understand your answer...!!

Let explain my situation. I have a DB where I store all computer hardware of a company and its user. All computers have a unique ID. This ID will be generated by combining the city, laptop or dekstop and a unique number.
This will look like "LondonLaptop-25". The ID will be build up during the registration of the computer. So when I register in a city-field that a user works in London (combo box from table city) and register in the brand-field that the user has a Toshiba (combo box from table brand), the unique ID field must be filled in automatically with LondonLaptop-Uniquenumber. Only this is a very long ID code so i have an abbreviation for the city and the laptop. Londen will be LD and Toshiba will be L. Now the ID will be: LDL-25
The abbreviations are stored in the same tables, city and brand as second column.

My question: when I choose a city in a combo box (1st column in table), how can a different field be filled with the abbreviation of that city (2nd column of same row in the table)..

Hope this is clear?! It's hard to explain myself in a different language!

Thanx in advance!
Tim

 

[KenHigg]

I'm not sold that this unique id scheme is the best approach from a database perspective but here goes... ))

So the combo box has a rowsource of two columns, col 1 is the city name and col 2 is the abbrev. of the city name. And lets say the name of the text box you want to put the abbrev of the city name is called 'myCityAbbrev'.

On the OnChange event for the combo box you could do something like:

me!myCityAbbrev = me!myComboBox.col(1)

Would this work?

 

[tstolk]

I'm almost there I think!
Only, if I paste me!myCityAbbrev = me!myComboBox.col(1)
in the OnChange event (ofcourse with the right names), an error message appears that sais that the macro can not be found...
Is this VBA or do I have to make a macro? I prefer VB but that has to be something like: me.myCityAbbrev.value = me.myComboBox.column(1) ??? This does not work either... Any idea?

Thnx for your great help Ken!

Chears!
Tim

 

[KenHigg]

This is vb code. Is this where you have it when it gives you the error?

 

[tstolk]

This is my exact line: Me!city_id = Me!lokatie.col(1)

The error message is:

Error 438 during run:
This property or methode is not supported by this object

In VB it says: Me!city_id = Null

Any idea?

Thnx again and again and again!

Grtz,
Tim

 

[KenHigg]

Sorry to be so dim

When you say your exact line, are you seeing this in the properties window for the combobox in the forms design view or is this 'exact' line in the visual basic code editor?

 

[tstolk]

Thats the exact line in VB code... When I place this line in the property window for the combo box it will return the error which say it can't find the macro...

Weird? ;-)

 

[KenHigg]

Not weird. It should be in the vba window and should give a macro error if it was directly in the property window...

So what do you have in the vba window?

This:

Me!city_id = Me!lokatie.col(1)

Or this:

Me!city_id = Null

???

If this is what you have:
Me!city_id = Me!lokatie.col(1)

Then you may have to account for possiblity of a null value being returned. In which case you could try something like:

Me!city_id = Me!lokatie.col(1) & ""

 

[tstolk]

Hi Ken, I'm back again...

It still doesn't work. When I choose a location, again there error 438 comes up. Then you can start a VB problem resolving (I don't know if the translation is correct). It opens VB editor and a yellow line appears over the incorrect line. When I hover over the yellow line, it says Me!city_id = Null.
Even when I add & "" to the line...

Any idea again?
Thnx!