Checkbox on a query-based form

[Bellyjeans]

Hi all,

I'm pulling my hair out trying to wrap my head around something that
I'm sure that there's a solution to. My database has a warning form
that's based on a query. The form pops up if a datediff function in
the query pulls a record that's been there for seven or more days.
This form is meant to remind the user to check these records when they
open the database and deal with them.

What I need to do is have a check box beside each record that lets
them confirm that they've dealt with the file... I presume that the
contents of the check box need to be stored somewhere, but I just
can't think of a way to do it. I'm sure that it could be done: if any
of you lovely Access people out there have any advice, I might just
love ya forever!

Thanks!

 

[Ryan]

In the table that the query is based on, add a yes/no field and name it
something like DealtWith. Add the DealtWith field to your query and set the
criteria to False. Now your query will filter out all the datediff and
DealtWith fields that are False and when the form opens and the user checks
the checkbox that record will no longer show up in the query.

 

[Bellyjeans]

Hi Ryan,

Thanks for your speedy response. What should I be setting the control
source of the check box to... the field in the table or the query?
I've tried both and I can't seem to check on the boxes (they're grey).

Cheers

 

[Ryan]

Did you add the Yes/No field in your table? If so then you need add the new
field to your query. Then if you go to your form in design view, there is a
button on the menu bar called field list. Open the field list and if your
form is truely based on the query, and you correctly added the yes/no field
to your query, then it will be an available field you can drag onto your
form. It knows that it is a yes/no field and puts a checkbox on the form.

 

[Bellyjeans]

Hmmm... I added the Yes/No field in my table and dragged it from the
field list, but I still can't make any selection. Hmmmm....

 

[Ryan]

So the Yes/No field is in the table, in the query, and was available on the
field list of the form, but you can not click it? Can you click it in the
table? Are there multiple tables in your query?

 

[Bellyjeans]

Yep, the Yes/No field is in the table, in the query, and I was able to
drag the check box onto the form, but it won't allow me to select
anything. There are two tables in my query.

 

[Ryan]

Without know your table design and how your relationships are set up I cant
fully know the reason why you cant check the Yes/No field. You should be
able to select it in the table. If you can, then one more thing to try is
this.. In your query, right click on the line that shows the relationship
between your two tables. This will bring up Join Properties. Choose the one
that says Select all records from 'the table with the checkbox' and only
those records from 'the table without the checkbox' where the joined fields
are equal. If that does not work, I would need to know the design of your
tables and their relationships to help you fix your problem.

 

[Bellyjeans]

Hi there,

Sorry for the delay in reply; I hope that somebody still sees this
topic.

I have two tables: tblPatientInformation and tblReferralsTracking.
They're linked with a one to many relationship on the PatientID field
(i.e. one patient can have many referrals). On the query are the
following fields:

From tblPatientInformation:
LastName
FirstName
Health Number

From tblReferralTracking:
ReferralForwardedDate
DateOfBooking
A datediff function which calculates the difference between the date
of referral and the date of booking; the criteria is set to more than
7 days

And as per your suggestion, I've added a yes/no field called DealtWith
to my tblReferralTracking. I've added this checkbox to my form, but
I'm not able to check any check boxes.

I hope this helps.

Cheers!

My tables

 

[Ryan]

Try adding your primary key field PatientID to your query. Since that field
will be in both tables you will have to tell the query wich table to use.
Your field will look like this.
PatientID[tblPatientInformation].[PatientID])

 

[Bellyjeans]

Hmm, doesn't seem to be working. The strange thing is that when I run
the query independently from the form based on it, I'm able to check
the DealtWith check box. It just won't let me check it on the form.

 

[Ryan]

That is strange. Maybe try re-creating the form. If the query allows you to
check the box, then the form should as well. I would try to create the form
again.
--
Please remember to mark this post as answered if this solves your problem.

Hmm, doesn't seem to be working. The strange thing is that when I run
the query independently from the form based on it, I'm able to check
the DealtWith check box. It just won't let me check it on the form.

Try adding your primary key field PatientID to your query. Since that field
will be in both tables you will have to tell the query wich table to use.
Your field will look like this.
PatientID[tblPatientInformation].[PatientID])

 

[Bellyjeans]

I've recreated the form and it works like a charm. Thanks, Ryan! If
I could, I'd give ya a cookie!

That is strange.  Maybe try re-creating the form.  If the query allows you to
check the box, then the form should as well.  I would try to create theform
again.
--
Please remember to mark this post as answered if this solves your problem..

Hmm, doesn't seem to be working.  The strange thing is that when I run
the query independently from the form based on it, I'm able to check
the DealtWith check box.  It just won't let me check it on the form.

Try adding your primary key field PatientID to your query.  Since that field
will be in both tables you will have to tell the query wich table to use.  
Your field will look like this.
PatientID[tblPatientInformation].[PatientID])

- Show quoted text -

 

[Ryan]

No problem, instead of a cookie, you could mark the as answered

I've recreated the form and it works like a charm. Thanks, Ryan! If
I could, I'd give ya a cookie!

That is strange. Maybe try re-creating the form. If the query allows you to
check the box, then the form should as well. I would try to create the form
again.
--
Please remember to mark this post as answered if this solves your problem..

Hmm, doesn't seem to be working. The strange thing is that when I run
the query independently from the form based on it, I'm able to check
the DealtWith check box. It just won't let me check it on the form.

Try adding your primary key field PatientID to your query. Since that field
will be in both tables you will have to tell the query wich table to use.
Your field will look like this.
PatientID[tblPatientInformation].[PatientID])

- Show quoted text -