n8dog
asked on
How To: Query as Record Source > 255 Fields ?
** PERSONAL WEBSITE DOMAIN & has NOTHING TO DO WITH this PRJ or the COMPANY I WORK FOR **
DB FILE >> http://n8dog.com/TMDB_ONLINE_SND.jpg
*((CHANGE THE EXTENSION from JPG to MDB once DL'd))
---------- DB File ----------
1.) Multiple tables exist. These various tables are for testing purposes of diff. attempts to solve the 480 Field barrier issue.
2.) Multiple Forms exist as well, for the same reasons as above.
3.) All sensitive data has been extracted from the tables due to obvious security reasons.
The current barrier is the 255 Field Query barrier.
Having this DB should make it a great deal easier discussing potential solutions.
I am looking to solve for the 255 Field Limit for the Query as a Record Source, while/or Solve the "Iterative" example that has Helper Functions like the Duplicator and Incrementor's on the Sample Forms.
Please contact so we can discuss. I am open to solves. I don't care about "most normalized" solution, just one that works for this data storing purpose.
-n8D
DB FILE >> http://n8dog.com/TMDB_ONLINE_SND.jpg
*((CHANGE THE EXTENSION from JPG to MDB once DL'd))
---------- DB File ----------
1.) Multiple tables exist. These various tables are for testing purposes of diff. attempts to solve the 480 Field barrier issue.
2.) Multiple Forms exist as well, for the same reasons as above.
3.) All sensitive data has been extracted from the tables due to obvious security reasons.
The current barrier is the 255 Field Query barrier.
Having this DB should make it a great deal easier discussing potential solutions.
I am looking to solve for the 255 Field Limit for the Query as a Record Source, while/or Solve the "Iterative" example that has Helper Functions like the Duplicator and Incrementor's on the Sample Forms.
Please contact so we can discuss. I am open to solves. I don't care about "most normalized" solution, just one that works for this data storing purpose.
-n8D
You can't solve the 255 field limit.
Seeing your tables it looks like you didn't normalize them and that's the real problem.
The fields 1-... 2-... 3-... etc. need to be in separate rows.
What table is the "most urgent" ?
Nic;o)
Seeing your tables it looks like you didn't normalize them and that's the real problem.
The fields 1-... 2-... 3-... etc. need to be in separate rows.
What table is the "most urgent" ?
Nic;o)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Understood & I appreciate the feedback.
As mentioned, I am open to all solutions..the Iterative example quickly solves the 255 issue, but kills the PERM UI, and Helper Functions (as-is).... So a solution solving the issue on that path is welcome :)
-n8D
As mentioned, I am open to all solutions..the Iterative example quickly solves the 255 issue, but kills the PERM UI, and Helper Functions (as-is).... So a solution solving the issue on that path is welcome :)
-n8D
Nope, it will only require the use of a recordset processing loop to fill the unbound fields of your form when you can't live with a datasheet subform or a continuous subform.
A form has however also a max of some 255 fields and to overcome that you can use one or multiple subforms.
Best is however to use a subform based on the table. When working "unbound" you can have a sequencenumber 1-20 to use for the filling of the fields.
Just name the fields on your form rt1 to rt20 and dda1 to dda20, etc.
Now filling can be done using:
dim rs as dao.recordset
set rs = currentdb.openrecordset("s
while not rs.eof
Me.Controls("rt" & rs!sequence) = rs!rt
Me.Controls("dda" & rs!sequence) = rs!dda
rs.movenext
wend
Getting the idea ?
Nico)
A form has however also a max of some 255 fields and to overcome that you can use one or multiple subforms.
Best is however to use a subform based on the table. When working "unbound" you can have a sequencenumber 1-20 to use for the filling of the fields.
Just name the fields on your form rt1 to rt20 and dda1 to dda20, etc.
Now filling can be done using:
dim rs as dao.recordset
set rs = currentdb.openrecordset("s
while not rs.eof
Me.Controls("rt" & rs!sequence) = rs!rt
Me.Controls("dda" & rs!sequence) = rs!dda
rs.movenext
wend
Getting the idea ?
Nico)
ASKER
jimhorn ...
What if I do not have SQL Server at my disposal?
nico5038 ...
You're correct, most of the table example/test solutions aren't normalized, but for functionality and time purposes, this worked...until the limitation :)
The normalized example would work, (Iterative), but it cost me the UI, layout, Record/User Restriction, and Helper/Speed Functions I had in place.
If I would intertwine the two, I'd be golden.
I butchered most of the DB to post that. However the core pieces of various attempts of different solves are there for example's/discussion's sake.
I need a UI that confines the USER to that which is displayed in Form: EXP_messAround....enables the lil' functions (Command Buttons) that are in Form: TC_log2_1CHK-0STMNT...and allows me to get around the 255 limitation....
-n8D
What if I do not have SQL Server at my disposal?
nico5038 ...
You're correct, most of the table example/test solutions aren't normalized, but for functionality and time purposes, this worked...until the limitation :)
The normalized example would work, (Iterative), but it cost me the UI, layout, Record/User Restriction, and Helper/Speed Functions I had in place.
If I would intertwine the two, I'd be golden.
I butchered most of the DB to post that. However the core pieces of various attempts of different solves are there for example's/discussion's sake.
I need a UI that confines the USER to that which is displayed in Form: EXP_messAround....enables the lil' functions (Command Buttons) that are in Form: TC_log2_1CHK-0STMNT...and allows me to get around the 255 limitation....
-n8D
>What if I do not have SQL Server at my disposal?
Then as the above experts have commented, or unless Nico's approach works, the answer is 'No, you can't do it'.
Then as the above experts have commented, or unless Nico's approach works, the answer is 'No, you can't do it'.
ASKER
nico5038....
I had thoughts on that, but couldn't get it working fast enough, and was also exploring this limitation.
OK.. so.. Ident all Records based on the TCR....some have 20, some 40, some 60...
and when using the Helpers...have a loop, loop through that record set, and supply the Duplicate or Incremented Data based on Record 1 ?
I was really hoping to confine the user more with regard to the UI.. as some scenarios are 3-1, 2-1, 1-3, 1-2, 3-3, etc....(Checks to Statements) - In essence, have the UI defined per the Condition and Media for each particular Entry... follow?
-n8D
I had thoughts on that, but couldn't get it working fast enough, and was also exploring this limitation.
OK.. so.. Ident all Records based on the TCR....some have 20, some 40, some 60...
and when using the Helpers...have a loop, loop through that record set, and supply the Duplicate or Incremented Data based on Record 1 ?
I was really hoping to confine the user more with regard to the UI.. as some scenarios are 3-1, 2-1, 1-3, 1-2, 3-3, etc....(Checks to Statements) - In essence, have the UI defined per the Condition and Media for each particular Entry... follow?
-n8D
Using an unbound form with the loop described will keep the form in the same format and allow (using subforms perhaps on a tab control to get additional space) to have all fields visible and editable.
It will however require more code as switching to a continuous form.
The button processing is rather straight forward and can also be achieved by manipulating the records in the table.
Just know too little to advise on that.
Now I'm off to bed as 0:00 is approaching...
Perhaps Jim can assist you further I'll check the responses tomorrow evening.
Nic;o)
It will however require more code as switching to a continuous form.
The button processing is rather straight forward and can also be achieved by manipulating the records in the table.
Just know too little to advise on that.
Now I'm off to bed as 0:00 is approaching...
Perhaps Jim can assist you further I'll check the responses tomorrow evening.
Nic;o)
ASKER
I am not familiar with how to do that. Could you provide a working sample of some form? Possibly within the sample DB I posted?
I'm really under the gun on this one.
-n8D
I'm really under the gun on this one.
-n8D
Follow the instructions from my previous comment:
1) Name the fields on your form rt1 to rt20 and dda1 to dda20, etc.
Now filling can be done using:
dim rs as dao.recordset
set rs = currentdb.openrecordset("s
while not rs.eof
Me.Controls("rt" & rs!sequence) = rs!rt
Me.Controls("dda" & rs!sequence) = rs!dda
rs.movenext
wend
This is for the 20 row situation, but can be used for 40 and 60 by adding new subforms for 20 additional rows.
Nic;o)
1) Name the fields on your form rt1 to rt20 and dda1 to dda20, etc.
Now filling can be done using:
dim rs as dao.recordset
set rs = currentdb.openrecordset("s
while not rs.eof
Me.Controls("rt" & rs!sequence) = rs!rt
Me.Controls("dda" & rs!sequence) = rs!dda
rs.movenext
wend
This is for the 20 row situation, but can be used for 40 and 60 by adding new subforms for 20 additional rows.
Nic;o)
ASKER
OK, ... I see your solution, however I don't see how that differs from what I have with my current bound form.
The original tables had rt1-20 - (remaining fields)...
The hang up is, 1 Condition can have 1, 2, or 3 rt'2, dda's, etc...it's not just MORE records.. its more nested. Do you follow?
CHK = 6 fields
STMNT = 2 fields
Possible Combinations:
1 CHK to 1 STMNT x 20 instances = 1Item * 8Fields/Item * 20Instances = 160 fields (MIN)
2 CHK to 1 STMNT x 20...
3 CHK to 1 STMNT x 20...
1 CHK to 2 STMNT x 20...
1 CHK to 3 STMNT x 20...
2 CHK to 2 STMNT x 20 instances = 2Items * 8Fields/Item * 20Instances = 320 fields..
3 CHK to 3 STMNT x 20 instances = 3Items * 8Fields/Item * 20Instances = 480 fields (MAX)
Do you see what I'm trying to solve? Have you looked at the DB I posted for a visual?
I really appreciate the help.. I need it :)
-n8D
The original tables had rt1-20 - (remaining fields)...
The hang up is, 1 Condition can have 1, 2, or 3 rt'2, dda's, etc...it's not just MORE records.. its more nested. Do you follow?
CHK = 6 fields
STMNT = 2 fields
Possible Combinations:
1 CHK to 1 STMNT x 20 instances = 1Item * 8Fields/Item * 20Instances = 160 fields (MIN)
2 CHK to 1 STMNT x 20...
3 CHK to 1 STMNT x 20...
1 CHK to 2 STMNT x 20...
1 CHK to 3 STMNT x 20...
2 CHK to 2 STMNT x 20 instances = 2Items * 8Fields/Item * 20Instances = 320 fields..
3 CHK to 3 STMNT x 20 instances = 3Items * 8Fields/Item * 20Instances = 480 fields (MAX)
Do you see what I'm trying to solve? Have you looked at the DB I posted for a visual?
I really appreciate the help.. I need it :)
-n8D
The "plain" solution for crossing the max is to use subforms.
Just create one for the (MIN) and re-use that form for the 320 and another time for the 480 solution.
First place a Tab control on your main form and make for the (MIN) only one tab visible, for the 320 two tabs and for the 480 all three.
Getting the idea ?
Nic;o)
Just create one for the (MIN) and re-use that form for the 320 and another time for the 480 solution.
First place a Tab control on your main form and make for the (MIN) only one tab visible, for the 320 two tabs and for the 480 all three.
Getting the idea ?
Nic;o)
ASKER
Have you DL'd the DB I provided?
Could you take a look at Form "EXP_messAround" ?
Open and scan through the records until the Media_Type_2 has a value.. you will notice the subForm is switched out with a Form of the proper format, and # of inputs...and again for Media_Type_3=TRUE.
Can you tell me how that is different from what you're talking about?
I had this working.. but ran into the Query limitation of 255. Otherwise, i'd be done !! :)
This is due monday.. so I need to finish it ASAP :|
-n8D
Could you take a look at Form "EXP_messAround" ?
Open and scan through the records until the Media_Type_2 has a value.. you will notice the subForm is switched out with a Form of the proper format, and # of inputs...and again for Media_Type_3=TRUE.
Can you tell me how that is different from what you're talking about?
I had this working.. but ran into the Query limitation of 255. Otherwise, i'd be done !! :)
This is due monday.. so I need to finish it ASAP :|
-n8D
You'll need to have three records for this, each bound to one of the subforms.
So I guess the solution will be to have an additional field to indicate the data for subform 1, 2 or 3.
This field will have to be part of the key (a so-called composite key) to make sure the rows remain unique.
This will be the "easiest" solution, although a change of a table design can be hard in other locations...
Don't forget that I didn't create the 255 limitation and that with a normalized table this limitation wouldn't have been a point of concern.
Nic;o)
So I guess the solution will be to have an additional field to indicate the data for subform 1, 2 or 3.
This field will have to be part of the key (a so-called composite key) to make sure the rows remain unique.
This will be the "easiest" solution, although a change of a table design can be hard in other locations...
Don't forget that I didn't create the 255 limitation and that with a normalized table this limitation wouldn't have been a point of concern.
Nic;o)
ASKER
True. The normalized table I have already, as well a NON-Normalized version (which was created first).
I follow you, but I am not entirely following how to create this relationship of the tables...
I mean, in essence, I still see the same 255 Field Query limitation occuring when I try and connect the subForms to the Tables. I guess I'm not seeing how you're piecing this solution together to the end that thwarts the 255 issue in a way I have not already attempted.
Help to understand?
-n8D
I follow you, but I am not entirely following how to create this relationship of the tables...
I mean, in essence, I still see the same 255 Field Query limitation occuring when I try and connect the subForms to the Tables. I guess I'm not seeing how you're piecing this solution together to the end that thwarts the 255 issue in a way I have not already attempted.
Help to understand?
-n8D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Number of fields in a recordset 255 "
No can do, you cann't, no way..