strSql INSERT INTO / Values - creates 2 records (1 Viewer)

[flect]

I've got my db for generating basic invoices, however the initial design was extremely rushed and consequently it's extremely inefficient.

Basically, when a new order is created; a query inserts a record for EVERY product in tblProducts to tblOrderDetails and the form just updates the quantity in tblOrderDetails and then the report only displays records with qty >1

The result of this is an average of 600 superfluous records per order in tblOrderDetails - a total of 150000+ more than needed.

Now that i've got more time I'm trying to redesign the form.

I've managed to get it to create a record in tblOrderDetails only when the qty is updated on the form using the following:

Private Sub txtQty_AfterUpdate()
Dim strSql As String
    If IsNull(Me.txtQty) Then
            strSql = "DELETE tblOrderdetails.*" & _
                     "FROM tblOrderdetails " & _
                     "WHERE tblOrderdetails.Product = " & Me.stampAutoID & ";"
                          Else
            strSql = "INSERT INTO tblOrderDetails (OrderID, Product, Qty) " & _
                    " Values (" & Me.Parent.OrderId & "," & Me.stampAutoID & " , " & Me.txtQty & ");"
    End If
CurrentDb.Execute strSql
End Sub

the only problem is that this creates 2 records - one of them with a null OrderID

results being:

DetailID OrderId Product Qty ToFollow ToFollowQty
151101 5582 14 No
151102 350 5582 14 No

everything seems straightforward enough - i can't figure out why this is happening.

any ideas?

 

[boblarson]

Do you have any code that might be making the After Update event fire again? Do you happen to be using a bound or unbound form?

 

[flect]

I've checked into it - the After Update isn't firing twice -i think i've traced the problem back to the form.

which is based on the query

so txtQTY on the form is bound to tblOrderdetails.Qty

as soon as I enter a value it creates a record on tblOrderDetails with no OrderID and then the strSql creates a duplicate (but correct) record.

if i make txtQTY unbound I get the following cascade:

i'm not sure where to go from here.

 

[boblarson]

You should just let the bound form do the work. You probably have to link the correct Master/Child links in to get the right OrderID added, but then you don't need the insert query.

 

[flect]

thanks bob - i think you're right - master/child hasn't even been set - i'll have another look into it.

cheers!

 

[flect]

Hi again Bob

I've played with it a bit, but now i have a new problem

Once the master/child relationship is set up, it only displays records on the subform with a Qty on tblOrderdetails.

So a new order displays no records whatsoever.

If i remove the master/child then it displays the correct records, but i'm back to the same problem with the txtQTY box.

I've uploaded a sample of the db (without master/child or orderID on the subform) if you or anyone else wants to have a look.

http://www.maxstern.com.au/db/dev_invoicesys.mdb

My bottom line goal is to display the records from (tbl)Products; have txtQty create a record on tblOrderdetails (if one doesn't already exist), display the value tblOrderdetails.Qty if the record exists, and then delete the record if the qty is null.

 

[flect]

:::Update:::

Success!!!

I had to go back and rethink a few things.

I had to create a new query for the form source object with a subquery for the quantity to display correctly.

Unfortunately I was still having the same problem with the double records so I decided to disable the SQL INSERT/Values code.

Was also still having the problem of the bound txtQty not adding the OrderID to tblOrderDetails

so; if you can't beat 'em - join 'em

I added some code to go in and add the correct OrderID to anything null,

Ditto to delete the record if the quantity was null.

Not exactly how I thought it was going to work, but the bottom line is that it does work

Private Sub Form_AfterUpdate()
    Dim strSql As String
    Dim strSqlDel As String

        strSql = "UPDATE tblOrderdetails SET tblOrderdetails.OrderId ='" & [Forms]![frmnavigation]![masterfilter] & "'" & vbCrLf & _
                    "WHERE (tblOrderdetails.OrderId) Is Null;"
    
        strSqlDel = "DELETE tblOrderdetails.*, tblOrderdetails.Qty " & vbCrLf & _
                    "FROM tblOrderdetails " & vbCrLf & _
                    "WHERE (tblOrderdetails.Qty) Is Null;"

    CurrentDb.Execute strSql
    CurrentDb.Execute strSqlDel
End Sub

I'm pretty happy with the result as this particular problem has had me stumped for weeks and weeks