next record

[Guest]

when my cursor is in the field of a form which is a primary key, i can not go
to the next record. how do i fix this problem?

 

[Dirk Goldgar]

when my cursor is in the field of a form which is a primary key, i
can not go to the next record. how do i fix this problem?

I'm afraid I can't picture the problem you're having. Can you explain
in more detail?

 

[Guest]

Dirk,
when i open the form, the first field i have is last name. this is one of
the primary keys in my table for the form. if the cursor is in the last name
field, (so i can enter data in it) i can not go the next record. this is a
problem becasue my form opens up all of the records in the table. so i need
to hit the button to go to a blank form to enter a new record. becasue the
cursor is in the last name field, it will not let me go to the next record or
a blank record. i need to click in a field that is not a primar key, like
age.

thanks
russ

 

[Dirk Goldgar]

Dirk,
when i open the form, the first field i have is last name. this is
one of the primary keys in my table for the form. if the cursor is in
the last name field, (so i can enter data in it) i can not go the
next record. this is a problem becasue my form opens up all of the
records in the table. so i need to hit the button to go to a blank
form to enter a new record. becasue the cursor is in the last name
field, it will not let me go to the next record or a blank record. i
need to click in a field that is not a primar key, like age.

That doesn't sound like normal behavior. I can certainly open a form,
find the cursor positioned in the text box that is bound to the primary
key, and page forward through records without hindrance. Is the form
"dirty" at the time -- does it show the little pencil in the record
selector, indicating that Access thinks you've edited the record? Is
there code behind the form? If so, what is it?

 

[Guest]

yes there is a pencil in the corner after i click the next button. when i
double click teh pencil it turns to an arrow and i can scroll through records
like i expect. how do i make the form open up like this, so i dont have to
double click the pencil. what do you mean by "dirty?"

 

[Dirk Goldgar]

yes there is a pencil in the corner after i click the next button.
when i double click teh pencil it turns to an arrow and i can scroll
through records like i expect. how do i make the form open up like
this, so i dont have to double click the pencil. what do you mean by
"dirty?"

The term "dirty" is used to describe data that has been modified but not
yet saved. So a record is dirty when it contains unsaved data. Access
shows the "pencil" in the record selector when the form's record is
dirty.

Now, the interesting thing here is that, if I understand you correctly,
the record is dirty when you first open the form. I'm not sure what the
"next" button is that you refer to, but if it's a button that brings you
to a new record, then apparently that also dirties the form.

If the form is being dirtied without your typing in or selecting from
any of the controls on the form, then there must be code or a macro
operating that is doing it. That could be in the form itself, or in the
code or macro that opens the form. Did you create this form yourself?
If so, what did you do in the way of coding or macros?

Open the form in design view, open the form's property sheet, and check
on the Other tab for the "Has Module" property. Is it set to "Yes"? If
it is, click the menu items View -> Code, copy the entire module
contents to the clipboard, and paste it into a reply to this message.
Note that you may have to switch the module window from Procedure View
to Full Module View in order to see and select all the code in the
module. There's a little pair of buttons at the module window's bottom
left corner that control this.

If the form's "Has Module" property is set to No, then we have to look
for macros. That's more trouble. Look on the Event tab of the form's
property sheet for any event property line that is not blank. Aside
from the TimerInterval and KeyPreview properties, report the value of
each non-blank property. There could also be macros attached to the
event properties of the individual controls on the form, so if you could
check those out to, it would be a help.

 

[Guest]

Yes, now it makes sense. I use a macro because my form is meant to enter
information into 2 different tables. the tables are linked by SSN, with a
one to many relationship. the only 2 common fields for the 2 tables is SSN
and LastName. for these 2 fields in the second table, they are not in the
form visually, but they are part of the data set. the macro i use sets the
SSN that i enter or that is already there as teh SSN of the second table.
same with the LastName. is there a better way to do this? it is these 2
fields that are not letting me go to the next record when the cursor is in
that field.

should i use code instead? is there a better way to link the 2 tables into
one form?

thank you for all of your help
russ

 

[Dirk Goldgar]

Yes, now it makes sense. I use a macro because my form is meant to
enter information into 2 different tables. the tables are linked by
SSN, with a one to many relationship. the only 2 common fields for
the 2 tables is SSN and LastName. for these 2 fields in the second
table, they are not in the form visually, but they are part of the
data set. the macro i use sets the SSN that i enter or that is
already there as teh SSN of the second table. same with the LastName.
is there a better way to do this? it is these 2 fields that are not
letting me go to the next record when the cursor is in that field.

should i use code instead? is there a better way to link the 2
tables into one form?

Aha! Now we're getting to the bottom of the mystery. You almost
certainly do *not* want to be using that macro, and you probably don't
even need anything equivalent to it, but before I make a recommendation,
let me ask you a few more questions.

1. What is the SQL of the query that is your form's recordsource?

2. What are the names of the two tables and the fields in each?

3. Which of these fields are indexed?

4. What are the primary key fields of the tables?

5. Is the relationship between the two tables enforced?

6. Which table is on the "one" side of the relationship, and which is on
the "many" side?

 

[Guest]

Dirk,

1. The SQL of the recordsource is: SELECT Demographics.LastName,
Demographics.FirstName, Demographics.SSN, Demographics.DOB, Demographics.Age,
Demographics.Sex, Demographics.Attending, New_Visit.LastName, New_Visit.SSN,
New_Visit.DateOfVisit, New_Visit.VisitLocERClinicFloorECF, New_Visit.Side,
New_Visit.Location, New_Visit.Joint, New_Visit.TypeOfInjury,
New_Visit.TimePain, New_Visit.Pain_DWMY FROM Demographics INNER JOIN
New_Visit ON Demographics.SSN = New_Visit.SSN ORDER BY Demographics.LastName;

2. The name of Table 1 is Demographics. Fields are LastName, FirstName, SSN (this is the primary key), DOB, Age, Sex, Phone, Address, City, State, Zip

Table 2 is named New_Visit. The Fields are LastName, SSN (primary key),
DateOfVisit (primary key), VisitLocERClinicFloorECF, Side, Location, Joint,
TypeOfInjury, demographics, Allergic, Injury, TimePain, Pain_DWMY, and
several others that are not used in the form.

3. In Demographics : no fields are indexed

In New_Visit : LastName and SSN are indexed such that duplicates are OK.

4. Table 1 the SSN is the primary key. Table 2 (New Visit) SSN and
DateofVisit are the primary key.

5. Referential integrity is enforced for cascading updates and deletes.

6. Demographics and New_Visit are related by SSN. Demographics is on the 1 side and New_Visit is on the many side.

Although lastname is not related, when i enter the record in teh form i need
the last name and SSN to go to both tables.

Also concernign teh age. I want to enter the Date of Birht, DOB, and have
the age calculated. It is my understanding that a calculated value should not
be stored. However, this data that is entered and sent to the Palm Pilot.
So I need the value, age, to be in a table to go to the palm. i have a code
taht calculates this value on the click of the age field. is tehre a way to
have this recalcuated when the form is opened or when the persons age
changes, everyyear?

thank you for your help, i really appreciate it.

Russ

 

[Dirk Goldgar]

(my comments begin near the end)

Dirk,

1. The SQL of the recordsource is: SELECT Demographics.LastName,
Demographics.FirstName, Demographics.SSN, Demographics.DOB,
Demographics.Age, Demographics.Sex, Demographics.Attending,
New_Visit.LastName, New_Visit.SSN, New_Visit.DateOfVisit,
New_Visit.VisitLocERClinicFloorECF, New_Visit.Side,
New_Visit.Location, New_Visit.Joint, New_Visit.TypeOfInjury,
New_Visit.TimePain, New_Visit.Pain_DWMY FROM Demographics INNER JOIN
New_Visit ON Demographics.SSN = New_Visit.SSN ORDER BY
Demographics.LastName;

Table 2 is named New_Visit. The Fields are LastName, SSN (primary
key), DateOfVisit (primary key), VisitLocERClinicFloorECF, Side,
Location, Joint, TypeOfInjury, demographics, Allergic, Injury,
TimePain, Pain_DWMY, and several others that are not used in the form.

In New_Visit : LastName and SSN are indexed such that duplicates are
OK.

4. Table 1 the SSN is the primary key. Table 2 (New Visit) SSN and
DateofVisit are the primary key.


Although lastname is not related, when i enter the record in teh form
i need the last name and SSN to go to both tables.

If SSN is the primary key of Demographics, and that table contains the
LastName field, why would you also have LastName as a field in
New_Visit? That would be redundant. A query like this:

SELECT
Demographics.LastName,
Demographics.FirstName,
Demographics.DOB,
Demographics.Age,
Demographics.Sex,
Demographics.Attending,
New_Visit.SSN,
New_Visit.DateOfVisit,
New_Visit.VisitLocERClinicFloorECF,
New_Visit.Side,
New_Visit.Location,
New_Visit.Joint,
New_Visit.TypeOfInjury,
New_Visit.TimePain,
New_Visit.Pain_DWMY
FROM
Demographics
INNER JOIN
New_Visit
ON Demographics.SSN = New_Visit.SSN
ORDER BY Demographics.LastName;

will automatically pull up the LastName from Demographics that
corresponds to the SSN you enter for New_Visit. If the purpose of your
form is to view and enter visits, then there's really no problem. The
only thing this won't let you do is enter a visit for a new patient who
doesn't have a record in Demographics. But for that you really should
open a form based on Demographics, enter the patient and close the form
or save the record, and then return to this form to enter visits.

Also concernign teh age. I want to enter the Date of Birht, DOB, and
have the age calculated. It is my understanding that a calculated
value should not be stored.
Right.

However, this data that is entered and
sent to the Palm Pilot. So I need the value, age, to be in a table to
go to the palm. i have a code taht calculates this value on the
click of the age field. is tehre a way to have this recalcuated when
the form is opened or when the persons age changes, everyyear?

Is there some reason you can't send the results of a query to the Palm
Pilot, instead of a table? Calculations like this should be made in a
query. For most purposes, a query can be substituted wherever a table
can be used. I don't know about exporting to a Palm Pilot, though. If
you really *have* to export an actual table, then I'd set up code to
stick the query results (including the calculated age) into a temporary
table, and then export that temporary table to the Palm Pilot.

Of course, that means the Palm Pilot's data could become out of date,
but that's a separate problem.

 

[Guest]

Dirk,
thank you for your help so far. the problem is the database software on teh
palm is not a relationship database. so the last name must be carried
through all of the forms that i use. second, the form i am attempting to
construct is for entry of a new patient, but usually i have to enter the
demographics as well if the patient has never been to the clinic before. so
the point of the form is to only open and enter information into one form and
not two (demographics adn new visit). the rest of the info is filled in on
the palm. so will this method you gave me allow for new entry without a
record in demographics? it will put the patient in demographics adn new
visit right? how do i go about having the last name automatically entered
into new_visit? i dont really want to re-type it.

thanks again
russ

 

[Dirk Goldgar]

Dirk,
thank you for your help so far. the problem is the database software
on teh palm is not a relationship database. so the last name must be
carried through all of the forms that i use.

Did you read and understand what I said about exporting a query to the
Palm instead of the table itself? I'll bet you can export a query that
picks up the LastName field from the Demographics table along with the
Visit data. If not, run a make-table or append query to put the fields
you need from both tables into a temporary table, and export *that* to
the Palm.

second, the form i am
attempting to construct is for entry of a new patient, but usually i
have to enter the demographics as well if the patient has never been
to the clinic before. so the point of the form is to only open and
enter information into one form and not two (demographics adn new
visit). the rest of the info is filled in on the palm. so will this
method you gave me allow for new entry without a record in
demographics? it will put the patient in demographics adn new visit
right?

The arrangement I described was for a visit-oriented form. If your form
is patient-oriented, so that you are entering data for one patient at a
time, then the best way to set it up is with a main form/subform
arrangement. The main form would be based on the Demographics table,
and would let you easily find a particular patient or enter a new one.
The subform would be based on the New_Visit table, and would be linked
to the main form by the SSN field so it shows only that patient's
visits, and the SSN from the main form is automatically entered in every
visit record you create on the subform. You may even want to have two
"visit" subforms on the main form -- one to operate in data entry mode
and just let you enter new visits, and one to display and edit the
records of old visits.

The data entry process would be:

1. Enter SSN in a text box or combo box on the main form.

2. If it's an existing patient, that patient's demographic data appears
immediately on the form, and the focus is set to the visit subform ready
to enter a new visit.

3. If the SSN isn't found, a popup message warns you and asks you to
confirm that you want to enter a new patient. Assuming you say yes, the
main form goes to a new record for you to enter the Demographic details.

4. On the visit subform, you enter the visit-related data.

how do i go about having the last name automatically entered
into new_visit? i dont really want to re-type it.

If you don't store LastName in New_Visit, then you don't have to retype
it. That's what relational databases are all about. The fact that the
Palm's database software isn't relational doesn't mean you should botch
up your Access database to match it. Instead, the processes of
downloading data to the Palm and uploading data from the Palm should
take care of restructuring the data as needed.