Solved: List grid solution for a user form

Hello everybody.

I am writing a code in Excel which allows user to retrieve invoice data from our SQL server. I am retrieving the data through adodb connection.

I have managed to import required data from SQL to excel, now I would like to take a further step.

I want to create a user form which enables the user to query the required invoice records from server, with specifying customer code, customer name, date or invoice number. I have no problem writing the code which will execute the query, but I couldn't find any solution to show the query results on the form.

Let me give you an example. Let's say I want to retrieve an invoice from the server. This invoice has been issued sometime between 12nd and 16th of this month. I know the customer name but I don't have the invoice number. When I query the server for invoices between 12nd and 16th, I should see the results in the form, let's say, in a grid, with columns such as customer name, customer code, invoice date, invoice number etc. and when I click (or double click) on the relative line, I should be able to start another query which will import all data related to that invoice. Do you have any idea how (or if) I can manage that? Thanks in advance for any answer!

We have an sql forum. Would you like your request moved to that forum?

Quote:

---

Originally Posted by lucas

We have an sql forum. Would you like your request moved to that forum?

---

Good idea, since I am working in Excel I thought Excel would be a more suitable choice, I did not figured the problem is more SQL-releated. Thanks for advice and I apologize for inconvenience.

Forgive my ignorance, I am quite new at the forum, how do I request to move it?

I would put the details in a listbox, simpler to work with than a grid, and then use the selected item to re-query.

You can just drop the recordset into an arry (ary = RS.GetRows) and then populate the listbox with that array (ListBox1.List = APplication.Transpose(ary)). Make sure you set the columncounts property.

Quote:

---

Originally Posted by xld

I would put the details in a listbox, simpler to work with than a grid, and then use the selected item to re-query.

You can just drop the recordset into an arry (ary = RS.GetRows) and then populate the listbox with that array (ListBox1.List = APplication.Transpose(ary)). Make sure you set the columncounts property.

---

I was planing to do it that way, than I figured that it would be easier for user to work with a grid. Besides, I will feel myself much better if it looks like a table with all the details and headers. Is there a way to make it work with a grid? if not, I will use a listbox anyway.

by the way, I am developing the application in Office 2007, but it also have to work in Office 2003.

I have used a listbox, thanks to all for helping.

I see this has been marked as solved, but I wonder why the OP didn't just use SQL Server Report Services as the query with the option to export the results to Excel? .02 Stan

Actually, I am very new on programming and don't know all the technologies / techniques, I am aware that there is something called "SQL Report Services" but I don't know what kind of requirements it can answer to.

Besides, what I am trying to do is creating a very simple userform on excel for office use. This will be used by employees that barely can use excel. I cannot expect them to start a query to SQL server and import required data to Excel(imagine how hard my life is :)) For myself, it is better to query data via external data wizard but other people on organization needs a simpler (way simpler) solution. That is why I decided to code something like this.

Anyway, if you have any idea that makes my life easier I really would appreciate it :)