Basic question: Difference between RecordSet and QueryDef Usage.

[ZaphodBBB]

Hi again

Can I create a recordset (which I find a lot easier to manipulate) from an SQL statement? (instead of a querydef).

I have a subform that has reviews of job (service checks) on it. Each job on the subform (datasheet view) has the date it was done, serial number, customer name, department, customer_id(invisible textbox), job number.

I want to create a recordset that I can use for the basis of reports.

Say I click on a job in the subform I would to pick up the job date and related customer ID and create a recordset based on any other jobs done on that same date and with the same customer ID.

So I need something along these lines:

strSQL = "SELECT JobNumber From tblCheckSheet Where tblCheckSheet.JobDate = " & txtJobDate.Value "AND "tblCheckSheet.CustomerID = " & txtCustomerID.Value

Once the statement is workable can I then do something like this... all Dim of rs and db and qdf etc already done.

Set qdf = db.CreateQueryDef ("",strSQL)
Set rs = qdf.OpenRecordset

Then say I have a report with various text boxes etc, can I just make their recordsource say:

txtAssessment = rs!Assessment

etc etc?


Please excuse my ignorance..I have done zero courses on access and have been pretty much thrown in the deep end to make this database. I have several 1000 - 1500 page books on hand but not the time to read every one from start to end and much of the information they give is either dealing with really basic things like textbox properties etc or delving into SQL server solutions and ADO and the like!

Again many thanks for all your help.

[ZaphodBBB]

Or should I have something like this instead of what I wrote previously...

strSQL = "SELECT JobNumber " & VbCrLf & _
"FROM [tblCheckSheet] " & VbCrLf & _
"WHERE (tblCheckSheet.JobDate ='" & Me.txtJobDate & "') & vbCrLf & _
AND (tblCheckSheet.CustomerID ='" & Me.txtCustomerID &'")
"

Looks like a real mess to me to be honest?

[ZaphodBBB]

No I wont be able to say

txtAssessment = rs!Assessment as there will be a group of records so I'll need to be able to somehow select individual records from the recordset and then dig out the individual fields from each record.

Only 3 hours sleep due to noisy neighbours so please excuse the confusion!

[ZaphodBBB]

I take it the statements that the QueryBuilder creates (in SQL View) cannot just be cut and pasted into the Module of a form?

Or maybe those that dont require any reference to Form Controls can be but those that need to refer to a form need all the ' and " included? I notice that the query builder will build an SQL query that does include reference to say a combobox but it doesn't seem to need the ' and " etc?

Straying a bit off my original topic here...

[NeoPa]

Hi again

Can I create a recordset (which I find a lot easier to manipulate) from an SQL statement? (instead of a querydef).

I have a subform that has reviews of job (service checks) on it. Each job on the subform (datasheet view) has the date it was done, serial number, customer name, department, customer_id(invisible textbox), job number.

I want to create a recordset that I can use for the basis of reports.

Say I click on a job in the subform I would to pick up the job date and related customer ID and create a recordset based on any other jobs done on that same date and with the same customer ID.

So I need something along these lines:

strSQL = "SELECT JobNumber From tblCheckSheet Where tblCheckSheet.JobDate = " & txtJobDate.Value "AND "tblCheckSheet.CustomerID = " & txtCustomerID.Value

Once the statement is workable can I then do something like this... all Dim of rs and db and qdf etc already done.

Set qdf = db.CreateQueryDef ("",strSQL)
Set rs = qdf.OpenRecordset

Then say I have a report with various text boxes etc, can I just make their recordsource say:

txtAssessment = rs!Assessment

etc etc?


Please excuse my ignorance..I have done zero courses on access and have been pretty much thrown in the deep end to make this database. I have several 1000 - 1500 page books on hand but not the time to read every one from start to end and much of the information they give is either dealing with really basic things like textbox properties etc or delving into SQL server solutions and ADO and the like!

Again many thanks for all your help.

Technically you can use SQL in the RecordSource but not a RecordSet object (rs).
You would be better advised here, though, to define it as a QueryDef but pass a WhereCondition to the report in the Open command (DoCmd.OpenReport). This will cause the QueryDef results to be filtered as specified.
I will deal (where I can) with each post separately to avoid confusion.
Remember this when reading the responses as they will be in that context.

[NeoPa]

Or should I have something like this instead of what I wrote previously...

strSQL = "SELECT JobNumber " & VbCrLf & _
"FROM [tblCheckSheet] " & VbCrLf & _
"WHERE (tblCheckSheet.JobDate ='" & Me.txtJobDate & "') & vbCrLf & _
AND (tblCheckSheet.CustomerID ='" & Me.txtCustomerID &'")
"

Looks like a real mess to me to be honest?

See posts in other thread for explanation for why you may (or may not) want to use this format.
Whatever you do choose to use, make sure that the string that's created is valid SQL before trying to use it.
I will include a revised version that assumes that Jobdate is a Date/time field and CustomerID is a numeric field.
I will formulate it such that, should you want it without any VbCrLfs you simply need to remove them (it will work with and without them).

strSQL = "SELECT JobNumber " & VbCrLf & _
         "FROM [tblCheckSheet] " & VbCrLf & _
         "WHERE (([JobDate]=" & Format(Me.txtJobDate,"\#m/d/yyyy\#") & ") " & VbCrLf & _
           "AND (CustomerID =" & Me.txtCustomerID & "))"

See (Literal DateTimes and Their Delimiters (#).) for further details about how to use literal dates in SQL.

[NeoPa]

I take it the statements that the QueryBuilder creates (in SQL View) cannot just be cut and pasted into the Module of a form?

Or maybe those that dont require any reference to Form Controls can be but those that need to refer to a form need all the ' and " included? I notice that the query builder will build an SQL query that does include reference to say a combobox but it doesn't seem to need the ' and " etc?

Straying a bit off my original topic here...

Important that you've noticed this nevertheless.
I'd be interested in seeing a reference to a control (combobox whatever) from the SQL produced by the query builder.
I know of ways to do it but never realised the query builder could do that for you.

Anyway, the important point here is that references to objects or items are not the same as literals. The tricky stuff (at least as far as formatting is concerned) is to handle these items as literals. When passing a reference to the item itself (whether it be a date; number; string or other type of object) these delimiters are not required.
Similarly, using the return value from a function (EG CDate()) in SQL equally needs no delimiters (This throws people over and over again :)).

[ZaphodBBB]

Hi NeoPa

First off congratulations on the 2K posts mark..and thanks so much for all your assistance!!

I have not read your posts to me yet but did notice you ask if I could put up a SQL statement produced by the Query Creator that references controls.

Here is one I made for exactly this question I have asked and it works fine. I searched an old database we have on SQL server in another city and found it and used it as the basis for this query. The textboxes are typed into the Criteria part of the Query Designer.

SELECT DISTINCTROW tblCheckSheet.JobDate, tblCheckSheet.SerialID, tblCheckSheet.Assessment
FROM tblCheckSheet
WHERE (((tblCheckSheet.JobDate)=([Forms]![frmFieldServiceDatabase]![qryReviewJobs subform].[Form]![txtJobDate])) AND ((tblCheckSheet.CustomerID)=([Forms]![frmFieldServiceDatabase]![qryReviewJobs subform].[Form]![txtCustomerID])));

When I typed the criteria I had to play about for a while to get it to work but finally I basically used the
style you or Mary gave me some time back. i.e.
take out all the brackets etc and the syntax of Forms!frmFieldServiceDataBase!qryReviewJobs subform.Form!txtCustomerID....I would not have had a clue how to make that with the weird ...subform.Form!....etc in it.

But it works exactly right..gives me however many instruments were checked on a given day at a given hospital.

Now I'll go read all the posts to both of my questions.

Thanks again

[NeoPa]

From your comments I gather that these references are actually created by you then, rather than by the Query Builder automatically.
That makes sense.

Bear in mind this (little) caveat when dealing with form controls as elements in SQL :
They can work, but will cause problems if the form referred to is not open at the time referenced. This may seem obvious, but is certainly important to understand.
Personally, I avoid using that concept for that reason, but that's not to say that I would necessarily recommend others avoid it too. You choose whatever suits you best.

[ZaphodBBB]

Thanks again

To be honest I had not thought about the form being open but as you say, once you think about it the form would have to be open...otherwise where do the values come from?

I guess one advantage of using the strSQL method is that you could store the relevant values into variables and use those as parameters of the query...so wouldn't matter weather the form is open or not.

Time to get out of here, the sun is shining for a change and in theory I'm on holiday...what am I doing at work!!

[NeoPa]

There's a rare chap who has his priorities right :)
Grats on the full member status Zaphod.