DIM/SET statements (Access 2k)

Topic

New Reply

Code below causes “Object Required” message when from from form button. I suspect that the DIM / SET commands are incorrect.
Object of code is to read a query and send an individual report based on each row of query. I want to loop until EOF is found in query. DO commands were pasted from converted macro to VB

Private Sub Command367_Click()
On Error GoTo Err_Command367_Click
Dim email As Recordset
Set email = db.openrecordset(“select-for-email”, dbopendynaset)
DoCmd.OpenQuery “select-for-email”, acNormal, acEdit
DoCmd.GoToRecord acQuery, “select-for-email”, acFirst
Do While Not email.EOF
DoCmd.GoToControl “lineid”
DoCmd.RunCommand acCmdCopy
SendKeys “^v”, False
SendKeys “{enter}”, False
DoCmd.SendObject acReport, “report-for-email”, “RichTextFormat(*.rtf)”, “chriss”, “”, “”, “Traveler”, “”, False, “”
DoCmd.GoToRecord acQuery, “select-for-email”, acNext

Loop
email.Close
Set email = Nothing

Exit_Command367_Click:
Exit Sub

Err_Command367_Click:
MsgBox Err.Description
Resume Exit_Command367_Click

End Sub

Reply | Quote

Replies

Having created a recordset from the query, you then need to deal just with recordset. You can forget about the query then.

Do while not email.EOF
looping code
email.movenext
loop
I don’t follow what you are trying to do in the loop. If you are trying to take a value from the recordset, and put it on the form, you could use something like :
me!lineid = email!lineid

Reply | Quote

Thanks for the time,
I am reading a field (lineid) from the query (select-for-email) and creating a one record report based on another query that the lineid is pasted into as a criteria.
Read query record, past lineid into report query, generate and send report, read query next record, loop until EOF.

I have a macro that reads the query and performs the action but does not handle the EOF. If you know how to loop a macro until EOF, I would plug that into the macro.

Reply | Quote

If you want to read the info from the query, then you don’t need the reocrdset.
Alternatively, you create a recordset from the query, then pull info from the recordset. You need one or the other, not both.

I have forgotten what you can do with macros.

If you use code instead, you can create the recordset from query, then loop through the recordset using the loop I showed before.

Reply | Quote

Don’t try to get that fancy with a macro because they break too easily and you can’t error trap them or handle anything but very simple conditions. You need code to do this properly.

Reply | Quote

Thanks for the reply. I don’t mind using code if I can get the statements right. I think the code is close per my original post to do the read and send object but the Dim/Set statements are causing the object failure. Any help there?

Reply | Quote

Reread John’s post here. You would need to add code to use the resulting keyvalue form the current field in the recordset to build a WhereCondition string and then you would send that to the report in the DoCmd.OpenReport statement.

If you’re getting an error message, tell us what the error is. There are hundreds of error messages in Access/Jet and Windows, so you’ll have to be specific.

Reply | Quote

Error with code above is: Runtime error 424, Object Required.
Clicking “debug” yellow highlights SET statement line. I don’t use VB very much so John’s reply was not clear to me.
I understood it to say that if I use the SET statement correctly, it will open the query and I will not have to use the next line, DoCmd.Open Query. With the debuger highlighting the SET, I must not be using it correctly.

Reply | Quote

Have you initialised db with a SET command?
Pat

Reply | Quote

Well, unless you initialized the db object on a module level in some other routine, it’s the object that is being objected to. You can only open a recordset based on certain objects, like a database object. So where is your “Dim db As DAO.Database” and “Set db = CurrentDb”?

Reply | Quote

Dim db as DAO.Database causes “COMPILE ERROR, User-defined type not defined” when run
Before my first post, I tried this statement and had same compile error. Most recent code follows:

Private Sub Command369_Click()
Dim db As DAO.Database, rs As Recordset
Set db = CurrentDb
Set rs = CurrentDb.openrecordset(“Select [lineid] FROM [select-for-email] GROUP BY lineid;”, dbOpenSnapshot)
Do While Not rs.EOF
DoCmd.GoToControl “lineid”
DoCmd.RunCommand acCmdCopy
SendKeys “^v”, False
SendKeys “{enter}”, False
DoCmd.SendObject acSendReport, “report-for-email”, acFormatRTF, “chriss”, “”, “”, “traveler”
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Sub

Reply | Quote

You need to set a reference to the “Microsoft DAO 3.x Object Library”

To do this open the VBE Select Too;s|References….

Scroll down until you see it, and then check it.

Have a look at the attached screenshot.

Reply | Quote

Thanks for reply!!!
Setting the DAO reference fixed the Dim statement. Compile Runtime error #13 now occurs, TYPE MISMATCH on the:
Set rs=Currentdb……… statement line.
I’m trying to open the query named “select-for-email”, first record, read only. Not using it correctly, I suspect. Thoughts?

Reply | Quote

[indent]

---

Set rs=Currentdb……… statement line.

---

[/indent]
Change the dim statement to:

dim rst as dao.recordset

and the quoted line to

Set rs=db.open……..

ADO and DAO both have Recordset datatypes and they aren’t the same

Reply | Quote

Code now loops to each record of the “select-for-email query. However, it is not going to the ‘lineid’ field and copying content to use as criteria for the report. Comment inline

Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(“Select [lineid] FROM [select-for-email]”, dbOpenSnapshot)
Do While Not rs.EOF
DoCmd.GoToControl “LineID” ‘go to lineid control
DoCmd.RunCommand acCmdCopy ‘copy content of lineid control
SendKeys “^v”, False ‘paste content of copy
SendKeys “{enter}”, False ‘enter
‘report-for-email’ is created with a query that uses the lineid as a criteria for just one lineid matched record
DoCmd.SendObject acSendReport, “report-for-email”, acFormatRTF, “chriss”, “”, “”, “traveler “, “”, False, “”
rs.MoveNext
Loop
rs.Close

Reply | Quote

In one of your earlier posts, you siad that lineid was a field in the recordset correct?

If so you can reference the field like this:

rst.fields(“lineid”)

But it also sounds like this is a prameter for a report, correct?

If so, why don’t you just set the parameter in the Report’s query to the value of the LineID control?

You can do this, by opening the query in design mode, right click on the criteria for the field, select build… and choose the form and control from the list boxes at the bottom of the Build Dialog.

Your criteria should look something like:

Forms![FormName]![ControlName]

I hope i haven’t misunderstood what you are doing.

Reply | Quote

My understanding is that you want to take each lineid from the recordset and use it as a parameter for the query. One way to do (which is what I think you are trying to do) is to transfer the value from the recordset to the form, and use the form value as the parameter.
To do that replace all these lines:
DoCmd.GoToControl “LineID” ‘go to lineid control
DoCmd.RunCommand acCmdCopy ‘copy content of lineid control
SendKeys “^v”, False ‘paste content of copy
SendKeys “{enter}”, False ‘enter

with
me!lineid = rst!lineid

Reply | Quote

We have success! I did leave in the send keys to auto-enter the report criteria. It now reads the query one record at a time, enters the report criteria (lineid), generates a one record report, and loops until EOF. Thanks for your time and the frustration of dealing with a VB novice.
lineVariable = rst!lineid
SendKeys lineVariable, False
SendKeys “{enter}”, False
DoCmd.SendObject acSendReport, “report-for-email”,

Reply | Quote

You don’t copy and paste the contents of a control, you simply assign its value to a variable:

lngVariable = Me!LineID

Reply | Quote

Charlotte,
You and John came up with the same solution. Thanks for your time and patience. Check out my comments to him.

Reply | Quote

Thank you, thank you, thank you!

I was having exactly the same problem with some code I’d pasted from an old WOW. Now it’s all better. Searching the Lounge was a lot faster and less stressful than trying to figure this one out for myself.

If only the help system could be persuaded to stick with DAO in the Code Editor — and explain the difference between ADO and DAO, for that matter.

Again, thank you,

Paulius

Reply | Quote