Help with Insert statment

[Grub]

Hi Yall...

This is my objective:

I want the following snip of code to insert 2 values into table
'Failures'. The first value is based on a string that a user would
type into a combo box. If the value is not in the list, the 'Not in
List' procedure runs...That's where I've placed my code. The second
value must be extracted from a control that resides on the same form
'frmMain'. I can get everything to work if I enter a constant into the
second value of the Insert Statement (it is currently a '36'). The
value that I want in there is from a text box called txtEquipIDHolder.
I have tried everything I know to get that to work but I'm stuck...
Private Sub cboFailures_NotInList(NewData As String, Response As
Integer)
Dim db As Database

Dim LSQL As String
Dim LResponse As Integer
Dim ctl As Control
Dim ctlEquip As Control
On Error GoTo Err_Execute

'Category combo box control
Set ctl = Me!cboFailures
Set ctlEquip = Me!txtEquipIDHolder

LResponse = msgBox(NewData & " is a new item. Do you wish to add
it to the combo box?", vbYesNo, "Add Item")
'Debug msgBox (txtEquip & " works!!!")

'User responded "Yes" to adding the new item to the combo box
If LResponse = vbYes Then
Set db = CurrentDb()
'Insert new item into underlying table
LSQL = "insert into Failures (FailureName,EquipmentID) values
('" & NewData & "','36')"
db.Execute LSQL, dbFailOnError
'show new Value
Me.cboFailures = Null
Me.cboFailures.Requery
Me.cboFailures = Me.cboFailures.ItemData(0)

'Reset Database
Set db = Nothing

'Set Response argument to indicate that data is being added.
Response = acDataErrAdded

Else
'Set Response argument to suppress error message and undo
changes
Response = acDataErrContinue
ctl.Undo
End If

On Error GoTo 0

Exit Sub

Err_Execute:
ctl.Undo
msgBox "Action failed"

End Sub

[deluxeinformation]

Grub wrote:

Hi Yall...

This is my objective:

I want the following snip of code to insert 2 values into table
'Failures'. The first value is based on a string that a user would
type into a combo box. If the value is not in the list, the 'Not in
List' procedure runs...That's where I've placed my code. The second
value must be extracted from a control that resides on the same form
'frmMain'. I can get everything to work if I enter a constant into the
second value of the Insert Statement (it is currently a '36'). The
value that I want in there is from a text box called txtEquipIDHolder.
I have tried everything I know to get that to work but I'm stuck...
Private Sub cboFailures_NotInList(NewData As String, Response As
Integer)
Dim db As Database

Dim LSQL As String
Dim LResponse As Integer
Dim ctl As Control
Dim ctlEquip As Control
On Error GoTo Err_Execute

'Category combo box control
Set ctl = Me!cboFailures
Set ctlEquip = Me!txtEquipIDHolder

LResponse = msgBox(NewData & " is a new item. Do you wish to add
it to the combo box?", vbYesNo, "Add Item")
'Debug msgBox (txtEquip & " works!!!")

'User responded "Yes" to adding the new item to the combo box
If LResponse = vbYes Then
Set db = CurrentDb()
'Insert new item into underlying table
LSQL = "insert into Failures (FailureName,EquipmentID) values
('" & NewData & "','36')"
db.Execute LSQL, dbFailOnError
'show new Value
Me.cboFailures = Null
Me.cboFailures.Requery
Me.cboFailures = Me.cboFailures.ItemData(0)

'Reset Database
Set db = Nothing

'Set Response argument to indicate that data is being added.
Response = acDataErrAdded

Else
'Set Response argument to suppress error message and undo
changes
Response = acDataErrContinue
ctl.Undo
End If

On Error GoTo 0

Exit Sub

Err_Execute:
ctl.Undo
msgBox "Action failed"

End Sub

Try LSQL = "insert into Failures (FailureName,EquipmentID) values
('" & NewData & "','" & txtEquipIDHolder & "')"

Bruce

[Grub]

Brilliant, worked as advertised...Thanks Bruce!
de***************@gmail.com wrote:

Grub wrote:

Hi Yall...

This is my objective:

I want the following snip of code to insert 2 values into table
'Failures'. The first value is based on a string that a user would
type into a combo box. If the value is not in the list, the 'Not in
List' procedure runs...That's where I've placed my code. The second
value must be extracted from a control that resides on the same form
'frmMain'. I can get everything to work if I enter a constant into the
second value of the Insert Statement (it is currently a '36'). The
value that I want in there is from a text box called txtEquipIDHolder.
I have tried everything I know to get that to work but I'm stuck...
Private Sub cboFailures_NotInList(NewData As String, Response As
Integer)
Dim db As Database

Dim LSQL As String
Dim LResponse As Integer
Dim ctl As Control
Dim ctlEquip As Control
On Error GoTo Err_Execute

'Category combo box control
Set ctl = Me!cboFailures
Set ctlEquip = Me!txtEquipIDHolder

LResponse = msgBox(NewData & " is a new item. Do you wish to add
it to the combo box?", vbYesNo, "Add Item")
'Debug msgBox (txtEquip & " works!!!")

'User responded "Yes" to adding the new item to the combo box
If LResponse = vbYes Then
Set db = CurrentDb()
'Insert new item into underlying table
LSQL = "insert into Failures (FailureName,EquipmentID) values
('" & NewData & "','36')"
db.Execute LSQL, dbFailOnError
'show new Value
Me.cboFailures = Null
Me.cboFailures.Requery
Me.cboFailures = Me.cboFailures.ItemData(0)

'Reset Database
Set db = Nothing

'Set Response argument to indicate that data is being added.
Response = acDataErrAdded

Else
'Set Response argument to suppress error message and undo
changes
Response = acDataErrContinue
ctl.Undo
End If

On Error GoTo 0

Exit Sub

Err_Execute:
ctl.Undo
msgBox "Action failed"

End Sub

Try LSQL = "insert into Failures (FailureName,EquipmentID) values
('" & NewData & "','" & txtEquipIDHolder & "')"

Bruce