combo box list not updating

[toolman74]

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[Klatuu]

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

 

[toolman74]

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

 

[Klatuu]

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?

 

[toolman74]

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela

 

[Klatuu]

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.

 

[toolman74]

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela

 

[Klatuu]

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[toolman74]

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[Klatuu]

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[toolman74]

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[Klatuu]

Adjuster ID and Adjuster name have to be the same data type. Since Adjuster
ID is an autonumber field, it is a Long Integer data type. I suspect
Adjuster name is probably a text field that contains the actual name. If so,
it should not be. It should Also be Adjuster ID and a long integer data
type. It should contain the value from the Adjuster ID field in the Adjuster
table. That is how you set relationships between tables. The Adjuster name
should be carried only in the Adjuster table. the Adjuster ID then links the
correct adjuster table to the Assn table. Then your combo should be the
control bound to the AdjusterID field in the Assn table. The row source for
your combo is then correct. It will display the adjuster name, but actually
carry the value of the Adjuster ID.

This is the root of your problem. I would suggest you add a field to the
Assn table named Adjuster ID, then create an update query that joins the Assn
table with the Adjuster table on the Adjuster Name. Update the new Adjuster
ID field in the Assn table to the value of the Adjuster ID field in the
Adjuster table. Once it is correct, delete the Adjuster Name field from the
Assn table.
Be sure you make a copy of your database first.
I know this seems like a lot of work, but it would be the correct way to
relate the tables and make life easier in the long run.

If there or other places in the application that use the Adjuster Name field
in the Assn table, you will need to fix those to use the the Adjuster ID
field also. In some cases, you may have to use a query that joins the two
tables on the Adjuster ID field and show the Adjuster Name field from the
Adjuster table.
--
Dave Hargis, Microsoft Access MVP

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

:

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[toolman74]

Sorry I didn't respond sooner - crazy over here! Thanks so much for your
help. I did as you suggested below and ran an update query. I still get the
same error - 3101 that there isn't a matching field in the table but if I hit
end on the error message it works perfectly - the entry is there and the
phone number carries through. Any suggestions on how to stop the error
message? If I hit Debug, it takes me the to Me.Requery line of the code.
Any suggestions?

Adjuster ID and Adjuster name have to be the same data type. Since Adjuster
ID is an autonumber field, it is a Long Integer data type. I suspect
Adjuster name is probably a text field that contains the actual name. If so,
it should not be. It should Also be Adjuster ID and a long integer data
type. It should contain the value from the Adjuster ID field in the Adjuster
table. That is how you set relationships between tables. The Adjuster name
should be carried only in the Adjuster table. the Adjuster ID then links the
correct adjuster table to the Assn table. Then your combo should be the
control bound to the AdjusterID field in the Assn table. The row source for
your combo is then correct. It will display the adjuster name, but actually
carry the value of the Adjuster ID.

This is the root of your problem. I would suggest you add a field to the
Assn table named Adjuster ID, then create an update query that joins the Assn
table with the Adjuster table on the Adjuster Name. Update the new Adjuster
ID field in the Assn table to the value of the Adjuster ID field in the
Adjuster table. Once it is correct, delete the Adjuster Name field from the
Assn table.
Be sure you make a copy of your database first.
I know this seems like a lot of work, but it would be the correct way to
relate the tables and make life easier in the long run.

If there or other places in the application that use the Adjuster Name field
in the Assn table, you will need to fix those to use the the Adjuster ID
field also. In some cases, you may have to use a query that joins the two
tables on the Adjuster ID field and show the Adjuster Name field from the
Adjuster table.
--
Dave Hargis, Microsoft Access MVP

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP


:

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

:

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[Klatuu]

Not sure why you would get that error on a requery. You could use an error
handler to ignore the error, but that isn't really a good solution. Go back
through all the controls on your form and all the fields in your record
source and be sure every thing is matchin up, including the row source of all
combos and listboxes if you have them. There is something not defined
correctly.
--
Dave Hargis, Microsoft Access MVP

Sorry I didn't respond sooner - crazy over here! Thanks so much for your
help. I did as you suggested below and ran an update query. I still get the
same error - 3101 that there isn't a matching field in the table but if I hit
end on the error message it works perfectly - the entry is there and the
phone number carries through. Any suggestions on how to stop the error
message? If I hit Debug, it takes me the to Me.Requery line of the code.
Any suggestions?

Adjuster ID and Adjuster name have to be the same data type. Since Adjuster
ID is an autonumber field, it is a Long Integer data type. I suspect
Adjuster name is probably a text field that contains the actual name. If so,
it should not be. It should Also be Adjuster ID and a long integer data
type. It should contain the value from the Adjuster ID field in the Adjuster
table. That is how you set relationships between tables. The Adjuster name
should be carried only in the Adjuster table. the Adjuster ID then links the
correct adjuster table to the Assn table. Then your combo should be the
control bound to the AdjusterID field in the Assn table. The row source for
your combo is then correct. It will display the adjuster name, but actually
carry the value of the Adjuster ID.

This is the root of your problem. I would suggest you add a field to the
Assn table named Adjuster ID, then create an update query that joins the Assn
table with the Adjuster table on the Adjuster Name. Update the new Adjuster
ID field in the Assn table to the value of the Adjuster ID field in the
Adjuster table. Once it is correct, delete the Adjuster Name field from the
Assn table.
Be sure you make a copy of your database first.
I know this seems like a lot of work, but it would be the correct way to
relate the tables and make life easier in the long run.

If there or other places in the application that use the Adjuster Name field
in the Assn table, you will need to fix those to use the the Adjuster ID
field also. In some cases, you may have to use a query that joins the two
tables on the Adjuster ID field and show the Adjuster Name field from the
Adjuster table.
--
Dave Hargis, Microsoft Access MVP

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

:

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP


:

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

:

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[toolman74]

Oh my! I am in such a panic... I went back through to try to find the
problem - going through the record sources, etc. I found that the combo box
had a control source of Assn Tbl.Adjuster Name. I assumed this was my
problem and proceeded to instead change the control to Adjuster Tbl.Adjuster
Name. I was then getting a different error 3079 that there were multiple
tables with that field. I figured that I then needed to go back and delete
out the Adjuster Name field from the Assn Table but got a message that was
still in a relationship. I ended up deleting all relationships in my
database and it still wouldn't let me delete it. I then deleted all of my
links in my queries and it still didn't work. Now I tried putting them back
but my form won't allow any new entries - only changes to existing ones. Any
suggestions? In hindsight I should have backed up my database before making
so many substantial changes but I thought it would be an easy fix. Thank you
so much!! At this point, I am fine with getting it back to working and just
doing what it takes to stop the error code. Fortunately, I won't need to use
that Assn Table for anything else. I'll build all future forms the right way
so the error shouldn't affect anything else. Thanks again for your continued
help!

Not sure why you would get that error on a requery. You could use an error
handler to ignore the error, but that isn't really a good solution. Go back
through all the controls on your form and all the fields in your record
source and be sure every thing is matchin up, including the row source of all
combos and listboxes if you have them. There is something not defined
correctly.
--
Dave Hargis, Microsoft Access MVP

Sorry I didn't respond sooner - crazy over here! Thanks so much for your
help. I did as you suggested below and ran an update query. I still get the
same error - 3101 that there isn't a matching field in the table but if I hit
end on the error message it works perfectly - the entry is there and the
phone number carries through. Any suggestions on how to stop the error
message? If I hit Debug, it takes me the to Me.Requery line of the code.
Any suggestions?

Adjuster ID and Adjuster name have to be the same data type. Since Adjuster
ID is an autonumber field, it is a Long Integer data type. I suspect
Adjuster name is probably a text field that contains the actual name. If so,
it should not be. It should Also be Adjuster ID and a long integer data
type. It should contain the value from the Adjuster ID field in the Adjuster
table. That is how you set relationships between tables. The Adjuster name
should be carried only in the Adjuster table. the Adjuster ID then links the
correct adjuster table to the Assn table. Then your combo should be the
control bound to the AdjusterID field in the Assn table. The row source for
your combo is then correct. It will display the adjuster name, but actually
carry the value of the Adjuster ID.

This is the root of your problem. I would suggest you add a field to the
Assn table named Adjuster ID, then create an update query that joins the Assn
table with the Adjuster table on the Adjuster Name. Update the new Adjuster
ID field in the Assn table to the value of the Adjuster ID field in the
Adjuster table. Once it is correct, delete the Adjuster Name field from the
Assn table.
Be sure you make a copy of your database first.
I know this seems like a lot of work, but it would be the correct way to
relate the tables and make life easier in the long run.

If there or other places in the application that use the Adjuster Name field
in the Assn table, you will need to fix those to use the the Adjuster ID
field also. In some cases, you may have to use a query that joins the two
tables on the Adjuster ID field and show the Adjuster Name field from the
Adjuster table.
--
Dave Hargis, Microsoft Access MVP


:

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

:

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP


:

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

:

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[toolman74]

Nevermind that, I just started over again from your last post and went back
to my backup db and ran the update query again. So I am back where I was
earlier today. It works beautifully, except for the error which I can work
around. Can you tell me how to override the error message?

Oh my! I am in such a panic... I went back through to try to find the
problem - going through the record sources, etc. I found that the combo box
had a control source of Assn Tbl.Adjuster Name. I assumed this was my
problem and proceeded to instead change the control to Adjuster Tbl.Adjuster
Name. I was then getting a different error 3079 that there were multiple
tables with that field. I figured that I then needed to go back and delete
out the Adjuster Name field from the Assn Table but got a message that was
still in a relationship. I ended up deleting all relationships in my
database and it still wouldn't let me delete it. I then deleted all of my
links in my queries and it still didn't work. Now I tried putting them back
but my form won't allow any new entries - only changes to existing ones. Any
suggestions? In hindsight I should have backed up my database before making
so many substantial changes but I thought it would be an easy fix. Thank you
so much!! At this point, I am fine with getting it back to working and just
doing what it takes to stop the error code. Fortunately, I won't need to use
that Assn Table for anything else. I'll build all future forms the right way
so the error shouldn't affect anything else. Thanks again for your continued
help!

Not sure why you would get that error on a requery. You could use an error
handler to ignore the error, but that isn't really a good solution. Go back
through all the controls on your form and all the fields in your record
source and be sure every thing is matchin up, including the row source of all
combos and listboxes if you have them. There is something not defined
correctly.
--
Dave Hargis, Microsoft Access MVP

Sorry I didn't respond sooner - crazy over here! Thanks so much for your
help. I did as you suggested below and ran an update query. I still get the
same error - 3101 that there isn't a matching field in the table but if I hit
end on the error message it works perfectly - the entry is there and the
phone number carries through. Any suggestions on how to stop the error
message? If I hit Debug, it takes me the to Me.Requery line of the code.
Any suggestions?

:

Adjuster ID and Adjuster name have to be the same data type. Since Adjuster
ID is an autonumber field, it is a Long Integer data type. I suspect
Adjuster name is probably a text field that contains the actual name. If so,
it should not be. It should Also be Adjuster ID and a long integer data
type. It should contain the value from the Adjuster ID field in the Adjuster
table. That is how you set relationships between tables. The Adjuster name
should be carried only in the Adjuster table. the Adjuster ID then links the
correct adjuster table to the Assn table. Then your combo should be the
control bound to the AdjusterID field in the Assn table. The row source for
your combo is then correct. It will display the adjuster name, but actually
carry the value of the Adjuster ID.

This is the root of your problem. I would suggest you add a field to the
Assn table named Adjuster ID, then create an update query that joins the Assn
table with the Adjuster table on the Adjuster Name. Update the new Adjuster
ID field in the Assn table to the value of the Adjuster ID field in the
Adjuster table. Once it is correct, delete the Adjuster Name field from the
Assn table.
Be sure you make a copy of your database first.
I know this seems like a lot of work, but it would be the correct way to
relate the tables and make life easier in the long run.

If there or other places in the application that use the Adjuster Name field
in the Assn table, you will need to fix those to use the the Adjuster ID
field also. In some cases, you may have to use a query that joins the two
tables on the Adjuster ID field and show the Adjuster Name field from the
Adjuster table.
--
Dave Hargis, Microsoft Access MVP


:

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

:

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP


:

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

:

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[toolman74]

Nevermind that, I just started over again from your last post and went back
to my backup db and ran the update query again. So I am back where I was
earlier today. It works beautifully, except for the error which I can work
around. Can you tell me how to override the error message?

Not sure why you would get that error on a requery. You could use an error
handler to ignore the error, but that isn't really a good solution. Go back
through all the controls on your form and all the fields in your record
source and be sure every thing is matchin up, including the row source of all
combos and listboxes if you have them. There is something not defined
correctly.
--
Dave Hargis, Microsoft Access MVP

Sorry I didn't respond sooner - crazy over here! Thanks so much for your
help. I did as you suggested below and ran an update query. I still get the
same error - 3101 that there isn't a matching field in the table but if I hit
end on the error message it works perfectly - the entry is there and the
phone number carries through. Any suggestions on how to stop the error
message? If I hit Debug, it takes me the to Me.Requery line of the code.
Any suggestions?

Adjuster ID and Adjuster name have to be the same data type. Since Adjuster
ID is an autonumber field, it is a Long Integer data type. I suspect
Adjuster name is probably a text field that contains the actual name. If so,
it should not be. It should Also be Adjuster ID and a long integer data
type. It should contain the value from the Adjuster ID field in the Adjuster
table. That is how you set relationships between tables. The Adjuster name
should be carried only in the Adjuster table. the Adjuster ID then links the
correct adjuster table to the Assn table. Then your combo should be the
control bound to the AdjusterID field in the Assn table. The row source for
your combo is then correct. It will display the adjuster name, but actually
carry the value of the Adjuster ID.

This is the root of your problem. I would suggest you add a field to the
Assn table named Adjuster ID, then create an update query that joins the Assn
table with the Adjuster table on the Adjuster Name. Update the new Adjuster
ID field in the Assn table to the value of the Adjuster ID field in the
Adjuster table. Once it is correct, delete the Adjuster Name field from the
Assn table.
Be sure you make a copy of your database first.
I know this seems like a lot of work, but it would be the correct way to
relate the tables and make life easier in the long run.

If there or other places in the application that use the Adjuster Name field
in the Assn table, you will need to fix those to use the the Adjuster ID
field also. In some cases, you may have to use a query that joins the two
tables on the Adjuster ID field and show the Adjuster Name field from the
Adjuster table.
--
Dave Hargis, Microsoft Access MVP


:

In the qry I linked 'Adjuster ID' (key) from the Adjuster tbl to 'Adjuster
name' (not key) in the Assn tbl.

:

How does the Adjuster table relate to the Assn tbl?
There needs to be a common field in both.
--
Dave Hargis, Microsoft Access MVP


:

I'm sorry if I wasn't clear. The original tbl, Assn tbl, has a primary key
that is just a generic ID field that Autonumbers the entries.

The combo box row source is:
SELECT [Adjuster Tbl].[Adjuster ID], [Adjuster Tbl].[Adjuster Name] FROM
[Adjuster Tbl] ORDER BY [Adjuster Name];

If it appears inconsistent with previous entries, I had been changing each
name to Product because it seems that people relate to Products better than
Adjusters. At this point, I want to give it to you exactly so that I don't
make a silly mistake changing names.

The error I'm getting is '3101' stating that they cannot find a matching
key field in Adjuster tbl. The form, Assn form, was originally set up off a
one-table (Assn tbl) query. This table has an 'Adjuster name' field. I
added the Adjuster tbl I created to the query and linked the 'Adjuster name'
field in the Assn tbl to the 'Adjuster ID' field in the Adjuster tbl which is
the key field. I tried to change the key to the 'Adjuster name' field in my
table so that they would at least match, but it locked up my combo box so I
changed it back.

Thanks for your continued help and persistence!!
Pamela

:

You don't need to redo everything, but the table does need a unique primary
key field. A couple of questions before we determine how to fix this
horrible design you have inherited (all tables should have some kind of
primary key field)
Is the query for the form's recordset based on one table or multiple tables?
What is the row source for the combo based on? If you could post the row
source of the combo, it would be helpful.

--
Dave Hargis, Microsoft Access MVP


:

Wow...this is great!! As you predicted, I found a snag. Unfortunately the
form I'm working on was originally done by someone else who created it from a
one-table query that contained all of the possible fields needed. This one
table has 50+ fields ranging from Order Info to Product Info to Company Info,
Employee Info, etc. He did not make Product Info the key. Your formula
works perfectly up to the requery part where I get an error that there isn't
a matching key field in the underlying table. Is there a work-around for
this or do I have to start from scratch with a new table and form? I must
say your formula was very impressive!!!

Pamela

:

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP


:

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela


:

I didn't suggest any changes in your query.
By Dialog mode, I mean in your Macro that opens the form, select Dialog in
the Window Mode property.
As to the requery, that is done in VBA after you call the macro.
How are you calling the Macro?
--
Dave Hargis, Microsoft Access MVP


:

Thanks, but I'm a little lost on this suggestion. After reading your post I
created a new query based on the product table but when I went back to my
table to have the Lookup Wizard use values from the product qry vs. the
product table, I got an error that it can't be changed due to the
relationships it has. I then went and deleted the relationships from that
table in the Relationships page but I still got the same error. Now what?
Is that what it means to "requery?" Also, I know you wrote the code in, but
as I originally stated, I have no idea what that means or where it goes.
Also, what is dialog mode? Thanks for your continued help!!

:

You requery just the combo box.
You should open the product form in dialog mode so the code in the Not In
List event will pause until the form is closed.
Then the next line of code should be:
Me.MyCombo.Requery

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box based off of a product table but there are regular times
that the list needs to be updated. I created a macro upon the NotInList
event that opens the product form on an add only basis but once the info is
added under products it doesn't show up in the combo box - even with a
refresh of the records. When I close out of the form and opened the product
table table, the new info is there and upon reopening my original form &
combo box the new info is there...but how I can get it to add it or update it
without having to close out and reopening? I've seen many things about
writing in code but that is way over the head of this newbie. Thanks, in
advance!!

Pamela

 

[Pamela]

This code is great..thanks so much but I'm having a problem with it in 1
application and I was hoping to get some help on it. I keep getting a Syntax
error (missing operator) on the .FindFirst line. I've gotten this code to
work on 2 other combo boxes and the only difference on this one is that this
one is concatenating 2 fields - FirstName and LastName - but the bound field
is still the Autonumber ID field. Any help you can give me on this would be
really great...it's been a real stumper for me.
Here is how I have it:
Private Sub cboAdjusterID_NotInList(NewData As String, Response As Integer)
If MsgBox(NewData & " is not in the list." & vbCrLf & "Would you like to add
" & NewData & "?", vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboAdjusterID.Undo

DoCmd.OpenForm "frmAdjuster", , , , acFormAdd, acDialog,
Me.InsuranceCoID
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[AdjusterID]= " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Me.cboAdjusterID.Undo

Response = acDataErrContinue
End If
End Sub

Somethings are more difficult with Macros. This is one of them. They don't
have the power to do what can be done with VBA. Here is how it would be done
in VBA. It will also do away with the error message you are getting. It
will also present the user with a message asking them if they want to add the
new entry or not. Since I don't know the names of your controls, fields, or
forms, you will have to substitue your actual names.

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Docmd.OpenForm "Name Of Your Form", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

'SearchFieldName is the name of the field in the form's recordset you used
to do the original combo box lookup
.FindFirst "[SearchFieldName] = " & NewData & "
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
'Change cboClientSearth to the name of your combo box
Me.cboClientSearch.Undo

Response = acDataErrContinue
End If

This line of code:
.FindFirst "[SearchFieldName] = " & NewData & "
assumes the table field you are using is a numeric data type. If it is
text, userthis syntax:
.FindFirst "[SearchFieldName] = """ & NewData & """"


Now, how to use this.
Open your form in design mode.
Select the combo box.
Open the properties dialog.
Select the events tab.
Delete the reference to the macro.
Click on the small button to the right of the Not In List event's text box
Select Code Builder.
The VB Editor will open
Paste the code above between the lines:

Private Sub NameOfCombo_NotInList(NewData As String, Response As Integer)

<<< The code goes here >>>

End Sub

I expect you may have some problems or questions before you get this
working, but once you have it, it will work much better for you and you have
learned a new trick. Please feel free to post back with any questions you
have.
--
Dave Hargis, Microsoft Access MVP

Not exactly sure what "calling the Macro" means so I'll give you what I can
and hopefully you can get what you need. I created the Macro off of the
Product Name combo box under Properties, Events, NotInList. There is a
dropdown arrow with the Macro option. In that Macro form, I chose OpenForm
and selected Product Form, Data Mode: Add and Dialog mode. That portion works
- the Product Form opens upon a NotInList entry. I enter the new Product
in the Product form, exit it and then back on the original form get an error
that the text entered isn't an item in the list. I checked the code screen
but didn't see anything about this Macro to which to add the Requery
procedure you gave me. I really appreciate your help on this!!

Pamela