set a forms record source in code based on a users selection 1

[KennyUK]

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

 

[wemeier]

You're on the right track. In the AfterUpdate event of the option group you set the form's recordsource to CD-A or CD-B, etc. After that, you must do a requery:

Me.Requery

This obtains a new set of records bound to the form based on the new recordsource.

You may also have to requery each subform depending on if they are dependent on the catalog.

 

[KennyUK]

Hey Wemeier,

Thank you for your reply, I will certainly try all that you suggested.

Thank you again,

Kenny

 

[jett2000]

Wemeier suggestion may be the best way. Basing any domain changes on a control and then a requery of the control.

Once a recordsource is set it can't be changed, until the next time you enter the form. You must initially set the recordsource equal to &quot;&quot; then based on input from user set the recordsource equal to whatever you want, but you can't change it again until you exit the form, reenter, and get parameters to set it again.

A possible work around would be to write code that would simulate closing and reopening the form each time a different option is chosen. Good luck!