Help With SQL Code in sub

  • Thread starter Thread starter Applebaum
  • Start date Start date
A

Applebaum

Hello,

I have a combo box with a Not-in-list code which inserts what the user typed
into a table. It works fine, but I'd like to expand this insert to add
specific text to another field. The combo list is for Vendors. When a new
Vendor name is typed in, the user is prompted to add it, and the code
inserts the text into tblVendors.CompanyName. There's another field in
tblVendors called PersonOrOrganization, I'd like to add the word
"Organization" to that field along with the insert into CompanyName. I
cannot figure out the right protocol to use. Here's what I have so far, if
anyone could help out I'd be most grateful!!

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Combo0_NotInList
Dim ctl As Control
Dim strSQL As String
' Return Control object that points to combo box.
Set ctl = Me!Combo0
' Prompt user to verify they wish to add new value.
If MsgBox("Item entered is not in list. Add it?", vbOKCancel) = vbOK
Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to products table.
strSQL = " INSERT INTO tblVendors (CompanyName) SELECT '" & NewData
& "'"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_Combo0_NotInList:
Exit Sub

Err_Combo0_NotInList:
'errorstuff
End Sub

I came up with a workaround to this, which brings up its own problem. The
AfterUpdate of this combo box takes the user to another form; I had put a
line in which set the field PersonOrOrganization to "Organization". This
worked perfectly, except I have other code in that form which gets triggered
when the form IsDirty, and adding "Organization" made the form dirty. If
someone made no changes, when closing the form they were prompted (as per my
code) whether or not to save changes.

Again, thanks for any input!!

Cheers,

Matthew
 
Hi,
Try this:
strSQL = " INSERT INTO tblVendors (CompanyName,PersonOrOrganization) Values( '" & _
NewData & "','Organization')"
 
Applebaum said:
Hello,

I have a combo box with a Not-in-list code which inserts what the
user typed into a table. It works fine, but I'd like to expand this
insert to add specific text to another field. The combo list is for
Vendors. When a new Vendor name is typed in, the user is prompted to
add it, and the code inserts the text into tblVendors.CompanyName.
There's another field in tblVendors called PersonOrOrganization, I'd
like to add the word "Organization" to that field along with the
insert into CompanyName. I cannot figure out the right protocol to
use. Here's what I have so far, if anyone could help out I'd be most
grateful!!

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Combo0_NotInList
Dim ctl As Control
Dim strSQL As String
' Return Control object that points to combo box.
Set ctl = Me!Combo0
' Prompt user to verify they wish to add new value.
If MsgBox("Item entered is not in list. Add it?", vbOKCancel) =
vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to products table.
strSQL = " INSERT INTO tblVendors (CompanyName) SELECT '" &
NewData & "'"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo
changes. Response = acDataErrContinue
ctl.Undo
End If

Exit_Combo0_NotInList:
Exit Sub

Err_Combo0_NotInList:
'errorstuff
End Sub

I came up with a workaround to this, which brings up its own problem.
The AfterUpdate of this combo box takes the user to another form; I
had put a line in which set the field PersonOrOrganization to
"Organization". This worked perfectly, except I have other code in
that form which gets triggered when the form IsDirty, and adding
"Organization" made the form dirty. If someone made no changes, when
closing the form they were prompted (as per my code) whether or not
to save changes.

Again, thanks for any input!!

Cheers,

Matthew
Click to expand...

I'd probably write something like this:

'---- start of suggested code ----
Private Sub Combo0_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Combo0_NotInList

Dim strSQL As String

' Prompt user to verify they wish to add new value.
If MsgBox("Item entered is not in list. Add it?", vbOKCancel) _
= vbOK _
Then
' Add string in NewData argument to products table.
strSQL = _
"INSERT INTO tblVendors " & _
"(CompanyName, PersonOrOrganization) " & _
"VALUES ('" & NewData & "', 'Organization')"

CurrentDb.Execute strSQL, dbFailOnError
' Set Response argument to indicate that data has been added.
Response = acDataErrAdded
Else
' If user chooses Cancel, suppress error message and undo
changes.
Response = acDataErrContinue
Me!Combo0.Undo
End If

Exit_Combo0_NotInList:
Exit Sub

Err_Combo0_NotInList:
'errorstuff, maybe the following:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Combo0_NotInList

End Sub
'---- end of suggested code ----
 
Add the other field to the append query.


strSQL = "INSERT INTO tblVendors (CompanyName, PersonOrOrganization)
SELECT '" & NewData & "', 'Organization' AS Expr1;"
 
When I tried this I got error:
"Number of query values and destination fields are not the same."

But Dan's suggestion worked. Thanks!
 
IT WORKED!!

THANK YOU!!


Dan Artuso said:
Hi,
Try this:
strSQL = " INSERT INTO tblVendors (CompanyName,PersonOrOrganization) Values( '" & _
NewData & "','Organization')"
Click to expand...
 
Back
Top