How to view records in recordset in datasheet view using VBA code 1

[vbvbvbvb]

I am having a recordset containing result of a query.
Could anyone please suggest a way to view that recordset in datasheet view.

i.e., say, rs is the recordset object which contains the result of my query.
I want to show the records in datasheet view, so that the users could see the records and modify the field values.
I do not want to show the records in Debug window.

so, please help me with the VBA code for viewing the recordset in datasheet view.

Thanks in advance

 

[rmikesmith]

Are you using Excel and by 'datasheet' do you mean Worksheet?

If so, you can easily populate a worksheet with you recordset data using the CopyFromRecordset method of the Range object. However, this does not provide you with an automatic way to edit/update the data, as there is no link between what's on worksheet and the recordset. I'm actually working on an application that uses this method to display the data on a worksheet (it's what the end-users are familiar with seeing) but all editing is done using a Userform that interacts with individual records in the recordset object.

I have also used a DataGrid component on a Userform and connected it to a recordset. In this case, you can allow editing the live data and it will update the original data store. However, there may be licensing issues depending on what software is on your machine (I have VB installed).

Regards,
Mike

 

[vbvbvbvb]

Thanks for the response, Smith.

Actually I do not mean Excel worksheet for datasheet view.
I am talking about datasheet view of records in Access table/query.

My requirement is - when a user fills in some date in Text boxes, and clicks a button, I use that data to create an SQL select query, execute it and store it in a recordset object.

I am able to retrieve the records and show them in the Debug window as follows

' Open a Recordset object.
recordset.Open Source:=strSQLFind, _

' Display the records in the Debug window.

Do While Not .EOF
For Each fld In .Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
.MoveNext
Loop


Instead of printing on the Debug window, I want to view the records of recordset object in datasheet view.(so that the users can modify the values of the selected records)

So, please suggest me a way to do so.

 

[PHV]

Create a form based on your query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbvbvbvb]

I am building the query at run time. If it were a stored query, I could build a form based on the query. But I am constructing the query at run time, and getting the required records in a recordset.

Now, I need to show that records in datasheet view, just as we see the records when we click a stored query.

Thank you

 

[TonyJollans]

You could always create a dummy (stored) query up front and then 'hijack' it ...

[blue]CurrentDb.QueryDefs("[i]QueryName[/i]").SQL = strSQLFind[green]' Or ADO equivalent[/green] 
DoCmd.OpenQuery "[i]QueryName[/i]"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[vbvbvbvb]

Hey Tony,

You are superb. I was trying to achive this since yestersday morning.
Thank you so much.

 

[TonyJollans]

My pleasure!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[