On Not in List even question

[RandyH]

Thanks in advance for any help!

I have the following script for a field called DialUpAccess in my Microsoft
Access database. This is a combo box that can look up entered values
(repeating values) or should allow values that were not previously not in
the list to be added.

The properties of the combo box are listed below:

The Control Source is 'DialUpAccess'
The Row Source Type is 'Value List'
The Row Source has this, "NetOP";"PC Anywhere";"RAS"
Limit to list is 'No'
Auto Expand is 'Yes'
Enabled is 'Yes'
Locked is 'No'

The 'On Not in List' event procedure is listed below:

Private Sub ComboDialup_NotInList(NewData As String, Response As Integer)
'Adds a remote access type that is not in the list to the list, if the user
wishes to do so.

Dim strMessage As String

Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you wish
to add it?"

intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb

Set rstKeyWord = dbsVBA.OpenRecordset("Hardware")

rstKeyWord.AddNew

rstKeyWord!ComboDialup = NewData

rstKeyWord.Update

Response = acDataErrAdded

Else

Response = acDataErrDisplay

End If
End Sub

Does anyone know what my problem here is?

Thanks again, Randy

 

[Joshua A. Booker]

Randy,

The NotInList event only fires if the LimitToList Property is True. Also,
the RowSource should be looking in the Hardware Table as that is where you
added the new value.

Try changing the LimiTtoList prop to 'Yes' and RowSourceType to Table/Query
and then the RowSource to a query or SQL statement such as this:

SELECT ComboDialup FROM Hardware

Assuming Hardware is the name of a table which contains a filed called
ComboDialup.

HTH,
Josh

 

[RandyH]

Joshua, when I do as you suggested, two things happen.
First, it looks like the combo box is retrieving every record in the table,
I want to only see one instance of any givin dial up type.
Second, I get a RunTime error (item not found in this collection) which
points to this line: rstKeyWord!ComboDialup = NewData, then I get a message
saying 'The Text You Entered is Not in the List'.

Randy

Randy,

The NotInList event only fires if the LimitToList Property is True. Also,
the RowSource should be looking in the Hardware Table as that is where you
added the new value.

Try changing the LimiTtoList prop to 'Yes' and RowSourceType to
Table/Query and then the RowSource to a query or SQL statement such as
this:

SELECT ComboDialup FROM Hardware

Assuming Hardware is the name of a table which contains a filed called
ComboDialup.

HTH,
Josh

Thanks in advance for any help!

I have the following script for a field called DialUpAccess in my
Microsoft
Access database. This is a combo box that can look up entered values
(repeating values) or should allow values that were not previously not in
the list to be added.

The properties of the combo box are listed below:

The Control Source is 'DialUpAccess'
The Row Source Type is 'Value List'
The Row Source has this, "NetOP";"PC Anywhere";"RAS"
Limit to list is 'No'
Auto Expand is 'Yes'
Enabled is 'Yes'
Locked is 'No'

The 'On Not in List' event procedure is listed below:

Private Sub ComboDialup_NotInList(NewData As String, Response As Integer)
'Adds a remote access type that is not in the list to the list, if the
user
wishes to do so.

Dim strMessage As String

Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you
wish
to add it?"

intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb

Set rstKeyWord = dbsVBA.OpenRecordset("Hardware")

rstKeyWord.AddNew

rstKeyWord!ComboDialup = NewData

rstKeyWord.Update

Response = acDataErrAdded

Else

Response = acDataErrDisplay

End If
End Sub

Does anyone know what my problem here is?

Thanks again, Randy

 

[Joshua A. Booker]

Hi Randy,

Tell us more about the Hardware table. It sounds like there is no field in
this table called 'ComboDialup.' It also sounds like there may be many
instances of dial-up type in this table. I think this calls for a new
lookup table that contains only one record per dial up type. The row source
for the combo should point to this lookup table and the NotInList code
should add a record to this table rather than to the Hardware table.

For example, a new table called tbl_DialUpTypes having fields 'TypeID'
autonumber and 'Type' Text would suffice.

TypeID Type
1 NetOP
2 PC Anywhere
3 RAS

Then the code would look something like this:

Private Sub ComboDialup_NotInList(NewData As String, Response As Integer)
'Adds a remote access type that is not in the list to the list, if the user
wishes to do so.
Dim strMessage As String
Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you
wish to add it?"
intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb
Set rstKeyWord = dbsVBA.OpenRecordset("tbl_DialUpTypes")

rstKeyWord.AddNew
rstKeyWord!Type = NewData
rstKeyWord.Update

'Always close your objects when done
rstKeyWord.Close
dbsVBA.Close
set rstKeyWord = Nothing
set dbsVBA = Nothing

Response = acDataErrAdded
Else
'Use the Continue command instead of Display
Response = acDataErrContinue
End If
End Sub

Until you get the NotInList code to add the record successfully, you will
get the 'The Text You Entered is Not in the List' message. Also, it's good
to close your recordset and database objects when you're done.

HTH,
Josh

Joshua, when I do as you suggested, two things happen.
First, it looks like the combo box is retrieving every record in the
table, I want to only see one instance of any givin dial up type.
Second, I get a RunTime error (item not found in this collection) which
points to this line: rstKeyWord!ComboDialup = NewData, then I get a
message saying 'The Text You Entered is Not in the List'.

Randy

Randy,

The NotInList event only fires if the LimitToList Property is True.
Also, the RowSource should be looking in the Hardware Table as that is
where you added the new value.

Try changing the LimiTtoList prop to 'Yes' and RowSourceType to
Table/Query and then the RowSource to a query or SQL statement such as
this:

SELECT ComboDialup FROM Hardware

Assuming Hardware is the name of a table which contains a filed called
ComboDialup.

HTH,
Josh

Thanks in advance for any help!

I have the following script for a field called DialUpAccess in my
Microsoft
Access database. This is a combo box that can look up entered values
(repeating values) or should allow values that were not previously not
in
the list to be added.

The properties of the combo box are listed below:

The Control Source is 'DialUpAccess'
The Row Source Type is 'Value List'
The Row Source has this, "NetOP";"PC Anywhere";"RAS"
Limit to list is 'No'
Auto Expand is 'Yes'
Enabled is 'Yes'
Locked is 'No'

The 'On Not in List' event procedure is listed below:

Private Sub ComboDialup_NotInList(NewData As String, Response As
Integer)
'Adds a remote access type that is not in the list to the list, if the
user
wishes to do so.

Dim strMessage As String

Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you
wish
to add it?"

intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb

Set rstKeyWord = dbsVBA.OpenRecordset("Hardware")

rstKeyWord.AddNew

rstKeyWord!ComboDialup = NewData

rstKeyWord.Update

Response = acDataErrAdded

Else

Response = acDataErrDisplay

End If
End Sub

Does anyone know what my problem here is?

Thanks again, Randy

 

[RandyH]

You Da Man, Josh!

Thanks alot, this is exactly what I was looking to achieve. Amazing how we
tend to make things harder than they really are...

Randy

Hi Randy,

Tell us more about the Hardware table. It sounds like there is no field
in this table called 'ComboDialup.' It also sounds like there may be many
instances of dial-up type in this table. I think this calls for a new
lookup table that contains only one record per dial up type. The row
source for the combo should point to this lookup table and the NotInList
code should add a record to this table rather than to the Hardware table.

For example, a new table called tbl_DialUpTypes having fields 'TypeID'
autonumber and 'Type' Text would suffice.

TypeID Type
1 NetOP
2 PC Anywhere
3 RAS

Then the code would look something like this:

Private Sub ComboDialup_NotInList(NewData As String, Response As Integer)
'Adds a remote access type that is not in the list to the list, if the
user wishes to do so.
Dim strMessage As String
Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you
wish to add it?"
intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb
Set rstKeyWord = dbsVBA.OpenRecordset("tbl_DialUpTypes")

rstKeyWord.AddNew
rstKeyWord!Type = NewData
rstKeyWord.Update

'Always close your objects when done
rstKeyWord.Close
dbsVBA.Close
set rstKeyWord = Nothing
set dbsVBA = Nothing

Response = acDataErrAdded
Else
'Use the Continue command instead of Display
Response = acDataErrContinue
End If
End Sub

Until you get the NotInList code to add the record successfully, you will
get the 'The Text You Entered is Not in the List' message. Also, it's
good to close your recordset and database objects when you're done.

HTH,
Josh

Joshua, when I do as you suggested, two things happen.
First, it looks like the combo box is retrieving every record in the
table, I want to only see one instance of any givin dial up type.
Second, I get a RunTime error (item not found in this collection) which
points to this line: rstKeyWord!ComboDialup = NewData, then I get a
message saying 'The Text You Entered is Not in the List'.

Randy

Randy,

The NotInList event only fires if the LimitToList Property is True.
Also, the RowSource should be looking in the Hardware Table as that is
where you added the new value.

Try changing the LimiTtoList prop to 'Yes' and RowSourceType to
Table/Query and then the RowSource to a query or SQL statement such as
this:

SELECT ComboDialup FROM Hardware

Assuming Hardware is the name of a table which contains a filed called
ComboDialup.

HTH,
Josh

Thanks in advance for any help!

I have the following script for a field called DialUpAccess in my
Microsoft
Access database. This is a combo box that can look up entered values
(repeating values) or should allow values that were not previously not
in
the list to be added.

The properties of the combo box are listed below:

The Control Source is 'DialUpAccess'
The Row Source Type is 'Value List'
The Row Source has this, "NetOP";"PC Anywhere";"RAS"
Limit to list is 'No'
Auto Expand is 'Yes'
Enabled is 'Yes'
Locked is 'No'

The 'On Not in List' event procedure is listed below:

Private Sub ComboDialup_NotInList(NewData As String, Response As
Integer)
'Adds a remote access type that is not in the list to the list, if the
user
wishes to do so.

Dim strMessage As String

Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you
wish
to add it?"

intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb

Set rstKeyWord = dbsVBA.OpenRecordset("Hardware")

rstKeyWord.AddNew

rstKeyWord!ComboDialup = NewData

rstKeyWord.Update

Response = acDataErrAdded

Else

Response = acDataErrDisplay

End If
End Sub

Does anyone know what my problem here is?

Thanks again, Randy