Query in Code not working

[Johnny Bright]

Hi all!

I hope I didn't accidentally post this twice.

Anyway, I get a runtime error 3061 Too Few Parameters. Expected 1 when I
run the following code.

Private Sub sngSun_LostFocus()

Dim mydb As DAO.Database
Dim rs As DAO.Recordset

Set mydb = CurrentDb

Set rs = mydb.OpenRecordset("select sum(SuppTot) as TheSum " _
& "From qrySupport where bytPer = " _
& bytPer, dbOpenSnapshot)

txtPerTot = rs("TheSum")
sngMon.SetFocus
End Sub

The underlying query works fine, but for some reason isn't working in code.
Any suggestions?

Thanks! This group is really helpful!

John

 

[Nikos Yannacopoulos]

John,

Is bytPer the name of a control on the form? If not, then in your code
it's just a variable with no value assigned to it.
If this is not the problem, then maybe bytPer is text? Your code treats
it as numeric. If indeed text, change the rs definition to:

Set rs = mydb.OpenRecordset("select sum(SuppTot) as TheSum " _
& "From qrySupport where bytPer = '" _
& bytPer & "'", dbOpenSnapshot)

Also, you should close the recordset and reset both the recordset and
the database object:

rs.Close
Set rs = Nothing
Set mydb = Nothing

soon as you're done with the recordset, i.e. right after the
txtPerTot = rs("TheSum")
line.


That said, the recordset operation for the purpose is kind of an
overkill, when all you really need is a DSum function:

txtPerTot = DSum("SuppTot", "qrySupport", _
"bytPer = " & Me.bytPer)

if bytPer is numeric, or

txtPerTot = DSum("SuppTot", "qrySupport", _
"bytPer = '" & Me.bytPer & "'")

if bytPer is text.

HTH,
Nikos

 

[Johnny Bright]

Hi Nikos! Thanks, that worked great, mostly! Never thought of DSUM.

bytPer is a numeric field on my form. I put your code in the current event
of the form and it works great! However, when I add a new record, as in:


MsgBox "Use the current period?", vbQuestion + vbYesNo, "Current Period"
If vbYes Then
bytPer = PubPer
Else
bytPer = PubPer + 1
End If

strUserID = PubUserID
bytPer = PubPer
DoCmd.GoToRecord , , acNewRec

I get
Run-time error 3071
Syntax error (Missing operator) in query expression 'bytPer ='

in my form's current event.

What have I done wrong here? Thanks again!

John

 

[Nikos Yannacopoulos]

John,

I can't tell what's wrong, I don't see anything wrong in this snippet
but it's hard to say out of context. Try to debug by:
* running the code line by line (F8),
* watching the variables' values as you do, to see if they are what you
expect
This shoukld help you identify the problem.

By the way, in your code you:
bytPer = PubPer + 1
if not vbYes, and then two lines further down you:
bytPer = PubPer
again, while the value of PubPer hasn't been changed in the meanwhile.
Was that what you intended?

Tip: prefix references to the form controls with the Me. keyword, e.g.
Me.bytPer isntead of bytPer. Not only it makes your code easier to read
(you know which references are form controls right away), it also
potentially resolves ambiguity at VBA level where you use the same name
for the controls on the forms as the table fields they are bound to.
Access has this nasty habit of keeping the same name if you use
autoform, or drag and drop from the recordsource field list in design
view, but this is not recommended practice.

Nikos

 

[fervet]

I have a similar problem... much simpler i hope. i just need to have the
results of a query displayed in the respective textboxes on my form after
entering the search criteria in Text2.

i am getting the error message: 3061 Too Few Parameters. Expected 1

this is the code i have:

Private Sub Text2_AfterUpdate()


Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("tester")
If rst.RecordCount = 0 Then
MsgBox "Not Found"
Else
Me.Text5 = rst![Level 1]
Me.Text7 = rst![Level 2]
Me.Text9 = rst![Level 3]
Me.Text11 = rst![Level 4]
Me.Text13 = rst![Level 5]
Me.Text15 = rst![Level 6]
Me.Text17 = rst![Machining Drawing Number]

End If
rst.Close
Set rst = Nothing

End Sub

tester is the name of my query and to the right of each Text# is the field i
would like to see.
can someone please help me to figure out whats wrong here.

thank you all so much.

 

[Steve Schapel]

Fervet,

I would assume the "tester" query contains something that can't be
evaluated, maybe a reference to a form control. If you need more
explicit help, perhaps you could post baqck with the SQL view of the
query, and also the name of the form.

But anyway, any reason for not simply using a bound form, with the
textboxes directly bound to the fields in the query?

--
Steve Schapel, Microsoft Access MVP

I have a similar problem... much simpler i hope. i just need to have the
results of a query displayed in the respective textboxes on my form after
entering the search criteria in Text2.

i am getting the error message: 3061 Too Few Parameters. Expected 1

this is the code i have:

Private Sub Text2_AfterUpdate()


Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("tester")
If rst.RecordCount = 0 Then
MsgBox "Not Found"
Else
Me.Text5 = rst![Level 1]
Me.Text7 = rst![Level 2]
Me.Text9 = rst![Level 3]
Me.Text11 = rst![Level 4]
Me.Text13 = rst![Level 5]
Me.Text15 = rst![Level 6]
Me.Text17 = rst![Machining Drawing Number]

End If
rst.Close
Set rst = Nothing

End Sub

tester is the name of my query and to the right of each Text# is the field i
would like to see.
can someone please help me to figure out whats wrong here.

thank you all so much.

Hi all!

I hope I didn't accidentally post this twice.

Anyway, I get a runtime error 3061 Too Few Parameters. Expected 1 when I
run the following code.

Private Sub sngSun_LostFocus()

Dim mydb As DAO.Database
Dim rs As DAO.Recordset

Set mydb = CurrentDb

Set rs = mydb.OpenRecordset("select sum(SuppTot) as TheSum " _
& "From qrySupport where bytPer = " _
& bytPer, dbOpenSnapshot)

txtPerTot = rs("TheSum")
sngMon.SetFocus
End Sub

The underlying query works fine, but for some reason isn't working in code.
Any suggestions?

Thanks! This group is really helpful!

John