Move and renumber records

[elyleeboy]

Hi all

I have a database that contains a list of records... ie:

1 | Question one text | Option 1 | Option 2 | etc...
2 | Question two text | Option 1 | Option 2 | etc...
3 | Question three text | Option 1 | Option 2 | etc...
4 | Question four text | Option 1 | Option 2 | etc...

I am not usinig autonumber, there my column titles are:
QuestionID | qText | Opt1 | Opt2 | etc...

What I am trying to do is give the user the option to move a question into a different position and therefor it takes on the QuestionID and then sorts by the QuestionID.

For example, I want to move question 4 into postion 2, therefor returning:

1 | Question one text | Option 1 | Option 2 | etc...
4 | Question four text | Option 1 | Option 2 | etc...
3 | Question three text | Option 1 | Option 2 | etc...
2 | Question two text | Option 1 | Option 2 | etc...

Any help would be greatfully appreciated

Ta

 

[Mile-O]

Your questionaired doesn't have a robust structure (as you have conceded) so why not fix it?

http://www.access-programmers.co.uk/forums/showthread.php?t=69629

 

[elyleeboy]

Don't quite get what you mean??

 

[Mile-O]

Option1, Options2, etc. is the approach you would take if using a spreadsheet tool.
Since you are using a database tool it would appear, by that table design, that you are mistaking it for a spreadsheet tool. Look at the suggested structure for a survey database by Pat Hartman on the link I posted - that's a logical design for a survey database.

 

[elyleeboy]

Dim currDB As DAO.Database
Dim currRS As Recordset
Dim lngMove As Long
Set currDB = CurrentDb()
Set currRS = currDB.OpenRecordset("AllQuestions", dbOpenDynaset)

lngMove = 2
currRS.Move lngMove.


Would the 'move' method not work, something like the above (althought this doesn't work either!).

Sorry about my level of experience... I have just been TOLD to create this functionality.

Thanks

 

[Pat Hartman]

Important lesson

Tables are not manipulated the way spreadsheets are. Spreadsheets are "flat" files. Records can be moved around and they will stay where they are moved because "flat" files are ordered sets. On the other hand, tables are unordered sets. Although they obviously have some physical order, they are not processed as "flat" files are. The database engine extracts the selected data based on the specifications of a query and returns a RecordSet. The RecordSet has no predictable order unless the query that made it included an Order By clause to sort the records. Small RecordSets created by Jet will usually be returned in the same order. This lulls people into believing that there is a predictable order. But, don't be fooled. There isn't and working with a large RecordSet will prove it to you.

Anyway, to solve your problem, you need to add a new column to the table. Call it SortOrder. Let the user manipulate the value in that field to order the questions. Include an Order By in your form's RecordSource query that orders by the SortOrder and then the primary key. You should make the SortOrder = to the primary key when a new record is added if the SortOrder is omitted. Fix up the existing data with an update query.