I have a database that uses four forms and each form has it's own table and each form has it's own search button to find a specific record within that table. I would like to use one search button that will look at all four tables and bring up the correct record when the request number is entered. Can I link all the tables to perform this or is code required? Can someone send me in the right direction for how to do this? Give me an example of code if it's required?
Thanks for any help I can get.

Laura Edmark

you may be able to get what you want with a union query but the best solution is to keep the data in a single table. That way you can use one form and save yourself a lot of work. You'll probably need to add a column to your table so you can identify which "set" a record belongs to. You would filter on that "set" value to get the current results with a fraction of the work.

I am in the process of rebuilding the database so that I do only have one form and one search function. There are four different types of actions and each action requires different fields - that's why I started out with four tables. I'd like to make the new form function in a way that when a specific action (Request for Change) is selected it will grey out or make unavailable those fields that are not required for that action and make available the required fields. I don't want people filling out more than is required and if I don't block out the unrequired fields they will. Would that take a huge amount of code? It would have to be code associated with that control. If I can do this I can build it quickly.

Thank you for your quick answer and help.

Laura Edmark



you may be able to get what you want with a union query but the best solution is to keep the data in a single table. That way you can use one form and save yourself a lot of work. You'll probably need to add a column to your table so you can identify which "set" a record belongs to. You would filter on that "set" value to get the current results with a fraction of the work.

I don't want people filling out more than is required and if I don't block out the unrequired fields they will. Would that take a huge amount of code? It would have to be code associated with that control.So, you're talking about disabling controls on a forms that are bound to table fields. Just by reading the last post of yours, and by seeing Pat's answer to your initial response, I would say that you might have to give us a picture of the current tables and form(s) you are working with. My first guess at an answer for you would be some (recordset) code, if a Union Query does not suit this situation.

And BTW, how is Portland? I hear it rains there all the time. ;) You know, one of the "bosses" here on the forum is from Portland.

Welcome to the board... :)

Hello there - yes I need to disable controls on a form. I'm attaching a copy of the database since it's so small so you can look at the form. When you click on the down arrow on the first control of the form it gives you the four different types of actions. Depending on which one the user chooses certain fields are required and others are not. If someone chooses Recruitment Action then based on that choice all the fields not required would then be disabled so the user can't fill out the wrong information.

Portland has been teasingly springish and now it's raining! But still the best place ever :)

Thanks again for all your help.

Laura

I tried to attach the database but it's too big so I'll try just a couple pictures. - Picture of table attached somewhere!




QUOTE=ajetrumpet;681702]So, you're talking about disabling controls on a forms that are bound to table fields. Just by reading the last post of yours, and by seeing Pat's answer to your initial response, I would say that you might have to give us a picture of the current tables and form(s) you are working with. My first guess at an answer for you would be some (recordset) code, if a Union Query does not suit this situation.

And BTW, how is Portland? I hear it rains there all the time. ;) You know, one of the "bosses" here on the forum is from Portland.

Welcome to the board... :)[/QUOTE]

Hello there - yes I need to disable controls on a form. When you click on the down arrow on the first control of the form it gives you the four different types of actions. Depending on which one the user chooses certain fields are required and others are not. If someone chooses Recruitment Action then based on that choice all the fields not required would then be disabled so the user can't fill out the wrong information.I see.

Well, you're probably going to have to do this manually, unless you have some sort of naming system set up for your controls (which, for something like this you should have) so you can write a "controls collection" loop instead of writing 30 one-liners.

Take a look at the attachment. It creates financing schedules based on your inputs. It is just a sloppy example, but the code behind the button called "Compare" is what you're looking for. Notice the naming pattern I have set up. I used the LIKE keyword, combined with the asterik (*) to loop through the form, basing the visibility off the number that is attached to each control name.

That would be the only way you could accomplish this (that I know of, anyway).

And BTW, if you want to see an schedule, you have to input your data into the first column of controls. :)

Thank you for the information - I'll look much closer at it tomorrow as my mother is in the hospital and I haven't had a chance to really look.

Laura


I see.

Well, you're probably going to have to do this manually, unless you have some sort of naming system set up for your controls (which, for something like this you should have) so you can write a "controls collection" loop instead of writing 30 one-liners.

Take a look at the attachment. It creates financing schedules based on your inputs. It is just a sloppy example, but the code behind the button called "Compare" is what you're looking for. Notice the naming pattern I have set up. I used the LIKE keyword, combined with the asterik (*) to loop through the form, basing the visibility off the number that is attached to each control name.

That would be the only way you could accomplish this (that I know of, anyway).

And BTW, if you want to see an schedule, you have to input your data into the first column of controls. :)

Let me know how it works out.

Good luck with the family situation...