Receiving runtime error

[Roni Sutton]

I have the following code launching on the 'Get Focus' of one of my fields on my form. Basically, the code is to run a query and display a message box indicating whether or not there are records in the query.

Private Sub CustomerLastName_GotFocus()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset ("qryEmployeeExists")

If rs.RecordCount > 0 Then
MsgBox "There are records in the query.
You may continue", vbExclamation
Else
MsgBox "You have not been authorized to
input data. Please see system
administrator immediately.",
vbExclamation
End If

End Sub

When I the code launches at the focus, I get the following run-time error, "Run-Time Error 3061 Too few parameters. 1 Expected."

When I click on Debug, I am put on the "Set rst = dbs.OpenRecordSet("qryEmployeeExists") line. I have checked to ensure that the query exists and that it's name is indeed qryEmployeeExists.

HELP!!

 

[Roni Sutton]

The plot thickens. I substituted another query in the code where 'qryEmployeeExists' is. The code works - both for a query w/records and one without. SO now we are down to the problem being the query itself. The query compares an employee id garnered from the LAN using VB code to employee numbers in a table to see if that employee exists in the table. SO the query is based on the employee table with the criteria being forms![frmPelAdd]![employeenumber]. The form referenced is the one the user is sitting in. Could the conflict be here?

Maybe another route would be to compare the user id retrieved from the LAN to the table before the field is populated. However, I'm not sure how to do that either.

I'm sorry for rambling. This is becoming very confusing.

Thanks to any of you who take the time to read through this mumbo jumbo and attempt to help me.

Roni

 

[chris_dono]

make sure that forms![frmPelAdd]![employeenumber] has a value in it.

 

[RoyVidar]

You will probably have to resolve the parameters (in addition to ensuring it contains a value)

Dim db As dao.Database
Dim rs As dao.Recordset
dim qdf as dao.querydef
dim prm as dao.parameter

Set db = CurrentDb()
set qdf=db.querydefs("qryEmployeeExists")
for each prm in qdf.parameters
prm.value=eval(prm.name)
next prm
Set rs = qdf.OpenRecordset()

 

[Mile-O]

You guys do realise you've answered a question that's almost 6 years old?

 

[RoyVidar]

Yes, now that you pointed it out

 

[matt020876]

Any idea how you this word work with assigning sql commands to a variable?

i.e


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb()
Set qdf = db.createQueryDefs("")
sql = "SELECT qryClaims.currenc, Sum(qryClaims.[Total Invoice Amount]) AS total from qryClaims GROUP BY qryClaims.currency HAVING (((qryClaims.currency)='USD')); "

 

[WillEllis]

You will probably have to resolve the parameters (in addition to ensuring it contains a value)

Dim db As dao.Database
Dim rs As dao.Recordset
dim qdf as dao.querydef
dim prm as dao.parameter

Set db = CurrentDb()
set qdf=db.querydefs("qryEmployeeExists")
for each prm in qdf.parameters
prm.value=eval(prm.name)
next prm
Set rs = qdf.OpenRecordset()

THANK YOU!!!!! This fixed a problem I was having...this is the greatest forum in the whole world!

 

[gillbates21]

query problem

I'm trying to set combo's cboULevel value to the result of the query.
The parameter query itself works.
I'm getting error 3061 (too few parameters. expected 1) with this code:

Private Sub cboUsername_BeforeUpdate(Cancel As Integer)

Dim sql As Recordset
Set sql = CurrentDb.OpenRecordset( _
"qrySelectULevel")

If sql.RecordCount > 0 Then
cboULevel.Value = sql.Fields(0)
Else
cboULevel.Value = 0
End If

End Sub

I'm using access 2000 so I can't use DAO objects.
Any ideas?

 

[johnPillar]

I've just fumbled my way through exactly the same puzzle... what you need is some code like this

Dim trns As Recordset
Dim cbal As Variant
Dim i As Long
Dim db As Database
Dim qdf As QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("get_transactions")
qdf.Parameters(0) = [Forms]![Main_form]![ctl_Accounts_list].[Form]![acc_id]
Set trns = qdf.OpenRecordset()

where you set up the parameter for the query (here I test for a value on a form), then run the query.

Hope this helps

John

 

[gillbates21]

nope,

i get error on Dim db As Database line saying that user-defined type not defined.

I have done all the updates to my access 2000, but somehow i can't use some of its objects. (DAO 3.6 is checked)