Data Access Nightmare.

[Rob Brown]

Hi,

I'm building my first web-based front end (using Access 97 & InterDev 6.0 / SP3).

I have an .asp page with the following controls on it:

rcdst (Recordset DTC)
cmdMoveFirst (Button DTC)
cmdMovePrev (Button DTC)
cmdMoveNext (Button DTC)
cmdMoveLast (Button DTC)
cmdAddNew (Button DTC)
cmdUpdate (Button DTC)
cmdDelete (Button DTC)
cmdSearch (Button DTC)
grdDUsers (Grid DTC)

There are also various text and list boxes on the page but I won't bore you with the details.

Originally I had the Recordset DTC's DataSource set to a table in my database and all my controls did what they were supposed to.

However, I have a lot of records and my users have to be able to filter the information on screen. So I added a couple of list boxes and a text box to allow them to create a custom query.

Again I won't bore you with the details but I had to change the Recordset DTC's DataSource property to a SQL String, that way when the user selects their paramaters and presses the Search button (cmdSearch) I close the recordset, set the SQLText property using the setSQLText function and re-open the recordset using a string such as:

SELECT * FROM tblDUsers WHERE User_ID Like '%bro&' ORDER BY User_ID;

Obviously this string can vary depending on what the user selects.

This works fine and I can see from the contents of my grid control that the DataSource property has been set.

However, now none of my buttons do what they're supposed to do:

If any of the following buttons are pressed my Grid DTC reverts to showing all records from the table (The intitial SQLText property is 'SELECT * FROM tblDUsers ORDER BY User_ID'):

cmdMoveFirst (Invokes: rcdst.MoveFirst())
cmdMovePrev (Invokes: rcdst.MovePrevious())
cmdMoveNext (Invokes: rcdst.MoveNext())
cmdMoveLast (Invokes: rcdst.MoveLast())

If any of the following buttons are pressed I get the following message:

Microsoft Cursor Engine error '80004005'

Insufficient base table information for updating or refreshing.

/WizDB/_ScriptLibrary/Recordset.ASP, line 311

cmdUpdate (Invokes: rcdset.UpdateRecord())
cmdDelete (Invokes: rcdset.DeleteRecord()/.MoveFirst())

The only button on my page which still works is:

cmdAddNew (Invokes:rcdset.AddRecord())

But the user can't update the new record once they've edited it so it doesn't really matter whether it works or not.

I need my users to be able to retrieve only the data they want to see and I also need them to be able to edit that data.

What am I doing wrong? Any suggestions would be greatly appreciated as this is driving me nuts.

I've attatched the page in case anyone wants to take a look at it (I realise I've rambled a lot in this thread).

Best regards,

Rob Brown.