Stockcheck更新stock表
Stockcheck更新stock表
提问于 2012-02-20 09:30:16
所以我的项目现在进展顺利，我wave创建了一个新的表单，供用户在盘点后手动输入库存水平。
到目前为止，我的代码如下所示
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 "Please Fill in all areas of the form to complete the Stock Take and try again" Else
If Lemonade = Null Then MsgBox "Please Fill in all areas of the form to complete the Stock Take and try again" Else
If Squash = Null Then MsgBox "Please Fill in all areas of the form to complete the Stock Take and try again" Else
If Tonic = Null Then MsgBox "Please Fill in all areas of the form to complete the Stock Take and try again" Else
If RedBull = Null Then MsgBox "Please Fill in all areas of the form to complete the Stock Take and try again" Else
If Nuts = Null Then MsgBox "Please Fill in all areas of the form to complete the Stock Take and try again" Else
If Crisps = Null Then MsgBox "Please 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 Subkk，除了我遗漏了两个sql查询之外，还有人能看到编码的其他问题吗？它看起来太大了，我觉得我严重遗漏了一些重要或明显的东西，这些东西会让它变得更容易管理。
除了它相当大的事实之外，我认为我有大量的“如果”段错误，至少可以说，有谁可以帮助我的sql，可能用于SQLUpdate？我担心这也会是一大块……一定有比这更简单的方法...:(
可以附加数据库，如果它可以帮助。
谢谢，山姆
回答 1
Stack Overflow用户
发布于 2012-02-20 11:42:14
您可以让这件事变得简单得多。最初更容易编码，以后更容易维护。
创建一个包含两个字段的表tblStockItems : item_id (主键)和item_name。
item_id item_name
1 Baileys
2 Becks
3 Bulmers创建另一个表tblScratchInventory，其中包含两个数值字段:item_id和quantity。
创建此查询并将其另存为qryScratchInventory
SELECT
inv.item_id,
items.item_name,
inv.quantity
FROM
tblScratchInventory AS inv
INNER JOIN tblStockItems AS items
ON inv.item_id = items.item_id
ORDER BY items.item_name;创建一个使用qryScratchInventory作为记录源的连续表单。将表单的“允许添加和允许删除”属性设置为“否”。在表单打开期间，您可以“刷新”tblScratchInventory，以便为记录一组新的库存值做好准备。
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb
strSql = "DELETE FROM tblScratchInventory;"
db.Execute strSql, dbFailOnError
strSql = "INSERT INTO tblScratchInventory ( item_id )" & vbCrLf & _
"SELECT tblStockItems.item_id" & vbCrLf & _
"FROM tblStockItems;"
db.Execute strSql, dbFailOnError
Set db = Nothing
Me.Requery
End Sub您还应该包括一个错误处理程序来处理dbFailOnError出现的任何问题。
将命令按钮cmdDone添加到窗体页脚。使用此代码作为它的单击事件。
Private Sub cmdDone_Click()
Dim lngNumNulls As Long
Dim strNullItems As String
Dim strMsg As String
If Me.Dirty Then Me.Dirty = False
lngNumNulls = DCount("*", "tblScratchInventory", "quantity Is Null")
Select Case lngNumNulls
Case 0
' do what you need here '
' then close the form: '
DoCmd.Close acForm, Me.name
Case Is <= 6 ' list individual items if not too many of them '
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSql As String
strSql = "SELECT item_name" & vbCrLf & _
"FROM qryScratchInventory" & vbCrLf & _
"WHERE quantity Is Null" & vbCrLf & _
"ORDER BY item_name;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)
With rs
Do While Not .EOF
strNullItems = strNullItems & vbCrLf & !item_name
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
strMsg = "Please input quantities for these items: " & strNullItems
Case Else
strMsg = "Please input quantities for all items."
End Select
If Len(strMsg) > 0 Then
MsgBox strMsg
End If
End Sub我不确定在确保所有库存物品都具有非空值之后，您想要如何处理库存值(案例0，cmdDone_Click中的“do what you need here”)。也许您希望使用UPDATE语句将数量转移到另一个表(TblStock?)。或者，您可以将表单建立在TblStock查询的基础上，而不是使用qryScratchInventory作为中间步骤。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9355138
复制相关文章
相似问题
腾讯云开发者
