HELP! Need form to be based on many, many fields. >255

[momon]

Hi,

I have two tables ( each with about 200 fields ) that are related to each other through the primary key. Basically, combining all the fields of corresponding rows of these two tables constitutes a complete record (complete record has about 400 fields).

Now, I want to display all the information pertaining to a single record on a form. The fields on the form must be updatable. For example, when you bound a form to a table, changing the values on the form, changes the corresponding values in the table.

How would I do this?


Please Note:
1) I tried to do this using a query but failed since the maximum number of fields for a query is also 255 although access 2002 supports up to 32 tables.
2) I can't use DLookUp() since the resulting fields are non-updatable


Many Thanks in advance!

 

[FoxProProgrammer]

My first thought is why do you need to display all the data on a single form? Can't it be grouped in some logical manner and split up? You could even use a tab control to split the data up if you want it all on one form. dz

 

[blarson0]

Can you use DLookUp() to get all the fields on the form, and then have a submit button that will use SQL to put the info into the 2 different tables?

I have used this method a few times when a form is not updateable, and it works well.

HTH!

-Brad

 

[momon]

Ok... FoxPro, currently I am using tab control. I have a single form with two pages (Page1 and Page2). Unfortunately, since the tab control exists within the form, form limitations still seem to exist.

For example, the datasource of Page1 can't be QueryA while the datasource of Page2 is QueryB. QueryA and QueryB are different, of course.

In short, with tab control, regardless of the number of pages, the data source must be the same.

Blarson: I have DLookUp() get some of the fields on the form ( about half ~150 ). The problem is because I got the values for the fields with DLookUp(), they are not EDITABLE.
I guess that is the better word to use.

 

[thornmastr]

From what you are saying you have two tables. One record from table A plus one record from Table B equals one real record.

First suggestion is just one table. I doubt it will be bigger than 2GB, and even if it grows bigger you can build another table with the same description, put it in a new database, and link both databases so you don’t have to be concerned about the size.

Second suggestion is forget about bound forms.

Define a typedef structure for your record. Use VB to do the IO to load your structure.
Have a series of forms having a not inordinate number of controls available to your structure.
Walk the user through your forms. Move data between the form and the data structure. When all forms have been processed use VB to write back the elements of your structure to your table.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[blarson0]

Robert, because there are more then 255 fields, he can't put it all in one table. But I really like your idea of using unbound fields.

momon, do you know much DAO? You could have 400 unbound fields and fill them in with some DAO script or data structure like Robert suggusted.

Another alternative is to use subforms. These can be based on different tables then the main form, and you can put one in each tab control. It might be worth a try!

HTH!

-Brad

 

[thornmastr]

Hi Brad,

I’m glad you brought this up. It gives me a chance to rant. An access record is not limited to 255 fields no matter what MS tells you. A row must be able to fit into a 2k memory block (2048 bytes). The number of fields has no meaning.

There is a workaround for this as well. Think BLOB without Steve McQueen. Technically, Access got no blobs, but Access has MEMO fields. He could define a memo field and comma delimit his variables. His record could have 2 million variables. We don’t have to care anymore. What we do have to do is what we do best, code that works well and executes well in a multi-process environment.

WHEW. I apologize for the rant but it was fun. Seriously, especially in light of the fact that he probably will not be able to use bound forms and is going to have to use at the least a simplified type of class structure, he may as well take the opportunity to really get a glimpse of how powerful a tool Access can be.

Just my $0.02 worth.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[momon]

Thanks Brad, Thanks Robert.
Brad, I guess the two methods you mentioned could work.

Does anyone have any specifics for how I would implement the DAO script? I am not really good at DAO.

Thanks in advance.

 

[blarson0]

manon, I'm really not very good with DAO yet either, but I am learning. Here is something you can play around with, hopefully someone with more knowlege about DAO can give you a better script.

Private Sub getData(recordnum as integer)

Dim db As Database
Dim rst1, rst2 As Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("Table 1&quot
Set rst2 = db.OpenRecordset("Table 2&quot
rst1.Move recordnum
rst2.Move recordnum

lblText1 = rst1!Field1
lblText2 = rst1!Field2
lblText3 = rst1!Field3
lblText4 = rst2!Field1
lblText5 = rst2!Field2

Me.Refresh

End Sub


This isn't that great of a solution, because you will have to type out all of your field and text label names. Maybe there is a way you could do it with a loop?


Robert, are you sure about that 255 field limit? I've never played around with it, but it is what I've always understood as the limit.
The memo field idea is pretty inventive, but not very practical. Doing searches, queries, filters, ordering data, etc... would become almost impossible.

HTH!

-Brad

 

[thornmastr]

Hi Brad,

A couple of suggestions here.

He can loop through the control collection and stuff both the control name and control value into a variant two dimensional array. Then he can match the variable name to the variable in his structure.

Also, remember, in this particular instance you are not going to search on memo fields or group them. All you want them for is a collection of something else. It is very easy, once you have the record, to use string functions since you are dealing with one very long delimited string, and pull all the values from the memo and then do whatever you want with them.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com