Add if not in combo box

[Nick]

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Daniel]

Look at

http://www.databasedev.co.uk/not_in_list.html
http://www.blueclaw-db.com/access_notinlist_advanced_example.htm
http://www.functionx.com/vbaccess/articles/notinlist.htm

Basically you need to use the not in list property and event.

 

[Klatuu]

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.

 

[Nick]

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Klatuu]

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Nick]

I type the employees' Last name.

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP


:

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Klatuu]

Sorry, Nick, that is not what I asked.
Can you tell the value in the Bound Column property of the combo box.
--
Dave Hargis, Microsoft Access MVP

I type the employees' Last name.

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!


:

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP


:

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Nick]

Ok, the bound column property is equal to "number" in Employee.SSI table.

Sorry, Nick, that is not what I asked.
Can you tell the value in the Bound Column property of the combo box.
--
Dave Hargis, Microsoft Access MVP

I type the employees' Last name.

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP


:

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!


:

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP


:

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Klatuu]

Sorry, Nick. I can't help you. I don't think I am making myself clear.
--
Dave Hargis, Microsoft Access MVP

Ok, the bound column property is equal to "number" in Employee.SSI table.

Sorry, Nick, that is not what I asked.
Can you tell the value in the Bound Column property of the combo box.
--
Dave Hargis, Microsoft Access MVP

I type the employees' Last name.

:

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP


:

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!


:

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP


:

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Nick]

Do you mean column one. which is the SSI.

Sorry, Nick. I can't help you. I don't think I am making myself clear.
--
Dave Hargis, Microsoft Access MVP

Ok, the bound column property is equal to "number" in Employee.SSI table.

Sorry, Nick, that is not what I asked.
Can you tell the value in the Bound Column property of the combo box.
--
Dave Hargis, Microsoft Access MVP


:

I type the employees' Last name.

:

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP


:

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!


:

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP


:

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Klatuu]

That is what I need! Thanks
It may take until later this afternoon before I can post back with the code.
I have to prepare for a presentation. Please be patient.
--
Dave Hargis, Microsoft Access MVP

Do you mean column one. which is the SSI.

Sorry, Nick. I can't help you. I don't think I am making myself clear.
--
Dave Hargis, Microsoft Access MVP

Ok, the bound column property is equal to "number" in Employee.SSI table.

:

Sorry, Nick, that is not what I asked.
Can you tell the value in the Bound Column property of the combo box.
--
Dave Hargis, Microsoft Access MVP


:

I type the employees' Last name.

:

You did not say what the combo's bound column is. It is important to know
that. In other words, do you type in the employee name or the SSI?
--
Dave Hargis, Microsoft Access MVP


:

This is the combo box row source type: tabe/Query
with row source: SELECT Employees.SSI, [Last Name] & ", " & [First Name] AS
Expr1
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Both Combo box and "Employee" are in the the underlying form "EmployeeInfo"
Form
Limit to List is set to "Yes"
SSI is primary key.
I hope I have covered all the info requested.
Thank You!


:

That is what the Not In List event is for. First, you need to set the Limit
To List property of the combo to Yes in form design. Then you need to code
the Not In List event to give the user the option to add it to the list or
not. There are a number of factors that will affect how you do the coding.
First, it will depend on what kind of row source it is. You have to handle
it differently for a value list than you do for a table/query. You usually
would not do this for a field list.
Then, assuming it is a table query and you want to add a new record, it will
depend on whether the value in the combo is the primary key and how much data
you will need to enter to identify the record. Also, it makes a difference
whether the record you are adding is in the form's underlying table or in
some other table.

If you can give some detail, I can show you the correct way to code this for
your situation.
--
Dave Hargis, Microsoft Access MVP


:

I am keying letter into a combo box to look up names. Sometime the name I
am looking for is not in the data base and I receive an error message. What
would I use to stop the error message and request Y/N that the name be added.

Private Sub Combo155_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSI] = '" & Me![Combo155] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub