I've Got The Dreaded Combo Box Blues!

[The Poster Warehouse]

Been hitting a dead end for months now. Scoured the archives and books
and I still can't get this to work!!

Okay. Here's my sad story:

I have a combo box in a subform and of course I want it to requery. I
know that this has been beaten to death, but somehow
my horse has not received the message & just won't die.

The real problem I am having is getting the table to requery. I have
only had success with the requery via a value list, but not
via a table or query.

The database is used for art prints and the combo box is for the
artist name and title. I know that I should have this field
divided into two fields, but that's not the issue.

Here's the abbreviated structure so as not to complicate things with
the extraneous:

MAIN Table ////////////

ID = AutoNumber
ArtistTitle = Text
OrderNumber = Text
CustomerFirstName = Text
CustomerLastName = Text
Address = Text

ARTIST Table /////////

ID# = AutoNumber
ID = Number
ArtistTitle = Text

The idea here is that in the subform a Customer may order several
prints and this is to be reflected.

ID# ID ArtistTitle
1 1 Monet - Still Life w/ Grapes
2 1 Van Gogh - Wheatfields
3 1 Degas - Ballet Dancers
4 2 Monet - Still Life w/ Grapes
5 2 Cezanne - Still Life w/ Apples

So this works fine. I am able to assign several artists and prints to
one customer's account.

The ideal is to call up the combo box to select the artist/title
needed and if the artist/title is not in the list, the ability to add
it and
have it requery alphabetically

The problem is that the combo box will not requery when I attempt to
add a new artist to the list even with the NotInList event
in place and the "Limit To List" set to "Yes".

I am calling the Table up via a query called ArtistTitle Query so that
the table can be sorted and hopefully requeried
alphabetically. Even if I sort and save the table "A-Z" and use that
directly instead of a query, it always appears via the combo
box in order originally entered.

Here is the NotInList event I am using:

Private Sub Combo26_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo26
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

Executing this as a value list was fine. Executing this with a table
or query allows for the pop-up MsgBox as expected, but
then encounters a error:

"Characters found at end of SQL Statement" and I see that it adds
whatever I enter to the end of the Row Source line;
essentially outside the table :

SELECT [ArtistTitle Query].[ArtistTitle] FROM [ArtistTitle Query];
Monet - The Artist's Garden

If I attempt to add a requery event via the "On Change" it triggers
this error message:

Run-time error '2181':
You must save the current field before you run the requery action.

Even if I could figure out how to accomplish, this I strongly suspect
that my trouble is deeper than that.

I feel that I can't use the value list approach because I see no way
to sort the listing alphabetically.

When I attempt to change the control source to the ArtistTable itself,
and then execute the NotInList event, the error message
appears:

"The record source 'ArtistTitle; Monet - The Artist's Garden'
specified on this form or report does not exist" etc.

This make sense I suppose since the event is referencing Combo26 and
not the ArtistTitle table. When I attempt to substitute
ArtistTitle for "combo26" the system will not even allow the
substitution as "unexpected compile error" is displayed, which I
assume means that you can't rename or reference anything different
from the control source within itself.

Any help is greatly, greatly appreciated. I know I must not be
applying some basics correctly, but this endless loop of trying
and failing has me pulling out what little hair I have left!

Thanks,
John

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

[Steve Jorgensen]

To use the acDataErrAdded option with OnNotInList, you must change the
underlying data so that when Access requeries the combo box, the item
will be there. Access requeries automatically when you respond with
acDataErrAdded.

--
Steve Jorgensen
Database application developer - available
http://www.coho.net/~jorgens

[John Mishefske]

The Poster Warehouse wrote:
> The problem is that the combo box will not requery when I attempt to
> add a new artist to the list even with the NotInList event
> in place and the "Limit To List" set to "Yes".
>

> Here is the NotInList event I am using:
>
> Private Sub Combo26_NotInList(NewData As String, Response As Integer)
> Dim ctl As Control
>
> ' Return Control object that points to combo box.
> Set ctl = Me!Combo26
> ' Prompt user to verify they wish to add new value.
> If MsgBox("Value is not in list. Add it?", _
> vbOKCancel) = vbOK Then
> ' Set Response argument to indicate that data
> ' is being added.
> Response = acDataErrAdded
> ' Add string in NewData argument to row source.
> ctl.RowSource = ctl.RowSource & ";" & NewData
> Else
> ' If user chooses Cancel, suppress error message
> ' and undo changes.
> Response = acDataErrContinue
> ctl.Undo
> End If
> End Sub
>

That code snippet might work for a Value List but not a Table/Query
row source. When working with values from a table you need to add any
new values to the underlying table and then requery the combo box control's
row source to include the new value.

Leave the row source as

SELECT [ArtistTitle Query].[ArtistTitle] FROM [ArtistTitle Query];

and Row Source Type as "Table/Query" but add the new value to the ARTIST
table. I'm assuming that the "ArtistTitle Query" gets all the values in the
ARTIST table.

Try this:

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

Dim nAns As Integer

nAns = MsgBox("Do you want to add '" & NewData & "' to the list of Artists?", _
vbQuestion Or vbYesNo, "New Artist")
If nAns = vbYes Then

CurrentDb.Execute "insert into ARTIST (ArtistTitle) values ('" & _
NewData & "');", dbFailOnError
Response = acDataErrAdded

With Me!Combo26
.SetFocus
.Undo
.Requery
.Value = NewData
End With
Else
Response = acDataErrContinue
Me!Combo26.Undo
End If

End Sub

--
'-------------------------------
' John Mishefske
'-------------------------------

[John Mishefske]

Looks like I had some extra un-needed code in there, try this:

Private Sub Combo26_NotInList(NewData As String, Response As Integer)
Dim nAns As Integer

nAns = MsgBox("Do you want to add '" & NewData & "' to the list of Artists?", _
vbQuestion Or vbYesNo, "New Artist")
If nAns = vbYes Then
CurrentDb.Execute "insert into ARTIST (ArtistTitle) values ('" & _
NewData & "');", dbFailOnError
Response = acDataErrAdded

[The Poster Warehouse]

Hello John

You wrote:

Thanks for the help (Steve too!). Tried the code but I get a "Syntax error in INSERT
INTO statement.

Info you might need to troubleshoot is the real title for the "ARTIST" table is "New
Item Pull Down Menu", but shouldn't I be using the query? I have it currently as:

CurrentDb.Execute "insert into Item Tracking Query (ArtistTitle) values ('" & _

NewData & "');", dbFailOnError

Item Tracking Query is where the "ArtistTitle" field resides. "ArtistTable" is also the
only field in the "New Item Pull Down Menu" Table other than ID (AutoNumber)

Many thanks for your help.

[John Mishefske]

The Poster Warehouse wrote:

> Thanks for the help (Steve too!). Tried the code but I get a "Syntax error in INSERT
> INTO statement.
>
> Info you might need to troubleshoot is the real title for the "ARTIST" table is "New
> Item Pull Down Menu", but shouldn't I be using the query? I have it currently as:
>
> CurrentDb.Execute "insert into Item Tracking Query (ArtistTitle) values ('" & _
> NewData & "');", dbFailOnError
>
> Item Tracking Query is where the "ArtistTitle" field resides. "ArtistTable" is also the
> only field in the "New Item Pull Down Menu" Table other than ID (AutoNumber)
>
> Many thanks for your help.
>
> John
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----

Well I would generally put it in the table underlying the query as so:

CurrentDb.Execute "insert into [New Item Pull Down Menu] (ArtistTitle) values ('" & _

NewData & "');", dbFailOnError

Tables with spaces in the name should be put in brackets [] and generally this is not
a good thing to do. Most likely you could use the query (depending on how the query is
written) by putting the query name in brackets as so:

CurrentDb.Execute "insert into [Item Tracking Query] (ArtistTitle) values ('" & _
NewData & "');", dbFailOnError

--
'-------------------------------
' John Mishefske
'-------------------------------

[The Poster Warehouse]

Hello John

You wrote,

> Well I would generally put it in the table underlying the query as so:
>
> CurrentDb.Execute "insert into [New Item Pull Down Menu] (ArtistTitle) values ('" & _
> NewData & "');", dbFailOnError
>
> Tables with spaces in the name should be put in brackets [] and generally this is not
> a good thing to do. Most likely you could use the query (depending on how the query is
> written) by putting the query name in brackets as so:
>
> CurrentDb.Execute "insert into [Item Tracking Query] (ArtistTitle) values ('" & _
> NewData & "');", dbFailOnError
>
> --
> '-------------------------------
> ' John Mishefske
> '-------------------------------

Well we're close.

I no longer get error messages about syntax, but now the event just loops between asking If I
want to add the name to the list and when I enter yes it says name not in list and looping
again to ask if I want to add. This happens choosing either of the two codes you just
supplied. The LimitToList is set to "yes"

Thanks again

[The Poster Warehouse]

UPDATE:

John,

I checked the Item Pull Down Table and the new entry IS appearing in that table list AND in the
right spot..

Now it's just getting the entry to be:

a) recognized in the subform to keep from looping, and
b) appear in combo box which at present it does not

TIA

[John Mishefske]

OK. Sounds like the blind leading the blind here <g>.

It's looping because it is not recognizing the new artisttitle.
Why don't you try putting in the block I took out as "unnecessary"
after the insert query:

CurrentDb.Execute "insert into ARTIST (ArtistTitle) values ('" & _

NewData & "');", dbFailOnError

Response = acDataErrAdded

' add this block and see if that does the trick:

With Me!Combo26
.SetFocus
.Undo
.Requery
.Value = NewData
End With

Or is that what you tried? This code appears in the subforms' module right?
When you are looking at the code in the editor, does the title bar say:

"Microsoft Visual Basic - <database name> - [Form_<subform name> (Code)]"

where <database name> is the Access database name and the <subform name> is
the name of the subform?

[The Poster Warehouse]

Hi John,

Thanks for keeping an interest in this and for all your assistance.

Here's the update:

The new code suggestions have at least stopped some portion of the looping, but the combo box is still not being requeried.  The "New Item Pull Down" table is being written to, as well as the ArtistTitle field in the "Item Tracking Query".
 

Here's the whole code I'm using:

Private Sub Combo26_NotInList(NewData As String, Response As Integer)
     Dim nAns As Integer

     nAns = MsgBox("Do you want to add '" & NewData & "' to the list of Artists?", _
         vbQuestion Or vbYesNo, "New Artist")
     If nAns = vbYes Then

     CurrentDb.Execute "insert into [Item Tracking Query] (ArtistTitle) values ('" & _

              NewData & "');", dbFailOnError
         Response = acDataErrAdded

          With Me!Combo26
          .SetFocus
          .Undo
          .Requery
          .Value = NewData
      End With

      Else
         Response = acDataErrContinue
         Me!Combo26.Undo
     End If

 End Sub
-----------------------------

I've tried switching from "insert into [Item Tracking Query] (ArtistTitle) "  to ""insert into [New Item Pull Down] (ArtistTitle) " with the same results ...table updates  but not combo box.
 

You are correct in asking:
 

This code appears in the subforms' module right?
When you are looking at the code in the editor, does the title bar say:

 "Microsoft Visual Basic -  - [Form_ (Code)]"

where  is the Access database name and the  is 
the name of the subform?

The answer to the above is, "yes".

What you don't know is that I'm working on a bogus version of the main form and that I have about 18 months worth of data already in place in a non updating field.  That's why I'm trying to get this to work so that I don't have to redo the whole field.

Now, when I try substituing the real table, query and field names I get a little further along but most recently, the error message is one that declares  the "Microsoft Jet database engine cannot find a record in the table with matching key fields(s) 'Item Tracking.ID'
 

However, I think what is happening is I have too many irons in the fire and I'm getting confused as well as confusing you.  Forgive me.  I'm self-taught but I think I will have start considering myself self-mistaught!

What I will do is take a little time starting over fresh via a scaled down demo using your code to see if I can at least get what you have coded  and believe in, to work.  That way, if all goes well, I will have a solid foundation to apply to real database.  Of course I always run the risk of make the fundamental mistakes again, but at least I'll reduce the risk of getting so bogged down, that nothing will work.

Thanks again for your valuable time and insight. If I have additional questions should I post again to the group or would it be more practical to contact you directly?  If this is acceptable, my email is pwpo...@azstarnet.com.  If not, I'll understand.

Many, many thanks.

John Mahoney