Create Accessible Variable in Query
- Thread starter The Stoat
- Start date
- Local time
- Today, 17:12
- Joined
- Feb 28, 2001
- Messages
- 30,562
It is possible to define a control's recordsource as a particular field in a query, though if the whole object (form, report) isn't bound to the same query it might get a little bit trickier.
But your choice of nomenclature worries me. We've had a similar question recently. If you mean "variable" in the sense of "a data item used for storage in the context of a module" then the answer is NO. Variables (in that sense of the word) are not members of a collection so you can't get at them except in the place that defined them. Queries and variables (in the "module-resident" usage) don't go together. They have no common point of overlap, if that helps to understand the problem.
If you mean "variable" in a more generic sense as "any place you can put data" then a query to a trash table is possible, just watch out for whether that query feeds the whole form/report or only part thereof. Forms and reports don't work as well when they have "split" recordsources.
But your choice of nomenclature worries me. We've had a similar question recently. If you mean "variable" in the sense of "a data item used for storage in the context of a module" then the answer is NO. Variables (in that sense of the word) are not members of a collection so you can't get at them except in the place that defined them. Queries and variables (in the "module-resident" usage) don't go together. They have no common point of overlap, if that helps to understand the problem.
If you mean "variable" in a more generic sense as "any place you can put data" then a query to a trash table is possible, just watch out for whether that query feeds the whole form/report or only part thereof. Forms and reports don't work as well when they have "split" recordsources.
The Stoat
The Grim Squeaker
- Local time
- Today, 23:12
- Joined
- May 26, 2004
- Messages
- 239
Hi Doc,
What i am trying to do is allow an update of selected records chosen by the user. The scenario is a line manager leaves the department and the staff need to be reassigned to a new manager or indeed managers. I have a popup continuous form that displays all the member of staff under the line manager. I then want to allow the user to select a new line manager from a drop down list and then select the staff to update by selecting a check box next to their record. This way i can reassign large numbers of staff quickly.
In order to do this i have created a make table query with the staffID field and a variable- a field in a query that does not belong to an exisiting table in this case -
I have then linked this in a new query to the staff details table using the staffID fields. This has given me a query that has a field that i can then bind my check box to.
When the user clicks the update button i am able to clone the recordset and update the linemanager field for those records that have a check against them.
Basicially i was wondering if i could avoid the make table step. I seem unable to create an extra field in the query that i can then bind the check box to and change the value of with the control unless it exists in a table first.
I've posted the popup form and the two queries just to give you some idea of what i am doing.
The person in the combobox is the new line manager i have selected for these staff members. The check box to the right of replace allows the user to select or deselect all of the staff members. When the cmdbutton is clicked any staff members who have not be reassigned will remain visible the others will be cleared from the form.
If it can't be done then fair enough, i just don't like creating ghost tables.
Thanks for any suggestions.
TS
What i am trying to do is allow an update of selected records chosen by the user. The scenario is a line manager leaves the department and the staff need to be reassigned to a new manager or indeed managers. I have a popup continuous form that displays all the member of staff under the line manager. I then want to allow the user to select a new line manager from a drop down list and then select the staff to update by selecting a check box next to their record. This way i can reassign large numbers of staff quickly.
In order to do this i have created a make table query with the staffID field and a variable- a field in a query that does not belong to an exisiting table in this case -
I have then linked this in a new query to the staff details table using the staffID fields. This has given me a query that has a field that i can then bind my check box to.
When the user clicks the update button i am able to clone the recordset and update the linemanager field for those records that have a check against them.
Basicially i was wondering if i could avoid the make table step. I seem unable to create an extra field in the query that i can then bind the check box to and change the value of with the control unless it exists in a table first.
I've posted the popup form and the two queries just to give you some idea of what i am doing.
The person in the combobox is the new line manager i have selected for these staff members. The check box to the right of replace allows the user to select or deselect all of the staff members. When the cmdbutton is clicked any staff members who have not be reassigned will remain visible the others will be cleared from the form.
If it can't be done then fair enough, i just don't like creating ghost tables.
Thanks for any suggestions.
TS
Attachments
Last edited:
Similar threads
