Database corruption maybe?

[David Gartrell]

Hi there

I have a product fulfilment database (Access 2000) which has a very annoying
problem in that sometimes it doesn't always record an order correctly. I
can enter 10 orders for the same person, products, quantities etc etc, in
fact i make sure everything is the same. However some of these orders will
be recorded correctly on the tables and some won't. The code for recording
the orders onto the tables is spot on - in fact I have a similar database
for another client which uses exactly the same VB code and it works
perfectly all the time. It's so frustrating because it has to work and yet
(just now and again) it doesn't!!!!!!

Basically everything checks out, and yet for some unfathomable reason some
orders are not recorded correctly!

In thr past I have had to use the repair function on this database once or
twice and i'm wondering if this may be playing a part here. Is there anyway
to tell whether there is still any hidden corruption lurking anywhere?

Hope someone can help prevent me from tearing all my hair out!

Thanks

David.

 

[Joseph Meehan]

Hi there

I have a product fulfilment database (Access 2000) which has a very
annoying problem in that sometimes it doesn't always record an order
correctly. I can enter 10 orders for the same person, products,
quantities etc etc, in fact i make sure everything is the same. However
some of these orders will be recorded correctly on the tables
and some won't. The code for recording the orders onto the tables is
spot on - in fact I have a similar database for another client which
uses exactly the same VB code and it works perfectly all the time. It's so
frustrating because it has to work and yet (just now and
again) it doesn't!!!!!!
Basically everything checks out, and yet for some unfathomable reason
some orders are not recorded correctly!

In thr past I have had to use the repair function on this database once or
twice and i'm wondering if this may be playing a part here. Is there
anyway to tell whether there is still any hidden corruption
lurking anywhere?
Hope someone can help prevent me from tearing all my hair out!

Thanks

David.

It might help if you can post the code and or tell us more about "it
doesn't always record an order correctly" What exactly is incorrect? Is
there any pattern to the problem?

 

[David Gartrell]

Thanks for responding Joseph,

What I mean is that an order could be entered on to a form for say 3
products. When the 'complete order' button is clicked the products entered
should be transferred to the relevent table by means of some VB code (see
below). However sometimes only the last product keyed actually makes it on
to the table, whereas at other times the whole order makes it on to the
table perfectly. As a test i've keyed exactly the same order in for the same
person several times. I've chosen the same products, entered them in the
same sequence, even chosen the same payment method. When i've looked at the
results some of the orders have appeared on the table correctly and whereas,
inexplicably, some have not.

Here is the code that adds the individual products to the order table

DoCmd.GoToRecord , , acNewRec
For loop1 = 1 To 40
If Me("qty" + Format$(loop1)) > 0 Then
det1 = [Forms]![order header1]![Order_number]
det2 = Me("prodord" + Format$(loop1))
det3 = Me("prodord" + Format$(loop1)).Column(1)
det4 = Me("qty" + Format$(loop1))
det5 = Me("retailvalue" + Format$(loop1))
If Me("sub" + Format$(loop1)).Value = True Then detsub.Value = True
DoCmd.GoToRecord , , acNewRec
End If
Next

to help here's a few details about the code and the form:

1. The order form comprises of a main form and a subform. The main form
contain information about total items, total weight, delivery address,
payment details etc and is connected to an 'order header' table. The
subform contains rows for recording individual items on the order and this
is linked to an 'order detail' table. The textboxes/controls on the subform
are as follows:

40 unbound combo boxes 'prodord1' to 'prodord40'. these are fed by the
products table and contain the product code, description, price etc. the
user selects one product from each combo box
40 unbound texboxes 'qty1' to 'qty40'. the user enters how many of each
item are required into these boxes
40 unbound textboxes retailvalue1 to retailvalue40. these are populated
once the user has selected a product and entered a quantity
det1 to det 5 are invisible textboxes linked to fields in the table. Det1
= ordernumber (taken from the main form), det2 = prodcode, Det3 = proddesc,
det4 = qty, det5 = retailprice

The subform contains a 'complete order' button. Once this is clicked the
code above takes the first product, quantity & retail entered and copies
this to the det1 to det5 textboxes i.e. the table. it then creates a new
entry on the table and then goes back round the loop and gets the next
product, quantity & retail and so on etc until the whole order has been
copied to the table. The result is that a 'one to many' relationship is
established between the 'order header' table and the 'order detail' table

The thing is, I know that all the above is correct and works because i've
got 18 databases for different clients and each one is basically identical
apart from different products etc, and also all the other databases work
perfectly. To be honest i'm totally stumped!

Basically, as i've said, it has to work but it doesn't, at least not all the
time. This is why i'm thinking that some obscure database corruption may be
at play.

Thanks for your time. Hopefully someone will have some useful suggestions.

All the best

David