change text box to a lookup field (without losing data integrity)

  • Thread starter Thread starter _Bigred
  • Start date Start date
B

_Bigred

Hello All,

(Access 2000)


I have a table that has a field "Agency Number" and would like to change
this to a lookup field, based on a seperate table containing values/records.

this table has MANY forms and reports based off it, and I'm wondering what
the PROPER procedure for changing this text box to a lookup field would be.

I tried once and then a couple of my forms would not display any records
after changing it.

any help with step by step directions on accomplishing this would be
awesome. I don't want to lose any data in my table (except the data that has
already been inputted into the "agency number" text box is not big deal.

TIA,
_Bigred
 
The url was very helpful, so what would you suggest as a solution to my
issue.

I want to be able to have the user select a predetermined value for this
text box, so that I can easily search for results by query to a form and/or
reports. (this would be to avoid inputting a million different
topic/keywords).

any thoughts would be awesome,
_Bigred
 
I want to be able to have the user select a predetermined value for this
text box, so that I can easily search for results by query to a form and/or
reports. (this would be to avoid inputting a million different
topic/keywords).
Click to expand...

If you're having the user search for data in a table datasheet...
don't.

Use a Form instead. You can create an unbound Form with textboxes,
combo boxes, listboxes, any other suitable control, and use those
controls as criteria in a Query; e.g. if the form is named frmCrit and
you have a combo cboDepartment, you could use a criterion of

=Forms![frmCrit]![cboDepartment]

The user would see the department name, but if you have the
DepartmentID as the bound column of the combo, the query would see
that value.

You can then base a Form (for onscreen display) or a Report (for
printing) on this query, and put a button on frmCrit to open the form
or report.
 
Well I really don't want to search using this method. I just want to ensure
that when INPUTTING records this particular field "Agency Topic" is a
uniform set of words that a user can input and no more.

for example:
Topics might be Overtime, Discipline, Pool Coding, Sick Leave.

I don't a user to be able to input something other then those topics.
(ultimately there are probably 30-35 topics I want as default choices) while
inputting these records.

After the user inputs the records - I would be doing some queries to reports
and forms.

is your below answer you gave - still be appropriate or does that change my
strategy?

_Bigred


John Vinson said:
I want to be able to have the user select a predetermined value for this
text box, so that I can easily search for results by query to a form and/or
reports. (this would be to avoid inputting a million different
topic/keywords).
Click to expand...

If you're having the user search for data in a table datasheet...
don't.

Use a Form instead. You can create an unbound Form with textboxes,
combo boxes, listboxes, any other suitable control, and use those
controls as criteria in a Query; e.g. if the form is named frmCrit and
you have a combo cboDepartment, you could use a criterion of

=Forms![frmCrit]![cboDepartment]

The user would see the department name, but if you have the
DepartmentID as the bound column of the combo, the query would see
that value.

You can then base a Form (for onscreen display) or a Report (for
printing) on this query, and put a button on frmCrit to open the form
or report.
Click to expand...
 
Well I really don't want to search using this method. I just want to ensure
that when INPUTTING records this particular field "Agency Topic" is a
uniform set of words that a user can input and no more.
Click to expand...

Well, this is a perfectly standard and usual Lookup capability. Use
it, by all means, by putting a Combo Box on a Form, and letting the
user do their data entry on that Form. It is NOT NECESSARY to use the
"Table Lookup" wizard to do this. I'll admit it makes it about two
keystrokes quicker to add a combo box to a Form, if you use the Lookup
datatype in your table - but it comes with a whole lot of
disadvantages to go with that rather trivial advantage!

Again... users should, in general, never even SEE a table datasheet;
they certainly should not be obliged to do data entry on a table
datasheet. It's just not the right tool for that purpose, and is much
too limited in its capabilities. Use a Form instead.
 
Back
Top