Access vba

Please help.

I'm trying to get selected items in a list box to a table. Sometimes I received error msg. "record is too large, error 3047". The table field name's data type is memo instead of text.

I thought the totle record size can't exceed 2000 byte, but not counting memo fields. How can I fix this problem.

Thank you in advance.

Memo fields do count they just don't count very much since only the address is stored and not the data itself. I thought it was 8 bytes, but I may be wrong on that.

As to how to fix the problem, that depends if the problem is consistent.

When you make a selection and get the error, if you make the same selection again do you get the same error?

If the answer is yes, then I suspect you will have to do some sort of redesign.

If the answer is no then I would suspect some sort of corruption and I would try making a copy of the table and see if that solves the issue.

First of all, thanks for you time and help.

Yes, the error is consistant. What kind of redisign you can think of?

Thanks again

Without knowing the current design or data requirements, it's a bit tough to be specific.

There are two possiblities that I can think of and it depends on how complete the data population is as to which is better.

Assuming all fields are always populated or populated the majority of the time you can break it up into multiple tables with a common key.

For example lets say you had product specifications for electronics since that is something I'm very familar with. Let's say there's about 100 specification that you need to track. I would break the specifications into 2-4 tables depending on the data etc.

I'd have a basic product table with the part number, name, model number, etc.

I'd have a table for compatability and interfaces. I'd have a table for operational specifications and one for physcial characteristics etc.

The basic product table is the main table and the other tables will have a foreign key into that table so they can all be linked together.

The other possible design is more flexible and does a better job of conserving space when fields are only populated sparsely.

With this design, again you have a main product table then you have 2 other tables.

The one table is a list of the specifications themselves (i.e. RPM, Height, Count USB ports, etc)

The second table has a foreign key to the product, a foreign key to the specification and then the actual value of the specification. This gives you a small table with LOTS of rows. While this is very flexible it can be a pain if you have lots of different types that need to be maintained for comparisons or queries.

Both of these require you to make an insert into the main table first and then retrieve the key before inserting into the other table(s).

The purpose I dump all selection of the list box to a table is just to display them, then copy/paste them to spreadsheet.

Since the selection of the list box is a variable, if the selection is small, then no error occors.

I'm not sure I get what you are trying to do.

so you don't keep the data in the table you delete it from the table once you are done with it?

Can you post the structure of the table, the code to populate it, and how you are displaying it (Is it a form, report, etc)?

I think there are some options that will work like having a table with an entry for each selection, but I'd need more info.