update form based on multiple tables

[kalulu1]

I've programmed a complex database for clinic follow-ups; it's all done. There are 9 forms, some are used only once and others have as many records as the number of clinic visits done by the patient. Some forms are fairly simple with maybe 120 fields, but my biggest issue is with the forms that have some 300 or nearly 500 fields. I tend to keep my tables with 125 fields maximum, to keep it fairly manageable on smaller computers that are equipped with little RAM.

The larger forms on this database have 4 tables (Initial History and Physical) or 2 tables (Follow-Ups), and when i need to update/add a record, some portions of the form (underlying tables 3 and 4) don't allow me to tick any field and thus to update the record.

If anyone out there knows what i'm talking about, i can certainly send my database. What I'd like to know is if you can resolve this as i have a deadline to meet, but also to explain how you resolved it so that I can also learn from my mistakes.

Thanks all the way from Zambia !

 

[jdraw]

I tend to keep my tables with 125 fields maximum

I don't know about your forms, but the comment above makes me wonder how normalized your table structures are.

 

[vbaInet]

, but my biggest issue is with the forms that have some 300 or nearly 500 fields.

This even is more worrying jd

Quite likely signs of unnormalization.

 

[missinglinq]

This is scary to even think about, but are these forms Unbound? Recordsets are limited to 255 fields, meaning that this is the limit for Bound forms as well. Managing Unbound forms with 300-500 fields would truly be a nightmare!

Linq ;0)>

 

[kalulu1]

I don't know about your forms, but the comment above makes me wonder how normalized your table structures are.

i followed the rules of uniqueness and keys (primary and foreign keys), and the database has its optimal structure and is close to 3rd nominal form. The way all tables are linked is of the following:

The main interface has an unbound combobox with AfterUpdate and NotInList events that activate/create records in tblPatients when the user creates a new PATID (patient ID) value. This assigns a value to a field called STUDYID (primary key, autonumber, new values: random). The same key STUDYID is a foreign key (number, long integer) in every other table that have their own primary key (autonumber, new values: random).

Other tables lile FollowUp1a and FollowUp1b have fields FOLLOWID (pk, autonumber) and STUDYID (number, long integer). They are linked to tblPatients on a one-to-many relationship with referal integrity.

Does that clear out your doubts on your question about normalisation?

 

[kalulu1]

This even is more worrying jd

Quite likely signs of unnormalization.

When i wrote earlier that "...forms that have 300 and some others 500 fields", i was referring about the paper forms used at the clinics. Every principal investigator comes with their own research questions, and this one in particular ended up with 4 to 7 pages of questions in a single paper form. Put in the equation that every page has alot of information to be ticked, and one can easily see how many fields get generated from that.

 

[vbaInet]

Building a database to replace a paper form with 300+ texboxes would normally require at least 8 tables. If your database is near 3rd N-F then you need to normalize further.

It would be better to see a screenshot of your Relationships diagram but for now tell us if this is a questionnaire form you're replacing with a database solution?

 

[kalulu1]

This is scary to even think about, but are these forms Unbound? Recordsets are limited to 255 fields, meaning that this is the limit for Bound forms as well. Managing Unbound forms with 300-500 fields would truly be a nightmare!

Linq ;0)>

There has been some confusion from start, and i was meant to say "300-500 fields on the paper form, with a result of building up to 4 tables to accomodate it. Please have a look at the attachment CEMART-v2011.ZIP and you'll soon realize what i mean.

frmMainMenu is the starting point: say you create a new patient record number 5040-200-12345-5, then click on "Initial History and Physical". You'll see that Parts 3 and 4 don't allow to update the records. This is where my problem is. Greatly appreciated from you all !

 

[kalulu1]

Building a database to replace a paper form with 300+ texboxes would normally require at least 8 tables. If your database is near 3rd N-F then you need to normalize further.

It would be better to see a screenshot of your Relationships diagram but for now tell us if this is a questionnaire form you're replacing with a database solution?

Enclosed a snapshot of relationship. Yes, this exercise is to capture elctronically a paper questionnaire. There are actually 8 questionnaires: Initial History, Follow-Up, Missed Visit, Labs, Adverse Event Log, Serious Adverse Events, Discontinuation, and Death.

 

[kalulu1]

Building a database to replace a paper form with 300+ texboxes would normally require at least 8 tables. If your database is near 3rd N-F then you need to normalize further.

It would be better to see a screenshot of your Relationships diagram but for now tell us if this is a questionnaire form you're replacing with a database solution?

Have u had a better look since i uploaded the database?

 

[kalulu1]

This is scary to even think about, but are these forms Unbound? Recordsets are limited to 255 fields, meaning that this is the limit for Bound forms as well. Managing Unbound forms with 300-500 fields would truly be a nightmare!

Linq ;0)>

Have u had a better look since i uploaded the database?

 

[vbaInet]

You don't need to post the same message twice Kalulu1. It is a community so you can address us in one message (well, I can't speak for the others but for me, I don't mind ).

It seems you need to amend the structure and relationship of your tables.

 

[kalulu1]

You don't need to post the same message twice Kalulu1. It is a community so you can address us in one message (well, I can't speak for the others but for me, I don't mind ).

It seems you need to amend the structure and relationship of your tables.

I've looked over and over, and I can't find what triggers tblPatients and IPH1a/IHP2a to work, and not the same tblPatients with IHP1b/IHP2b.

Would you mind having a look and tell me where the relationships go wrong. The structures look good to my opinion.

I'd appreciate any help. At this stage, I have nobody to go to for support.

 

[dardisco]

Hi,
I'm working on a similar project and would be willing to look over your design.
I'm having trouble with the same issue, although my tables are somewhat smaller. Contact me through this site if you like. Chris

 

[boblarson]

Hi,
I'm working on a similar project and would be willing to look over your design.

That is a 10 month old thread. Seems like they might have worked it out by now.