Copy to Clipboard? (1 Viewer)

theKruser · Sep 4, 2011

I would like to be able to create a button on a form that will call a query/report and copy the results in plain text to the clipboard.

I am assuming the best way would be to create a hidden text box on the form and assign it's value with VBA, then use the acCmdCopy method. However, I am not sure how to call a query in VBA and assign the default value to a query result.

This problem is further compounded by the need to copy the results of a form with subform to the clipboard in other forms.

PLease help if you can. I am lost and on a deadline. Thank you in advance for your help! Have a nice day.

HiTechCoach · Sep 6, 2011

Where will you be pasting the data?

theKruser · Sep 6, 2011

I got it partially figured out. I have an unbound text bow that I fill from a query through vba. I then use the acCmdCopy method adn paste into a .txt file.

Noe the issue becomes using vba to grab the info from subsets of records off of the main record (second table) in which there may be one record, there may be 15. I am thinking DAO, but am not sure how to get it to work.

Once I figure it out, I will post the solution here.

If anyone has a suggestion, I would be ever grateful. Thank you and have a nice day!

HiTechCoach · Sep 7, 2011

Open a recordset and loop trough the records that are in the sub form. Append the data to th4 text box control.

Again, what are you doing with the data once you get it on the clipboard?

theKruser · Sep 8, 2011

Thanks for the reply!

Question: can you please provide an example of how one would create the record set in VBA and then loop through it? Suppose the following:

tblReport
ReportID (PK)
ReportName
ReportTimestamp
InfoBox1
InfoBox2
etc...

tblSubReport
SubReportID (PK)
ReportID (FK)
SubReportName
SubReportTimestamp
InfoBox1
InfoBox2
etc...

Thank you in advance for your help!

To answer your question, the copied text will be pasted into a .txt file, a chat program, and into the body of an email.

theKruser · Sep 8, 2011

OK...I am about to see if my comupter can fly. I can get the records to display correctly in the debug window using the following code:

Code:

Dim db as DAO.Database
Dim rst as DAO.Recordset
 
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSubReport")
 
Do Until rst.EOF
    If (rst!ReportID = [Forms]![frmReport].[ReportID]) Then
        Debug.Print "1.  " & rst!1 & vbNewLine & "2.  " & rst!2
        Debug.Print "**********"
    End If
    rst.MoveNext
Loop

Now i cannot get it wo display in the text box on the form. I am so frustrated.

Any help would be greatly appreciated. Thanks in advance!

theKruser · Sep 8, 2011

Also...when I try to replace

Code:

Set rst = db.OpenRecordset("tblSubReport")

with

Code:

Set rst = db.OpenRecordset("SELECT * FROM tblSubReport WHERE rst!SubReportID = [Forms]![frmReport].[ReportID]);", dbOpenDynaset)

I get an error: "Too few parameters. Expected X"

When I give it one parameter, X=2...when I give it 2, X=3! It is like I just cannot get this to work!

HiTechCoach · Sep 9, 2011

theKruser said:
Also...when I try to replace

Code:

Set rst = db.OpenRecordset("tblSubReport")

with

Code:

Set rst = db.OpenRecordset("SELECT * FROM tblSubReport WHERE rst!SubReportID = [Forms]![frmReport].[ReportID]);", dbOpenDynaset)

I get an error: "Too few parameters. Expected X"

When I give it one parameter, X=2...when I give it 2, X=3! It is like I just cannot get this to work!

Great job. You are getting very close.

When you open a recordset you can not include form references like you are trying to do.

Try:

Code:

Set rst = db.OpenRecordset( "SELECT * FROM tblSubReport WHERE rst!SubReportID = " & [Forms]![frmReport].[ReportID] & ";" , dbOpenDynaset)

Here is another trick.

If the code is in the parent form and the sub form's name is sfmMyFormName then you could use something like this:

Code:

Set rst = Me.sfmMyFormName.form.RecordsetClone

theKruser · Sep 9, 2011

I tried your suggestion, but I still get the same error, but telling me expected 1.

theKruser · Sep 9, 2011

Also, do you have any insight how I can get the results to show in a text box on the main form? I have the text box and it already fills with info from the main form...all of this is just to get the subreport results to append to what I alrady have.

HiTechCoach · Sep 9, 2011

theKruser said:
I tried your suggestion, but I still get the same error, but telling me expected 1.

Which suggestion did you try?

If you tried the first one:

I spotted an issue with your SQL. This part is not correct: rst!SubReportID

Try:

Code:

Set rst = db.OpenRecordset( "SELECT * FROM tblSubReport WHERE tblSubReport.SubReportID = " & [Forms]![frmReport].[ReportID] & ";" , dbOpenDynaset)

I assumed that SubReportID was a numeric data type. U find it best to always make your primary keys autonumber.

If is a text data type then you will need to use:

try:

Code:

Set rst = db.OpenRecordset( "SELECT * FROM tblSubReport WHERE tblSubReport.SubReportID = " & Chr(34) & [Forms]![frmReport].[ReportID] & Chr(34) & ";" , dbOpenDynaset)

theKruser · Sep 9, 2011

You were right. It is an autonumber. It works now. Thank you!

Now, any idea how I can get it to show in a text box on the main form?

HiTechCoach · Sep 9, 2011

The basic idea is to add it to the end of the data in the text box like this:

Code:

Me.txtMyTextBoxName =  Me.txtMyTextBoxName & rst!myFieldname

theKruser · Sep 9, 2011

You, sir, are a genius!

Thank you so much for your help!!

theKruser · Sep 9, 2011

For those who are interested, here is the final fix to this issue:

Code:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim var as String
 
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblSubReport WHERE tblSubReport.ReportID = " & [Forms]![frmReport].[ReportID] & ";" , dbOpenDynaset")
 
Do Until rst.EOF
    var = "1.  " & rst!1 & vbNewLine & "2.  " & rst!2 & vbNewLine
    Me.txtMyTextBoxName = Me.txtMyTextBoxName & var
    rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing

A big thank you goes out to HiTechCoach for all of his help!!

HiTechCoach · Sep 9, 2011

You're welcome. I am glad I could assist in your learning.

You did a great job. And thanks for posting your solution so other may benefit.

theKruser · Sep 9, 2011

Of course!

Copy to Clipboard? (1 Viewer)

theKruser

Registered User.

HiTechCoach

Well-known member

theKruser

Registered User.

HiTechCoach

Well-known member

theKruser

Registered User.

theKruser

Registered User.

theKruser

Registered User.

HiTechCoach

Well-known member

theKruser

Registered User.

theKruser

Registered User.

HiTechCoach

Well-known member

theKruser

Registered User.

HiTechCoach

Well-known member

theKruser

Registered User.

theKruser

Registered User.

HiTechCoach

Well-known member

theKruser

Registered User.

Similar threads

Users who are viewing this thread