Update table With Stocktake

[theevilsam]

Hey guys.

So my project is coming along well now and I wave created a new form for the user to manually enter stock levels after a stock take.

The coding I have so far is as follows

Private Sub StockTake_Click()

'Introduce items and variables
Dim Carling As Integer
Dim Carlsburg As Integer
Dim IPA As Integer
Dim Strongbow As Integer
Dim RevJames As Integer
Dim Becks As Integer
Dim WKDBlue As Integer
Dim WKDRed As Integer
Dim SmirnoffIce As Integer
Dim KoppaburgPear As Integer
Dim KoppaburgSum As Integer
Dim Bulmers As Integer
Dim Vodka As Integer
Dim Gin As Integer
Dim Sherry As Integer
Dim Sambuca As Integer
Dim Rum As Integer
Dim Port As Integer
Dim Whiskey As Integer
Dim Baileys As Integer
Dim Jagermeister As Integer
Dim Martini As Integer
Dim CokeCan As Integer
Dim Coke As Integer
Dim LemonadeCan As Integer
Dim Lemonade As Integer
Dim Squash As Integer
Dim Tonic As Integer
Dim RedBull As Integer
Dim Nuts As Integer
Dim Crisps As Integer
Dim SQLError As String
Dim SQLDelete As String
Dim SQLUpdate As String
Dim SQLBackup As String
Dim PubStock As Integer

'define items. these "Txtname" are text boxes on the form that the user can enter the values into... already this seems like a massive 'code.
Carling = txtCarling
Carlsburg = txtCarlsburg
IPA = txtIPA
Strongbow = txtStrongbow
RevJames = txtRevJames
Becks = txtBecks
WKDBlue = txtWKDBlue
WKDRed = txtWKDRed
SmirnoffIce = txtSmirnoffIce
KoppaburgPear = txtKopPear
KoppaburgSum = txtKopSum
Bulmers = txtBulmers
Vodka = txtVodka
Gin = txtGin
Sherry = txtSherry
Sambuca = txtSambuca
Rum = txtRum
Port = txtPort
Whiskey = txtWhiskey
Baileys = txtBaileys
Jagermeister = txtJagermeister
Martini = txtMartini
CokeCan = txtCokeCan
Coke = txtCokeDra
LemonadeCan = txtLemonadeCan
Lemonade = txtLemonadeDra
Squash = txtSquash
Tonic = txtTonic
RedBull = txtRedBull
Nuts = txtNuts
Crisps = txtCrisps

'introduce SQL coding
SQLDelete = "DELETE * FROM TblStock"

SQLUpdate = "update the tblstock with the values from the form based on the name of the product. tblStock contains StockID*, ProductID and stockLevel which is just the added stock values, not the on hand stock. thus the new value inputed on the form should replace all current values in the table. "

SQLBackup = "export all data in tblStock to update values in an excel table, ie, add to the data allready there, not replace it."

'Start actual coding
If Carling = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Carlsburg = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If IPA = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Strongbow = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If RevJames = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Becks = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If WKDBlue = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If WKDRed = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If SmirnoffIce = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If KoppaburgPear = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If KoppaburgSum = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Bulmers = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Vodka = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Gin = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Sherry = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Sambuca = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Rum = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Port = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Whiskey = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Baileys = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Jagermeister = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Martini = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If CokeCan = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Coke = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If LemonadeCan = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Lemonade = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Squash = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Tonic = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If RedBull = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Nuts = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else
If Crisps = Null Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again" Else PubStock = 1


If PubStock = 1 Then DoCmd.RunSQL SQLBackup
DoCmd.SetWarnings False
DoCmd.RunSQL SQLDelete
DoCmd.SetWarnings True
DoCmd.RunSQL SQLUpdate

If PubStock <> 1 Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again"

End Sub

kk, appart from the fact i am missing two of the sql queries, can anyone see any other problems with the coding? it seems overly large and i feel like i am seriously missing something important or obvious that would make this more manageable.

Other than the fact it is rather large, and I think I have the massive If segment wrong, can anyone help me with the sql that might be used for the SQLUpdate?

Can attatch database if it can help.

Thanks
Sam

[June7]

You are using an unbound form?

Every item must be filled in even if the value is 0?

Have you run Debug>Compile? Have you tested code?

Go ahead and attach database for analysis.

[pbaldy]

Also, you have fields for every item? That would be a big design mistake, and a bad violation of normalization rules if so. Even if not, you're hard-coding for each, which means every time you get a new product (or drop one) you have to modify tables, forms, reports, code, everything. I would normalize the data before anything else.

Code-wise, nothing is "=" to Null. You'd have to use IsNull() or this which tests for both Null and a zero length string:

If Len(Me.SomeControl & vbNullString) = 0 Then

Also, you are declaring all those variables as Integer, which can't take a Null value anyway. Your code will error if a textbox is Null. You can declare as Variant or use the Nz() function.

[June7]

And there really is no need for the variables anyway. Can just verify the controls by reference and the declaration of variable type issue is eliminated:

If IsNull(Me.txtCarling) _
Or IsNull(Me.txtCarlsburg) _
...
Or IsNull(Me.txtCrisps) Then MsgBox "Plese Fill in all areas of the form to complete the Stock Take and try again"

Review http://www.techrepublic.com/article/...access/6125114

[theevilsam]

hey. here is the database.

Your right about the form... For some reason it seemed like the easiest way...

So the best way would be to have a form with a way of updating the stock table of each item individually?

So this would use a combo box which uses he product table and has a hidden column with the product ID and a viewable product description (Item name) when the user selects an item from the drop down list they enter the new stock in a text box.

The button would then need coding that would Export the data to an excel file and then remove all records from tblstock that have a product id = to the value from the drop down box. the new value for the stock level would then be inserted into the table.

Have I got this right...

If so could I get a bit of a hand, I attempted to do it the other way because I dont really have any idea about combo boxes or this method. of course, I may have got it wrong again, so maybe another painter in the right direction.

Thanks

Sam

[orange]

I think you should tell us more about the proposed application and database. What is the purpose of the database in a few sentences?
Perhaps there are some options and alternatives. Easier to sort the database table structures out now than after you have a bunch of forms and procedures developed.

There are many of us who do not have acc2007 or 2010 and can not use the accdb format.

[theevilsam]

the database is a epos system for a pub. the database is set up to deal with stock levels with queries designed to show stock on hand. the problem is that there could be thousands of sales every week. i am trying to create a stock input form that will allow the user to do a stock take, enter in the values of stock that are in the pub, and then export all of the data from the sales table and the stock table to an excel file before clearing the two tables and inserting the new value into the stock table.

There are forms designated draught, spirit, bottle and soft drink. Each form has a number of buttons which input data into a table (TblCurrSale) from the product table based on a product ID.
Upon pressing the total button, available at the bottom of each page, the totals form is opened and the data from tblcurrsale is copied into a table tblcalc. Reports of both tables are viewable on this totals page.
The user can then enter a value into a txt field and this value is inputted into the calc table as a negative number. A query is constantly running that totals the values in the calc table. When this reaches 0 or less then the calc table is printed as a reciept and the change value is viewable on the form. on pressing the next sale button the calc table is cleared and the contents of the curr sale are copied to the total sales table before they are cleared.
There is also a stock table and form, in which deliveries are inputted into the stock table. the deliveries always have the same lowest value (1barrell of beer, 1 bottle of vodka etc) so this is the amount that each button inputs into the stock table based on the product ID and the delivery value as based on the product table.
deliveries are regular as are sales so access cannot handle millions of values in its database if there are a number of calculations to be made constantly. also stock takes are necessary as barrels are not exactly however many pints and there is going to be wastage etc.

I thought of a form that would force the user to input the new values for all products at once as this would make the sales table easier to manipulate, It is more sensible as June pointed out to do it for each item individually though, so I need to use a drop down box I think with a text field on the form.

Does this sound right to you.

The database is complete and I am pretty sure the tables are all normalised.

Thanks

Sam

[June7]

Yes, tables appear normalized. I guess you are trying to build forms to be most user-friendly and so having to use INSERT sql actions to save records.

However, I don't really follow the description of data flow. Not clear to me what role Excel plays, why export to Excel?
And this 'user to do a stock take, enter in the values of stock that are in the pub' sounds like an inventory is conducted daily. Not quite following the interaction on the Totals form.

Popup and modal property settings on forms play heck with debugging and project analysis. I would not set these until everything works and ready for deployment. I prefer overlapping windows instead of tabbed documents. I understand wanting to maximize screens for POS display but a nuisance in development. Probably should set RecordSelectors and NavigationButtons properties to No, maybe CloseButton as well if you want to control how the user quits a form.

Instead of 4 nearly identical forms for beverage types, could try a tab control.

Why build a query for each product instead of using filter criteria?

[theevilsam]

Using excel as an archive store for all of the data. the stock take would be done quarterly, and records of the sales would have to be stored for the business records.

I'll be honest, my lecturer in access was pretty useless and we covered very little. i built this on information i found myself.

Now that you mention filter criteria it makes more sense, but I don't really know how to even start at that.

thanks for the advise about recordselectors etc as well.

do you think a combobox could be used to solve the stock take? and do you have any idea how i would go about doing it?

Thanks

Sam

[June7]

Set filter criteria for queries in Design view. Access Help has guidelines on building queries.

I am sure combobox could be one aspect of data entry redesign. As for going into specifics, that's basic Access functionality and a little involved to try and describe in a forum post and I really don't want to build it for you. If you need some ideas about comboboxes, check out tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 about comboboxes in the Access Forms: Control Basics section.