NotInList INSERT INTO 2 Fields HOW?

L

lmv

I have a combobox that triggers the next box to only list suppliercode with
that ProdID. The supplierCode field then has the number SOMETIMES the ProdID
can have a different number if it comes from a different supplier I want to
use my NotInList event to add the info. I know how to add just the supplier
code but what is the syntax to add the prodID as well? (this tblsupplierCode
has an auto number set as PK)

Thanks!

Private Sub cboSupplierCode_NotInList(NewData As String, Response As Integer)
On Error GoTo SupplierCode_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Purchase Order Subform")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Supplier Code has been added to the list."
Response = acDataErrAdded
Else
MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
"Use the ESC to return value to the box."
Response = acDataErrContinue
End If
SupplierCode_NotInList_Exit:
Exit Sub
SupplierCode_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume SupplierCode_NotInList_Exit
End Sub
 
P

Pieter Wijnen

You could either Open a Form in Dialog mode to add the two fields,
SupplierCode prefilled, or use a input box to prompt for a deviant ProdID

Pieter

lmv said:
I have a combobox that triggers the next box to only list suppliercode with
that ProdID. The supplierCode field then has the number SOMETIMES the
ProdID
can have a different number if it comes from a different supplier I want
to
use my NotInList event to add the info. I know how to add just the
supplier
code but what is the syntax to add the prodID as well? (this
tblsupplierCode
has an auto number set as PK)

Thanks!

Private Sub cboSupplierCode_NotInList(NewData As String, Response As
Integer)
On Error GoTo SupplierCode_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Purchase Order Subform")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Supplier Code has been added to the list."
Response = acDataErrAdded
Else
MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
"Use the ESC to return value to the box."
Response = acDataErrContinue
End If
SupplierCode_NotInList_Exit:
Exit Sub
SupplierCode_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume SupplierCode_NotInList_Exit
End Sub
Click to expand...



--
 
L

lmv

Thanks for the response but I don't know what you mean by dialog mode. And I
don't know what you mean by input box. Isn't there anyway to have VBA code to
add to 2 fields instead of only one there is already a productID code
available on the form I am using and it is what is triggering the info in the
cbosuppliercode?

strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"

Thanks!

Pieter Wijnen said:
You could either Open a Form in Dialog mode to add the two fields,
SupplierCode prefilled, or use a input box to prompt for a deviant ProdID

Pieter

lmv said:
I have a combobox that triggers the next box to only list suppliercode with
that ProdID. The supplierCode field then has the number SOMETIMES the
ProdID
can have a different number if it comes from a different supplier I want
to
use my NotInList event to add the info. I know how to add just the
supplier
code but what is the syntax to add the prodID as well? (this
tblsupplierCode
has an auto number set as PK)

Thanks!

Private Sub cboSupplierCode_NotInList(NewData As String, Response As
Integer)
On Error GoTo SupplierCode_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Purchase Order Subform")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Supplier Code has been added to the list."
Response = acDataErrAdded
Else
MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
"Use the ESC to return value to the box."
Response = acDataErrContinue
End If
SupplierCode_NotInList_Exit:
Exit Sub
SupplierCode_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume SupplierCode_NotInList_Exit
End Sub
Click to expand...



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Click to expand...
 
P

Pieter Wijnen

O,k below is an example on how to use InputBox

Pieter

Private Sub cboSupplierCode_NotInList(NewData As String, Response As
Integer)
On Error GoTo SupplierCode_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
Dim ProdID As String
intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Purchase Order Subform")
If intAnswer = vbYes Then
ProdID = InputBox "Add new ProdID?"
If Len(ProdID) Then
strSQL = "INSERT INTO tblSupplierCodes([SupplierCode], ProdID) " &
_
"VALUES ('" & NewData & "','" & ProdID & "');"
else
strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"
End If
DoCmd.SetWarnings False
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Supplier Code has been added to the list."
Response = acDataErrAdded
Else
MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
"Use the ESC to return value to the box."
Response = acDataErrContinue
End If
SupplierCode_NotInList_Exit:
Exit Sub
SupplierCode_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume SupplierCode_NotInList_Exit
End Sub


lmv said:
Thanks for the response but I don't know what you mean by dialog mode. And
I
don't know what you mean by input box. Isn't there anyway to have VBA code
to
add to 2 fields instead of only one there is already a productID code
available on the form I am using and it is what is triggering the info in
the
cbosuppliercode?

strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"

Thanks!

Pieter Wijnen said:
You could either Open a Form in Dialog mode to add the two fields,
SupplierCode prefilled, or use a input box to prompt for a deviant ProdID

Pieter

lmv said:
I have a combobox that triggers the next box to only list suppliercode
with
that ProdID. The supplierCode field then has the number SOMETIMES the
ProdID
can have a different number if it comes from a different supplier I
want
to
use my NotInList event to add the info. I know how to add just the
supplier
code but what is the syntax to add the prodID as well? (this
tblsupplierCode
has an auto number set as PK)

Thanks!

Private Sub cboSupplierCode_NotInList(NewData As String, Response As
Integer)
On Error GoTo SupplierCode_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Purchase Order Subform")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Supplier Code has been added to the list."
Response = acDataErrAdded
Else
MsgBox "Please choose a Supplier Code from the list." & vbCrLf &
_
"Use the ESC to return value to the box."
Response = acDataErrContinue
End If
SupplierCode_NotInList_Exit:
Exit Sub
SupplierCode_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume SupplierCode_NotInList_Exit
End Sub
Click to expand...



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList Event 1
Not In List CBO Help 0
Not In List Cbo Help 5
using multiple fields from a table 3
NotInList ComboBox Problem 5
Help fixing my NotInList code for a combo box 3
Need help with this NotInList code for a combo box 4
Limit to List question 11

Top