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

  • Thread starter Thread starter Fred Boer
  • Start date Start date
F

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..... )
 
Fred Boer said:
So...... how did I manage this change? Well... can you guess? ;)
Click to expand...

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!
 
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
 
Fred Boer said:
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!
Click to expand...

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.
 
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
 
Fred Boer said:
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.
Click to expand...

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!
Click to expand...

You get summers off, even with the new job?
 
So...... how did I manage
this change? Well... can you guess? ;)
Click to expand...

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.
 
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
 
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
 
Fred Boer said:
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...
Click to expand...


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

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

Cheers!
Fred
 
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...
 
Fred Boer said:
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...
Click to expand...

<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
 
Fred Boer said:
Ok, many of you will know (and be sick to the teeth in hearing about) my
small library application.
Click to expand...

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
 
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 said:
Well, that sounds like a useful feature - I'll add it to the list!
Click to expand...

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
 
Fred Boer said:
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>
Click to expand...

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 said:
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/
Click to expand...
 
Back
Top