Hi dears
there is 8 combo box must be make filter on "RqryAnimalsRepoert"
if i choose one items from one combo box like "DamID"...and leave other "null"
the report must be make filter as "DamID" only and leave the other filter blank
...
please, see the attached file
regards
21 1435
You will need to use VBA to test for the combo boxes to be null to build your filter string. Sorry, I can't open the attachment at work.
i did it ...by this code -
Private Sub btnOpenreport_Click()
-
' cboDamID
-
If IsNull(Me.cboDamID) Then
-
Me.Filter = "DamID=''"
-
Me.FilterOn = True
-
Else
-
Me.Filter = "DamID='[cboDamID]'"
-
Me.FilterOn = True
-
End If
-
-
'cboAnimalStatus
-
If IsNull(Me.cboAnimalStatus) Then
-
Me.Filter = "AnimalStatus=''"
-
Me.FilterOn = True
-
Else
-
Me.Filter = "AnimalStatus='[cboAnimalStatus]'"
-
Me.FilterOn = True
-
-
End If
-
'cboAnimalType
-
If IsNull(Me.cboAnimalType) Then
-
Me.Filter = "AnimalType=''"
-
Me.FilterOn = True
-
Else
-
Me.Filter = "AnimalType='[cboAnimalType]'"
-
Me.FilterOn = True
-
End If
-
-
DoCmd.OpenQuery "RqryAnimalsRepoert"
-
-
End Sub
-
So you got it to work using this code, or this is the code that you tried and it isn't working?
i tried this code ...but its not working
Your current code will only filter on one field because you are replacing the value in the filter each time you set it for the next combo box. Also, the references to your combo boxes were inside the double quotes so literal text cboAnimalStatus (etc.) was getting pass to the filter, not the value selected in the combo box. Try the following: - Private Sub btnOpenreport_Click()
-
Dim strFilter As String
-
-
' cboDamID
-
If Not IsNull(Me.cboDamID) Then
-
strFilter = "DamID='" & [cboDamID] & "'"
-
End If
-
-
'cboAnimalStatus
-
If Not IsNull(Me.cboAnimalStatus) Then
-
If strFilter = "" Then
-
strFilter = "AnimalStatus='" & [cboAnimalStatus] & "'"
-
Else
-
strFilter = strFilter & " And AnimalStatus ='" & cboAnimalStatus & "'"
-
End If
-
End If
-
-
'cboAnimalType
-
If Not IsNull(Me.cboAnimalType) Then
-
If strFilter = "" Then
-
strFilter = "AnimalType='" & [cboAnimalType] & "'"
-
Else
-
strFilter = strFilter & " And AnimalType ='" & cboAnimalType & "'"
-
End If
-
End If
-
-
Debug.Print strFilter
-
-
Me.Filter = strFilter
-
Me.FilterOn = (strFilter <> "")
-
-
DoCmd.OpenQuery "RqryAnimalsRepoert"
-
-
End Sub
Sorry… what is the strcomputer
… i defined strfilter as string
…and no action
Sorry about that. I another project going on in my text editor and I didn't notice the the autocomplete had filled in the wrong thing. I also fixed some other stuff. Try it now.
Until now there is no action , the query still importing all the data… and i made some modification on the code… also no responce…
You are tired with me, brother
Add the following line at line 29:
This will output what the filter will be into the Immediate window. If you don't see the Immediate window, press Ctrl+G in the code editor and it will appear below your code.
DamID='f-000000' and AnimalStatus ='alive' and AnimalType ='cheep'
this is the filter ... it is working as the first time
but if i left any combo box ... the filter will be empty and dont importing any data from the query
regards
Try leaving cboDamID blank and post back the result from the Immediate window.
Also, are you wanting the RqryAnimalsRepoert query to get this filter as well?
if i leaving cboDamID blank ... the Immediate window didnt print any thing
...
Please post your code. I just tested my code and it works no matter what combination of combo boxes I use.
-
Option Compare Database
-
-
-
Private Sub btnOpenreport_Click()
-
Dim strfilter As String
-
-
' cboDamID
-
If Not IsNull(Me.cboDamID) Then
-
strfilter = "DamID='" & [cboDamID] & "'"
-
If strfilter = "" Then
-
strfilter = "AnimalStatus='" & [cboAnimalStatus] & "'"
-
End If
-
End If
-
-
'cboAnimalStatus
-
If Not IsNull(Me.cboAnimalStatus) Then
-
If strfilter = "" Then
-
strfilter = "AnimalStatus='" & [cboAnimalStatus] & "'"
-
Else
-
strfilter = strfilter & " and AnimalStatus ='" & cboAnimalStatus & "'"
-
-
End If
-
End If
-
-
'cboAnimalType
-
-
If Not IsNull(Me.cboAnimalType) Then
-
If strfilter = "" Then
-
strfilter = "AnimalType='" & [cboAnimalType] & "'"
-
Else
-
strfilter = strfilter & " and AnimalType ='" & cboAnimalType & "'"
-
-
End If
-
End If
-
-
Me.Filter = strfilter
-
Me.FilterOn = (strfilter <> "")
-
Debug.Print strfilter
-
DoCmd.OpenQuery "RqryAnimalsRepoert"
-
-
End Sub
-
-
Your If-Then for the cboDamID is messed up. Try re-copying my code into yours.
sir .. still importing all data from the query ...
and the filter print :
AnimalStatus='alive' And AnimalType ='sheep'
note: i left the cboDamID BLANK
regards
Okay, so we have the filter working. Now, we need a way to get that filter to the query. Please post the SQL code for your query. I'm guessing that it might be simpler to just build the query string in code and then run it that way, but I can't say for sure until I see your query.
please, see below - SELECT qAnimals.AnimalID, AnimalsStatus.AnimalStatus, AnimalsTypes.AnimalType, qAnimals.Age, qAnimals.locationID, qAnimals.fixedassets, qAnimals.AnimalSex, qAnimals.SireID, qAnimals.DamID, qAnimals.Ready, qAnimals.ReadyDate
-
FROM AnimalsStatus INNER JOIN (AnimalsTypes INNER JOIN qAnimals ON AnimalsTypes.AnimalTypeID = qAnimals.AnimalTypeID) ON AnimalsStatus.AnimalStatusID = qAnimals.AnimalStatusID;
Please use CODE tags when post code, VBA or SQL.
Add the following between lines 38 and 39: - Dim strSQL As String
-
Dim db As DAO.Database
-
Dim qry As DAO.QueryDef
-
-
strSQL = "SELECT qAnimals.AnimalID, AnimalsStatus.AnimalStatus, " & _
-
"AnimalsTypes.AnimalType, qAnimals.Age, qAnimals.locationID, " & _
-
"qAnimals.fixedassets, qAnimals.AnimalSex, qAnimals.SireID, qAnimals.DamID, " & _
-
"qAnimals.Ready, qAnimals.ReadyDate " & _
-
"FROM AnimalsStatus INNER JOIN (AnimalsTypes INNER JOIN qAnimals ON " & _
-
"AnimalsTypes.AnimalTypeID = qAnimals.AnimalTypeID) ON " & _
-
"AnimalsStatus.AnimalStatusID = qAnimals.AnimalStatusID " & _
-
"WHERE " & strFilter
-
-
Set db = CurrentDb
-
Set qry = db.QueryDefs("RqryAnimalsRepoert") 'Check spelling on this query name
-
-
qry.SQL = strSQL
-
qry.Close
-
-
Set db = Nothing
-
Set qry = Nothing
Note the spelling check on line 15. I copied what you had in post #15, but just double check it.
My Bro..
thanks ..now it is working in my demo Database , i will apply this code in the main project and inform u the final result ...
many many thanks for your support
regards
Glad to help. Good luck on your project.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Xenophobe |
last post by:
I would like to use the same form for adding new records and editing
existing records. New form values are contained in $_POST. Database values
are contained in an associated array. It seems to me...
|
by: Neil |
last post by:
I have a very puzzling situation with a database. It's an Access 2000 mdb
with a SQL 7 back end, with forms bound using ODBC linked tables. At our
remote location (accessed via a T1 line) the time...
|
by: meganrobertson22 |
last post by:
Hi Everyone-
I am trying to use a simple macro to set the value of a combo box on a
form, and I can't get it to work.
I have a macro with 2 actions: OpenForm and SetValue. I can open my
form,...
|
by: Zlatko Matić |
last post by:
There is a form (single form) and a combobox. I want that current record of
the form is adjusted according to selected value in the combobox. Cuurrent
record should be the same as the value in the...
|
by: meganrobertson22 |
last post by:
hi everybody-
what is the best way to add data from one form to another?
i have 2 tables: person and contract. here are some of the fields.
table: person
personid (autonumber and primary...
|
by: motessa |
last post by:
Hello All,
I am new to Access and have been looking for answers to my problem on
the internet and have not found it yet. I hope someone can give me a
hint. Thanks so much.
I have a form...
|
by: Robert |
last post by:
I borrowed one of the forms from the MS Access Solutions database and
altered it to fit my needs. The form was the 'EditProducts' form where you
select a category from a combo which then populates...
|
by: Dave |
last post by:
I want to put the information that the user selects in my combo boxes
into a subform that lies on the same form as the combo boxes.
Thanks for your help already,
Dave
|
by: Gordon Padwick |
last post by:
A form contains controls, one or more of which can be other forms. A form that contains another form is known as a main form. A form contained by a main form is known as a subform. A subform itself...
|
by: cliffschooling |
last post by:
I've used Access for some time but nver had to use any code. Ive a problem getting combo boxes to cascade in a sub form, I've done some searches and the problem appears to be in the identifaction of...
|
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: 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: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
| |