Combo Box - NotInList Event - need help

I think I've painted myself into a corner, and I'm hoping someone can help
me out.

I have a table of books (tblBooks), which includes a field (strPubName) for
Publisher Name and another field (strPubCity) for Publisher City. These two
fields have a many-to-one relationship with tables, (tlkpPubName and
tlkpPubCity) respectively. The lookup tables only have one field (strPubName
and strPubCity), which is their primary key.

I also have an entry form which has two fields: cbxPubName and cbxPubCity.

cbxPubName is a combo box of RowSourceType "Table/Query" and its RowSource
is tlkpPubName. Its LimitToList property is "yes", and it executes an event
procedure to add new entries to tlkpPubName when the OnNotInList event
occurs.

cbxPubCity used to work the same way (code for that event procedures is
shown below), until I decided to save the entry person (me) some keystrokes
by reducing the city options based on a publisher. Most publishers have only
one city, although some have two. What I did was set cbxPubCity up with it's
RowSource as:

SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
tblBooks.strPubName, tblBooks.strPubCity HAVING
(((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );

This worked great until I had a new publsher. At that point, I could enter
the publisher, adding it to the list with my Event Procedure, but since I
was building the list for cbxPubCity based on the cities available for a
specific strPubName, there were no entries in the cbxPubCity drop down list.
I am caught in a viscious circle where I have to pick something from the
list, but the entry I need will never show up until I can save the record
with the city in it.

Here is my code for the cbxPubCity OnNotInList event:

-------------------------------------------------
Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database, rst As DAO.Recordset

msg = "You have entered a value not in the list." & vbCrLf & "Do you want to
add it?"

If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
Set db = CurrentDb
'Open the recordset that has the RowSource data
Set rst = db.OpenRecordset("tlkpPubCity")
'Add the new data to the recordset
With rst
.AddNew
!strPubCity = NewData 'Add data.
.Update 'Save changes.
.Close
End With
'Tells Access you added a new item
Response = acDataErrAdded

Else
'If No was chosen in the MsgBox then tell
'Access you didn't want the new item
Response = acDataErrContinue
Me.cbxPubCity.Undo

End If
'Clean up after yourself
Set rst = Nothing
Set db = Nothing

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

(Someone here helped me with that code long ago!) I feel there should be
something fairly simple that could be done right before the "else" clause,
but I'm not sure what.

I would apprecaite any suggestions. Thanks.

Alice
--
Book collecting terms illustrated. Occasional books for sale.
http://www.mywingsbooks.com/

re: Combo Box - NotInList Event - need help

my-wings wrote:[color=blue]
> I think I've painted myself into a corner, and I'm hoping someone can help
> me out.
>
> I have a table of books (tblBooks), which includes a field (strPubName) for
> Publisher Name and another field (strPubCity) for Publisher City. These two
> fields have a many-to-one relationship with tables, (tlkpPubName and
> tlkpPubCity) respectively. The lookup tables only have one field (strPubName
> and strPubCity), which is their primary key.
>
> I also have an entry form which has two fields: cbxPubName and cbxPubCity.
>
> cbxPubName is a combo box of RowSourceType "Table/Query" and its RowSource
> is tlkpPubName. Its LimitToList property is "yes", and it executes an event
> procedure to add new entries to tlkpPubName when the OnNotInList event
> occurs.
>
> cbxPubCity used to work the same way (code for that event procedures is
> shown below), until I decided to save the entry person (me) some keystrokes
> by reducing the city options based on a publisher. Most publishers have only
> one city, although some have two. What I did was set cbxPubCity up with it's
> RowSource as:
>
> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
> tblBooks.strPubName, tblBooks.strPubCity HAVING
> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>
> This worked great until I had a new publsher. At that point, I could enter
> the publisher, adding it to the list with my Event Procedure, but since I
> was building the list for cbxPubCity based on the cities available for a
> specific strPubName, there were no entries in the cbxPubCity drop down list.
> I am caught in a viscious circle where I have to pick something from the
> list, but the entry I need will never show up until I can save the record
> with the city in it.
>
> Here is my code for the cbxPubCity OnNotInList event:
>
> -------------------------------------------------
> Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)
>
> Dim db As DAO.Database, rst As DAO.Recordset
>
> msg = "You have entered a value not in the list." & vbCrLf & "Do you want to
> add it?"
>
> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> Set db = CurrentDb
> 'Open the recordset that has the RowSource data
> Set rst = db.OpenRecordset("tlkpPubCity")
> 'Add the new data to the recordset
> With rst
> .AddNew
> !strPubCity = NewData 'Add data.
> .Update 'Save changes.
> .Close
> End With
> 'Tells Access you added a new item
> Response = acDataErrAdded
>
> Else
> 'If No was chosen in the MsgBox then tell
> 'Access you didn't want the new item
> Response = acDataErrContinue
> Me.cbxPubCity.Undo
>
> End If
> 'Clean up after yourself
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
> ----------------------------------------------------------
>
> (Someone here helped me with that code long ago!) I feel there should be
> something fairly simple that could be done right before the "else" clause,
> but I'm not sure what.
>
> I would apprecaite any suggestions. Thanks.
>
> Alice[/color]

What happens if you set the LimitToList to false based upon whether this
is a new record or not?

re: Combo Box - NotInList Event - need help

my-wings wrote:
[color=blue]
> I think I've painted myself into a corner, and I'm hoping someone can help
> me out.
>
> I have a table of books (tblBooks), which includes a field (strPubName) for
> Publisher Name and another field (strPubCity) for Publisher City. These two
> fields have a many-to-one relationship with tables, (tlkpPubName and
> tlkpPubCity) respectively. The lookup tables only have one field (strPubName
> and strPubCity), which is their primary key.
>
> I also have an entry form which has two fields: cbxPubName and cbxPubCity.
>
> cbxPubName is a combo box of RowSourceType "Table/Query" and its RowSource
> is tlkpPubName. Its LimitToList property is "yes", and it executes an event
> procedure to add new entries to tlkpPubName when the OnNotInList event
> occurs.
>
> cbxPubCity used to work the same way (code for that event procedures is
> shown below), until I decided to save the entry person (me) some keystrokes
> by reducing the city options based on a publisher. Most publishers have only
> one city, although some have two. What I did was set cbxPubCity up with it's
> RowSource as:
>
> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
> tblBooks.strPubName, tblBooks.strPubCity HAVING
> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>
> This worked great until I had a new publsher. At that point, I could enter
> the publisher, adding it to the list with my Event Procedure, but since I
> was building the list for cbxPubCity based on the cities available for a
> specific strPubName, there were no entries in the cbxPubCity drop down list.
> I am caught in a viscious circle where I have to pick something from the
> list, but the entry I need will never show up until I can save the record
> with the city in it.
>
> Here is my code for the cbxPubCity OnNotInList event:
>
> -------------------------------------------------
> Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)
>
> Dim db As DAO.Database, rst As DAO.Recordset
>
> msg = "You have entered a value not in the list." & vbCrLf & "Do you want to
> add it?"
>
> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> Set db = CurrentDb
> 'Open the recordset that has the RowSource data
> Set rst = db.OpenRecordset("tlkpPubCity")
> 'Add the new data to the recordset
> With rst
> .AddNew
> !strPubCity = NewData 'Add data.
> .Update 'Save changes.
> .Close
> End With
> 'Tells Access you added a new item
> Response = acDataErrAdded
>
> Else
> 'If No was chosen in the MsgBox then tell
> 'Access you didn't want the new item
> Response = acDataErrContinue
> Me.cbxPubCity.Undo
>
> End If
> 'Clean up after yourself
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
> ----------------------------------------------------------
>
> (Someone here helped me with that code long ago!) I feel there should be
> something fairly simple that could be done right before the "else" clause,
> but I'm not sure what.
>
> I would apprecaite any suggestions. Thanks.
>
> Alice[/color]

Such as
me.Combo0.LimitToList = (Not Me.NewRecord)

re: Combo Box - NotInList Event - need help

my-wings wrote:
[color=blue]
> I think I've painted myself into a corner, and I'm hoping someone can help
> me out.
>
> I have a table of books (tblBooks), which includes a field (strPubName) for
> Publisher Name and another field (strPubCity) for Publisher City. These two
> fields have a many-to-one relationship with tables, (tlkpPubName and
> tlkpPubCity) respectively. The lookup tables only have one field (strPubName
> and strPubCity), which is their primary key.
>
> I also have an entry form which has two fields: cbxPubName and cbxPubCity.
>
> cbxPubName is a combo box of RowSourceType "Table/Query" and its RowSource
> is tlkpPubName. Its LimitToList property is "yes", and it executes an event
> procedure to add new entries to tlkpPubName when the OnNotInList event
> occurs.
>
> cbxPubCity used to work the same way (code for that event procedures is
> shown below), until I decided to save the entry person (me) some keystrokes
> by reducing the city options based on a publisher. Most publishers have only
> one city, although some have two. What I did was set cbxPubCity up with it's
> RowSource as:
>
> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
> tblBooks.strPubName, tblBooks.strPubCity HAVING
> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>
> This worked great until I had a new publsher. At that point, I could enter
> the publisher, adding it to the list with my Event Procedure, but since I
> was building the list for cbxPubCity based on the cities available for a
> specific strPubName, there were no entries in the cbxPubCity drop down list.
> I am caught in a viscious circle where I have to pick something from the
> list, but the entry I need will never show up until I can save the record
> with the city in it.
>
> Here is my code for the cbxPubCity OnNotInList event:
>
> -------------------------------------------------
> Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)
>
> Dim db As DAO.Database, rst As DAO.Recordset
>
> msg = "You have entered a value not in the list." & vbCrLf & "Do you want to
> add it?"
>
> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> Set db = CurrentDb
> 'Open the recordset that has the RowSource data
> Set rst = db.OpenRecordset("tlkpPubCity")
> 'Add the new data to the recordset
> With rst
> .AddNew
> !strPubCity = NewData 'Add data.
> .Update 'Save changes.
> .Close
> End With
> 'Tells Access you added a new item
> Response = acDataErrAdded
>
> Else
> 'If No was chosen in the MsgBox then tell
> 'Access you didn't want the new item
> Response = acDataErrContinue
> Me.cbxPubCity.Undo
>
> End If
> 'Clean up after yourself
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
> ----------------------------------------------------------
>
> (Someone here helped me with that code long ago!) I feel there should be
> something fairly simple that could be done right before the "else" clause,
> but I'm not sure what.
>
> I would apprecaite any suggestions. Thanks.
>
> Alice[/color]

Forgot another thing. You might want to change the SQL rowsource for
the combo too.

re: Combo Box - NotInList Event - need help

"Salad" <oil@vinegar.com> wrote in message
news:rY4Te.1330$9x2.86@newsread3.news.pas.earthlin k.net...[color=blue]
> my-wings wrote:
>[color=green]
>>
>> Here is my code for the cbxPubCity OnNotInList event:
>>
>> -------------------------------------------------
>> Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)
>>
>> Dim db As DAO.Database, rst As DAO.Recordset
>>
>> msg = "You have entered a value not in the list." & vbCrLf & "Do you want
>> to add it?"
>>
>> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
>> Set db = CurrentDb
>> 'Open the recordset that has the RowSource data
>> Set rst = db.OpenRecordset("tlkpPubCity")
>> 'Add the new data to the recordset
>> With rst
>> .AddNew
>> !strPubCity = NewData 'Add data.
>> .Update 'Save changes.
>> .Close
>> End With
>> 'Tells Access you added a new item
>> Response = acDataErrAdded
>>
>> Else
>> 'If No was chosen in the MsgBox then tell
>> 'Access you didn't want the new item
>> Response = acDataErrContinue
>> Me.cbxPubCity.Undo
>>
>> End If
>> 'Clean up after yourself
>> Set rst = Nothing
>> Set db = Nothing
>>
>> End Sub
>> ----------------------------------------------------------
>>
>> (Someone here helped me with that code long ago!) I feel there should be
>> something fairly simple that could be done right before the "else"
>> clause, but I'm not sure what.
>>
>> I would apprecaite any suggestions. Thanks.
>>
>> Alice[/color]
>
> Such as
> me.Combo0.LimitToList = (Not Me.NewRecord)[/color]


I did try adding the line

Me.cbxPubCity.LimitToList = False

right before the "else" line of the above code, but even though the property
was changed, I still got the Access error message: "The text you entered
isn't an item in the list. Select an item from the list, or enter text that
matches one of the listed items." Maybe there's some other place for such an
instruction to go, but I can't think where.

Thanks for your thoughts, though.

Alice

re: Combo Box - NotInList Event - need help

It looks to me as if your combobox doesn't use tlkpPubCity at all, so it
doesn't make any difference that you're adding the new item to that table.
I can see two possible approaches to this:
1. Add a field to tlkpPubCity, so that it has both strPubName and
strPubCity. Modify your combobox to use a query based on this table for its
rowsource, and add code to your NotInList event procedure to add the current
PubName to the new record.
2. Simplify your combobox's RowSource like this:
SELECT tblBooks.strPubCity FROM tblBooks WHERE
(((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
Now you can set its LimitToList property to No in design view. (You
probably couldn't do this before, so it was ineffective to try to do this
programmatically, Salad.) Be sure your ColumnCount, ColumnWidth, and
BoundColumn properties are set correctly.
Since the NotInList event procedure won't fire in this scenario, you can
do your validation in the combobox's BeforeUpdate event, perhaps like this:
If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
if msgbox("No books for " & cbxPubName & " in " & strPubCity &
".", vbyesno,"Add New City?") = vbno then
cbxPubCity.undo
cancel=true
endif
endif

HTH


"my-wings" <my_wings@TAKEOUTatt.net> wrote in message
news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...[color=blue]
> I think I've painted myself into a corner, and I'm hoping someone can help
> me out.
>
> I have a table of books (tblBooks), which includes a field (strPubName)[/color]
for[color=blue]
> Publisher Name and another field (strPubCity) for Publisher City. These[/color]
two[color=blue]
> fields have a many-to-one relationship with tables, (tlkpPubName and
> tlkpPubCity) respectively. The lookup tables only have one field[/color]
(strPubName[color=blue]
> and strPubCity), which is their primary key.
>
> I also have an entry form which has two fields: cbxPubName and cbxPubCity.
>
> cbxPubName is a combo box of RowSourceType "Table/Query" and its RowSource
> is tlkpPubName. Its LimitToList property is "yes", and it executes an[/color]
event[color=blue]
> procedure to add new entries to tlkpPubName when the OnNotInList event
> occurs.
>
> cbxPubCity used to work the same way (code for that event procedures is
> shown below), until I decided to save the entry person (me) some[/color]
keystrokes[color=blue]
> by reducing the city options based on a publisher. Most publishers have[/color]
only[color=blue]
> one city, although some have two. What I did was set cbxPubCity up with[/color]
it's[color=blue]
> RowSource as:
>
> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
> tblBooks.strPubName, tblBooks.strPubCity HAVING
> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>
> This worked great until I had a new publsher. At that point, I could enter
> the publisher, adding it to the list with my Event Procedure, but since I
> was building the list for cbxPubCity based on the cities available for a
> specific strPubName, there were no entries in the cbxPubCity drop down[/color]
list.[color=blue]
> I am caught in a viscious circle where I have to pick something from the
> list, but the entry I need will never show up until I can save the record
> with the city in it.
>
> Here is my code for the cbxPubCity OnNotInList event:
>
> -------------------------------------------------
> Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)
>
> Dim db As DAO.Database, rst As DAO.Recordset
>
> msg = "You have entered a value not in the list." & vbCrLf & "Do you want[/color]
to[color=blue]
> add it?"
>
> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> Set db = CurrentDb
> 'Open the recordset that has the RowSource data
> Set rst = db.OpenRecordset("tlkpPubCity")
> 'Add the new data to the recordset
> With rst
> .AddNew
> !strPubCity = NewData 'Add data.
> .Update 'Save changes.
> .Close
> End With
> 'Tells Access you added a new item
> Response = acDataErrAdded
>
> Else
> 'If No was chosen in the MsgBox then tell
> 'Access you didn't want the new item
> Response = acDataErrContinue
> Me.cbxPubCity.Undo
>
> End If
> 'Clean up after yourself
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
> ----------------------------------------------------------
>
> (Someone here helped me with that code long ago!) I feel there should be
> something fairly simple that could be done right before the "else" clause,
> but I'm not sure what.
>
> I would apprecaite any suggestions. Thanks.
>
> Alice
> --
> Book collecting terms illustrated. Occasional books for sale.
> http://www.mywingsbooks.com/
>
>[/color]

re: Combo Box - NotInList Event - need help

"MacDermott" <macdermott@nospam.com> wrote in message
news:Un6Te.7623$_84.4207@newsread1.news.atl.earthl ink.net...[color=blue]
> It looks to me as if your combobox doesn't use tlkpPubCity at all, so it
> doesn't make any difference that you're adding the new item to that table.[/color]

You're right that the combo box doesn't use tlkpPubCity, but there is a
many-to-one relationship between tlkpPubCity.strPubCity and
tblBooks.strPubCity, and I can't save the record unless I update
tlkpPubCity.

I will pore over your code. I think #2 has possibilities, provided I can
also use the same opportunity to update tlkpPubCity for the reason mentioned
above.

Thanks!

Alice

[color=blue]
> I can see two possible approaches to this:
> 1. Add a field to tlkpPubCity, so that it has both strPubName and
> strPubCity. Modify your combobox to use a query based on this table for
> its
> rowsource, and add code to your NotInList event procedure to add the
> current
> PubName to the new record.
> 2. Simplify your combobox's RowSource like this:
> SELECT tblBooks.strPubCity FROM tblBooks WHERE
> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> Now you can set its LimitToList property to No in design view. (You
> probably couldn't do this before, so it was ineffective to try to do this
> programmatically, Salad.) Be sure your ColumnCount, ColumnWidth, and
> BoundColumn properties are set correctly.
> Since the NotInList event procedure won't fire in this scenario, you
> can
> do your validation in the combobox's BeforeUpdate event, perhaps like
> this:
> If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
> cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
> if msgbox("No books for " & cbxPubName & " in " & strPubCity &
> ".", vbyesno,"Add New City?") = vbno then
> cbxPubCity.undo
> cancel=true
> endif
> endif
>
> HTH
>
>
> "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...[color=green]
>> I think I've painted myself into a corner, and I'm hoping someone can
>> help
>> me out.
>>
>> I have a table of books (tblBooks), which includes a field (strPubName)[/color]
> for[color=green]
>> Publisher Name and another field (strPubCity) for Publisher City. These[/color]
> two[color=green]
>> fields have a many-to-one relationship with tables, (tlkpPubName and
>> tlkpPubCity) respectively. The lookup tables only have one field[/color]
> (strPubName[color=green]
>> and strPubCity), which is their primary key.
>>
>> I also have an entry form which has two fields: cbxPubName and
>> cbxPubCity.
>>
>> cbxPubName is a combo box of RowSourceType "Table/Query" and its
>> RowSource
>> is tlkpPubName. Its LimitToList property is "yes", and it executes an[/color]
> event[color=green]
>> procedure to add new entries to tlkpPubName when the OnNotInList event
>> occurs.
>>
>> cbxPubCity used to work the same way (code for that event procedures is
>> shown below), until I decided to save the entry person (me) some[/color]
> keystrokes[color=green]
>> by reducing the city options based on a publisher. Most publishers have[/color]
> only[color=green]
>> one city, although some have two. What I did was set cbxPubCity up with[/color]
> it's[color=green]
>> RowSource as:
>>
>> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
>> tblBooks.strPubName, tblBooks.strPubCity HAVING
>> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>>
>> This worked great until I had a new publsher. At that point, I could
>> enter
>> the publisher, adding it to the list with my Event Procedure, but since I
>> was building the list for cbxPubCity based on the cities available for a
>> specific strPubName, there were no entries in the cbxPubCity drop down[/color]
> list.[color=green]
>> I am caught in a viscious circle where I have to pick something from the
>> list, but the entry I need will never show up until I can save the record
>> with the city in it.
>>
>> Here is my code for the cbxPubCity OnNotInList event:
>>
>> -------------------------------------------------
>> Private Sub cbxPubCity_NotInList(NewData As String, Response As Integer)
>>
>> Dim db As DAO.Database, rst As DAO.Recordset
>>
>> msg = "You have entered a value not in the list." & vbCrLf & "Do you want[/color]
> to[color=green]
>> add it?"
>>
>> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
>> Set db = CurrentDb
>> 'Open the recordset that has the RowSource data
>> Set rst = db.OpenRecordset("tlkpPubCity")
>> 'Add the new data to the recordset
>> With rst
>> .AddNew
>> !strPubCity = NewData 'Add data.
>> .Update 'Save changes.
>> .Close
>> End With
>> 'Tells Access you added a new item
>> Response = acDataErrAdded
>>
>> Else
>> 'If No was chosen in the MsgBox then tell
>> 'Access you didn't want the new item
>> Response = acDataErrContinue
>> Me.cbxPubCity.Undo
>>
>> End If
>> 'Clean up after yourself
>> Set rst = Nothing
>> Set db = Nothing
>>
>> End Sub
>> ----------------------------------------------------------
>>
>> (Someone here helped me with that code long ago!) I feel there should be
>> something fairly simple that could be done right before the "else"
>> clause,
>> but I'm not sure what.
>>
>> I would apprecaite any suggestions. Thanks.
>>
>> Alice
>> --
>> Book collecting terms illustrated. Occasional books for sale.
>> http://www.mywingsbooks.com/
>>
>>[/color]
>
>[/color]

re: Combo Box - NotInList Event - need help

Sounds to me as if you have no need for tlkpPubCity -
why not get rid of it?

"my-wings" <my_wings@TAKEOUTatt.net> wrote in message
news:Ex7Te.13836$qY1.366@bgtnsc04-news.ops.worldnet.att.net...[color=blue]
>
> "MacDermott" <macdermott@nospam.com> wrote in message
> news:Un6Te.7623$_84.4207@newsread1.news.atl.earthl ink.net...[color=green]
> > It looks to me as if your combobox doesn't use tlkpPubCity at all, so it
> > doesn't make any difference that you're adding the new item to that[/color][/color]
table.[color=blue]
>
> You're right that the combo box doesn't use tlkpPubCity, but there is a
> many-to-one relationship between tlkpPubCity.strPubCity and
> tblBooks.strPubCity, and I can't save the record unless I update
> tlkpPubCity.
>
> I will pore over your code. I think #2 has possibilities, provided I can
> also use the same opportunity to update tlkpPubCity for the reason[/color]
mentioned[color=blue]
> above.
>
> Thanks!
>
> Alice
>
>[color=green]
> > I can see two possible approaches to this:
> > 1. Add a field to tlkpPubCity, so that it has both strPubName and
> > strPubCity. Modify your combobox to use a query based on this table for
> > its
> > rowsource, and add code to your NotInList event procedure to add the
> > current
> > PubName to the new record.
> > 2. Simplify your combobox's RowSource like this:
> > SELECT tblBooks.strPubCity FROM tblBooks WHERE
> > (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> > Now you can set its LimitToList property to No in design view. (You
> > probably couldn't do this before, so it was ineffective to try to do[/color][/color]
this[color=blue][color=green]
> > programmatically, Salad.) Be sure your ColumnCount, ColumnWidth, and
> > BoundColumn properties are set correctly.
> > Since the NotInList event procedure won't fire in this scenario, you
> > can
> > do your validation in the combobox's BeforeUpdate event, perhaps like
> > this:
> > If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
> > cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
> > if msgbox("No books for " & cbxPubName & " in " & strPubCity[/color][/color]
&[color=blue][color=green]
> > ".", vbyesno,"Add New City?") = vbno then
> > cbxPubCity.undo
> > cancel=true
> > endif
> > endif
> >
> > HTH
> >
> >
> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> > news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...[color=darkred]
> >> I think I've painted myself into a corner, and I'm hoping someone can
> >> help
> >> me out.
> >>
> >> I have a table of books (tblBooks), which includes a field (strPubName)[/color]
> > for[color=darkred]
> >> Publisher Name and another field (strPubCity) for Publisher City. These[/color]
> > two[color=darkred]
> >> fields have a many-to-one relationship with tables, (tlkpPubName and
> >> tlkpPubCity) respectively. The lookup tables only have one field[/color]
> > (strPubName[color=darkred]
> >> and strPubCity), which is their primary key.
> >>
> >> I also have an entry form which has two fields: cbxPubName and
> >> cbxPubCity.
> >>
> >> cbxPubName is a combo box of RowSourceType "Table/Query" and its
> >> RowSource
> >> is tlkpPubName. Its LimitToList property is "yes", and it executes an[/color]
> > event[color=darkred]
> >> procedure to add new entries to tlkpPubName when the OnNotInList event
> >> occurs.
> >>
> >> cbxPubCity used to work the same way (code for that event procedures is
> >> shown below), until I decided to save the entry person (me) some[/color]
> > keystrokes[color=darkred]
> >> by reducing the city options based on a publisher. Most publishers have[/color]
> > only[color=darkred]
> >> one city, although some have two. What I did was set cbxPubCity up with[/color]
> > it's[color=darkred]
> >> RowSource as:
> >>
> >> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
> >> tblBooks.strPubName, tblBooks.strPubCity HAVING
> >> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> >>
> >> This worked great until I had a new publsher. At that point, I could
> >> enter
> >> the publisher, adding it to the list with my Event Procedure, but since[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> >> was building the list for cbxPubCity based on the cities available for[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >> specific strPubName, there were no entries in the cbxPubCity drop down[/color]
> > list.[color=darkred]
> >> I am caught in a viscious circle where I have to pick something from[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> list, but the entry I need will never show up until I can save the[/color][/color][/color]
record[color=blue][color=green][color=darkred]
> >> with the city in it.
> >>
> >> Here is my code for the cbxPubCity OnNotInList event:
> >>
> >> -------------------------------------------------
> >> Private Sub cbxPubCity_NotInList(NewData As String, Response As[/color][/color][/color]
Integer)[color=blue][color=green][color=darkred]
> >>
> >> Dim db As DAO.Database, rst As DAO.Recordset
> >>
> >> msg = "You have entered a value not in the list." & vbCrLf & "Do you[/color][/color][/color]
want[color=blue][color=green]
> > to[color=darkred]
> >> add it?"
> >>
> >> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> >> Set db = CurrentDb
> >> 'Open the recordset that has the RowSource data
> >> Set rst = db.OpenRecordset("tlkpPubCity")
> >> 'Add the new data to the recordset
> >> With rst
> >> .AddNew
> >> !strPubCity = NewData 'Add data.
> >> .Update 'Save changes.
> >> .Close
> >> End With
> >> 'Tells Access you added a new item
> >> Response = acDataErrAdded
> >>
> >> Else
> >> 'If No was chosen in the MsgBox then tell
> >> 'Access you didn't want the new item
> >> Response = acDataErrContinue
> >> Me.cbxPubCity.Undo
> >>
> >> End If
> >> 'Clean up after yourself
> >> Set rst = Nothing
> >> Set db = Nothing
> >>
> >> End Sub
> >> ----------------------------------------------------------
> >>
> >> (Someone here helped me with that code long ago!) I feel there should[/color][/color][/color]
be[color=blue][color=green][color=darkred]
> >> something fairly simple that could be done right before the "else"
> >> clause,
> >> but I'm not sure what.
> >>
> >> I would apprecaite any suggestions. Thanks.
> >>
> >> Alice
> >> --
> >> Book collecting terms illustrated. Occasional books for sale.
> >> http://www.mywingsbooks.com/
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

re: Combo Box - NotInList Event - need help

Well, I didn't really think anyone would be interested in the details of
what I did with every field, but if you've ever done a term paper, you know
that publisher city is part of a standard citation, so that's why I need it.

Since the cases where a single publisher produces books in more than one
city are few and far between, it did occur to me that normalization could be
tossed out the window in this case, and I could just put publisher name and
city in the same field and have two entries for the few publishers that
needed them. As an example, in a data base of about 600 books, I only had
about six (1%) where the publisher operated in more than one city.

The reason I avoided this solution (combining the fields) for so long is
that the standard citation convention is "City: Publisher Name". Since about
90% of the publishers seem to be in New York, this would have meant more
keystrokes instead of fewer to select the publisher from a drop down list.

But given the difficulty of my first solution, I did finally cave. I've
gotten rid of the strPubCity field entirely, and am entering all publishers
as "Publisher Name: City" (backwards from the standard citation). If I live
long enough, I might parse this in code by searching for the colon and
flip-flop the parts again when I generate my web pages, but I think I can
live with it for now. (I may be back asking for help to test the field for
the colon, though...I've found myself forgetting it on a few books.)

Thanks for your help though!

Alice

--
All the "lists" and individual book descriptions in the "Books for Sale"
section of the website below are generated by this Access program we've been
discussing:
http://www.mywingsbooks.com/



"MacDermott" <macdermott@nospam.com> wrote in message
news:I2rTe.8592$FW1.8229@newsread3.news.atl.earthl ink.net...[color=blue]
> Sounds to me as if you have no need for tlkpPubCity -
> why not get rid of it?
>
> "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> news:Ex7Te.13836$qY1.366@bgtnsc04-news.ops.worldnet.att.net...[color=green]
>>
>> "MacDermott" <macdermott@nospam.com> wrote in message
>> news:Un6Te.7623$_84.4207@newsread1.news.atl.earthl ink.net...[color=darkred]
>> > It looks to me as if your combobox doesn't use tlkpPubCity at all, so
>> > it
>> > doesn't make any difference that you're adding the new item to that[/color][/color]
> table.[color=green]
>>
>> You're right that the combo box doesn't use tlkpPubCity, but there is a
>> many-to-one relationship between tlkpPubCity.strPubCity and
>> tblBooks.strPubCity, and I can't save the record unless I update
>> tlkpPubCity.
>>
>> I will pore over your code. I think #2 has possibilities, provided I can
>> also use the same opportunity to update tlkpPubCity for the reason[/color]
> mentioned[color=green]
>> above.
>>
>> Thanks!
>>
>> Alice
>>
>>[color=darkred]
>> > I can see two possible approaches to this:
>> > 1. Add a field to tlkpPubCity, so that it has both strPubName and
>> > strPubCity. Modify your combobox to use a query based on this table
>> > for
>> > its
>> > rowsource, and add code to your NotInList event procedure to add the
>> > current
>> > PubName to the new record.
>> > 2. Simplify your combobox's RowSource like this:
>> > SELECT tblBooks.strPubCity FROM tblBooks WHERE
>> > (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>> > Now you can set its LimitToList property to No in design view. (You
>> > probably couldn't do this before, so it was ineffective to try to do[/color][/color]
> this[color=green][color=darkred]
>> > programmatically, Salad.) Be sure your ColumnCount, ColumnWidth, and
>> > BoundColumn properties are set correctly.
>> > Since the NotInList event procedure won't fire in this scenario, you
>> > can
>> > do your validation in the combobox's BeforeUpdate event, perhaps like
>> > this:
>> > If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
>> > cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
>> > if msgbox("No books for " & cbxPubName & " in " & strPubCity[/color][/color]
> &[color=green][color=darkred]
>> > ".", vbyesno,"Add New City?") = vbno then
>> > cbxPubCity.undo
>> > cancel=true
>> > endif
>> > endif
>> >
>> > HTH
>> >
>> >
>> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
>> > news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...
>> >> I think I've painted myself into a corner, and I'm hoping someone can
>> >> help
>> >> me out.
>> >>
>> >> I have a table of books (tblBooks), which includes a field
>> >> (strPubName)
>> > for
>> >> Publisher Name and another field (strPubCity) for Publisher City.
>> >> These
>> > two
>> >> fields have a many-to-one relationship with tables, (tlkpPubName and
>> >> tlkpPubCity) respectively. The lookup tables only have one field
>> > (strPubName
>> >> and strPubCity), which is their primary key.
>> >>
>> >> I also have an entry form which has two fields: cbxPubName and
>> >> cbxPubCity.
>> >>
>> >> cbxPubName is a combo box of RowSourceType "Table/Query" and its
>> >> RowSource
>> >> is tlkpPubName. Its LimitToList property is "yes", and it executes an
>> > event
>> >> procedure to add new entries to tlkpPubName when the OnNotInList event
>> >> occurs.
>> >>
>> >> cbxPubCity used to work the same way (code for that event procedures
>> >> is
>> >> shown below), until I decided to save the entry person (me) some
>> > keystrokes
>> >> by reducing the city options based on a publisher. Most publishers
>> >> have
>> > only
>> >> one city, although some have two. What I did was set cbxPubCity up
>> >> with
>> > it's
>> >> RowSource as:
>> >>
>> >> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP BY
>> >> tblBooks.strPubName, tblBooks.strPubCity HAVING
>> >> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>> >>
>> >> This worked great until I had a new publsher. At that point, I could
>> >> enter
>> >> the publisher, adding it to the list with my Event Procedure, but
>> >> since[/color][/color]
> I[color=green][color=darkred]
>> >> was building the list for cbxPubCity based on the cities available for[/color][/color]
> a[color=green][color=darkred]
>> >> specific strPubName, there were no entries in the cbxPubCity drop down
>> > list.
>> >> I am caught in a viscious circle where I have to pick something from[/color][/color]
> the[color=green][color=darkred]
>> >> list, but the entry I need will never show up until I can save the[/color][/color]
> record[color=green][color=darkred]
>> >> with the city in it.
>> >>
>> >> Here is my code for the cbxPubCity OnNotInList event:
>> >>
>> >> -------------------------------------------------
>> >> Private Sub cbxPubCity_NotInList(NewData As String, Response As[/color][/color]
> Integer)[color=green][color=darkred]
>> >>
>> >> Dim db As DAO.Database, rst As DAO.Recordset
>> >>
>> >> msg = "You have entered a value not in the list." & vbCrLf & "Do you[/color][/color]
> want[color=green][color=darkred]
>> > to
>> >> add it?"
>> >>
>> >> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
>> >> Set db = CurrentDb
>> >> 'Open the recordset that has the RowSource data
>> >> Set rst = db.OpenRecordset("tlkpPubCity")
>> >> 'Add the new data to the recordset
>> >> With rst
>> >> .AddNew
>> >> !strPubCity = NewData 'Add data.
>> >> .Update 'Save changes.
>> >> .Close
>> >> End With
>> >> 'Tells Access you added a new item
>> >> Response = acDataErrAdded
>> >>
>> >> Else
>> >> 'If No was chosen in the MsgBox then tell
>> >> 'Access you didn't want the new item
>> >> Response = acDataErrContinue
>> >> Me.cbxPubCity.Undo
>> >>
>> >> End If
>> >> 'Clean up after yourself
>> >> Set rst = Nothing
>> >> Set db = Nothing
>> >>
>> >> End Sub
>> >> ----------------------------------------------------------
>> >>
>> >> (Someone here helped me with that code long ago!) I feel there should[/color][/color]
> be[color=green][color=darkred]
>> >> something fairly simple that could be done right before the "else"
>> >> clause,
>> >> but I'm not sure what.
>> >>
>> >> I would apprecaite any suggestions. Thanks.
>> >>
>> >> Alice
>> >> --
>> >> Book collecting terms illustrated. Occasional books for sale.
>> >> http://www.mywingsbooks.com/
>> >>
>> >>
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

re: Combo Box - NotInList Event - need help

Gracious!
I wasn't suggesting you get rid of the field!
Only the extra table you're not using, since you're reading your
combobox values directly out of the table underlying the form.


"my-wings" <my_wings@TAKEOUTatt.net> wrote in message
news:zBGTe.19255$qY1.14422@bgtnsc04-news.ops.worldnet.att.net...[color=blue]
> Well, I didn't really think anyone would be interested in the details of
> what I did with every field, but if you've ever done a term paper, you[/color]
know[color=blue]
> that publisher city is part of a standard citation, so that's why I need[/color]
it.[color=blue]
>
> Since the cases where a single publisher produces books in more than one
> city are few and far between, it did occur to me that normalization could[/color]
be[color=blue]
> tossed out the window in this case, and I could just put publisher name[/color]
and[color=blue]
> city in the same field and have two entries for the few publishers that
> needed them. As an example, in a data base of about 600 books, I only had
> about six (1%) where the publisher operated in more than one city.
>
> The reason I avoided this solution (combining the fields) for so long is
> that the standard citation convention is "City: Publisher Name". Since[/color]
about[color=blue]
> 90% of the publishers seem to be in New York, this would have meant more
> keystrokes instead of fewer to select the publisher from a drop down list.
>
> But given the difficulty of my first solution, I did finally cave. I've
> gotten rid of the strPubCity field entirely, and am entering all[/color]
publishers[color=blue]
> as "Publisher Name: City" (backwards from the standard citation). If I[/color]
live[color=blue]
> long enough, I might parse this in code by searching for the colon and
> flip-flop the parts again when I generate my web pages, but I think I can
> live with it for now. (I may be back asking for help to test the field for
> the colon, though...I've found myself forgetting it on a few books.)
>
> Thanks for your help though!
>
> Alice
>
> --
> All the "lists" and individual book descriptions in the "Books for Sale"
> section of the website below are generated by this Access program we've[/color]
been[color=blue]
> discussing:
> http://www.mywingsbooks.com/
>
>
>
> "MacDermott" <macdermott@nospam.com> wrote in message
> news:I2rTe.8592$FW1.8229@newsread3.news.atl.earthl ink.net...[color=green]
> > Sounds to me as if you have no need for tlkpPubCity -
> > why not get rid of it?
> >
> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> > news:Ex7Te.13836$qY1.366@bgtnsc04-news.ops.worldnet.att.net...[color=darkred]
> >>
> >> "MacDermott" <macdermott@nospam.com> wrote in message
> >> news:Un6Te.7623$_84.4207@newsread1.news.atl.earthl ink.net...
> >> > It looks to me as if your combobox doesn't use tlkpPubCity at all, so
> >> > it
> >> > doesn't make any difference that you're adding the new item to that[/color]
> > table.[color=darkred]
> >>
> >> You're right that the combo box doesn't use tlkpPubCity, but there is a
> >> many-to-one relationship between tlkpPubCity.strPubCity and
> >> tblBooks.strPubCity, and I can't save the record unless I update
> >> tlkpPubCity.
> >>
> >> I will pore over your code. I think #2 has possibilities, provided I[/color][/color][/color]
can[color=blue][color=green][color=darkred]
> >> also use the same opportunity to update tlkpPubCity for the reason[/color]
> > mentioned[color=darkred]
> >> above.
> >>
> >> Thanks!
> >>
> >> Alice
> >>
> >>
> >> > I can see two possible approaches to this:
> >> > 1. Add a field to tlkpPubCity, so that it has both strPubName and
> >> > strPubCity. Modify your combobox to use a query based on this table
> >> > for
> >> > its
> >> > rowsource, and add code to your NotInList event procedure to add the
> >> > current
> >> > PubName to the new record.
> >> > 2. Simplify your combobox's RowSource like this:
> >> > SELECT tblBooks.strPubCity FROM tblBooks WHERE
> >> > (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> >> > Now you can set its LimitToList property to No in design view. (You
> >> > probably couldn't do this before, so it was ineffective to try to do[/color]
> > this[color=darkred]
> >> > programmatically, Salad.) Be sure your ColumnCount, ColumnWidth, and
> >> > BoundColumn properties are set correctly.
> >> > Since the NotInList event procedure won't fire in this scenario,[/color][/color][/color]
you[color=blue][color=green][color=darkred]
> >> > can
> >> > do your validation in the combobox's BeforeUpdate event, perhaps like
> >> > this:
> >> > If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
> >> > cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
> >> > if msgbox("No books for " & cbxPubName & " in " &[/color][/color][/color]
strPubCity[color=blue][color=green]
> > &[color=darkred]
> >> > ".", vbyesno,"Add New City?") = vbno then
> >> > cbxPubCity.undo
> >> > cancel=true
> >> > endif
> >> > endif
> >> >
> >> > HTH
> >> >
> >> >
> >> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> >> > news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...
> >> >> I think I've painted myself into a corner, and I'm hoping someone[/color][/color][/color]
can[color=blue][color=green][color=darkred]
> >> >> help
> >> >> me out.
> >> >>
> >> >> I have a table of books (tblBooks), which includes a field
> >> >> (strPubName)
> >> > for
> >> >> Publisher Name and another field (strPubCity) for Publisher City.
> >> >> These
> >> > two
> >> >> fields have a many-to-one relationship with tables, (tlkpPubName and
> >> >> tlkpPubCity) respectively. The lookup tables only have one field
> >> > (strPubName
> >> >> and strPubCity), which is their primary key.
> >> >>
> >> >> I also have an entry form which has two fields: cbxPubName and
> >> >> cbxPubCity.
> >> >>
> >> >> cbxPubName is a combo box of RowSourceType "Table/Query" and its
> >> >> RowSource
> >> >> is tlkpPubName. Its LimitToList property is "yes", and it executes[/color][/color][/color]
an[color=blue][color=green][color=darkred]
> >> > event
> >> >> procedure to add new entries to tlkpPubName when the OnNotInList[/color][/color][/color]
event[color=blue][color=green][color=darkred]
> >> >> occurs.
> >> >>
> >> >> cbxPubCity used to work the same way (code for that event procedures
> >> >> is
> >> >> shown below), until I decided to save the entry person (me) some
> >> > keystrokes
> >> >> by reducing the city options based on a publisher. Most publishers
> >> >> have
> >> > only
> >> >> one city, although some have two. What I did was set cbxPubCity up
> >> >> with
> >> > it's
> >> >> RowSource as:
> >> >>
> >> >> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP[/color][/color][/color]
BY[color=blue][color=green][color=darkred]
> >> >> tblBooks.strPubName, tblBooks.strPubCity HAVING
> >> >> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> >> >>
> >> >> This worked great until I had a new publsher. At that point, I could
> >> >> enter
> >> >> the publisher, adding it to the list with my Event Procedure, but
> >> >> since[/color]
> > I[color=darkred]
> >> >> was building the list for cbxPubCity based on the cities available[/color][/color][/color]
for[color=blue][color=green]
> > a[color=darkred]
> >> >> specific strPubName, there were no entries in the cbxPubCity drop[/color][/color][/color]
down[color=blue][color=green][color=darkred]
> >> > list.
> >> >> I am caught in a viscious circle where I have to pick something from[/color]
> > the[color=darkred]
> >> >> list, but the entry I need will never show up until I can save the[/color]
> > record[color=darkred]
> >> >> with the city in it.
> >> >>
> >> >> Here is my code for the cbxPubCity OnNotInList event:
> >> >>
> >> >> -------------------------------------------------
> >> >> Private Sub cbxPubCity_NotInList(NewData As String, Response As[/color]
> > Integer)[color=darkred]
> >> >>
> >> >> Dim db As DAO.Database, rst As DAO.Recordset
> >> >>
> >> >> msg = "You have entered a value not in the list." & vbCrLf & "Do you[/color]
> > want[color=darkred]
> >> > to
> >> >> add it?"
> >> >>
> >> >> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> >> >> Set db = CurrentDb
> >> >> 'Open the recordset that has the RowSource data
> >> >> Set rst = db.OpenRecordset("tlkpPubCity")
> >> >> 'Add the new data to the recordset
> >> >> With rst
> >> >> .AddNew
> >> >> !strPubCity = NewData 'Add data.
> >> >> .Update 'Save changes.
> >> >> .Close
> >> >> End With
> >> >> 'Tells Access you added a new item
> >> >> Response = acDataErrAdded
> >> >>
> >> >> Else
> >> >> 'If No was chosen in the MsgBox then tell
> >> >> 'Access you didn't want the new item
> >> >> Response = acDataErrContinue
> >> >> Me.cbxPubCity.Undo
> >> >>
> >> >> End If
> >> >> 'Clean up after yourself
> >> >> Set rst = Nothing
> >> >> Set db = Nothing
> >> >>
> >> >> End Sub
> >> >> ----------------------------------------------------------
> >> >>
> >> >> (Someone here helped me with that code long ago!) I feel there[/color][/color][/color]
should[color=blue][color=green]
> > be[color=darkred]
> >> >> something fairly simple that could be done right before the "else"
> >> >> clause,
> >> >> but I'm not sure what.
> >> >>
> >> >> I would apprecaite any suggestions. Thanks.
> >> >>
> >> >> Alice
> >> >> --
> >> >> Book collecting terms illustrated. Occasional books for sale.
> >> >> http://www.mywingsbooks.com/
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

re: Combo Box - NotInList Event - need help

Sent earlier via email by mistake:

Oh. I've probably been living with this for too long, lol. But...what about
the normalization thing? If I have 600 records and 90% of them say "New
York" in that field, shouldn't I be using a separate table to store those
values, rather than having each of my main records say "New York"? Maybe I
don't understand the normalization principle. (And it's merely theoretical
now, since I HAVE actually deleted the table. I've got books to enter...I'd
like this thing to be perfect, but sometimes reality intervenes!)

Alice


"MacDermott" <macdermott@nospam.com> wrote in message
news:8DpUe.9991$FW1.7759@newsread3.news.atl.earthl ink.net...[color=blue]
> Gracious!
> I wasn't suggesting you get rid of the field!
> Only the extra table you're not using, since you're reading your
> combobox values directly out of the table underlying the form.
>
>
> "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> news:zBGTe.19255$qY1.14422@bgtnsc04-news.ops.worldnet.att.net...[color=green]
>> Well, I didn't really think anyone would be interested in the details of
>> what I did with every field, but if you've ever done a term paper, you[/color]
> know[color=green]
>> that publisher city is part of a standard citation, so that's why I need[/color]
> it.[color=green]
>>
>> Since the cases where a single publisher produces books in more than one
>> city are few and far between, it did occur to me that normalization could[/color]
> be[color=green]
>> tossed out the window in this case, and I could just put publisher name[/color]
> and[color=green]
>> city in the same field and have two entries for the few publishers that
>> needed them. As an example, in a data base of about 600 books, I only had
>> about six (1%) where the publisher operated in more than one city.
>>
>> The reason I avoided this solution (combining the fields) for so long is
>> that the standard citation convention is "City: Publisher Name". Since[/color]
> about[color=green]
>> 90% of the publishers seem to be in New York, this would have meant more
>> keystrokes instead of fewer to select the publisher from a drop down
>> list.
>>
>> But given the difficulty of my first solution, I did finally cave. I've
>> gotten rid of the strPubCity field entirely, and am entering all[/color]
> publishers[color=green]
>> as "Publisher Name: City" (backwards from the standard citation). If I[/color]
> live[color=green]
>> long enough, I might parse this in code by searching for the colon and
>> flip-flop the parts again when I generate my web pages, but I think I can
>> live with it for now. (I may be back asking for help to test the field
>> for
>> the colon, though...I've found myself forgetting it on a few books.)
>>
>> Thanks for your help though!
>>
>> Alice
>>
>> --
>> All the "lists" and individual book descriptions in the "Books for Sale"
>> section of the website below are generated by this Access program we've[/color]
> been[color=green]
>> discussing:
>> http://www.mywingsbooks.com/
>>
>>
>>
>> "MacDermott" <macdermott@nospam.com> wrote in message
>> news:I2rTe.8592$FW1.8229@newsread3.news.atl.earthl ink.net...[color=darkred]
>> > Sounds to me as if you have no need for tlkpPubCity -
>> > why not get rid of it?
>> >
>> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
>> > news:Ex7Te.13836$qY1.366@bgtnsc04-news.ops.worldnet.att.net...
>> >>
>> >> "MacDermott" <macdermott@nospam.com> wrote in message
>> >> news:Un6Te.7623$_84.4207@newsread1.news.atl.earthl ink.net...
>> >> > It looks to me as if your combobox doesn't use tlkpPubCity at all,
>> >> > so
>> >> > it
>> >> > doesn't make any difference that you're adding the new item to that
>> > table.
>> >>
>> >> You're right that the combo box doesn't use tlkpPubCity, but there is
>> >> a
>> >> many-to-one relationship between tlkpPubCity.strPubCity and
>> >> tblBooks.strPubCity, and I can't save the record unless I update
>> >> tlkpPubCity.
>> >>
>> >> I will pore over your code. I think #2 has possibilities, provided I[/color][/color]
> can[color=green][color=darkred]
>> >> also use the same opportunity to update tlkpPubCity for the reason
>> > mentioned
>> >> above.
>> >>
>> >> Thanks!
>> >>
>> >> Alice
>> >>
>> >>
>> >> > I can see two possible approaches to this:
>> >> > 1. Add a field to tlkpPubCity, so that it has both strPubName
>> >> > and
>> >> > strPubCity. Modify your combobox to use a query based on this table
>> >> > for
>> >> > its
>> >> > rowsource, and add code to your NotInList event procedure to add the
>> >> > current
>> >> > PubName to the new record.
>> >> > 2. Simplify your combobox's RowSource like this:
>> >> > SELECT tblBooks.strPubCity FROM tblBooks WHERE
>> >> > (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>> >> > Now you can set its LimitToList property to No in design view. (You
>> >> > probably couldn't do this before, so it was ineffective to try to do
>> > this
>> >> > programmatically, Salad.) Be sure your ColumnCount, ColumnWidth,
>> >> > and
>> >> > BoundColumn properties are set correctly.
>> >> > Since the NotInList event procedure won't fire in this scenario,[/color][/color]
> you[color=green][color=darkred]
>> >> > can
>> >> > do your validation in the combobox's BeforeUpdate event, perhaps
>> >> > like
>> >> > this:
>> >> > If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
>> >> > cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
>> >> > if msgbox("No books for " & cbxPubName & " in " &[/color][/color]
> strPubCity[color=green][color=darkred]
>> > &
>> >> > ".", vbyesno,"Add New City?") = vbno then
>> >> > cbxPubCity.undo
>> >> > cancel=true
>> >> > endif
>> >> > endif
>> >> >
>> >> > HTH
>> >> >
>> >> >
>> >> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
>> >> > news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...
>> >> >> I think I've painted myself into a corner, and I'm hoping someone[/color][/color]
> can[color=green][color=darkred]
>> >> >> help
>> >> >> me out.
>> >> >>
>> >> >> I have a table of books (tblBooks), which includes a field
>> >> >> (strPubName)
>> >> > for
>> >> >> Publisher Name and another field (strPubCity) for Publisher City.
>> >> >> These
>> >> > two
>> >> >> fields have a many-to-one relationship with tables, (tlkpPubName
>> >> >> and
>> >> >> tlkpPubCity) respectively. The lookup tables only have one field
>> >> > (strPubName
>> >> >> and strPubCity), which is their primary key.
>> >> >>
>> >> >> I also have an entry form which has two fields: cbxPubName and
>> >> >> cbxPubCity.
>> >> >>
>> >> >> cbxPubName is a combo box of RowSourceType "Table/Query" and its
>> >> >> RowSource
>> >> >> is tlkpPubName. Its LimitToList property is "yes", and it executes[/color][/color]
> an[color=green][color=darkred]
>> >> > event
>> >> >> procedure to add new entries to tlkpPubName when the OnNotInList[/color][/color]
> event[color=green][color=darkred]
>> >> >> occurs.
>> >> >>
>> >> >> cbxPubCity used to work the same way (code for that event
>> >> >> procedures
>> >> >> is
>> >> >> shown below), until I decided to save the entry person (me) some
>> >> > keystrokes
>> >> >> by reducing the city options based on a publisher. Most publishers
>> >> >> have
>> >> > only
>> >> >> one city, although some have two. What I did was set cbxPubCity up
>> >> >> with
>> >> > it's
>> >> >> RowSource as:
>> >> >>
>> >> >> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks GROUP[/color][/color]
> BY[color=green][color=darkred]
>> >> >> tblBooks.strPubName, tblBooks.strPubCity HAVING
>> >> >> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
>> >> >>
>> >> >> This worked great until I had a new publsher. At that point, I
>> >> >> could
>> >> >> enter
>> >> >> the publisher, adding it to the list with my Event Procedure, but
>> >> >> since
>> > I
>> >> >> was building the list for cbxPubCity based on the cities available[/color][/color]
> for[color=green][color=darkred]
>> > a
>> >> >> specific strPubName, there were no entries in the cbxPubCity drop[/color][/color]
> down[color=green][color=darkred]
>> >> > list.
>> >> >> I am caught in a viscious circle where I have to pick something
>> >> >> from
>> > the
>> >> >> list, but the entry I need will never show up until I can save the
>> > record
>> >> >> with the city in it.
>> >> >>
>> >> >> Here is my code for the cbxPubCity OnNotInList event:
>> >> >>
>> >> >> -------------------------------------------------
>> >> >> Private Sub cbxPubCity_NotInList(NewData As String, Response As
>> > Integer)
>> >> >>
>> >> >> Dim db As DAO.Database, rst As DAO.Recordset
>> >> >>
>> >> >> msg = "You have entered a value not in the list." & vbCrLf & "Do
>> >> >> you
>> > want
>> >> > to
>> >> >> add it?"
>> >> >>
>> >> >> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
>> >> >> Set db = CurrentDb
>> >> >> 'Open the recordset that has the RowSource data
>> >> >> Set rst = db.OpenRecordset("tlkpPubCity")
>> >> >> 'Add the new data to the recordset
>> >> >> With rst
>> >> >> .AddNew
>> >> >> !strPubCity = NewData 'Add data.
>> >> >> .Update 'Save changes.
>> >> >> .Close
>> >> >> End With
>> >> >> 'Tells Access you added a new item
>> >> >> Response = acDataErrAdded
>> >> >>
>> >> >> Else
>> >> >> 'If No was chosen in the MsgBox then tell
>> >> >> 'Access you didn't want the new item
>> >> >> Response = acDataErrContinue
>> >> >> Me.cbxPubCity.Undo
>> >> >>
>> >> >> End If
>> >> >> 'Clean up after yourself
>> >> >> Set rst = Nothing
>> >> >> Set db = Nothing
>> >> >>
>> >> >> End Sub
>> >> >> ----------------------------------------------------------
>> >> >>
>> >> >> (Someone here helped me with that code long ago!) I feel there[/color][/color]
> should[color=green][color=darkred]
>> > be
>> >> >> something fairly simple that could be done right before the "else"
>> >> >> clause,
>> >> >> but I'm not sure what.
>> >> >>
>> >> >> I would apprecaite any suggestions. Thanks.
>> >> >>
>> >> >> Alice
>> >> >> --
>> >> >> Book collecting terms illustrated. Occasional books for sale.
>> >> >> http://www.mywingsbooks.com/
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

re: Combo Box - NotInList Event - need help

Well, more experienced developers than me may take issue with what I'm about
to say, but here's how I see it:

If you only have 600 (or even 6000) records, and a reasonably modern
machine, I'd be very surprised if you saw any performance difference at all.
IF you had 6 million records, and most of the time you didn't care what city
the record referenced, you might gain a tiny bit in performance if you
stored a numeric value in your main table, and used it to reference a table
with two columns - one your numeric key and the other the actual string
value of the city name. But my guess is that if you're looking up the
string value for every record, you've lost any performance gains you'd
gotten from using a numeric key.
Now, if you need to store additional information about each city, e.g.
the state it's in, THEN there's a good reason to reference a table. At that
point, you've got 3 (or more) fields in your lookup table, and you only need
one field in your main table. IMHO, that's where normalization really pays
off.
The big problem with storing "New York" in every one of your 600 records
(or in 90% of them) is making sure they all say "New York", not "NY" or
"N.Y." or "New York". But I think we addressed that at the top of this
thread.

I'd be interested in what others have to say on this point...


"my-wings" <my_wings@TAKEOUTatt.net> wrote in message
news:T9YUe.31881$qY1.21032@bgtnsc04-news.ops.worldnet.att.net...[color=blue]
> Sent earlier via email by mistake:
>
> Oh. I've probably been living with this for too long, lol. But...what[/color]
about[color=blue]
> the normalization thing? If I have 600 records and 90% of them say "New
> York" in that field, shouldn't I be using a separate table to store those
> values, rather than having each of my main records say "New York"? Maybe I
> don't understand the normalization principle. (And it's merely theoretical
> now, since I HAVE actually deleted the table. I've got books to[/color]
enter...I'd[color=blue]
> like this thing to be perfect, but sometimes reality intervenes!)
>
> Alice
>
>
> "MacDermott" <macdermott@nospam.com> wrote in message
> news:8DpUe.9991$FW1.7759@newsread3.news.atl.earthl ink.net...[color=green]
> > Gracious!
> > I wasn't suggesting you get rid of the field!
> > Only the extra table you're not using, since you're reading your
> > combobox values directly out of the table underlying the form.
> >
> >
> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> > news:zBGTe.19255$qY1.14422@bgtnsc04-news.ops.worldnet.att.net...[color=darkred]
> >> Well, I didn't really think anyone would be interested in the details[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> >> what I did with every field, but if you've ever done a term paper, you[/color]
> > know[color=darkred]
> >> that publisher city is part of a standard citation, so that's why I[/color][/color][/color]
need[color=blue][color=green]
> > it.[color=darkred]
> >>
> >> Since the cases where a single publisher produces books in more than[/color][/color][/color]
one[color=blue][color=green][color=darkred]
> >> city are few and far between, it did occur to me that normalization[/color][/color][/color]
could[color=blue][color=green]
> > be[color=darkred]
> >> tossed out the window in this case, and I could just put publisher name[/color]
> > and[color=darkred]
> >> city in the same field and have two entries for the few publishers that
> >> needed them. As an example, in a data base of about 600 books, I only[/color][/color][/color]
had[color=blue][color=green][color=darkred]
> >> about six (1%) where the publisher operated in more than one city.
> >>
> >> The reason I avoided this solution (combining the fields) for so long[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> >> that the standard citation convention is "City: Publisher Name". Since[/color]
> > about[color=darkred]
> >> 90% of the publishers seem to be in New York, this would have meant[/color][/color][/color]
more[color=blue][color=green][color=darkred]
> >> keystrokes instead of fewer to select the publisher from a drop down
> >> list.
> >>
> >> But given the difficulty of my first solution, I did finally cave. I've
> >> gotten rid of the strPubCity field entirely, and am entering all[/color]
> > publishers[color=darkred]
> >> as "Publisher Name: City" (backwards from the standard citation). If I[/color]
> > live[color=darkred]
> >> long enough, I might parse this in code by searching for the colon and
> >> flip-flop the parts again when I generate my web pages, but I think I[/color][/color][/color]
can[color=blue][color=green][color=darkred]
> >> live with it for now. (I may be back asking for help to test the field
> >> for
> >> the colon, though...I've found myself forgetting it on a few books.)
> >>
> >> Thanks for your help though!
> >>
> >> Alice
> >>
> >> --
> >> All the "lists" and individual book descriptions in the "Books for[/color][/color][/color]
Sale"[color=blue][color=green][color=darkred]
> >> section of the website below are generated by this Access program we've[/color]
> > been[color=darkred]
> >> discussing:
> >> http://www.mywingsbooks.com/
> >>
> >>
> >>
> >> "MacDermott" <macdermott@nospam.com> wrote in message
> >> news:I2rTe.8592$FW1.8229@newsread3.news.atl.earthl ink.net...
> >> > Sounds to me as if you have no need for tlkpPubCity -
> >> > why not get rid of it?
> >> >
> >> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> >> > news:Ex7Te.13836$qY1.366@bgtnsc04-news.ops.worldnet.att.net...
> >> >>
> >> >> "MacDermott" <macdermott@nospam.com> wrote in message
> >> >> news:Un6Te.7623$_84.4207@newsread1.news.atl.earthl ink.net...
> >> >> > It looks to me as if your combobox doesn't use tlkpPubCity at all,
> >> >> > so
> >> >> > it
> >> >> > doesn't make any difference that you're adding the new item to[/color][/color][/color]
that[color=blue][color=green][color=darkred]
> >> > table.
> >> >>
> >> >> You're right that the combo box doesn't use tlkpPubCity, but there[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> >> >> a
> >> >> many-to-one relationship between tlkpPubCity.strPubCity and
> >> >> tblBooks.strPubCity, and I can't save the record unless I update
> >> >> tlkpPubCity.
> >> >>
> >> >> I will pore over your code. I think #2 has possibilities, provided I[/color]
> > can[color=darkred]
> >> >> also use the same opportunity to update tlkpPubCity for the reason
> >> > mentioned
> >> >> above.
> >> >>
> >> >> Thanks!
> >> >>
> >> >> Alice
> >> >>
> >> >>
> >> >> > I can see two possible approaches to this:
> >> >> > 1. Add a field to tlkpPubCity, so that it has both strPubName
> >> >> > and
> >> >> > strPubCity. Modify your combobox to use a query based on this[/color][/color][/color]
table[color=blue][color=green][color=darkred]
> >> >> > for
> >> >> > its
> >> >> > rowsource, and add code to your NotInList event procedure to add[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> >> > current
> >> >> > PubName to the new record.
> >> >> > 2. Simplify your combobox's RowSource like this:
> >> >> > SELECT tblBooks.strPubCity FROM tblBooks WHERE
> >> >> > (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> >> >> > Now you can set its LimitToList property to No in design view.[/color][/color][/color]
(You[color=blue][color=green][color=darkred]
> >> >> > probably couldn't do this before, so it was ineffective to try to[/color][/color][/color]
do[color=blue][color=green][color=darkred]
> >> > this
> >> >> > programmatically, Salad.) Be sure your ColumnCount, ColumnWidth,
> >> >> > and
> >> >> > BoundColumn properties are set correctly.
> >> >> > Since the NotInList event procedure won't fire in this[/color][/color][/color]
scenario,[color=blue][color=green]
> > you[color=darkred]
> >> >> > can
> >> >> > do your validation in the combobox's BeforeUpdate event, perhaps
> >> >> > like
> >> >> > this:
> >> >> > If IsNull(DLookup("strPubCity","tblBooks","strPubName ='" &
> >> >> > cbxPubName & "' AND strPubCity='" & cbxPubCity & "'")) then
> >> >> > if msgbox("No books for " & cbxPubName & " in " &[/color]
> > strPubCity[color=darkred]
> >> > &
> >> >> > ".", vbyesno,"Add New City?") = vbno then
> >> >> > cbxPubCity.undo
> >> >> > cancel=true
> >> >> > endif
> >> >> > endif
> >> >> >
> >> >> > HTH
> >> >> >
> >> >> >
> >> >> > "my-wings" <my_wings@TAKEOUTatt.net> wrote in message
> >> >> > news:ke4Te.190607$5N3.77449@bgtnsc05-news.ops.worldnet.att.net...
> >> >> >> I think I've painted myself into a corner, and I'm hoping someone[/color]
> > can[color=darkred]
> >> >> >> help
> >> >> >> me out.
> >> >> >>
> >> >> >> I have a table of books (tblBooks), which includes a field
> >> >> >> (strPubName)
> >> >> > for
> >> >> >> Publisher Name and another field (strPubCity) for Publisher City.
> >> >> >> These
> >> >> > two
> >> >> >> fields have a many-to-one relationship with tables, (tlkpPubName
> >> >> >> and
> >> >> >> tlkpPubCity) respectively. The lookup tables only have one field
> >> >> > (strPubName
> >> >> >> and strPubCity), which is their primary key.
> >> >> >>
> >> >> >> I also have an entry form which has two fields: cbxPubName and
> >> >> >> cbxPubCity.
> >> >> >>
> >> >> >> cbxPubName is a combo box of RowSourceType "Table/Query" and its
> >> >> >> RowSource
> >> >> >> is tlkpPubName. Its LimitToList property is "yes", and it[/color][/color][/color]
executes[color=blue][color=green]
> > an[color=darkred]
> >> >> > event
> >> >> >> procedure to add new entries to tlkpPubName when the OnNotInList[/color]
> > event[color=darkred]
> >> >> >> occurs.
> >> >> >>
> >> >> >> cbxPubCity used to work the same way (code for that event
> >> >> >> procedures
> >> >> >> is
> >> >> >> shown below), until I decided to save the entry person (me) some
> >> >> > keystrokes
> >> >> >> by reducing the city options based on a publisher. Most[/color][/color][/color]
publishers[color=blue][color=green][color=darkred]
> >> >> >> have
> >> >> > only
> >> >> >> one city, although some have two. What I did was set cbxPubCity[/color][/color][/color]
up[color=blue][color=green][color=darkred]
> >> >> >> with
> >> >> > it's
> >> >> >> RowSource as:
> >> >> >>
> >> >> >> SELECT tblBooks.strPubName, tblBooks.strPubCity FROM tblBooks[/color][/color][/color]
GROUP[color=blue][color=green]
> > BY[color=darkred]
> >> >> >> tblBooks.strPubName, tblBooks.strPubCity HAVING
> >> >> >> (((tblBooks.strPubName)=Forms!frmBooks!cbxPubName) );
> >> >> >>
> >> >> >> This worked great until I had a new publsher. At that point, I
> >> >> >> could
> >> >> >> enter
> >> >> >> the publisher, adding it to the list with my Event Procedure, but
> >> >> >> since
> >> > I
> >> >> >> was building the list for cbxPubCity based on the cities[/color][/color][/color]
available[color=blue][color=green]
> > for[color=darkred]
> >> > a
> >> >> >> specific strPubName, there were no entries in the cbxPubCity drop[/color]
> > down[color=darkred]
> >> >> > list.
> >> >> >> I am caught in a viscious circle where I have to pick something
> >> >> >> from
> >> > the
> >> >> >> list, but the entry I need will never show up until I can save[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> > record
> >> >> >> with the city in it.
> >> >> >>
> >> >> >> Here is my code for the cbxPubCity OnNotInList event:
> >> >> >>
> >> >> >> -------------------------------------------------
> >> >> >> Private Sub cbxPubCity_NotInList(NewData As String, Response As
> >> > Integer)
> >> >> >>
> >> >> >> Dim db As DAO.Database, rst As DAO.Recordset
> >> >> >>
> >> >> >> msg = "You have entered a value not in the list." & vbCrLf & "Do
> >> >> >> you
> >> > want
> >> >> > to
> >> >> >> add it?"
> >> >> >>
> >> >> >> If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
> >> >> >> Set db = CurrentDb
> >> >> >> 'Open the recordset that has the RowSource data
> >> >> >> Set rst = db.OpenRecordset("tlkpPubCity")
> >> >> >> 'Add the new data to the recordset
> >> >> >> With rst
> >> >> >> .AddNew
> >> >> >> !strPubCity = NewData 'Add data.
> >> >> >> .Update 'Save changes.
> >> >> >> .Close
> >> >> >> End With
> >> >> >> 'Tells Access you added a new item
> >> >> >> Response = acDataErrAdded
> >> >> >>
> >> >> >> Else
> >> >> >> 'If No was chosen in the MsgBox then tell
> >> >> >> 'Access you didn't want the new item
> >> >> >> Response = acDataErrContinue
> >> >> >> Me.cbxPubCity.Undo
> >> >> >>
> >> >> >> End If
> >> >> >> 'Clean up after yourself
> >> >> >> Set rst = Nothing
> >> >> >> Set db = Nothing
> >> >> >>
> >> >> >> End Sub
> >> >> >> ----------------------------------------------------------
> >> >> >>
> >> >> >> (Someone here helped me with that code long ago!) I feel there[/color]
> > should[color=darkred]
> >> > be
> >> >> >> something fairly simple that could be done right before the[/color][/color][/color]
"else"[color=blue][color=green][color=darkred]
> >> >> >> clause,
> >> >> >> but I'm not sure what.
> >> >> >>
> >> >> >> I would apprecaite any suggestions. Thanks.
> >> >> >>
> >> >> >> Alice
> >> >> >> --
> >> >> >> Book collecting terms illustrated. Occasional books for sale.
> >> >> >> http://www.mywingsbooks.com/
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]