Text column holds value of ID

[Shaimaa T]

Hi all,

I have a table Actions as follow

Action ID Action
1 cutting
2 sowing
3 ripping
.. ..

At some time , on clicking the button that opens this lookup table I found some of the values of Action equal to Action ID

Action ID Action
1 1
2 sowing
3 3

What are all possible scenarios that would lead to Action column taking value of Action ID?

Many thanks!

 

[namliam]

an update query or manual manipulation of the data in the table.

 

[Shaimaa T]

Yes but there is nowhere in code where we update Action to be ActionID

 

[namliam]

Access wont magicaly do it on its own... unless its coded someplace OR someone is manipulating your data.

 

[CJ_London]

perhaps you are using a lookup field in a table design? This can cause people to enter the wrong information

 

[Shaimaa T]

CJ_London, Can you please explain?

 

[CJ_London]

lookup fields are a feature of access, when you create your table it is an option for many field types - it is effectively a combo box. Having said that, it is a feature which has it's problems and most professional developers don't use them for a number of reasons.

One of the problems is as you describe. The field stores a number but displays text, over time this can be forgotten and you end up populating a number into the text part - if you try the other way round, you will get an error because although a number can be text, text cannot be a number.

 

[spikepl]

on clicking the button that opens this lookup table

You do not make applications where buttons open tables. The users' only access to manipulate data should be forms, otherwise you cannot protect your data properly.

Your "lookup table" would typically be used as row source for a combo box, so user selects the familiar text shown but the records store the bound first column - the ID.

 

[Shaimaa T]

Thanks everyone !

The Actions button opens the Actions table which is available in SQL Server so it is linked in access.

The action ID is a number whereas the Action is text . There is no combobox here

Do you mean that in another form where there is a column named action it will be a combobox having values from actions table hence acting as a lookup? Is that what is meant by look up here?

If so, the combobox displays the action values and some of the values are numbers which means someone modified them in actions table

But it also doesn't make sense that someone modified them to be actionID values in about 3-4 random records

 

[CJ_London]

Do you mean that in another form where there is a column named action it will be a combobox having values from actions table hence acting as a lookup? Is that what is meant by look up here?

yes, but in access you can do the same at the table level for access tables. Didn't realise you were using sql server, which doesn't have lookup fields so my suggestion is irrelevant.