Access 97 limit

I have a form with a table of 20 lines and 17 text inputs per line = 340 fields....access 97 can only store a max of 255 fields per table, so I have two tables for this forms' data....(called JS1 & JS2) now I was wondering if I could replace all these 340 fields with , say 20 fields to represent each line and use comma delimited data per field...this would (If it will work) reduce the need for the two tables to represent this form, but how do I format this representation, and still be able to reference a particular 'cell' of the 340 represented? All the fields are numbers except for one on each line with is text. I do not need to do any maths on the numbers just make sure they ARE numbers typed by the user and show them on the form when taken from the DB, and printed when again collected from the DB onto another asp form. I know I could use a more stable/powerful DB, like SQL, but will get project running then I can look at improvements like that......any thoughts on getting me started?....>>>

You may have a very good reason for it, but the database design sounds bizarre

Do you need to store the data all in one table or is there data in there that would be better stored in separate, related tables, which can then be referenced to collate the required data.

indeed... if you have 20 lines in the html table and 17 boxes in each, mirror that in the database: have one table with 17 fields (one field for each text box in the form) and with 20 records, one record for each line. makes much more sense, isn't it?

OK guys...Unfortuneately there are no fields that can be collated into separate tables ....these whole forms are full of unique data not used anywhere else in the project, so yes I can see about changing the DB Schema but as each form is unique cant try that......OK Shadow on the other idea will have a go at that and let u know...thanks guys.....cul....>>

Right, I’ve got the DB structure OK now, with 17 fields and up to the DB Max in lines. My fields are just labeled field1, field2 up to field17. The other fields are called EstNo, ShtNum and Line, which will always have entries, if they are in the table at all, but the field ones may or may not be empty..I have it reading the DB OK and filling out the form fine..Its when it comes to the user changing the data in the fields, on the form, and saving the fields to the DB that the prob is…My code so far for the save button is….

query = ("SELECT * FROM JS WHERE EstNo="&"'"&estno&"'" &" AND ShtNum="&i)
ShepRS.Open Query,ConnDB,adOpenStatic, adLockOptimistic
ShepRS.movefirst
x = 1
do while NOT ShepRS.EOF
y = 1
if ShepRS("Line") = x then ' if Line x exists in JS table
if isEmpty(tempStore(x,y)) then ' if JS sheet line x is empty
ShepRS.delete
ShepRS.update
else
for y = 1 to 17 ' write JS sheet line x to table.
ShepRS("field"&y) = tempStore(x,y)
next
ShepRS.update
end if
else ' Line x is not in JS table
if isempty(tempStore(x,y)) then ' sheet line is empty do nothing
else ' sheet line is not empty & Line not in JS Table
ShepRS.addNew ' add line data to table
ShepRS("EstNo") = estno
ShepRS("ShtNum") = i
ShepRS("Line") = x
for y = 1 to 17
ShepRS("field"&y) = tempStore(x,y)
next
ShepRS.update
end if
end if
x = x+1
ShepRS.movenext
loop

The english description of the code is….If the line exists in the table then check to see if the same line on the sheet is empty (all fields empty, if some have data then this is false) and if it is then delete the line from the table..else if the line does not exist in the table and the line is totally blank on the sheet then move on to the next line else if there is no line in the table but some of the fields have data then write the new line into the table.
The prob is the lines concerning …..isempty(tempStore(x,y)….this is only looking at he first field of the line, whereas I need it to look at all fields on the line and ONLY IF ALL are empty then do whichever……All the above code does is add another line to the DB with the Estno , ShtNum OK and the Line number of the next line which has the first field empty., then it finishes..that’s all it does to the whole table..…

your code is bit messy, use the [ Code] and [ /Code] tags (remove the spaces) in the future to preserve the structure...
anyway, tempStore(x,y) is indeed only one value. what you mean by "I need it to look at all fields on the line"? what is line in this aspect? I see only two dimensional array, no line or fields...

OK Shadow, yes the code looks a lot better on my screen , will remember Code & /Code in future......ok the fields on each line are represented by the y value (1 to 17) and the lines, on the form, are the value x (from 1 up to whatever number as there will be balnk lines on the form i thought there was no sense in storing their values, so the number of lines will vary from estno to estno up to a max of 20 per ShtNum ....help?

hmm... so you have to check if tempStore(x,y) is empty for every 1<=y<=17 right? if so, have such code:

and have this function written somewhere in the page:

Fine Shadow, ur function helped a lot..it works fine....now
I’m nearly back on track…altering the DB schema has caused probs all over the place!…but sorting them out , with just one prob left before I get back on track………In the table JS there can be anything from 1 to 20 in the number of Lines per ShtNum. There will always be at least one Line associated with a particular ShtNum… so u can have the DB Table like so…..

Now the prob is ..When I delete all Lines associated with say ShtNum 2 , I need the ShtNums after that to be re-numbered to 2 & 3 in the above example DB..



Any help here please ?.....>>

very simple:

Aghhh!!..I hate it when u make things so simple..!!....thank u very much Shadow...one line of code replaced my 7. I had this for my delete....


the simple things are always the best...cul in my next thread..>>>

lol
no problem, glad I was able to help!