Help with SQL statement

[Eric]

Hope someone can help me here. (Access 97)

I have a combo box set up with a list of product codes
that a user can select from, or can type into the field. I
have some code set up where if the user selects a invalid
product code, it is supposed to generate a new record.
Well, it doesn't, and it is in a endless loop within the
code.

I need a SQL statement that will enter in a new product
code for me, but need some help with the statement. Here
is what I currently have now:

Private Sub cboProduct_AfterUpdate()
Dim Product As String, TarWgt As Double, FileNum As
Integer, strSQL As String, strMsg As String
On Error GoTo cboProduct_ErrorHandler

Product = Me.cboProduct.Value
'Find the File Number
FileNum = DLookup
("FileNumber", "tblProdSpecs", "Product = '" & Product
& "'")
Me.FileLocation.Value = FileNum

'Find the Product's Target Weight.
TarWgt = DLookup("Target", "tblProdSpecs", "Product
= '" & Product & "'")
Me.TargetWeight.Value = TarWgt

cboProduct_Exit:
Exit Sub

cboProduct_ErrorHandler:
If Err.Number = 94 Then
strMsg = MsgBox("The product, " &
Me.cboProduct.Value & ", cannot be located in the product
list. Do you wish to add this to the product list?",
vbYesNo)
If strMsg = vbYes Then
strSQL = "UPDATE tblProdSpecs SET
tblProdSpecs.FileNumber = 0 WHERE
(((tblProdSpecs.FileNumber) Is Null));" 'this does not
work, the endless loop since the filenumber <> 0 anywhere
in the table.
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Resume Next
Else
Resume cboProduct_Exit
End If
Else
MsgBox Err.Number & " " & Err.Description
Resume cboProduct_Exit
End If

End Sub

 

[Dirk Goldgar]

Hope someone can help me here. (Access 97)

I have a combo box set up with a list of product codes
that a user can select from, or can type into the field. I
have some code set up where if the user selects a invalid
product code, it is supposed to generate a new record.
Well, it doesn't, and it is in a endless loop within the
code.

I need a SQL statement that will enter in a new product
code for me, but need some help with the statement. Here
is what I currently have now:

Private Sub cboProduct_AfterUpdate()
Dim Product As String, TarWgt As Double, FileNum As
Integer, strSQL As String, strMsg As String
On Error GoTo cboProduct_ErrorHandler

Product = Me.cboProduct.Value
'Find the File Number
FileNum = DLookup
("FileNumber", "tblProdSpecs", "Product = '" & Product
& "'")
Me.FileLocation.Value = FileNum

'Find the Product's Target Weight.
TarWgt = DLookup("Target", "tblProdSpecs", "Product
= '" & Product & "'")
Me.TargetWeight.Value = TarWgt

cboProduct_Exit:
Exit Sub

cboProduct_ErrorHandler:
If Err.Number = 94 Then
strMsg = MsgBox("The product, " &
Me.cboProduct.Value & ", cannot be located in the product
list. Do you wish to add this to the product list?",
vbYesNo)
If strMsg = vbYes Then
strSQL = "UPDATE tblProdSpecs SET
tblProdSpecs.FileNumber = 0 WHERE
(((tblProdSpecs.FileNumber) Is Null));" 'this does not
work, the endless loop since the filenumber <> 0 anywhere
in the table.
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Resume Next
Else
Resume cboProduct_Exit
End If
Else
MsgBox Err.Number & " " & Err.Description
Resume cboProduct_Exit
End If

End Sub

Your SQL statement doesn't work because it is not an UPDATE you need to
do but an INSERT -- and append query. This is probably what you need:

strSQL = _
"INSERT INTO tblProdSpecs (Product) VALUES ('" & _
Product & "')"

For that to execute successfully, though, the other fields in
tblProdSpecs must none of them be required, or else they must have
suitable default values defined.

Can I take it that you are going to allow the user to enter products in
cboProducts that don't exist in tblProdSpecs, and that's why you aren't
using the NotInList event to handle "non-existent" products?

BTW, There are more efficient ways than DLookup to get the File Number
and Target Weight for items that do exist. For example, you could have
these as additional columns in the combo box. Then assign the values
directly from the combo box's Column() to the other text boxes, or set
those text boxes' controlsource expressions to pick up the values from
the combo box.