D
David Gartrell
Hi there
I wonder if someone could help me please.
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.
What I mean by this 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 whereas
others,
inexplicably, 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
automatically
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 i'm making sense and that someone will
have some useful suggestions.
All the best
David
I wonder if someone could help me please.
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.
What I mean by this 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 whereas
others,
inexplicably, 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
automatically
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 i'm making sense and that someone will
have some useful suggestions.
All the best
David