Multiple Column Combo box, NotInList event

[hhhh]

Hello all,
I have a combo box on a form with 2 columns. When a new value is entered I
am using the NotInList event to handle it. My problem is I have a second
column in the Combo Box that represents the full description of the first
Column and I haven't been able to update that field with the new full
description. The second column comes from the same table the first
does(tblDDLTaskTypes).

I had started to program in an Input box to accept the new description value
but am getting object errors among others. I am not that experienced with
vba and can't get the syntax correct. I have inherited this db and am not
sure which way to go.

Here is the code that accepts the new value. I have not been able to figure
out how to get the second field entered with an InputBox.

Private Sub Task_NotInList(NewData As String, Response As Integer)
Dim strTask As String
Dim MessValue As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared of values
If NewData = "" Then Exit Sub
'Message for the New Data message box
Msg = "'" & NewData & "' is not currently in the list of Task Types." & vbCr
& vbCr
Msg = Msg & "Do you want to add it?"
'Here is the message box that pops up making reference to the field
MessValue = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Task Type...")
If MessValue = vbYes Then
'here is where the table and field are referenced for the list
strTask = "Insert Into tblDDLTaskTypes ([Task]) values ('" & NewData &
"')"
'This is where the new value is added to that field to the first
column
CurrentDb.Execute strTask, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Thanks, I haven't worked with double column combo/notinlist situations
before,
Many TIA,
E.

 

[Graham Mandeno]

Hi E

It is common to need to add a record with information that is not enitrely
supplied by what the user has typed into the combobox (NewData). For
example, you might be adding a new customer record with other required
fields such as phone number or address.

To capture all this new information, it's best to use a form. The technique
I use is to open a form modally and pass the NewData value in OpenArgs.
Here is a simple example using your code:
If MessValue = vbYes Then
DoCmd.OpenForm "frmAddDDLTaskType", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

The Form_Load procedure in the opened form simply takes the passed value and
pops it into the required field:
Me!Task = Me.OpenArgs

You may find you need to deal with occurrences like the user cancelling the
add operation from the form, or changing the text that was passed from
NewData (thus making the content of the combo invalid once again) but this
should get you most of the way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello all,
I have a combo box on a form with 2 columns. When a new value is entered I
am using the NotInList event to handle it. My problem is I have a second
column in the Combo Box that represents the full description of the first
Column and I haven't been able to update that field with the new full
description. The second column comes from the same table the first
does(tblDDLTaskTypes).

I had started to program in an Input box to accept the new description
value
but am getting object errors among others. I am not that experienced with
vba and can't get the syntax correct. I have inherited this db and am not
sure which way to go.

Here is the code that accepts the new value. I have not been able to
figure
out how to get the second field entered with an InputBox.

Private Sub Task_NotInList(NewData As String, Response As Integer)
Dim strTask As String
Dim MessValue As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared of values
If NewData = "" Then Exit Sub
'Message for the New Data message box
Msg = "'" & NewData & "' is not currently in the list of Task Types." &
vbCr
& vbCr
Msg = Msg & "Do you want to add it?"
'Here is the message box that pops up making reference to the field
MessValue = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Task Type...")
If MessValue = vbYes Then
'here is where the table and field are referenced for the list
strTask = "Insert Into tblDDLTaskTypes ([Task]) values ('" & NewData
&
"')"
'This is where the new value is added to that field to the first
column
CurrentDb.Execute strTask, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Thanks, I haven't worked with double column combo/notinlist situations
before,
Many TIA,
E.

 

[eric]

Thanks for the tip Graham,
I will try out making a form that will handle it, but I have about 10 other
fields with the same notinlist coding already being used and I would like to
stay consistent.

The thing about the form is it must be easy to use and fast as it is now.
This is the only problem that exists. Isn't there a simple way to add some
lines to ask for the corresponding value instead?

I'm absolutely not knocking what you're saying, I'm just saying I'd like to
stay consistent. Now if I've messed up all the others with that same code
then someone tell me so I can go redo it all.

Eric

Hi E

It is common to need to add a record with information that is not enitrely
supplied by what the user has typed into the combobox (NewData). For
example, you might be adding a new customer record with other required
fields such as phone number or address.

To capture all this new information, it's best to use a form. The technique
I use is to open a form modally and pass the NewData value in OpenArgs.
Here is a simple example using your code:
If MessValue = vbYes Then
DoCmd.OpenForm "frmAddDDLTaskType", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

The Form_Load procedure in the opened form simply takes the passed value and
pops it into the required field:
Me!Task = Me.OpenArgs

You may find you need to deal with occurrences like the user cancelling the
add operation from the form, or changing the text that was passed from
NewData (thus making the content of the combo invalid once again) but this
should get you most of the way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello all,
I have a combo box on a form with 2 columns. When a new value is entered I
am using the NotInList event to handle it. My problem is I have a second
column in the Combo Box that represents the full description of the first
Column and I haven't been able to update that field with the new full
description. The second column comes from the same table the first
does(tblDDLTaskTypes).

I had started to program in an Input box to accept the new description
value
but am getting object errors among others. I am not that experienced with
vba and can't get the syntax correct. I have inherited this db and am not
sure which way to go.

Here is the code that accepts the new value. I have not been able to
figure
out how to get the second field entered with an InputBox.

Private Sub Task_NotInList(NewData As String, Response As Integer)
Dim strTask As String
Dim MessValue As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared of values
If NewData = "" Then Exit Sub
'Message for the New Data message box
Msg = "'" & NewData & "' is not currently in the list of Task Types." &
vbCr
& vbCr
Msg = Msg & "Do you want to add it?"
'Here is the message box that pops up making reference to the field
MessValue = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Task Type...")
If MessValue = vbYes Then
'here is where the table and field are referenced for the list
strTask = "Insert Into tblDDLTaskTypes ([Task]) values ('" & NewData
&
"')"
'This is where the new value is added to that field to the first
column
CurrentDb.Execute strTask, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Thanks, I haven't worked with double column combo/notinlist situations
before,
Many TIA,
E.

 

[Graham Mandeno]

Hi Eric

There is no problem with consistency. It's just that similar problems of
different complexities sometimes need different approaches to the best
solution.

There is no doubt that to add a new record to a table when you only have a
single field to specify, it is smartest to use a SQL Insert statement.

However, that method can't work when you need to get the values for
additional fields, which are as yet unknown, so the form is the way to go.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the tip Graham,
I will try out making a form that will handle it, but I have about 10
other
fields with the same notinlist coding already being used and I would like
to
stay consistent.

The thing about the form is it must be easy to use and fast as it is now.
This is the only problem that exists. Isn't there a simple way to add some
lines to ask for the corresponding value instead?

I'm absolutely not knocking what you're saying, I'm just saying I'd like
to
stay consistent. Now if I've messed up all the others with that same code
then someone tell me so I can go redo it all.

Eric

Hi E

It is common to need to add a record with information that is not
enitrely
supplied by what the user has typed into the combobox (NewData). For
example, you might be adding a new customer record with other required
fields such as phone number or address.

To capture all this new information, it's best to use a form. The technique
I use is to open a form modally and pass the NewData value in OpenArgs.
Here is a simple example using your code:
If MessValue = vbYes Then
DoCmd.OpenForm "frmAddDDLTaskType", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

The Form_Load procedure in the opened form simply takes the passed value and
pops it into the required field:
Me!Task = Me.OpenArgs

You may find you need to deal with occurrences like the user cancelling the
add operation from the form, or changing the text that was passed from
NewData (thus making the content of the combo invalid once again) but
this
should get you most of the way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello all,
I have a combo box on a form with 2 columns. When a new value is
entered I
am using the NotInList event to handle it. My problem is I have a
second
column in the Combo Box that represents the full description of the first
Column and I haven't been able to update that field with the new full
description. The second column comes from the same table the first
does(tblDDLTaskTypes).

I had started to program in an Input box to accept the new description
value
but am getting object errors among others. I am not that experienced with
vba and can't get the syntax correct. I have inherited this db and am not
sure which way to go.

Here is the code that accepts the new value. I have not been able to
figure
out how to get the second field entered with an InputBox.

Private Sub Task_NotInList(NewData As String, Response As Integer)
Dim strTask As String
Dim MessValue As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared of values
If NewData = "" Then Exit Sub
'Message for the New Data message box
Msg = "'" & NewData & "' is not currently in the list of Task Types." &
vbCr
& vbCr
Msg = Msg & "Do you want to add it?"
'Here is the message box that pops up making reference to the field
MessValue = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Task Type...")
If MessValue = vbYes Then
'here is where the table and field are referenced for the list
strTask = "Insert Into tblDDLTaskTypes ([Task]) values ('" & NewData
&
"')"
'This is where the new value is added to that field to the first
column
CurrentDb.Execute strTask, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Thanks, I haven't worked with double column combo/notinlist situations
before,
Many TIA,
E.