How to set-up a "prompt" in Access 1

[sgreenwood]

While I am "fairly" adept at using Excel, I'm reletively new to Access. I believe what I am trying to do shouldn't be too difficult, but I can't seem to figure out how to do it!

I'm working with an Access database and I want to be able to set it up so that the user will be prompted to enter a particular value that will then correspond with a particular field in the table. Once entered the user will only see the data rows that match his input.

Let me give you an example of what I'm attempting to do. I work for a farming company and I am building this application for our chemical manager. The table contains the following fields: FIELD,DATE,CROP,CHEM1,AMT1,CHEM2,AMT2,CHEM3,AMT3,etc.

I am attempting to build a prompt so the user can enter the "FIELD" for which he wants to view the history and once entered will only be shown the records for that field. The table contains approx 200,000 records, so I don't want him to have to search through all of them every time he wants to view a particular field.

Is this possible? I'm sure it is..but I'm not having any luck figuring it out!

Any help would be greatly appreciated.

Thanks,
Steve

 

[ETID]

Well ...I see from the structure that your going to have problems...


instead of CHEM1,AMT1,CHEM2,AMT2,CHEM3,AMT3,etc.

you should have a fieds called Chem_type and AMT_Type

then chem1 chem2 etc. will be variables found under Chem_Type...

Your original quest though should be handled at the input form level. but you database structure is way to "spreadsheetesque" at this point... work on relating 2 or more tables to minimize user input and error.

Then we can work on the form.

 

[sgreenwood]

Thanks very much for your response. I understand what you are saying, but the structure can't be changed.

I probably didn't go deep enough into the original explanation. One additional field in each record is "recommendation number". Each record in the table comes from a single numbered "recommendation" form (this form is a government form and cannot be altered.) This form has fields for up to eight chemical entries. The system I am downloading the data from gives me the data by recommendation number. This is done this way so there is a record by date and field as to what chems were used (setting re-entry times into the field...days until harvest allowed etc.) So anyway...to make a long story even longer, that's why the structure can't be changed!

I'm just looking for a way to give the user a prompt that asks what field (ranch) the user wants to see and he can enter "208SOUTH" and see all of the recommendations that relate to ranch 208SOUTH.

I'm sure I've made this as confusing as can be...but I hope someone can figure it out!

Thanks again for your response.

 

[ETID]

Ok,...but are user working directly in table(s), query, form or report to view the data?

 

[sgreenwood]

Actually I can use whatever way would be best. I was thinking probably query, but as I mentioned, I'm not all that familiar with Access. What would be your suggestion as to the best way to accomplish what I'm trying to do?

Again, thanks for you help.

 

[ETID]

The query is the quickest/easiest...but if you don't have a high confidence level in your end users' computer skills then form is best (because you have more control to lock down user actions)

...But as far as a query, you simply need to add a criteria enclosed in brackets to prompt a user for a filter value.

I.E.
To return all records that have a date greater than a user defined date...in the date field

the citeria would be..

>=[Please Enter Starting date]

 

[sgreenwood]

BINGO!

That is exactly what I was looking for. Thank you SO much for your help!