By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,160 Members | 1,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,160 IT Pros & Developers. It's quick & easy.

Help with Insert statment

P: n/a
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

Jan 8 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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

Jan 8 '07 #2

P: n/a
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
Jan 9 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.