|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 7th, 2008, 08:36 AM
|
|||
|
|||
|
Selecting Records
Didnt know what to title this one....
In a bit of a mind block here on how I want to tackle this one. What I am looking for is a way to create a query based on Field Name and the Unique Attributes resulting in a count. Say I have Three fields in a table (field1 field2 field3) I want a drop down that shows the three fields WHen the user selects one a query is run populating another drop down with the Unique values from that field. The user then selects a unique value from the second drop down box and it runs another query that gives the user a total count of occurances of that attribute in the db. So I choose "Type" Field In the second dropdown box the unique values for that fields are (Accident, Fire, Robbery) I choose Fire In the textbox the resulting number of fire records shows up There are currently 25 fire related instances in the database The thing is that the user can then change the query and query another field with different attributes.... That make sense.... Just looking for a little guidance on logic here....any help woudl be appreciated....
|
|
#2
May 7th, 2008, 08:48 AM
|
|||
|
|||
|
I think I might be getting somewhere...
I have this: Just trying to figure out how to populate the second dropdown box with the values from the query... Thoughts? Am I on the right path??? ALTHOUGH it appears I again have soem syntax issues with the SQL String...help? Code:
Private Sub Combo89_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim varFieldType As String varFieldType = Combo85 MsgBox varFieldType Dim MySQL As String MySQL = "" MySQL = "SELECT dbo_Core2.'" & varFieldType & "' FROM dbo_Core2 GROUP BY dbo_Core2.'" & varFieldType & "';" MsgBox MySQL End Sub Last edited by Jaykappy : May 7th, 2008 at 08:50 AM.
|
|
#3
May 7th, 2008, 08:48 AM
|
|||
|
|||
|
If you've got your selection criteria form set up (sounds like you're using combo boxes)
All you have to do is build a query that shows all your information then in the criteria section just use things like [forms]![yourformname]![yourfieldname1]
__________________
---------------- If we've helped you and you have solved your problem please post that it's been resolved so we know! The suspense kills me!
|
|
#4
May 7th, 2008, 08:53 AM
|
|||
|
|||
|
Hang on a tic. Are you trying to pick a specific field and build your query based on the field they pick and apply some criteria to that field or are you looking at a static field list and applying criteria to that list?
For instance let's say you have the following table: Tbl_Example Code:
PersonID ServDate Cost 1 1/1/2008 20.00 1 2/1/2008 40.00 2 3/1/2008 20.00 Are you trying to build a mechanism to build a query based on any of the three columns of data and apply a criteria to that column OR are you taking the whole table and trying to apply criteria to one or more of those columns?
|
|
#5
May 7th, 2008, 09:18 AM
|
|||
|
|||
|
PersonID ServDate Cost
1 1/1/2008 20.00 1 2/1/2008 40.00 2 3/1/2008 20.00 First Drop down lists (PersonID, ServDate, Cost) (Hardcoded to the dropdown box Second Dropdown will run a query that grabs the field name and populates the second dropdown with all the unique values When that is choosen a second query will run calculating how many unique instances (records) there are in the database. So I choose ServDate in the 1st dropdown When I click the second Dropdown it will have bee populated with the three dates above After I choose say "3/1/2008" A Textbox will give me a number (1) in this case for the single occurance ServDate of 3/1/2008 results in 1 record This query will allow the user to tally how many of a particular robberies for example are in the database... My above code I think is getting me close...I am taking the Field value from the 1st dropdown...and trying to fit that into a query that will get the unique values....then just need to push them to the 2nd Dropdown box... Last edited by Jaykappy : May 7th, 2008 at 09:20 AM.
|
|
#6
May 7th, 2008, 09:21 AM
|
||||
|
||||
|
I think dlookup is what you're looking for.
The syntax in vba is: YourField = dlookup("[Field2Return]"), "TableOrQueryName", "CriteriaField = " & me.Value) Which does this: Code:
SELECT Field2Return AS Expr1 FROM [TableOrQueryName] WHERE ((([TableOrQueryName].CriteriaField)=me.value)); The criteria is optional.
__________________
Did I help you? If so gimme rep by clicking on the  at the top right corner of this post  Madness does not always howl. Sometimes, it is the quiet voice at the end of the day saying, "Hey, is there room in your head for one more?" Last edited by sbenj69 : May 7th, 2008 at 09:45 AM. Reason: forgot an ampersand
|
|
#8
May 7th, 2008, 09:37 AM
|
|||
|
|||
|
I tried this
MySQL = dlookup("[varFieldType]"), "dbo_Core2", "CriteriaField = " me.Value) Getting an error....is there a "(" missing from right after dlookup....I add it then get another error? What is the Me.Value? THANK YOU VERY MUCH rpeare.... Code:
Dim varFieldType As String
varFieldType = Combo85
Dim MySQL As String
MySQL = dlookup("[varFieldType]", "dbo_Core2",)
Last edited by Jaykappy : May 7th, 2008 at 09:40 AM.
|
|
#10
May 7th, 2008, 09:53 AM
|
|||
|
|||
|
Combo85 is the Field Dropdown that determines the field name to base the query on. I set this to a varaible "varFieldType"
It is saying: Compile Error: Method or Data Member not found AND HIGHLIGHTS Me.Value Code:
Dim varFieldType As String
varFieldType = Combo85
Dim MySQL As String
MySQL = DLookup("[varFieldType]", "dbo_Core2", "[varFieldType] = " & Me.Value)
MsgBox MySQL
|
|
#11
May 7th, 2008, 10:01 AM
|
|||
|
|||
|
Are we on the same page here...dotn know.
The code I am writing is on the Mouse Down of the Second Drop down.... I already choose the Field Name in the first dropdown.... I am getting the name from the Variable varFieldType when I click the second Dropdown Now I am trying to get all the unique values from that Field and place them in the textbox I just clicked. Thanks rpeare
|
|
#12
May 7th, 2008, 12:18 PM
|
|||
|
|||
|
OK I see whats going on here..
but I dont have a Field and value to look for... I want to choose a field (from the 1st dropdown box) and populate a drop down box with ALL the unique values from that field... So if there are 200 unique values in the "Status" field I want to place all those in the second combobox. So I choose Status in the First Box When I click the second combobox I need a query to populate the second box with all these unique values. The the user can select a attribute from that specific field... Then I can run a query to calculate sums etc...
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
