Setting a Forms Record Source in code based on user selection

[KennyUK]

Hi Jim,

Thank you very much for taking the time to read this, I really appreciate it. Forgive me if this seems to be very long letter but Id rather you has all the facts so that I do not waste your time.

All I wanna do is this: I have a data entry 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. (this all works great)

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

What I would like is when a user clicks button 1 of the option group, the forms Record source is set to something like &quot;CD-[C] *&quot; and the input mask of a text box called txtCatNo on the form gets set to >&quot;CD-C &quot;<0CCC;0;_ But when a user clicks on button 2 the forms record source is set to &quot;CD-[A] *&quot; and the input mask of a text box called txtCatNo on the form gets set to >&quot;CD-A&quot;<0CCC;0;_ and so on and so forth.

I have tried doing this using a conditional macro that sets the Record source of the main form, but it sets it once and then wont change it to anything else no matter what button you press. I have also tried a Module on the frames &quot;after update&quot; event that looks at the Frame values and sets the record source value according to the value of the frame but no luck either
Sounds and looks easy on paper but do you think I can get it to work in access - I openly admit that I am not yet skilled enough to do this on my
own but I learn best from example which is why I am asking for your kind help.

What do you think - any suggestions would be great.

Thank you in advance

 

[ToeShot]

This may not be an exact solution that you are looking for but in this situation if I understand it correctly maybe you can do something like this.

create an unbound text box on your form. and mark it as invisible or visible false
write a procedure that when it receives an identifier it opens the query with a specific criteria. then on the radio buttons after update event call that procedure passing the value of the button clicked. Determine the button click in the called procedure then assign that value to the unbound text box. Then open the query with the parameter assigned to the unbound text box.

example:
form has text box 1 as an unbound text box named text1 and is not visble

you have four radio buttons

1) CD-A
2) CD-B
3) CD-C
4) CD-D

on each one of the after update event of each button you would call a procedure for example DetermineFilter(intValue as Integer). so your call would look like this:

DetermineFilter(1) <-- if radio button 1 was pushed
DetermineFilter(2) <-- if radio button 2 was pushed
DetermineFilter(3) <-- if radio button 3 was pushed
DetermineFilter(4) <-- if radio button 4 was pushed

then your procedure would do this

DetermineFilter(intValue as Integer)

Select Case intvalue

Case 1
text1 = &quot;CD-A&quot;
Me.Requery
Exit Sub

Case 2
text1 = &quot;CD-B&quot;
Me.Requery
Exit Sub

Case 3
text1 = &quot;CD-C&quot;
Me.Requery
Exit Sub

Case 4
text1 = &quot;CD-D&quot;
Me.Requery
Exit Sub

End Select
End Sub

your query would have a criteria under the Catalog Number like this

Like [Forms]![frmMainDataEntry]![text1]

I am assuming you will change your record source to a query with all the information required.

Specail Notes: This proccess will slow your database app down. If you do not have many users using it or not a lot of records then the sacrafice may be worth it. Also you may need to make a second form to assign to assign the text one to the remains hidden and open at all times. with a default value of &quot;CD-A&quot; . You may Also on your main forms on curent event check to see which button is check then call the procedure. Like I said this may not be the best way but it will work and give you time to find a better solution to your problem.