A problem with cutting and pasting records

[planix]

The Background: I have a table which I am using as the basis for an interactive survey. The table holds the Question Number, the Question Content, where to go if the answer is positive, where to go if the answer is negative, and which response format should be used.

The table is accessed in code which seeks records by the Question number and sets up a survey display form using the content and response format fields. A global variable takes the where to go field(s) and moves to a next question number depending on some decision rules related to the answer to the question.

The problem: I had all this working well until I decided I needed to add another question. For some stupid reason (forgot evrything I knew, doh!) I wanted to insert this question in sequence. So I cut all the records, inserted a new record, filled in the fields, and then pasted all the other records back in. Why this is dumb is that my search sequence in code just steps through the table until it finds the right question number- they don't need to be in sequence. Also... it broke my application.

I have obviously altered some fundamental property in the table in relation to the order of records. Because now when I come to the question that I added I get an error indicating that the record does not exist.

I have this vague idea that I know what is wrong but I really don't know how to fix it. It has something to do with how Access actually stores table data not being in the sort order displayed. Something like that.

Does anyone know what I have changed in the record storeage which might lead to this problem? If so, any ideas on how I might fix it.

 

[BNPMike]

Never access a table if you need a sort order. Tables (ie relations) have no order so Jet is not bound to deliver any.

The simplest thing to do is be relational and that means build your recordset with a query that has an ORDER BY clause.

People will disagree (including Microsoft) but my rule is never access a table directly at all. This comes from the fact that the Relational Model was built on the Three Schema Model which assumes you always deal with the "External Schema". In the Relational Model the External Schema is defined by views. In Access, views are queries.

 

[planix]

Thanks. Good advice which I will now follow.

Can you help me with the coding side of things.

What I was doing was creating a recordset from the table and then stepping through it. I have created a sorted query and then tried to create a recordset from this by

Set theDB = CurrentDb
Set QTable = theDB.OpenRecordset("qryQuestions")

but this breaks the rest of the code insofar as references to the Question number(a field in the query and table) do not seem to be recognised.

I am wondering if I should not be trying to create a recordset from a query.

Any thoughts.

 

[BNPMike]

Sorry, I don't know DAO. Make sure the query contains the field, and that it is updatable - try this in a datasheet view.

Apart from that, you'll need to post your code and error message here so people can see what might be wrong.

 

[willir]

planix

If you are having conflicts in sorting the your data - table order and Order By, then it sounds like that you may have to tweak your design.

We can try to give you a quick fix, but I suspect that looking at your design may give you a better solution in the longer run.

If you want, post your design related to this specific problem.

...And, Mike, I and others defintely support your belief that access data directly from the table object is not a good idea.

Cheers
Richard

 

[planix]

Thanks all.

I agree it is a design problem. I will go with a quick fix that I have worked out for now. But I will ask you all again because I'd really like to make this a generic sort of app.

Basically I design and use a lot of surveys and questionnaires. I have tried to cut the data collection steps by using computer assisted administration. In the past I wrote a VBA front end for a questionnaire that manipulated data in a couple of tables in a general clinical admin database. This worked fine but a lot of the methods were hard coded.

My plan would be to write an interface that would enable me to design the forms and add questions on the fly without doing any coding in the background. For this I will probably go with VB or Delphi but I am not too clear on the object heirachies and creating controls etc at run time.

Still... could be fun.

Thanks for your thoughts.