Empty line inserted in database

[jimgros]

Hi,



I have a sales form, and a sales_lines form.

When I enter the details of a sale I can click on the "Add sales lines" button which opens the sales_lines form (the sale_ID is passed on to the sales_line form). In sales_lines form I can edit all the line items for that sale and then I perform a pretty straight forward insert SQL.

This is the only place where I have an Insert sql line (not very far in development...). I did not remove the alert for sql commands on my table so I get the popup saying "We will now append 1 row into sales_lines table". However after I create a sale with for example 3 sales_lines, and I get 3 popups each saying "we will append 1 row" I always end up with 4 lines in my sales_lines table. The first one is empty and then the 3 others are the ones I entered.

Does anybody know where that might come from?

[NoellaG]

Hi,

looks like a very difficult way to enter 1 to many data. Can't you use a form/subform?

gr
NG

[rpeare]

I agree with Noella, why aren't you using a form/subform pair, you're making it much harder on yourself than it needs to be doing it this way.

[jimgros]

That's how i started out but it was getting very busy on the screen because there are quite a few fields to fill in/choose per item line.

If there's really no easy explanation for my issue, i guess going back to form/subform would be a solution.

Thanks for the suggestion.

[rpeare]

If you can post a sample database with garbage data and an explanation of exactly what you're doing to create the error we can take a look. The easiest thing to do though would be to make one of the fields in your table required, when you try to append an empty record that 'blank' record will just not be added because it doesn't meet the criteria of a complete record. Not a good practice though it's better to try and find out why it's happening and fix it.

[jimgros]

Hi everyone,

Thanks for your help on this matter.
First point I want to make is that I followed your advise and created one big sales form with a subform for sales lines. This way no need to use arguments and no big complications to be foreseen. The test that i've done are the result of this new form, not the old form
2nd point: the line is not totally empty, since i have an autonumber for sales_line table.
In order to explain you how all this works I decided to be exhaustive. Perhaps I will be too exhaustive but it might save some questions in the future.

Context: we are a wholesale company. We sell items 2 ways:
1. per unit based on a standard price list and discount
2. bulk based on negotiated price
price are based on a weight unit, which is then multiplied by the the total sale wiehgt to get the total value of each sale line.

form description:
The sales form contains all details related to the sales (when purchased, client id, payment details) and the sale_line subform.
The sale_line subform
- has on top of it a combo box for selection of the stock item
- then it contains a subform (called here "Stockdetails", linked to the sales_line form with stock_id) where we look at the stock item more in details to see how much units or weight we still have in stock and to confirm that we are talking about the right stock item
- then it contains another subform (Called here "listprice", which performs a Dlookup into the standard table based on weights and other criteria) where the standard listed price appears for items sold individually
- then it contains a few bound fields for discount of items sold individually or bulk wieght and bulk price for items sold in bulk.
- Then it has a add sales line button.

Result:
I've attached in zip the excel table of what is contained after 2 test sales.
As you can see each time i do a sale I've only purposely added 3 lines, however for each sale, 4 lines appear in my sales_line table. This first one is empty and is the "ghost line" the others all contain the right data based on what I entered. I have warnings each time I add a line to my table and this warning only appears 3 times for each sale, each time telling me that I'm adding 1 row.

Question:
Perhaps the empty sales line is created when I create a new sales record? (to create a new sale record I simply click on the "new" button on the access toolbar). Is that where the problem is? How can I avoid that?

Code: only the relevant code is listed below. No code associated elsewhere except making fields visisble or invisible based on bulk purchase or individual purchase

Please note I'm a beginner at this, so if I made an obvious mistake, please notify me. Please also note that for your convenience I've changed the names to make it clearer for you, so please don't pay attention to the potential spelling mistakes. The code works fine except for that extra line that appears in my sales_lines table.

Private Sub addsalesline_Click()
Dim sql As String '' sql String
Dim unitprice As Double '' price after discount for individual sales
Dim totalsalesvalue As Double ''price per weight * weight

unitprice = Nz(Me.listprice.Form!listprice, 0) * (1 + (Nz(Me.Discount, 0)) / 100)

If Me.Stockdetails.Form!unit_or_bulk = "Bulk" Then
totalsalesvalue = bulk_price * weight
End If
If Me.Stockdetails.Form!unit_or_bulk = "unit" Then
totalsalesvalue = unitprice * Me.Stockdetails.Form!weight
Me.weight = Me.Stockdetails.Form!weight
Me.Nr_units = 1
End If

sql = "Insert into Sales_lines " & _
"(Sale_ID, " & _
"Stock_ID, " & _
"Weight, " & _
"Nr_units, " & _
"Listprice, " & _
"Discount, " & _
"listdiscountprice, " & _
"bulkprice, " & _
"Total_line_value)" & _
" Values (" & Sale_ID & "," & Stock_ID & "," & Nz(weight, 0) & "," & Nz(Nr_units, 0) & "," & Nz(Me.listprice.Form!listprice, 0) & "," & Nz(Discount, 0) & "," & unitprice & "," & Nz(bulkprice, 0) & "," & totalsalesvalue & ");"

DoCmd.RunSQL sql
sql = ""
Me.Stock_ID = ""
Me.weight = ""
Me.Nr_units = ""
Me.bulkprice = ""
Me.Discount = ""
Me.listprice.Visible = True
Me.discount.Visible = True
Me.weight.Visible = True
Me.bulkprice.Visible = True
Me.Nr_units.Visible = True
End Sub

[rpeare]

I don't see anything glaring with your code, but the reason I asked for a sample of your database is that I suspect whatever code you've got running on your form is generating a blank line on it's own. Here's what I would do, create an order, go to the subform where you enter what they bought, but do not enter anything, but click your add record button. Then go to your table and see if a blank line exists. I suspect it will.

As I said in my previous post you can prevent records from being added to your database by making a field REQUIRED (look at the field properties of your table) if you make, say, your item field required then no blank records will be added. But again, the only way to know for sure what the real problem is would be to look at a sample of your database.

[jimgros]

solved, however with some tweaks.

Interesting afternoon trying to solve this thing.

For those of you who want to know how i solved it: I think it had to do with 2 things:

1. I was using a autonumber for my sale_lines which apprently wasnt repsonding great to my code. If I was creating a new sale and the autonumber of my first sale_line was 23, then this 23 would not be the first record to appear, each time my code would be invoked it would start recording at nr 24 and the sale_line_id 23 was left untouched. I'm not sure why. However, the sales_lines_id is not very useful, so I just got rid of it.

2. I learned today that when you close a form Access automatically saves the record that you were busy on. Took my a while to figure out how to get rid of that. So I used the me.undo command on my sale_line subform on the "Before Update" event. that seems to work. I also created a "Close" button on my sale form that also performs an undo, just in case.

The combination of the 2 points above is what created that empty line with a sales_line_id which made no sense. The empty line was alwasy with the lowest sales_line_id possible for the new sales I was creating.

I don't know if you follow my drift (after 1 afternoon of frustration, I can't really make much sense), anyhow, i thank you for wanting to help. It's saturday evening on this continent, time to go drink.