J
JoJo
I have been using a functional, albeit somewhat cumbersome, Access database
that I developed with almost zippo previous knowledge of Access (hence the
cumbersome but functional part). I decided that I’m tired of “cumbersome.â€
I’ve seen threads that address part of my issue; I suspect someone’s going to
tell me I’m doing things all wrong, but I’m leery of doing a major rewire.
However, since my complaint is not about storage but about viewing data,
well, here goes.
I’m working in Access 2003. Basically, I have 2 tables: Patients and TestA
(also have B thru J, but if I can get A to work, the rest are the same).
Patients table has 3 columns: PtID (PKey), FirstName, LastName
TestA has 10 columns: APtID (PKey), AResult1, AResult2, AResult3, etc.
through AResult9.
Patients table is relatively static—patient names and ID numbers have been
entered already. The two tables have a 1-to-1 relationship based on PtID.
TestA table contains results of Test A for patients who have completed that
test. Those results have been entered via a form, also designated TestA.
Data capture/storage is fine. My issue is with the form, specifically
viewing of FirstName and LastName as identification confirmation. Yes, I’ve
seen this issue on several threads on this site.
OK, so I wanted my form to reflect everything in the TestA table (which it
does) PLUS the patient first and last name from the Patients table. As I’ve
seen on this site, one easy way to do that is a DLookup. However, Access
2003 also lets you base a form on multiple tables, which displays like
DLookup, but is even easier(but probably just as evil, developmentally?) to
set up, including concatenating the names. Seriously, no query, no subform,
no mess, no fuss.
That, in a nutshell, is the basis for every question to follow.
What I want is to be able to 1) go to a new record on the form, 2) enter a
PtID in the control box on the form (control source is the PtID from the
TestA table), and 3) have Access pull the patient’s name from the Patients
table into the control on the form whose control source is the concatenated
FirstName and LastName from the Patients table. I’m not trying to plug that
info into the TestA table—just want to SEE it on the form.
Then I want to 4) be able to enter data on the rest of the screen. Here’s
what I’m doing now; it’s working, but inefficiently cumbersome.
I enter the PtID in that box on the form. Then I click on the control box
where the Names should appear. What I have discovered is that just because I
told Access I want the Patients table to cough up the associated patient name
value for that PtID, it doesn’t mean that it will. One apparently has to pat
Access’ back to get it to cough, and apparently one way to do that is the
Action called Requery.
So I set up a macro to Requery when I clicked on the name control box. That
pulls the names from Patient alright, but I only see it blink, and then
Access immediately kicks me to the beginning of the record set where, yikes,
if I don’t realize where I am and start typing, I can overwrite existing data
by touching just about any key.
OK, so what I would like from the point of Requery is a way to keep myself
on that new record. That is eluding me. The best I’ve been able to do is to
add an Action to the macro, GoToRecord, which gives me the choice of getting
kicked to a brand-new record, where at least I can’t damage anything already
in there. Then I have to manually scroll my way through the record set to
the new record, which shows the PtID, has the name nice and updated, and has
the rest of the form all pretty and blank and ready to capture data. That’s
a royal pain (I mean, after all, isn’t a computer supposed to do everything
for you?!), and it lays open the later possibility that somebody who is not
as invested in all of this as I am is going to goof something up big time.
(Note: I’m the only user for now.)
My workaround, now that I have a hundred patients to scroll through, was to
rob (I’m an SQL dummy) some code from an old database in our system that had
a Search button. So now I can hit a Search button on my form, enter the
PtID, and go right to the record I want. Functional but stupid.
I would even be happy if I could hit the End arrow at the bottom of the
Access screen and jump to what should be the last record—i.e., the one I just
entered. However, my tables and form records seem INSISTENT on sorting on
that primary key, basically PtID, as opposed to natural order (in the order
entered, right?). While ascending PtID makes sense when scrolling through
records, efficiency dictates that I not do too much scrolling . I think I
have tried everything “the book†says to clear out existing sort orders, but
to no avail. I can’t get a table or a form to revert to order entered (maybe
it doesn’t ever go back?) or to go by order entered from this point forward.
If I solved this sorting issue, ALL of the above would go away, because I
could set the GoToRecord line on the macro to Last, and boom, in one click,
at the end of the dataset would be my new entry—i.e., the last one added.
The bottom line is, I want a form to pull a LITTLE information from a
different table than it’s writing to—and not throw me all over the data set.
Are my requests and techniques really that ill conceived? Sorry for the
novel...
that I developed with almost zippo previous knowledge of Access (hence the
cumbersome but functional part). I decided that I’m tired of “cumbersome.â€
I’ve seen threads that address part of my issue; I suspect someone’s going to
tell me I’m doing things all wrong, but I’m leery of doing a major rewire.
However, since my complaint is not about storage but about viewing data,
well, here goes.
I’m working in Access 2003. Basically, I have 2 tables: Patients and TestA
(also have B thru J, but if I can get A to work, the rest are the same).
Patients table has 3 columns: PtID (PKey), FirstName, LastName
TestA has 10 columns: APtID (PKey), AResult1, AResult2, AResult3, etc.
through AResult9.
Patients table is relatively static—patient names and ID numbers have been
entered already. The two tables have a 1-to-1 relationship based on PtID.
TestA table contains results of Test A for patients who have completed that
test. Those results have been entered via a form, also designated TestA.
Data capture/storage is fine. My issue is with the form, specifically
viewing of FirstName and LastName as identification confirmation. Yes, I’ve
seen this issue on several threads on this site.
OK, so I wanted my form to reflect everything in the TestA table (which it
does) PLUS the patient first and last name from the Patients table. As I’ve
seen on this site, one easy way to do that is a DLookup. However, Access
2003 also lets you base a form on multiple tables, which displays like
DLookup, but is even easier(but probably just as evil, developmentally?) to
set up, including concatenating the names. Seriously, no query, no subform,
no mess, no fuss.
That, in a nutshell, is the basis for every question to follow.
What I want is to be able to 1) go to a new record on the form, 2) enter a
PtID in the control box on the form (control source is the PtID from the
TestA table), and 3) have Access pull the patient’s name from the Patients
table into the control on the form whose control source is the concatenated
FirstName and LastName from the Patients table. I’m not trying to plug that
info into the TestA table—just want to SEE it on the form.
Then I want to 4) be able to enter data on the rest of the screen. Here’s
what I’m doing now; it’s working, but inefficiently cumbersome.
I enter the PtID in that box on the form. Then I click on the control box
where the Names should appear. What I have discovered is that just because I
told Access I want the Patients table to cough up the associated patient name
value for that PtID, it doesn’t mean that it will. One apparently has to pat
Access’ back to get it to cough, and apparently one way to do that is the
Action called Requery.
So I set up a macro to Requery when I clicked on the name control box. That
pulls the names from Patient alright, but I only see it blink, and then
Access immediately kicks me to the beginning of the record set where, yikes,
if I don’t realize where I am and start typing, I can overwrite existing data
by touching just about any key.
OK, so what I would like from the point of Requery is a way to keep myself
on that new record. That is eluding me. The best I’ve been able to do is to
add an Action to the macro, GoToRecord, which gives me the choice of getting
kicked to a brand-new record, where at least I can’t damage anything already
in there. Then I have to manually scroll my way through the record set to
the new record, which shows the PtID, has the name nice and updated, and has
the rest of the form all pretty and blank and ready to capture data. That’s
a royal pain (I mean, after all, isn’t a computer supposed to do everything
for you?!), and it lays open the later possibility that somebody who is not
as invested in all of this as I am is going to goof something up big time.
(Note: I’m the only user for now.)
My workaround, now that I have a hundred patients to scroll through, was to
rob (I’m an SQL dummy) some code from an old database in our system that had
a Search button. So now I can hit a Search button on my form, enter the
PtID, and go right to the record I want. Functional but stupid.
I would even be happy if I could hit the End arrow at the bottom of the
Access screen and jump to what should be the last record—i.e., the one I just
entered. However, my tables and form records seem INSISTENT on sorting on
that primary key, basically PtID, as opposed to natural order (in the order
entered, right?). While ascending PtID makes sense when scrolling through
records, efficiency dictates that I not do too much scrolling . I think I
have tried everything “the book†says to clear out existing sort orders, but
to no avail. I can’t get a table or a form to revert to order entered (maybe
it doesn’t ever go back?) or to go by order entered from this point forward.
If I solved this sorting issue, ALL of the above would go away, because I
could set the GoToRecord line on the macro to Last, and boom, in one click,
at the end of the dataset would be my new entry—i.e., the last one added.
The bottom line is, I want a form to pull a LITTLE information from a
different table than it’s writing to—and not throw me all over the data set.
Are my requests and techniques really that ill conceived? Sorry for the
novel...