Running a query in VBA

[Fear Naught]

Hi folks.

I have tried to find the answer in these forums to no avail.

I have a database that staff use to record their business journies, fuel bought etc, etc. On the form for inputting fuel bought users have to input the mileage on the car when they re-fuelled. What I want to show is the mileage recorded on the previous fill so that I can calculate fuel consumption.

I have a query that does that but I need to be able to run that query in a function to return the value to a text box on my form. I have tried what is my usual method, that is to say running the SQL in VBA but I get a NULL result. As I know the stored query works I tried that but got and error saying "expected 1"

The code for both examples is below

Code for embedded SQL

Public Function funcPrevMile()

Dim strSQL As String
Dim Rs As DAO.Recordset

strSQL = "select max(mileage) as mMile from tblfuel F where f.date <  " & Forms!frmFuel!Date & "   and f.usernameid = getuserid() and f.vehicleid = getvehicleid()"

Set Rs = CurrentDb.OpenRecordset(strSQL)

funcPrevMile = Rs!mMile

Rs.Close
Set Rs = Nothing
End Function

Code for trying to run a stored query

Public Function funcPrevMile()
Dim qryMax As DAO.QueryDef
Dim Rs As DAO.Recordset
Set qryMax = CurrentDb.QueryDefs("qryMaxMiles")

Set Rs = qryMax.OpenRecordset

funcPrevMile = Rs!mMile

Rs.Close
Set Rs = Nothing
End Function

I do not get syntax errors just no data. If I run the query outside of the code I get the required result.

HELP

EDIT: Yes I know that I have a reserved name as the "date" field in my table. I will work to get rid of that in due course.

 

[ColinEssex]

sometimes its easier to run the query via a macro and then open the macro through the VB code.

Col

 

[Fear Naught]

Colin,

Thanks for that. However, to me that seems to be just putting another layer into the function.

Incidentally how would I open the macro through VB. Also remember I do not want to open the query for viewing just get result of the query to pass back to the form.

Fear Naught!

 

[Karma]

Hi folks.

I have tried to find the answer in these forums to no avail.

I have a database that staff use to record their business journies, fuel bought etc, etc. On the form for inputting fuel bought users have to input the mileage on the car when they re-fuelled. What I want to show is the mileage recorded on the previous fill so that I can calculate fuel consumption.

I have a query that does that but I need to be able to run that query in a function to return the value to a text box on my form. I have tried what is my usual method, that is to say running the SQL in VBA but I get a NULL result. As I know the stored query works I tried that but got and error saying "expected 1"

The code for both examples is below

Code for embedded SQL

Public Function funcPrevMile()

Dim strSQL As String
Dim Rs As DAO.Recordset

strSQL = "select max(mileage) as mMile from tblfuel F where f.date <  " & Forms!frmFuel!Date & "   and f.usernameid = getuserid() and f.vehicleid = getvehicleid()"

Set Rs = CurrentDb.OpenRecordset(strSQL)

funcPrevMile = Rs!mMile

Rs.Close
Set Rs = Nothing
End Function

Code for trying to run a stored query

Public Function funcPrevMile()
Dim qryMax As DAO.QueryDef
Dim Rs As DAO.Recordset
Set qryMax = CurrentDb.QueryDefs("qryMaxMiles")

Set Rs = qryMax.OpenRecordset

funcPrevMile = Rs!mMile

Rs.Close
Set Rs = Nothing
End Function

I do not get syntax errors just no data. If I run the query outside of the code I get the required result.

HELP

EDIT: Yes I know that I have a reserved name as the "date" field in my table. I will work to get rid of that in due course.

strSQL = "select max(mileage) as mMile from tblfuel AS F where f.date < " & Forms!frmFuel!Date & " and f.usernameid = getuserid() and f.vehicleid = getvehicleid()"

I don't know if this is an error you've created when copying the code to the forum but if you want to alias a table you need to put AS before the alias name.

Usually "expected 1" means it's looking for a parameter with the number being the number of parameters it can't find. In this case it may be because the alias is incorrect or because one of your parameters is incorrectly referenced.

Additionally you've used Date as a field name. This is considered bad practice as date is a reserved word - worth looking up - as it has an intrinsic meaning in both SQL and VBA

HTH

K.

 

[Fear Naught]

Thanks for your reply Karma.

To alias a column name you do need to use the "AS" qualifier but not when aliasing a table name. My SQL runs fine as it is in a stored query. Whenin the debugger the date variable " & Forms!frmFuel!Date & " gives me a date. For some reason the recordset part (Rs!mMile) gives me NULL!!

Baffled

 

[Karma]

You should be able to open a recordset based on a stored query in this manner, always works for me.

i.e

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("QRY_ISSUESLOG_SELECT_RESOLVER")

MsgBox rst(0)

Set rst = Nothing
Set db = Nothing

 

[Fear Naught]

I have tried this but get Run-Time error 3061; Too few parameters. Expected 1. Debug shows the faulty line to be:

Set Rs = CurrentDb.OpenRecordset("qryMaxMiles")

There are no parameters set in the query!!

 

[Karma]

Having tried to replicate the error i'm 99.99% certain the problem is the field called date.

I created a table with 1 field called date to which i added 3 dates.

12/12/2006
13/12/2006
14/12/2006

I created a form with a recordsoource to the table a bound textbox also called date.

i created a stored query with this SQL called query4

SELECT tb1.Date
FROM tb1
WHERE (((tb1.Date)<[Forms]![Form1]![DATE]));

On the Load event of the form i added this.

Private Sub Form_Current()
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo ErrHandle
Set db = CurrentDb

Set rst = db.OpenRecordset("query4")

MsgBox rst(0)

Set rst = Nothing
Set db = Nothing

Exit Sub

ErrHandle:

MsgBox err.Number & " " & err.Description

End Sub

I then opened the form and got the error 3061 too few parameters expected 1

I then moved through the records and got the same error for each record.

Can't say fairer than that.

K.

 

[Fear Naught]

Well I have renamed the field "Date" to "FuelDate" and still get the same error.

The SQL for the query is now:

SELECT Max(Mileage) AS mMile
FROM tblfuel
WHERE (((FuelDate)<Forms!frmFuel!FuelDate) And 
((UserNameID)=getuserid()) And ((VehicleID)=getvehicleid()));

The function that calls is is now

Public Function funcPrevMile()
    
  Dim Rs As DAO.Recordset
  Set Rs = CurrentDb.OpenRecordset("qryMaxMiles")


  funcPrevMile = Nz(Rs!mMile, 0)

  Rs.Close
  Set Rs = Nothing
End Function

Does anybody know what I am doing wrong - this is really baffling me!!

 

[Mr.K]

This may be related to your problem:
http://www.mvps.org/access/queries/qry0013.htm

 

[jc1104]

Is you FuelData field datatype set to DATE/TIME?
If so, the Date/Time must be defined within hash marks, ie #15/8/2006#.

Therefore,

SELECT Max(Mileage) AS mMile
FROM tblfuel
WHERE (((FuelDate)<Forms!frmFuel!FuelDate) And 
((UserNameID)=getuserid()) And ((VehicleID)=getvehicleid()));

would become:

SELECT Max(Mileage) AS mMile
FROM tblfuel
WHERE (((FuelDate)< '#' & Forms!frmFuel!FuelDate & '#' ) And 
((UserNameID)=getuserid()) And ((VehicleID)=getvehicleid()));

If the variables are replaced, it might read:

SELECT Max(Mileage) AS mMile
FROM tblfuel
WHERE (((FuelDate)<#8/15/2006#) And 
((UserNameID)=getuserid()) And ((VehicleID)=getvehicleid()));

Hope this helps.

JC