Solved got Error in last Row of sub_form

[FahadTiger]

Hi Expert
when Im trying to renumber of sub_form for every new invoice ..i used this for control source of unbound textbox
=IIf(IsNull([ItemCode]);Null;Nz(DCount("ItemCode";"SalesOrderDetailsT";"SaleID=" & [SaleID] & "AND SaleOrder <" & [SaleOrder]);0)+1)
its work good..but I Get Error at the last row

 

[Gasman]

Check to see if you are on a new record?

 

[FahadTiger]

Check to see if you are on a new record?

the Error at last Row ...when No rcord

 

[theDBguy]

This previous discussion might also apply.

Value not showing on Main Form

I have a very strange problem. Background: I have a main form on based on a table. I have an unbound combo search field on this form. After update I select the correct record from the table to display (recordset bookmark). I have sub form on the main form containing transactions pertaining to...

www.access-programmers.co.uk

 

[The_Doc_Man]

You are clearly on a new record in the last row of your display in post #1 of this thread. If so, your DCount criteria include doing anything mathematical, either summing or comparing) against a NULL (guessing either [SalesOrderDetails].[SalesID] or [SalesOrderDetails].[SaleOrder]). Doing math against a NULL is bad. You get errors.

So the question is why that last record exists at all, and why those fields (which I'm guessing would be linking fields) aren't autoloaded. But you actually gave us a hint: "i used this for control source of unbound textbox" - and if it is unbound, it has nothing automatically linking it to anything. Your design essentially automatically creates an orphaned record which leads to the nulls being where you don't want them.

 

[Pat Hartman]

The code is incorrect - it uses DCount() instead of DMax() and it is in the wrong place. Put the following code into the form's BeforeInsert event. This event runs as soon as the user starts typing into the subform.

Me.ItemCode = Nz(DMax("ItemCode";"SalesOrderDetailsT";"SaleID=" & Me.SaleID ;0)+1)

I don't know why SalesOrder is in the criteria at all. The criteria should be ONLY the ID of the parent record.

Using DMax() rather than DCount() allows for potentially deleting rows. So, if you add, 1,2,3 and then delete 2, Your code would attempt to add 3 again rather than 4.

Other thoughts. What is the purpose of the item numbers? Do you want to renumber them to eliminate gaps if you delete one? Using my suggested technique, if you cancel an add, you don't burn an item number as you do with autonumbers. So, if you are adding #4 but do not complete it and end up cancelling it but come back later, then the next item number will still be 4.

 

[FahadTiger]

The code is incorrect - it uses DCount() instead of DMax() and it is in the wrong place. Put the following code into the form's BeforeInsert event. This event runs as soon as the user starts typing into the subform.

Me.ItemCode = Nz(DMax("ItemCode";"SalesOrderDetailsT";"SaleID=" & Me.SaleID ;0)+1)

I don't know why SalesOrder is in the criteria at all. The criteria should be ONLY the ID of the parent record.

Using DMax() rather than DCount() allows for potentially deleting rows. So, if you add, 1,2,3 and then delete 2, Your code would attempt to add 3 again rather than 4.

Other thoughts. What is the purpose of the item numbers? Do you want to renumber them to eliminate gaps if you delete one? Using my suggested technique, if you cancel an add, you don't burn an item number as you do with autonumbers. So, if you are adding #4 but do not complete it and end up cancelling it but come back later, then the next item number will still be 4.

Sir part hartman ... All I want is a new numbering for each sub-invoice starting from the one

 

[Eugene-LS]

I Get Error at the last row

Try this expression:

=IIf(IsNull([SaleOrder]);Null;Nz(DCount("*";"SalesOrderDetailsT";"SaleID=" & [SaleID] & "AND SaleOrder <" & [SaleOrder]);0)+1)

 

[Pat Hartman]

All I want is a new numbering for each sub-invoice starting from the one

Did you try my suggestion?

 

[FahadTiger]

Try this expression:

=IIf(IsNull([SaleOrder]);Null;Nz(DCount("*";"SalesOrderDetailsT";"SaleID=" & [SaleID] & "AND SaleOrder <" & [SaleOrder]);0)+1)

 

[FahadTiger]

Try this expression:

=IIf(IsNull([SaleOrder]);Null;Nz(DCount("*";"SalesOrderDetailsT";"SaleID=" & [SaleID] & "AND SaleOrder <" & [SaleOrder]);0)+1)

its perfect ...thank you

 

[FahadTiger]

Did you try my suggestion?

i did...but its not working...thank you
It was my mistake because I used a field equal to zero and not Null

 

[Pat Hartman]

i did...but its not working...thank you
It was my mistake because I used a field equal to zero and not Null

You did NOT try my suggestion since it does not need to include the IsNull() function since the code ONLY runs when it is supposed to. It only runs when you are creating a new record. That is the power of understanding what form events are supposed to be used for.

Since you did not use my suggestion, you probably also stuck with the dangerous DCount() rather than the safe DMax() as well.

For those of you who don't understand the danger of DCount(), I sure hope, you have covered ALL your bases and never, ever, accidentally delete a row. Even if you think you are not allowing a delete, why would you want to find out that there is an error by having an insert fail because a duplicate sequence was generated (unless of course you forgot to also set a multi-field unique index on SaleID + ItemNum by using the indexes dialog). Sure makes you look stupid to the user. Think of using DMax() for this purpose rather than DCount() as defensive driving or a safety play if you play bridge. You have a 100% option or an option that might fail. Why would you ever choose the option that might fail?

 

[FahadTiger]

You did NOT try my suggestion since it does not need to include the IsNull() function since the code ONLY runs when it is supposed to. It only runs when you are creating a new record. That is the power of understanding what form events are supposed to be used for.

Since you did not use my suggestion, you probably also stuck with the dangerous DCount() rather than the safe DMax() as well.

For those of you who don't understand the danger of DCount(), I sure hope, you have covered ALL your bases and never, ever, accidentally delete a row. Even if you think you are not allowing a delete, why would you want to find out that there is an error by having an insert fail because a duplicate sequence was generated (unless of course you forgot to also set a multi-field unique index on SaleID + ItemNum by using the indexes dialog). Sure makes you look stupid to the user. Think of using DMax() for this purpose rather than DCount() as defensive driving or a safety play if you play bridge. You have a 100% option or an option that might fail. Why would you ever choose the option that might fail?

I think I was stupid in explaining this...
I wanted to count from the one in each invoice .. I was wrong in relying on the itemcod because it bears the number zero and is not null

 

[Pat Hartman]

I wanted to count from the one in each invoice

You keep thinking you want to count but that is not at all what you want to do. How hard will it be to just try what I suggested --- exactly ?????????????

It is only ONE line of code in ONE form level event.