Option Group buttons as query criteria on a form???

[KennyUK]

Hi gang, as an act of desperation after going grey, pulling my hair out and nearly throwing my computer out of the window, I wondered if any one can tell me where I am going wrong. First of all I am using Access 2000.

I have one form called frmMainDataEntry which allows the user to add data about various items and this is stored in various tables. There is also two sub forms which are linked to the main form by relationships so that when a given record is selected in the main form, sub data is shown in the sub forms

The one common link with all the items is the prefix of the catalogue number that I use, in as much as the prefix CD-C refers to one specific item type and CD-A refers to another and so on. Each record in the database is assigned a Catalogue Number according to the item type and the Prefix is followed by a number like so.. CD-C 0001 or CD-A 0001 and so on. The Catalogue number is entered in a text box on the form called TxtCatNo.

I have created a number of toggle buttons as an option group called Frame56 (also on the form) and named each button according to the item type so:
Button1 = CD-C (Value 1)
Button2 = CD-A (value 2)
And so on

So I dont have to have several different data entry forms, one for each item type, I want to use a query that the main form will be based on using the buttons as critera that limits the records shown so that when button1 is pressed, only those records who's catalogue number starts with the prefix CD-C is shown. I would like the text box TxtCatNo to have its input mask set to >&quot;CD-C &quot;<0CCC;0;_ if its a CD-C item or >&quot;CD-A &quot;<0CCC;0;_ if its a CD-A item and so on, to make sure the user only has to enter the numeric portion and this makes sure the correct prefix is used


To sum it up, You open the form and it would be blank (dont know how to do this either), you hit the button CD-C and all records with a CatNo prefix of CD-C are displayed. You can also enter new records and the CatNo box would already have the CD-C prefix as a input mask.
When you have done with CD-C, you would simply hit the Button CD-A and all records prefixed with CD-A are displayed, and so on and so forth.

When you are looking at the form in design view, it seems a easy idea to have but try as i might I just can not get it to all work together.

Any suggestions, is querys the way to go, or modules, any help what so ever would be most gratefully recieved. If it would help, I can send a small sample Database.

Thank you in advance,

Kenny.

PS. Forgive my cross posting but I was not sure where to post this as it involves forms, coding, querys and so on, sorry if I have annoyed anyone.

 

[matzos]

Well, I'll take a stab at this although I may be oversimplifying the problem.

If you are going to use the user selection of a option group, your query (and you should be able to use a query) needs to have a WHERE expression [such as LEFT([CatNo],4)] and a Criteria of iif([Forms]![frmMainDataEntry]![Frame56] = 1,&quot;CD-C&quot;,&quot;CD-A&quot or another iif statement if there are more than two selections.

This will only pull the particular 4 digit prefix based on the value of the option group. You will most likely have to refresh the form after each Click event on the Option Group.

I hope this helps.