#ERROR - Invalid argument!?

[Stevehewitt]

Hi Guys,

I have never come across this before I honestly have no idea what to do.

We have a table called 'tblSales' where each record has a unique reference number.
I recently looked at this table after users were saying there are problems and there is one record where every field is:

#Error

Tick boxes are in triple state. (neutral)

When that table is open I contstantly get 'Invalid argument.' Error 3001.

This record cannot be selected or deleted anyway. (Either normal deletion, delete query, direct SQL)

Can someone please help me!!!

Thanks,


Steve.

 

[lupins46]

The record is corrupt.
You can try the Compact/Repair tool on the Tools menu to try to delete it.

But make sure you have a backup before you start.

The most likely causes are either someone switched off without shutting dowm properly or a network transmission problem.

 

[Stevehewitt]

I've tried Compact / Repair without any luck, although thanks for the prompt reply.

I've copied all the data from the tables to excel excluding the corupt the one and copied the structure from the tblSales to tblSalesNew and pasted the records back in.
I then renamed tblSales to tblSalesOld and tblSalesNew to tblSales. This seems to work OK but it was a bit of headache. (We also use AutoNumbers for a reference)

Thanks for your help.

Steve.

 

[dhookom]

Steve,
You solution is the only method I have found. I also encourage you to use the autonumber totally behind-the-scenes so that no one ever sees it.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Stevehewitt]

It's only used for a reference number, but I'm thinking of using a VBA code based system instead so that if these issues do happen again I won't be playing with Append queries to get to the right number for the reference field.

All fun isn't it!?!

Thanks for your help,


Steve.

 

[willir]

Steve

Do you know what caused the corruption??

Further discussion on the autonumber...

I recently looked at this table after users were saying there are problems...

Here is an experiement...
Open up your Sales form on one computer. Start entering new data. Do not commit the record. Take note of the autonumber.

Now, go to another workstation, and repeat the process.

If you are using sequential autonumbers, chances are that the both workstations are attempting to use the same number. Autonumber is a handy tool, but with a multiuser system, it is dangerous!

Review MichaelRed's FAQ on the use of autonumber...

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

This may be what cause your corruption.

Resolutions...
- Instead of sequential, use "random" autonumbers. Be warned, this gets wierd - some of the numbers will be negative. (So it may become necessary to hide the number so as to avoid confusion as suggested by Duane.) (In fact, I may be worng, but I suspected you are not using the random autonumber since it appears you are not hiding this field. And rest assured, sequential or random, end users can not change this number.)
- You can also use the GUID number which is 16 bits large.
- Create your own numbering system with a centralized module - sequential, date + time branding, etc.

Richard

 

[Stevehewitt]

Hi,

Thanks for your reply. I am serious considoring doing this as I had a call from them today saying that the numbers are not correct. This poses a major issue as the autonumber is saved by the main form into a subform which contains specification details of the order (units, whereas the main sales table has the customer ID, delivery options, date, salesman etc.)
Now of course they don't link up, meaning that there are no specifications for the sales which in turns means that the management reports showing the costings and financial details will not be able to work out the details (total value are added up on the fly in the report using hidden sub-reports).
Messy!

Thanks,

Steve.