Concensus book for intermediates and help-needed examples

Hello,

Got that great double set of Developers Handbooks, but as
much as I know about Access I seem to be missing a lot
between basics and that offered in the Handbooks. (Some
examples below.) Is there a consensus on a good Access
book for learning the intermediate things involving a
complex database design (15 to 20 tables and various 1-1,
1-many, many-many relationships)?

Examples of the kind of thing I need to learn:
* On a form, fill city and state fields in Contact table
when a ZIP Code is selected from from ZIPCode table combo
box.

* Limit the number of records in a Configuration table to
one -- editing, but no adding or deleting.

* Have the title on a form be a variable which gets
filled from a field in the Configuration table.

* And sooooo many more things!!!

Thanks,
George

Hi George.

Will let others suggest the books, but the examples you ask about can all be
solved with DLookup():
Private Sub Zip_AfterUpdate()
If Not IsNull(Me.Zip) Then
Me.City = DLookup("City", "ZIPCode table", "Zip = """ & Me.Zip &
"""")
End If
End Sub

For basics on how DLookup() works, see:
http://allenbrowne.com/casu-07.html

For suggestions on how to set up a configuration table to retrieve value
from, see:
http://allenbrowne.com/ser-18.html

To create a table that can have only one record, use a Number type for the
primary key, and set this Validation Rule for the field (lower pane in table
design):
[ID] = 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"George" wrote in message
...

Got that great double set of Developers Handbooks, but as
much as I know about Access I seem to be missing a lot
between basics and that offered in the Handbooks. (Some
examples below.) Is there a consensus on a good Access
book for learning the intermediate things involving a
complex database design (15 to 20 tables and various 1-1,
1-many, many-many relationships)?

Examples of the kind of thing I need to learn:
* On a form, fill city and state fields in Contact table
when a ZIP Code is selected from from ZIPCode table combo
box.

* Limit the number of records in a Configuration table to
one -- editing, but no adding or deleting.

* Have the title on a form be a variable which gets
filled from a field in the Configuration table.

* And sooooo many more things!!!

Thanks,
George

Hello Allan,

I want to respond in a timely fashion, though I haven't
been able to get your code to work yet. I get the gist
of what you're suggesting and it's bumped me up a bit in
my understanding of how Access works. So thanks much.

George

-----Original Message-----
Hi George.

Will let others suggest the books, but the examples you
ask about can all be
solved with DLookup():
Private Sub Zip_AfterUpdate()
If Not IsNull(Me.Zip) Then
Me.City = DLookup("City", "ZIPCode table", "Zip
= """ & Me.Zip &
"""")
End If
End Sub

For basics on how DLookup() works, see:
http://allenbrowne.com/casu-07.html

For suggestions on how to set up a configuration table
to retrieve value
from, see:
http://allenbrowne.com/ser-18.html

To create a table that can have only one record, use a
Number type for the
primary key, and set this Validation Rule for the field
(lower pane in table
design):
[ID] = 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"George" wrote in
message
...

Got that great double set of Developers Handbooks, but
as
much as I know about Access I seem to be missing a lot
between basics and that offered in the Handbooks.
(Some
examples below.) Is there a consensus on a good Access
book for learning the intermediate things involving a
complex database design (15 to 20 tables and various 1-
1,
1-many, many-many relationships)?

Examples of the kind of thing I need to learn:
* On a form, fill city and state fields in Contact
table
when a ZIP Code is selected from from ZIPCode table
combo
box.

* Limit the number of records in a Configuration table
to
one -- editing, but no adding or deleting.

* Have the title on a form be a variable which gets
filled from a field in the Configuration table.

* And sooooo many more things!!!

Thanks,
George


.

I setup my Configuration table ID as you suggested and
added the Validation Rule, but wasn't quite satisfied
with the results because it didn't address what the user
would see. So I investigated the form settings and
discovered the Form Selector for the first time! This
was the finishing touch to your suggestion. I disallowed
additions and deletions and removed the navigation bar.
So a user can open the form, make changes to the record
displayed, and and close the form, but never add a new
record.

Yahoo!

Alan - this information you have shared is wonderful.

I'm able to make great use of the DLookup examples on your website.

Thanks so much.

Thanks. Feedback appreciated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul James" wrote in message
...
Alan - this information you have shared is wonderful.

I'm able to make great use of the DLookup examples on your website.

Thanks so much.

Finally got this straightened out. I couldn't get the
coding to work with the Contact ZIP field, but as soon as
I put it with the combo box everything worked.

Yay.

-----Original Message-----
Hi George.

Will let others suggest the books, but the examples you
ask about can all be
solved with DLookup():
Private Sub Zip_AfterUpdate()
If Not IsNull(Me.Zip) Then
Me.City = DLookup("City", "ZIPCode table", "Zip
= """ & Me.Zip &
"""")
End If
End Sub

For basics on how DLookup() works, see:
http://allenbrowne.com/casu-07.html

For suggestions on how to set up a configuration table
to retrieve value
from, see:
http://allenbrowne.com/ser-18.html

To create a table that can have only one record, use a
Number type for the
primary key, and set this Validation Rule for the field
(lower pane in table
design):
[ID] = 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"George" wrote in
message
...

Got that great double set of Developers Handbooks, but
as
much as I know about Access I seem to be missing a lot
between basics and that offered in the Handbooks.
(Some
examples below.) Is there a consensus on a good Access
book for learning the intermediate things involving a
complex database design (15 to 20 tables and various 1-
1,
1-many, many-many relationships)?

Examples of the kind of thing I need to learn:
* On a form, fill city and state fields in Contact
table
when a ZIP Code is selected from from ZIPCode table
combo
box.

* Limit the number of records in a Configuration table
to
one -- editing, but no adding or deleting.

* Have the title on a form be a variable which gets
filled from a field in the Configuration table.

* And sooooo many more things!!!

Thanks,
George


.