NotInList INSERT INTO 2 Fields HOW?

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14014-notinlist-insert-into-2-fields-how.html
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")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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" wrote in message
news:93ECF387-65E9-4343-8CC0-F4FF311F154B@microsoft.com...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
> 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."
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
> Response = acDataErrContinue
> End If
> SupplierCode_NotInList_Exit:
> Exit Sub
> SupplierCode_NotInList_Err:
> MsgBox err.Description, vbCritical, "Error"
> Resume SupplierCode_NotInList_Exit
> End Sub
>



--
----------------------------------------
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

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!

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
"Pieter Wijnen" wrote:

> 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" wrote in message
> news:93ECF387-65E9-4343-8CC0-F4FF311F154B@microsoft.com...
> >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?" _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
> > , 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
> >
>
>
>
> --
> ----------------------------------------
> 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
>
>
>

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 & "');"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
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" wrote in message
news:AC297918-FF6C-49E4-AE1C-3C6D65EE589B@microsoft.com...
> 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" wrote:
>
>> 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" wrote in message
>> news:93ECF387-65E9-4343-8CC0-F4FF311F154B@microsoft.com...
>> >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
>> >
>>
>>
>>
>> --
>> ----------------------------------------
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14014
>> 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
>>
>>
>>

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4382 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!