I have a form that shows all of the clients in the database. I want to add a check box that checked show only active clients and unchecked shows all client.
25  2522 
I have a form that shows all of the clients in the database. I want to add a check box that checked show only active clients and unchecked shows all client.
Put the check box in the footer or header of the form and then put some code in the after_update event of the control -
If mycheckbox then 'is true
-
me.recordsource="Select * from mytable"
-
else
-
me.recordsource="Select * from mytable where active=false"
-
end if
-
It could be rowsource rather than recordsource and if the sql statement is complex, you can set it for the form for both versions and then cut and paste the sql into your code
So I would do this?
Private Sub Check76_AfterUpdate() - If mycheckbox Then 'is true
-
Me.Recordsource = "SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName FROM tblStaff INNER JOIN tblClient ON tblStaff.LastName = tblClient.EmploymentConsultant ORDER BY tblClient.LastName"
-
Else
-
Me.Recordsource= "SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName FROM tblStaff INNER JOIN tblClient ON tblStaff.LastName = tblClient.EmploymentConsultant ORDER BY tblClient.LastName where Active = false"
-
End If
-
End Sub
This is the same query that I used to put my info into the list box. I get an error here - tblClient.LastName where Active = false
So I took out this and I don't get the error - ORDER BY tblClient.LastName
but down at the bottom where it says form view it flashes very quickly "this recordset is not updatable"
So I've been doing some more reading. When I take the code and run a query on the data, I can not update. I have primary keys set, checked everything else that has been suggested. What am I doing wrong?
- Private Sub Check76_AfterUpdate()
-
If mycheckbox Then 'is true
-
Me.RecordSource = "SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName FROM tblStaff INNER JOIN tblClient ON tblStaff.LastName = tblClient.EmploymentConsultant"
-
Else
-
Me.RecordSource = "SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName FROM qryClientEC where tblClient.Active=false"
-
End If
-
End Sub
I had to modify my table, I found that the unupdateable had to do with my table having a lookup value for the Employment Consultants. I took that out, now I can update. The only problem is that now the checkbox does nothing. No errors, no nothing.
- Private Sub Check76_AfterUpdate()
-
If mycheckbox Then 'is true
-
Me.RecordSource = "SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName FROM tblStaff INNER JOIN tblClient ON tblStaff.LastName = tblClient.EmploymentConsultant"
-
Else
-
Me.RecordSource = "SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName FROM qryClientEC where tblClient.Active=false"
-
End If
-
End Sub
I had to modify my table, I found that the unupdateable had to do with my table having a lookup value for the Employment Consultants. I took that out, now I can update. The only problem is that now the checkbox does nothing. No errors, no nothing.
I think you need to add a
me.refresh
after changing the recordsource
I can tell it is refreshing, but still not taking out the records that I want.
You shouldn't actually need the refresh. I think the problem lies in your queries.
The where statement on your second query is wrong. It should be ...
WHERE qryClientEC.Active=false
changed that, still not working. It seems that tish should be simple to do.
Run these two queries on their own as queries and see if the results are what you expect them to be. -
SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName
-
FROM tblStaff INNER JOIN tblClient
-
ON tblStaff.LastName = tblClient.EmploymentConsultant
-
-
SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName
-
FROM qryClientEC
-
WHERE qryClientEC.Active=false
-
The second one worked, except I just need to change false to true, because I want it to show all of the active people. The first one had a data type mismatch
The second one worked, except I just need to change false to true, because I want it to show all of the active people. The first one had a data type mismatch
I accidently left a quotation mark at the end so make sure that is removed.
Otherwise your problem is here
ON tblStaff.LastName = tblClient.EmploymentConsultant
Have LastName and EmployementConsultant got the same data type?
I changed the tblStaff.LastName to tblStaff.ID. I had forgot I changed this datatype when the query was locked for editing.
Now I have what I want in the two SQL statements. 100 records for all and 93 for active (True)
Am I putting this under the right place? I put it under Check76 AfterUpdate. I am running this on List89
I copied and pasted the SQL and still nothing.
Private Sub Check76_AfterUpdate()
If mycheckbox Then 'is true
Me.RecordSource = "SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName FROM tblStaff INNER JOIN tblClient ON tblStaff.LastName = tblClient.EmploymentConsultant"
Else
Me.RecordSource = "SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName FROM qryClientEC where tblClient.Active=False"
End If
End Sub
You have extra fields in the second query. These have to match up to the controls on the form.
Manually set your form to the second query and see what happens.
That worked. I have 93 records that all say active. Is there an easier way to run the second query to retriee all records?
I just realised why this wasn't working. You refer to mycheckbox but your check box is actually called Check76. It should work now. -
Private Sub Check76_AfterUpdate()
-
If Me!Check76 Then 'is true
-
Me.RecordSource = "SELECT tblClient.ID, tblClient.LastName, tblClient.FirstName, tblClient.Active, tblStaff.LastName, tblStaff.FirstName FROM tblStaff INNER JOIN tblClient ON tblStaff.LastName = tblClient.EmploymentConsultant"
-
Else
-
Me.RecordSource = "SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName FROM qryClientEC where tblClient.Active=False"
-
End If
-
End Sub
I still didn't get this to work. Imbetween responses, I made buttons that ran the two queris. I set the query with active for when it opened and run the query with all of the clients when you push the button. This will work until I figure out why the checkbox doesn't work.
I still didn't get this to work. Imbetween responses, I made buttons that ran the two queris. I set the query with active for when it opened and run the query with all of the clients when you push the button. This will work until I figure out why the checkbox doesn't work.
Did you make the change in post #18
yes I did. Should it be me.list76 instead of me.recordsource?
Here is what I have. I had to change the first sql because of the datatype mismatch, but the query itself works. -
Private Sub Check76_AfterUpdate()
-
If Me!Check76 Then 'is true
-
Me.RecordSource = "SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName FROM qryClientEC ORDER BY qryClientEC.tblClient_LastName"
-
Else
-
Me.RecordSource = "SELECT qryClientEC.tblClient_ID, qryClientEC.tblClient_LastName, qryClientEC.tblClient_FirstName, qryClientEC.Active, qryClientEC.tblStaff_ID, qryClientEC.tblStaff_LastName, qryClientEC.tblStaff_FirstName FROM qryClientEC where tblClient.Active=False"
-
End If
-
End Sub
-
yes I did. Should it be me.list76 instead of me.recordsource?
Are you populating a form or a list?
To populate a form it is
Me.RecordSource
to populate a list it is
Me.list76.RowSource
I was trying just me.list89. When I did rowsource it worked.
Nicole will like this addition because usually she only has to look at certain records, but sometimes she needs to see all of them. Thank you so much and have a nice weekend
Are you populating a form or a list?
To populate a form it is
Me.RecordSource
to populate a list it is
Me.list76.RowSource
Did you get my last post? Not sure as we posted at the same time.
I was trying just me.list89. When I did rowsource it worked.
Nicole will like this addition because usually she only has to look at certain records, but sometimes she needs to see all of them. Thank you so much and have a nice weekend
You're welcome.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kathy Krizl |
last post by:
I'm probably doing something stupid, but I have a make table query.
One of the tables I reference has some check box fields in it. Their
Data Type is Yes/No, their field property format is Yes/No,...
|
by: Marcus |
last post by:
I have the following code:
Private Sub cmdDatasheet_Click()
DoCmd.OpenQuery "qryCourse_Details"
End Sub
I would like to check to make sure that this above query has zero rows,
then show a...
|
by: scprosportsman |
last post by:
Please help guys, i am trying to set up a database here at work and im
fairly new to access in terms of writing functions and queries and
stuff. I have 2 different places on my design that will...
|
by: dad |
last post by:
I am building a database to track the maintenance records on a fleet
of cars. I need to run a report on that will display data based on
whether a box is checked or not (i.e. all cars that have the...
|
by: ferg |
last post by:
I have a Customer table. The table has two different CHECK constraints.
Then there is the Customer details dialog, which provides the user with
an UI for changing users. I have some UPDATE sql,...
|
by: Alan Little |
last post by:
I have affiliates submitting batches of anywhere from 10 to several
hundred orders. Each order in the batch must include an order ID,
originated by the affiliate, which must be unique across all...
|
by: jimc52 |
last post by:
Hello Everyone: I am hoping one of the gurus here will give me some help.
I have designed a form with some check boxes. I put the checkboxes there on the form so later I could use them as flags on...
|
by: Euge |
last post by:
Hi,
I really hope someone will be able to help me with this one as I am sure im just missing something simple.
I have an unbound form which has 20 yes/no unbound check boxes. The purpose of...
|
by: Andrew Meador |
last post by:
I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query...
|
by: happyse27 |
last post by:
Hi All,
I modified the user registration script, but not sure how to make it check for each variable in terms of preventing junk registration and invalid characters?
Two codes below :
a)...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
