forms based on queries - change values?

[g diddy]

Hi

I have a form (Allocation_Sub_Form) which is based upon a query. This query draws information from a number of tables depending on what invigilators are invigilating particular exams. This form is then used as a subform on another form (Allocation_Form). One of the fields that is retrieved from the query is a check box however when the form is open it is not possible to change the value in this check box. Is there a way that a user can check/uncheck this box? Originally there had just been a button that opened a seperate form however this showed every person rather than just the invigilators for that exam therefore as the number of invigilators has increased dramatically, this has proved to be too inefficient.

Your help is greatly appreciated!

Thanks for your time

[MikeTheBike]

Hi

One question

Can the record containing the checkbox be uniquely identified from the information contained in the sub-form and/or the main form (ie. PK information)?

If the answer is yes the there are two possibilities

1) Use the existing form you have just abandoned, but filtered when opened based on the PK information to return just that record, or, if this is not possible

2) Execute an update query in code on the specific record using the PK information

Does that make sense in this context?


MTB

[g diddy]

thanks for your quick reply mike! unfortunately the checkbox cannot be uniquely identified. i've thought about a number of possible solutions but i keep hitting brick walls. mainly because this subform depends entirely on the query - for each particular exam there are a certain number of invigilators so it would be ideal to have this checkbox appear just for them, but if i don't use the query the only other option appears to keep it as it was with just a button that gave the entire list. my knowledge with access isn't amazing though so i'm hoping there is away round this that i haven't yet heard of!? one of the solutions i had thought about was having an update query that would run when the check box was clicked that would update the record for that invigilator. but as the subform has multiple records on it i'm not sure this will work.
thanks very much for your time!

[MikeTheBike]

Hi

If by

subform has multiple records

you mean it is a continuous form, then the information available (or the value of the fields in the current record) always relates to to selected (current) record. Therefore, if the form RecordSource contains PK info then, when you use a button to open a form, this can be filtered to only show the record for the PK information for the currently selected record on tye sub/main form.

I don't know if that helps, but if you cannot uniquely identify the record to be updated, then you cannot update it (using a form or VBA update query)! If this really is the case then, perhaps, you database structure needs revising!

HTH


MTB

[g diddy]

thank you very much for your advice. i've got it working now. never would have done it without you!