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.

 

[Taff07]

Kenny,

I would probably myself go down the route of some coding behind the on_change activity of your Option boxes.

Using the code below which will change the recordsource of the form and then refresh the form with the changes. Get the specific SQL you require from a query that would normally produce the result you need.

----------------------------

dim StrSql as string
dim CatOption as string

select case me.frame56

case 1
CatOption = &quot;CD-C&quot;
case 2
CatOption = &quot;CD-A&quot;

end select

StrSql = &quot;SELECT tblCats.* FROM tblCats&quot; _
& &quot;WHERE (((tblCats.CAtID)=&quot; & CatOption & &quot)&quot;

' Adjust above to your needs, just a simple example of my own head. By the sounds of it yours will be a bit more complicated.

me.recordsource = StrSql
me.Requery

--------------------------------------

I think that should sort out the Records shown.

For your prefix, one way would be to assign 'CatOption' above, to a textbox, have another box to enter the remaining digits and concatenate them together afterwards.

Not sure exactly what you mean by blank, but assuming you mean absolutely nothing showing but the option box to start. set all the controls to visible = false. Then include within the on_change code. a list to make everything visible. Depending on the number of controls you have this may be a viable option or not.

Hope some of this is of some help to you.

Thanks

Ian

 

[KennyUK]

Hi Ian,

Many many thanks for your kind reply. I have tried all that you have suggested but nothing works ??

Have no idea at all where I am going wrong but thanks for trying.

Kenny

 

[Taff07]

Kenny,

Do you want to send me the sample database, and I`ll see if I can achieve anything for you.

Send it to : Taff07@Yahoo.com

Ian