Using a form to select data from a query using SQL

[swills]

I am trying to use a form to select certain criteria and amend a query depending on those selections. The form is shown when my access database first starts up and i have 3 criteria:
Date
Database
Release

These are then used to query a table and amend the query appropriately. Using various sources on the internet i have produced the following code in VBA. However i seem to be getting a type mismatch. The code is as follows:

Dim db As DAO.Database
Dim qdf As DAO.QueryDefs
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMemAll")
strSQL = "SELECT tblDetails.* " & _
"FROM tblDetails " & _
"WHERE tblDetails.Run Date='" & Me.cboDate.Value & "' " & _
"AND tblDetails.Database Name='" & Me.cboDB.Value & "' " & _
"AND tblDetails.Release='" & Me.cboRelease.Value & "' "

qdf.SQL = strSQL


DoCmd.OpenQuery "qryMemAll"
DoCmd.Close acForm, Me.Name

Set qdf = Nothing
Set db = Nothing

Also it doesnt seem to like the qdf.SQL statement. Any help will be greatly appreciated. thanks in advance.

Swills

 

[Colby]

In your strSQL you are querying Run Date using a text field; if your cboDate is an actual date that could do it.

 

[swills]

Ahh, i can see where youre coming from here, however in design view the the run date is actually a text field. The default value is merely set to =date()

Would this cause a problem?
cheers for your input

 

[Colby]

the =date() returns a true date according to the system, I would throw a couple of # in there and see what happens.
Post back and let me know.

 

[swills]

thanks colby, i no longer get the mismatch error, i just put some numbers in instead of the date and it seems to work. thanks again

My main problem now is the fact that it errors when it reaches a certain line in teh code:
qdf.SQL = strSQL

The error i get is:
compile error
method or data member not found

the .SQL part of the code is highlighted after this error.
In fact after i type qdf. there isnt even an option for SQL.

Any ideas what this could be? thanks again

 

[Colby]

if you take your sql string and run it in Access as a normal qry will it work?
this has bitten me more than I care to count - you try to fix the code when
it is actually the query. Give it a shot and let me know

 

[swills]

ok i think im just being completely stupid now. how do i need to change my strSQL to make it work in a query in SQL view?

cheers again

 

[Colby]

no bigee'
copy and paste your string into a sql view inside of Access.
remove your special vba steps and input actual reference or names
SELECT tblDetails.*
FROM tblDetails
WHERE tblDetails.Run Date='2006-01-01'
AND tblDetails.Database Name='your name'
AND tblDetails.Release='your release number'

If it runs then we will need to look again

let me know.

 

[swills]

ok i entered the code you posted, changing the details where applicable. i get the following error:

syntax error (missing operator) in query expression
tblDetails.Run Date='13'
AND tblDetails.Database Name='SP341'
AND tblDetails.Release='3.4.2'

any ideas?

 

[swills]

hi again, i managed to sort that, i simply put [] round the field names in SQL view.
So now this query works when used as a query alone.

just a case of getting it working in VBA now.

any ideas?

cheers for help so far

 

[swills]

just to confirm i am back to the following problem as detailed above:

My main problem now is the fact that it errors when it reaches a certain line in teh code:
qdf.SQL = strSQL

The error i get is:
compile error
method or data member not found

the .SQL part of the code is highlighted after this error.
In fact after i type qdf. there isnt even an option for SQL.

cheers

 

[Colby]

now take the working sql and paste it in the strSQL
code.

 

[swills]

ok ive done that but now im getting a type mismatch again, to stop getting the type mismatch i have to remove the ("qryMemAll") from the following line of code:
Set qdf = db.QueryDefs("qryMemAll")

the code now reads:

Dim db As DAO.Database
Dim qdf As DAO.QueryDefs
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMemAll")
strSQL = "SELECT tblDetails.* " & _
"FROM tblDetails " & _
"WHERE tblDetails.[Run Date]='13' " & _
"And tblDetails.[Database Name]='SP341' " & _
"And tblDetails.Release='3.4.2'; "

qdf.SQL = strSQL


DoCmd.OpenQuery "qryMemAll"

Set qdf = Nothing
Set db = Nothing

sorry to keep bothering you

 

[Colby]

can you post the db taking any proprietary info out?

 

[swills]

Ok the db is attached, no important info is attached as teh data is made up while i am setting the db up

cheers colby, your help is much appreciated
i finish work in 10 mins so i will get your reply 2moro

 

[Colby]

check out the attached, I was a little confused on what you are actually trying to do - but here is one stab at it.

Let me know if I can do anything else.

 

[swills]

Hi colby, thanks again for your help. i think we are nearly there, if you are ok to continue helping me i will give you a bit more background to the reason why i am doing this.

Basically the database is a repository from an automated test program. The data you see is automaticallly placed in there by the test program without any validation. The reason for the form is to query the results and be able to create a report from the queried results.

Your code certainly seems to be working better than mine. However when i run the code my query results are just deleted and i am left with a blank query.

Am i going about this the wrong way completely? should i be querying the table and not the query?

thanks for your help

 

[Colby]

Swills, no problem on helping; I am with you now until this gets done. When I looked at your db I noticed you were setting the query def to your crosstab which was not dependent on any of the selection made on the form. This is why I changed it around to use the qryTest.
What are you wanting to be displayed? Do you need the results of qryTest to become an Access report able to be viewed and emailed out?

 

[swills]

Cheers colby, your help is much appreciated.

basically i created the crosstab query to query the results of tblmem and tbdetails and output them in a way that would allow me to create a chart on a report. i had this all working fine with no problems. my manager then requested that i set up a form so that when the db starts you can select certain criteria from the table and then output this as a crosstab query like qryMemAll.

I think i have been coming at this from the wrong angle, i have been trying to query the query which is causing problems, should i just be querying the table and outputting the results in a crosstab query?

if so, what code would do this?

Also on a side not do you have any idea how to stop my current crosstab query (qryMemAll) from sorting the fields alphabetically?

cheers

 

[Colby]

Okay, so here is what I am hearing...

User makes the 3 selections on the main form
Then the crosstab runs to display the recordset created by those selections
Display these results (crosstab) in report type format or export to excel.

How close am I? Let me know.