Point Sales Ms Access Application

I'm almost finishing the point of sales application in ms access , now I have three issues:

(1) While on the form I have developed a code using Dsum that calculate the current stock balance immediately when a user select the stock code,this code give an error before the stock code selection is made , I feel like it will scare the users. The error is caused by the fact that at that point the product ID is not yet select and the code is evaluating a null value. How do I suppress this ? Otherwise the code works well.

(2) I have also tried to include a control called Quantity Sold within Dsum so that when a user punches more stock than on file it should not accept saving

((DSum("QuantityReceived","QryPurchases","[ProductID]=" &[ProductID]) - DSum("QuantitySold","QrySold","[ProductID]=" &[ProductID])) - ([Quantity Sold])) (This is housed in a control called [stock control])

Now I'm trying to attach a code to control called [Quantity Sold] which will not accepted if stock is more than what is on file.

Private Sub Before update_ Quantity Sold( Cancel as integer)
If (Stock Control < = 0) Then
MsgBox " Please check your inventory you do not have sufficient stock"
Cancel = True
Me.Quantity Sold.Setfocus
Exit Sub
End If
End Sub

The above code is giving an error it does not want to exit, where have I gone wrong????

(3) How do we format our ms application to print on pos printer? example receipt not those A4 pages????????


Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
1. You can use Nz to handle Null values in Access:

Nz(DSum(Your DSum), "")

2. Anytime you get an error you should post the contents of that error. A screenshot often helps.

Is that your EXACT code as copied from your application? If so, you've declared it wrong. It should be:

Private Sub YourControlName_BeforeUpdate(Cancel as Integer)

3. You have to design your reports to fit the paper size, of course, but you would set that report to print to the Default Printer.
Dale FyeOwner, Dev-Soln LLCCommented:
1.  You could hide the balance control until a stock ID is entered.  Since I don't particularly like to hide controls, a way to go about that would be to add another textbox (unbound and locked) of the same size, and place it on top of txt_Balance.  Then, use the Form_Current event to set the visible property of this new dummyBalance control to True and the Balance control to False.  Then, use the ProductID_AfterUpdate event to reverse that.  However, if the product entered is not valid then you would still get an error code, so I would also use the ProductID_BeforeUpdate event to make sure that a valid ProductID has been entered and if not , then display a warning message and set the Cancel argument to true.

2.  I think part of your problem with the QuantitySold issue is you may be getting ahead of yourself.  You mentioned QuantitySold but then in your code you mention StockControl, which is not mentioned elsewhere in your discussion.  I would try something like the following, in the BeforeUpdate event of your QuantitySold textbox.

Private Sub QuantitySold_BeforeUpdate (Cancel as integer)

    If (me.QuantitySold) > me.Balance Then
        MsgBox " Insufficient inventory to meet this requirement"
        Cancel = True
   End If

End Sub

Open in new window

3.  Cannot help you with the Point of Sale printer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you so much for providing extra needed information,but it is just that the query is closed I would have loved you to have some points as well


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I've asked the Mods to reopen the question.
Dale FyeOwner, Dev-Soln LLCCommented:
I don't need any points, I was just adding onto Scott's post.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.