Something hard turned out to be easy! Can you guess how? :)

[Fred Boer]

Hi All:

I've just had one of those fun moments in working with Access - and I just
have to talk about it! And, of course, there's nobody who might be the
slightest bit interested, except in here!

Ok, many of you will know (and be sick to the teeth in hearing about) my
small library application. Well, I had someone who was trying out the
application contact me by email and ask for a change in how the application
works. I thought that this change would be a complete re-write of big chunks
of the thing and told her I couldn't do it. But then I went in and tinkered
and I found an easy solution! And, yes, you will have every right to roll
your eyes in wonder that this wouldn't have ocurred to me instantly, or that
I didn't do this from the start - but I'm tickled with it...

In the application, Patrons are assigned an ID number. In tblPatrons, this
is assigned using an Autonumber field. (I know, that's not a great thing to
do, but bear with me...). The circulation system uses this ID number to
manage tracking borrowed books. To sign out a book, you choose a patron
(either by name, or by IDnumber) from a combobox. The Patron ID number comes
into play in a couple of other forms: one in which you search for Patron
borrowing records, and, of course, in the form bound to tblPatrons toedit
Patron information.

My user said "My patrons have an existing ID number - can't I use that
number instead?". My PatronID number is autonumber; her ID numbers were
alphanumeric. So, I thought, "no way!". That will involve oodles of code
rewriting, and form re-editing. But it didn't! So...... how did I manage
this change? Well... can you guess?

P.S. (I *think* I've given enough info..... )

 

[Dirk Goldgar]

So...... how did I manage this change? Well... can you guess?

I can think of several ways to go about it, Fred, though I don't know which
of them would be considered "easy" and which would not. Come on and tell us
what you did!

 

[Fred Boer]

Hi Dirk:

Oh, you twisted my arm! Well, I added a field for the "new" patron ID
(PatronIDNew) in tblPatrons. Then, all I did was change every patron
combobox to include this new field. I hid the "real" PatronID number column
in the combobox, and displayed the "new" PatronIDNew number instead.

That's it! The user thinks she is using "her" ID number, but all the code
still runs just as it always has. I also had to add a new field in the
patron information form for her to insert her own number, and voila!

SO.. it's just a shell game - I manipulate a few comboboxes and it looks
like I've made significant changes to the application. It isn't really much,
but I really wasn't expecting to be able to manage this, and it turned out
to be easy, and, I just thought it was neat! Sometimes an "Access" hobby can
be so fun!

Cheers!
Fred

 

[Dirk Goldgar]

Hi Dirk:

Oh, you twisted my arm! Well, I added a field for the "new" patron ID
(PatronIDNew) in tblPatrons. Then, all I did was change every patron
combobox to include this new field. I hid the "real" PatronID number
column in the combobox, and displayed the "new" PatronIDNew number
instead.

That's it! The user thinks she is using "her" ID number, but all the code
still runs just as it always has. I also had to add a new field in the
patron information form for her to insert her own number, and voila!

Sweet and simple. I thought it might be something like that.

Now, since you got me thinking about it ... if you wanted to make your
application even more flexible, you could set up your PatronID combo boxes
with rowsources like this:

SELECT PatronID, Nz(UserPatronID, PatronID As DisplayID
FROM tblPatrons;

That way, if they use their own PatronID (called, in this case,
UserPatronID), that's what the combo will display. If they don't use that
field, then they get the system-generated ID.

If you were doing that, you'd wouldn't want to mix the two cases; you'd
either want them to supply their own ID for all patrons, or else use the
system-generated ID for all patrons. Otherwise, you could potentially have
the same ID value for two different patrons -- one system-generated and one
user-defined. So you'd want to have an option stored in a configuration
table, indicating which system they are using. In code for your Patron
form, you'd show or hide the UserPatronID text box, and make it required or
not, according to the option setting.

 

[Fred Boer]

Dear Dirk:

Thanks for the excellent suggestion! I hadn't thought of trying to make the
application flexible enough to give the user the choice. I'll implement that
as the next item on my Access "to do" list. With summer vacation coming up,
I might actually have time to do it as well!

Cheers!
Fred

 

[Dirk Goldgar]

Dear Dirk:

Thanks for the excellent suggestion! I hadn't thought of trying to make
the application flexible enough to give the user the choice. I'll
implement that as the next item on my Access "to do" list.

You're welcome. I didn't mean to give you extra work, but it serves you
right for teasing us.

With summer vacation coming up, I might actually have time to do it as
well!

You get summers off, even with the new job?

 

[John W. Vinson]

So...... how did I manage
this change? Well... can you guess?

Well, I'd have probably used either a Text ID field in parallel with the
autonumber, or a one-to-one relationship to an alternate-ID table.

 

[Arvin Meyer MVP]

Hey Fred, have you seen this:

http://www.datastrat.com/Download/AlphaNumeric2K.zip

it might give you some ideas.

 

[Fred Boer]

Mea culpa! I'll do the work as my penance!

Do I still get summers off?! Surely you jest! Yes, I'm still officially a
teacher, and still have that great benefit! I will, however, need to be
spending a lot of time this summer studying to be prepared for next year!

But yes, the vacation is still ahead of us - we think we'll be taking a trip
to Florida at some point during the summer...

Cheers!
Fred

 

[Fred Boer]

Hi John!

A one-to-one relationship, eh? Hmm. That gives me something to think about;
I've never used a one-to-one relationship..

Thanks!
Fred

 

[Dirk Goldgar]

But yes, the vacation is still ahead of us - we think we'll be taking a
trip to Florida at some point during the summer...

If you make a mistake and pass through New Jersey, give me a call.

 

[Fred Boer]

Hello Arvin:

Thanks for that link! It certainly could come in handy! Luckily your website
is in my favourites for quick reference!

Cheers!
Fred

 

[Fred Boer]

I'd love that, Dirk! But it's hard to get lost when the directions are:
"Take the I-75 south for two days...."

Fred

P.S. But still, ... if I secretly re-program the Tom Tom, maybe nobody would
notice...

 

[Tony Toews [MVP]]

But yes, the vacation is still ahead of us - we think we'll be taking a trip
to Florida at some point during the summer...

<shudder> Who'd want to go to hot humid Florida in summer. Come on
up to Canada where it's cooler. <smile> No, wait, you're Canadian.

Tony

 

[Tony Toews [MVP]]

Ok, many of you will know (and be sick to the teeth in hearing about) my
small library application.

No, we're not sick of hearing about your library app. I'm quite happy
to read news of how it's going.

BTW what I'd really, really like in such an app is to flag certain
authors as being favourites. Then the program using the Z39.50
protocol to find all books by that author, but not large print, audio,
etc. And then I can run this every three or six months and it will
tell me what books are new for all such authors. Then I can either
purchase those books or inter library loan them.

Tony

 

[Fred Boer]

I know what you mean, Tony - for most of my life I avoided Florida,
imagining I wouldn't like it. And the summer is really too hot, but various
circumstances are making this a reasonable choice.

I was enticed down to Florida a few years ago and my family and I found we
really liked it: driving down a little gravel road in the everglades and
seeing real, live wild alligators, swimming in the ocean and seeing a manta,
barracuda and lots of little tropical fish around our feet, and just the fun
of going to an exotic foreign country!

Fred

P.S. And... of course... air conditioning! <g>

 

[Fred Boer]

Hi Tony:

Well, that sounds like a useful feature - I'll add it to the list!

Fred

 

[Tony Toews [MVP]]

Well, that sounds like a useful feature - I'll add it to the list!

Let me know when/if it makes it there. I'm sure there's lots of
readers who'd love that kind of functionality.

Tony

 

[Tony Toews [MVP]]

I know what you mean, Tony - for most of my life I avoided Florida,
imagining I wouldn't like it. And the summer is really too hot, but various
circumstances are making this a reasonable choice.

I was enticed down to Florida a few years ago and my family and I found we
really liked it: driving down a little gravel road in the everglades and
seeing real, live wild alligators, swimming in the ocean and seeing a manta,
barracuda and lots of little tropical fish around our feet, and just the fun
of going to an exotic foreign country!

Fred

P.S. And... of course... air conditioning! <g>

Hehehe.

I was complaining to Arvin Meyer, who lives in Florida, about why
there was a MS Access conference a number of years back in September
in FL. I wanted it to be in February once I was tired on Canadian
winter. Arvin reminded me that hotel rooms were a lot more expensive
in February then September.

Tony

 

[daniel rau]

Hehehe.

I was complaining to Arvin Meyer, who lives in Florida, about why
there was a MS Access conference a number of years back in September
in FL. I wanted it to be in February once I was tired on Canadian
winter. Arvin reminded me that hotel rooms were a lot more expensive
in February then September.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/