accessing data/form

[sunilm]

Hi all,
I am using the form object in the data menu in excel to access my data. But the problem is that every time I open up the form, it starts at the very first record. Actually I used the showdataform method in vba to do that. Is there some way I could direct the form to start at the current record rather than at the first record. I dont want to hit the criteria button every time. I dont know how to access the built in form object in excel. Anybody has any ideas.

Thanks,

Sunil.

[BrianB]

Here is a really simple solution I an quite pleased with. You can use an ordinary worksheet as a dataform and format it/add code/print it/ etc. as required.

The basic idea is to use a formula to read the first row of your table (containing the column headers) into column B and a similar formula in Column C to access a record detail for the corresponding field using its row number (stored in a named cell "RowNo").

To enable simple copying of the formulas down the 2 columns I use column A (starting at A4) with the numbers 1,2,3, ..etc.

So column B has the formula starting in B4 :-
=INDIRECT("'data'!R1C" &$A4,FALSE)

and column C :-
=INDIRECT("'data'!R" &RowNo &"C" &$A4,FALSE)

I have also added code connected to buttons to MovePrevious and MoveNext and Find - which all change the "RowNo" cell value to show the appropriate record.

This will work on any Excel table providing it is called "data", so it it possible to just copy the worksheet to another workbook and it will work.

[sunilm]

Hi Brian,
Thanks for the tip,
I tried the formula in excel, but I dont know how introduce a variable called RowNo in excel. I tried doing it using VBA and the indirect function is not available to it. Or is rowno a cell name, if it is then how is it going to store the row number of the current record.
Thanks again,
Sunil.