By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,154 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,154 IT Pros & Developers. It's quick & easy.

Combo Box - NotInList Event - need help

P: n/a
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/
Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
my-wings wrote:
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


What happens if you set the LimitToList to false based upon whether this
is a new record or not?
Nov 13 '05 #2

P: n/a
my-wings wrote:
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


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

Nov 13 '05 #3

P: n/a
my-wings wrote:
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


Forgot another thing. You might want to change the SQL rowsource for
the combo too.
Nov 13 '05 #4

P: n/a

"Salad" <oi*@vinegar.com> wrote in message
news:rY***************@newsread3.news.pas.earthlin k.net...
my-wings wrote:

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


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

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
Nov 13 '05 #5

P: n/a
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******@TAKEOUTatt.net> wrote in message
news:ke********************@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 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/

Nov 13 '05 #6

P: n/a

"MacDermott" <ma********@nospam.com> wrote in message
news:Un*****************@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 can
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, 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******@TAKEOUTatt.net> wrote in message
news:ke********************@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 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/


Nov 13 '05 #7

P: n/a
Sounds to me as if you have no need for tlkpPubCity -
why not get rid of it?

"my-wings" <my******@TAKEOUTatt.net> wrote in message
news:Ex*****************@bgtnsc04-news.ops.worldnet.att.net...

"MacDermott" <ma********@nospam.com> wrote in message
news:Un*****************@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 can
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, 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******@TAKEOUTatt.net> wrote in message
news:ke********************@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 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/



Nov 13 '05 #8

P: n/a
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" <ma********@nospam.com> wrote in message
news:I2*****************@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******@TAKEOUTatt.net> wrote in message
news:Ex*****************@bgtnsc04-news.ops.worldnet.att.net...

"MacDermott" <ma********@nospam.com> wrote in message
news:Un*****************@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 can
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, 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******@TAKEOUTatt.net> wrote in message
> news:ke********************@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 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/
>>
>>
>
>



Nov 13 '05 #9

P: n/a
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******@TAKEOUTatt.net> wrote in message
news:zB*******************@bgtnsc04-news.ops.worldnet.att.net...
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" <ma********@nospam.com> wrote in message
news:I2*****************@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******@TAKEOUTatt.net> wrote in message
news:Ex*****************@bgtnsc04-news.ops.worldnet.att.net...

"MacDermott" <ma********@nospam.com> wrote in message
news:Un*****************@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 can 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, 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******@TAKEOUTatt.net> wrote in message
> news:ke********************@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

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/
>>
>>
>
>



Nov 13 '05 #10

P: n/a
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" <ma********@nospam.com> wrote in message
news:8D*****************@newsread3.news.atl.earthl ink.net...
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******@TAKEOUTatt.net> wrote in message
news:zB*******************@bgtnsc04-news.ops.worldnet.att.net...
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" <ma********@nospam.com> wrote in message
news:I2*****************@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******@TAKEOUTatt.net> wrote in message
> news:Ex*****************@bgtnsc04-news.ops.worldnet.att.net...
>>
>> "MacDermott" <ma********@nospam.com> wrote in message
>> news:Un*****************@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 can >> 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, 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******@TAKEOUTatt.net> wrote in message
>> > news:ke********************@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
> 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/
>> >>
>> >>
>> >
>> >
>>
>>
>
>



Nov 13 '05 #11

P: n/a
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******@TAKEOUTatt.net> wrote in message
news:T9*******************@bgtnsc04-news.ops.worldnet.att.net...
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" <ma********@nospam.com> wrote in message
news:8D*****************@newsread3.news.atl.earthl ink.net...
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******@TAKEOUTatt.net> wrote in message
news:zB*******************@bgtnsc04-news.ops.worldnet.att.net...
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" <ma********@nospam.com> wrote in message
news:I2*****************@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******@TAKEOUTatt.net> wrote in message
> news:Ex*****************@bgtnsc04-news.ops.worldnet.att.net...
>>
>> "MacDermott" <ma********@nospam.com> wrote in message
>> news:Un*****************@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

can
>> 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, 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******@TAKEOUTatt.net> wrote in message
>> > news:ke********************@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
> 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/
>> >>
>> >>
>> >
>> >
>>
>>
>
>



Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.