Help Please

[Samantha Wright]

I am running Access 2002, what I'm trying to achieve is to conditionally
transfer records from tblDetail01 to tblDetail02 subject to a order qty
being greater than a stock quantity and to halt the append and display a
warning box

My very basic insert code goes like this

INSERT INTO Detail02 ( OrderId, ProductID, ProductName, Quantity,
[Select], OrderDate )
SELECT Detail01.OrderId, Detail01.ProductID, Detail01.ProductName,
Detail01.Quantity, Detail01.[Select], Detail01.OrderDate
FROM Detail01
WHERE (((Detail01.[Select])=Yes));

I have an additional field in Detail01 named stock which holds the
updated stock quantity so I could prevent the appending of no good
records by changing the statement but I am lost on how to do this
correctly and either stop the append or give a warning that not all
products have been transferred

I would be grateful for any help


Thanks

 

[Ken Snell [MVP]]

You cannot "halt" an append query when you provide it with a number of
records through a SELECT clause. To do what you want, you'll have to use
code to test the values in the two tables and decide if the append query
should run or not -- if yes, run the query; if not, display a message box to
the user and don't run the query.

 

[Samantha Wright]

You cannot "halt" an append query when you provide it with a number of
records through a SELECT clause. To do what you want, you'll have to use
code to test the values in the two tables and decide if the append query
should run or not -- if yes, run the query; if not, display a message box to
the user and don't run the query.

Thank you for the quick reply

What I was trying to achieve is something along the following lines

"If Me.DETAIL01!Qty < Me.DETAIL01!Stock Then
Dim intanswer As Integer
intanswer = MsgBox("Stock Quantity Insufficient !", vbYesNoCancel +
vbQuestion, "Do You Want To Transfer ?")
Select Case intanswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.Close
DoCmd.RunSQL "Etc Etc"

Case vbNo
MsgBox "Cancel Event"
End If
End Sub"

But I can't figure out the correct coding to use or even if I am heading
in the wrong direction

Any assistance would be appreciated

Thank you

 

[Ken Snell [MVP]]

You're on the right track. What you'll need to do is to run an append query
that inserts just a single record, instead of selecting a series of records
(or at least what appeared to me to be a series of records in your original
post).

Here would be some generic code for this, using the syntax for inserting a
single record where you provide the actual values for each field (that is
what I am doing with my use of example data: 1, 'NameOfProduct', True,
etc.):

If Me.DETAIL01!Qty < Me.DETAIL01!Stock Then
Dim intanswer As Integer
Dim strSQL As String
intanswer = MsgBox("Stock Quantity Insufficient !", vbYesNoCancel + _
vbQuestion, "Do You Want To Transfer ?")
Select Case intanswer
Case vbYes
strSQL = "INSERT INTO Detail02 " & _
"( OrderId, ProductID, ProductName, Quantity, [Select],
OrderDate )" & _
" Values ( 1, 1, 'NameOfProduct', 1, True, #10/22/2005# )"
DoCmd.SetWarnings False
CurrentDb.Execute strSQL, dbFailOnError
Case vbNo
MsgBox "Cancel Event"
End Select
End If


Note that you can provide the actual values to the SQL statement from
controls on your form; you would concatenate them into the string, e.g.,

strSQL = "INSERT INTO Detail02 " & _
"( OrderId, ProductID, ProductName, Quantity, [Select],
OrderDate )" & _
" Values ( " & Me.ControlName1.Value & ", " &
Me.ControlName2.Value & _
", '" & Me.ControlName3.Value & "', " & Me.ControlName4.Value &
_
", " & Me.ControlName.Value5 & ", #" & Me.ControlName6.Value &
"# )"


--

Ken Snell
<MS ACCESS MVP>

--

Ken Snell
<MS ACCESS MVP>