473,287 Members | 1,674 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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/
Nov 13 '05 #1
11 2760
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
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
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

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

"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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: jake | last post by:
I need to create a combobox that allows users the option to add entries to the dropdown list. I don't want every new entry to to be added to the combobox list, just the more comman ones. Is this...
3
by: Marcus Canales | last post by:
Hello all, Here is my situation. I have a combo box on a form. The combo box is linked to a qry (qryCustomers) and the form is linked to tblOrders. The way I currently use the combo box is to...
3
by: mal | last post by:
Sorry for repost - system added to another subject for some reason Have tried numerous ideas from the group to solve this one. It is such a simple example that it should be straightforward ! I...
2
by: Gustavo\ | last post by:
Hi, I have a combo box in a form. It has limit to list set to "yes", and I have declared a NotOnList event procedure to take care of adding a value not on the list. Now, my problem is that,...
7
by: tina.boroff | last post by:
I have a combo box that gathers insurance company names from my CCtable. If you add a name that isn't in the list it: 1)asks if you want to add the company 2)if so opens up the CCform (based...
0
by: Gautam Dasaka | last post by:
Hi, I am creating an Access database form to store invoice information in the same file. This is a single user file, but tested on a few workstations.. I am using a combo box (CboSupplier_List)...
1
imrosie
by: imrosie | last post by:
Hello (from Rosie the newbie), I recently got help with a wonderful event to perform this from 'thescripts'...it recognizes that a name is not in the list an allows for (after parsing first and...
1
by: Jason Northampton | last post by:
Hello This is the first time I've used a discusion forum and up until now I have managed to use and or modify VB code from the various sites on the web, until now! This is a simple problem and I...
1
by: DJH | last post by:
I’m really having a problem with a project and I wonder if someone could help me. I have a bound combo box with multiple columns on a form called Concentrates For Parts. It is powered by a query...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.