form won't allow input -- it did previously

[Guest]

Two of us have been designing a form, which all of a sudden has stopped
allowing any data to be put into it. One can still add data, of course, to
the underlying table (there are 2 tables). I checked the Properties for the
form and I don't see where data entry is restricted/denied. What could have
happened??

Thanks,

Meredith

 

[Dirk Goldgar]

In

Two of us have been designing a form, which all of a sudden has
stopped allowing any data to be put into it. One can still add data,
of course, to the underlying table (there are 2 tables). I checked
the Properties for the form and I don't see where data entry is
restricted/denied. What could have happened??

Is the form based on a query (whether a stored query or an in-line
select statement)? If so, is that query updatable? If you open that
query directly, can you edit and add records?

 

[Guest]

No, the form is based on fields from 2 tables. As I said, we've been working
on this for some time and it always allowed us to put data into it. My
co-designer/friend just recently changed the type of field in one of the
underlying tables from Yes/No to text and I think that's when the problems
started but neither of us knows why ... or if there's even any connection
with that issue. I deleted one of the previously Yes/No fields and reinserted
it as a Text field but that didn't solve the problem, either with data input
into that field or any other ..

Meredith

 

[Dirk Goldgar]

In

No, the form is based on fields from 2 tables.

If the form is based on fields from 2 tables, then it's either set up as
a main form/subform arrangement, or it is based on a query -- probably
an in-line SQL statement. Open the form in design view, bring up the
property sheet for the form, and copy/paste the Record Source property
from the Data tab of the property sheet into your reply to this message.

As I said, we've been
working on this for some time and it always allowed us to put data
into it. My co-designer/friend just recently changed the type of
field in one of the underlying tables from Yes/No to text and I think
that's when the problems started but neither of us knows why ... or
if there's even any connection with that issue. I deleted one of the
previously Yes/No fields and reinserted it as a Text field but that
didn't solve the problem, either with data input into that field or
any other ..

I'm not sure what you and your co-designer did, but my first reaction
would be that changing the type of the field wouldn't cause the problem
you're describing. Still, let's see what the form's record source is.
That may explain it.

 

[Guest]

SELECT [Intake Worksheet2].*, ReferralSourcesAll.* FROM [Intake Worksheet2],
ReferralSourcesAll;

The 2 table names are Intake Worksheet2 and ReferralSourcesAll ... I guess
that's obvious

 

[John W. Vinson]

No, the form is based on fields from 2 tables.

Then... unless you're using a Form with a Subform, which would probably be a
good idea.... it's based on a query joining those two tables. If they're
linked tables, you'll need to drop and recreate the link in order to correctly
reflect the changed field; if they're local tables you may need to open the
query in design view and make sure it's still correct.

Perhaps you could open the query in SQL view and post it here.

John W. Vinson [MVP]

 

[John W. Vinson]

SELECT [Intake Worksheet2].*, ReferralSourcesAll.* FROM [Intake Worksheet2],
ReferralSourcesAll;

The 2 table names are Intake Worksheet2 and ReferralSourcesAll ... I guess
that's obvious

This query will certainly not be updateable, and it's almost surely in correct
in that you don't have a JOIN between the tables. As written, if there are 100
records in [Intake Worksheet2] and 2000 records in ReferralSourcesAll, you'll
get all 200,000 possible combinations!

What are these tables? How are they related, if at all? Does either table have
a Primary Key, and is there any field that would let you join both tables?

John W. Vinson [MVP]

 

[Guest]

Hi, John. I'm not sure how to link the 2 tables; at present they don't have a
common field. One is to hold data relating to a prospective patient and that
patient's ultimate disposition; the other is information about referral
sources. Do you have a suggestion in how to join the 2? I didn't know, to be
honest with you, that it was necessary to create a form with a query ...

Are you saying the alternative would be to base the form on the patient info
table and insert the referral sources as a subform? Can there be more than
one subform, as I have already included one that tracks dates and times ... ??

Thanks,

Meredith

 

[John W. Vinson]

Hi, John. I'm not sure how to link the 2 tables; at present they don't have a
common field. One is to hold data relating to a prospective patient and that
patient's ultimate disposition; the other is information about referral
sources. Do you have a suggestion in how to join the 2? I didn't know, to be
honest with you, that it was necessary to create a form with a query ...

What is the real-world logical relationship between patients and referrals? Is
each patient referred by one source; or perhaps by multiple sources; or do
referrals and patients simply have nothing to do with one another?

Are you saying the alternative would be to base the form on the patient info
table and insert the referral sources as a subform? Can there be more than
one subform, as I have already included one that tracks dates and times ... ??

You can indeed have multiple subforms, but until I understand how - in the
real world, not in database jargon - referrals and patients are related to one
another, I'm not sure I can recommend a new subform.

John W. Vinson [MVP]

 

[Guest]

I think they do actually have something in common -- which I hadn't thought
of and which is obvious. Each patient is referred by one referral source, so
there's a combo box in the form which allows the intake person to choose that
for each patient. That info then is saved in the Intake Worksheet2 table. So,
I guess I should set up a relationship (which I had not done to date) linking
the referral source in both tables? Then, I'm assuming that a query would
have the correct parameters when used as the source for the form?

Would a subform have any advantages to the above?

Thanks,

Meredith

 

[John W. Vinson]

I think they do actually have something in common -- which I hadn't thought
of and which is obvious. Each patient is referred by one referral source, so
there's a combo box in the form which allows the intake person to choose that
for each patient. That info then is saved in the Intake Worksheet2 table. So,
I guess I should set up a relationship (which I had not done to date) linking
the referral source in both tables?

Yes. This will prevent you from inadvertantly entering a nonexistant referral
source into the patient info table.

Then, I'm assuming that a query would
have the correct parameters when used as the source for the form?

Nope. Base your Form on the Intake Worksheet2 table, alone; and simply put a
combo box on the form to select the referral. If you need to add new
referrals, use either a little simple form bound to the referral source table,
and/or VBA code in the combo's Not In List event. See "Not In List" at
http://www.mvps.org/access for examples of how to use this event.

John W. Vinson [MVP]