Record is Too Large

[safril]

Hi, can anyone help me to fix the problem? When I open "frm_Banding" form and fill some value, it will be cause error "Record is too large". Am I missed something?

 

[mike60smart]

Hi
Your table structures are all wrong.

You are trying to use Short Text as Foreign Keys

Also you have repeating groups in tbl_Banding, tbl_PK and tbl_Kasasi

 

[mike60smart]

Hi Safril
Can you explain what the purpose of the database is?

 

[mike60smart]

You also have multiple Lookup Fields in your tables, which is a NO NO in Access.

See this explanation of why you should not use them.

http://access.mvps.org/Access/lookupfields.htm

 

[CJ_London]

If all the relationships are 1-1, why use separate tables? - I can see that you have a lot of fields but don't think it totals more than 255 which is the limit for fields in a table and one of the reasons for doing so.

And if there is a good reason for keeping separate, you should be linking on the ID, not your text field.

Either way, in addition to the 255 field restriction there is also a limit on total record size of around 4000 bytes (the size of a block on your disk). Your text fields all appear to be 255 chars length so you can only have around 15 fields of this size, let alone the other fields. So consider reducing them to a size more appropriate to the data (perhaps 14 for your NRP_Terdakwa for example). Or use a more efficient data type such as a single or double for numeric fields - a 14 character code will require 24 bytes as text, 4 as a single or 8 for a double. OK, you lose the preceding zero if there is one, but this can be overcome with formatting. See this link for data sizes https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/data-type-summary.

Size also matters for efficient performance. Using a text field to link on means your need two indexes, one in each table. Per the above example, this means an additional 24 bytes for each index. The way indexing works, it picks up a block of index data from the disk and searches - if you are using text, it will pick up around 150 indexes (indexes also include a pointer), using a single it will pick up around 1000 indexes - simplistically around 6-7 times faster. And another reason for linking on ID, not NRP_Terdakwa

I would also lose the calculated fields - they are a convenience with penalties - penalties which you are now incurring. Calculate when you need to, either in a query or an unbound control on a form. Remember, tables are for storing data, not providing a view for users.

But I think you will also have an issue in that forms should be based on one table, not multiple tables. Redesign your form so that the linked tables appear in their own subforms - and ensure the subform linkchild/master properties are populated

Otherwise you will need lots of code to ensure the linking values are completed before you enter values in the sub tables - and even then may not be successful. If you want to try this route, you will need to bring through (for example) the NRP_Terdakwa field from tblBanding and in code, populate it in the after update event of the NRP_Terdakwa control or perhaps the form beforeupdate event

 

[mike60smart]

In the attached, which still requires a lot of work, I have modified the table structures for tbl_Terdakwa, tbl_PH, tbl_PP & tbl_Oditur.

The Form that now opens at startup allows you to add data for all of the above tables.

In your original version you had the Form opening with the Record Source set to a Query which included all of these tables.

Your Main Form should be bound to the underlying Table ie tbl_Terdakwa and then you create Subforms to deal with the related data for PH, PP & Oditur.

 

[safril]

Hi Safril
Can you explain what the purpose of the database is?

Hi, thanks for your reply. I'm a newbie in Microsoft Access. I want to create a simple database system that integrates with Microsoft Word via Mail Merge. This database is used in court offices.

Details:

tbl_Terdakwa	contains Defendant's data information
tbl_PP	contains Clerk data information
tbl_Oditur	contains Prosecutor's data information
tbl_PH	contains legal advisory data information
tbl_Banding	contains information on the defendant's data at the Appellate Court level
tbl_Kasasi	contains information on the Defendant's data at the Cassation level
tbl_PK	contains the Defendant's data information at the review level.

Each defendant can choose 1 Clerk, 1 Prosecutor, and 1 Legal Counsel.
1 defendant can also make an appeal, cassation, and review.

But I haven't created a form/table to add Clerk, Prosecutor/Attorney, and Legal Counsel because I'm confused about how to create and connect them in the relation between tables.

 

[mike60smart]

Hi
Can you give me an example of the data you would enter into the following tables:-

tbl_PH
tbl_Banding
tbl_Kasasi

In all of the above table we need to get rid of ALL of the calculated fields.