No fields in SubForm

  • Thread starter Thread starter MB
  • Start date Start date
M

MB

I have a subform on my form for data entry. The main form has employee name,
division and department. The subform is for data entry on the computer
equipment they use. The records that have already been entered show up, but
when adding a new record, the fields are not there. The form is there, the
column headings are there, but no fields to enter data. The fields on the
main form (employee info) are there, however.

I'm using 2007. The subform's Data Entry, Allow Additions and Allow Edits
are set to yes and the Cycle is set to All Records.

The query's Unique Values is set to Yes and Unique Records is set to No. If
I change these, then my combo boxes have duplicates and no records show up in
the subform.

Whether I use a button to add new record or the navigation bar at the bottom
of the form, the result is the same: a blank new record appears, but no
fields in the subform to fill in.

(I'm still a novice, so I'm not good with code.)

Thank you for your help.



MB
 
MB said:
I have a subform on my form for data entry. The main form has employee
name,
division and department. The subform is for data entry on the computer
equipment they use. The records that have already been entered show up,
but
when adding a new record, the fields are not there. The form is there,
the
column headings are there, but no fields to enter data. The fields on the
main form (employee info) are there, however.

I'm using 2007. The subform's Data Entry, Allow Additions and Allow
Edits
are set to yes and the Cycle is set to All Records.

The query's Unique Values is set to Yes and Unique Records is set to No.
If
I change these, then my combo boxes have duplicates and no records show up
in
the subform.

Whether I use a button to add new record or the navigation bar at the
bottom
of the form, the result is the same: a blank new record appears, but no
fields in the subform to fill in.
Click to expand...


When you refer to "the query", am I right in assuming you're talking about
the query that is the RecordSoruce for the subform? I'll assume so, for
now.

If the query's Unique Values property is set to Yes, then the query will not
be updatable, no matter what the AllowEdits property of the subform is set
to. That's because the result set is created by "collapsing" duplicate
records together into a single record, so there's no longer a specific
source record to update. That also means no new records can be created in
that recordset. And when there are no records to be displayed on a form,
and no new records can be created, Access leaves the whole detail section of
the form blank.

To fix this problem, you must set the query's Unique Values property to No.

I'm not sure what you mean when you say, "my combo boxes have duplicates".
If you're using this same query also as the rowsource for a combo box, and
you want to remove duplicates from the combo box, then you need to create a
different query for the combo box, and if necessary apply the Unique Values
property to that query.
 
Hi Dirk, thanks for the quick reply.

Yes the query is the record source. I changed the Unique Value to No and
now the fields do appear in my subform. However, the records that were
already there are already gone. The employee information in the main form is
there, but the records in the subform are gone. (But the data is in the
query.) ?
 
MB said:
Hi Dirk, thanks for the quick reply.

Yes the query is the record source. I changed the Unique Value to No and
now the fields do appear in my subform. However, the records that were
already there are already gone. The employee information in the main form
is
there, but the records in the subform are gone. (But the data is in the
query.) ?
Click to expand...


That suggests that there's a problem with the subform linkage to the main
form. Look at the properties of the subform control on the main form (this
is the control that acts as a window to display the subform). What's in the
Link Master Fields and Link Child Fields properties? Do those values look
right to you -- do they correctly describe the relationship between the
employee record and the data that the subform shows?

If your inspection doesn't reveal the problem to you, please post those
properties and the SQL of the query.
 
The Master and Child both say DataID. (Don't know if this is right.)

Here's the SQL of the query (called qryCompInfo)

SELECT tblCompInfo.PCName, tblCompInfo.Mfg, tblCompInfo.Model,
tblCompInfo.Specifications, tblCompInfo.[S/N], tblCompInfo.Monitor,
tblCompInfo.OS, tblCompInfo.PurchDate, tblCompInfo.PurchPrice,
tblCompInfo.WarrExpDate, tblCompInfo.MaintContract, tblCompInfo.MaintExpDate,
tblCompInfo.DataID
FROM tblCompInfo;

Thanks again, Dirk.
 
MB said:
The Master and Child both say DataID. (Don't know if this is right.)

Here's the SQL of the query (called qryCompInfo)

SELECT tblCompInfo.PCName, tblCompInfo.Mfg, tblCompInfo.Model,
tblCompInfo.Specifications, tblCompInfo.[S/N], tblCompInfo.Monitor,
tblCompInfo.OS, tblCompInfo.PurchDate, tblCompInfo.PurchPrice,
tblCompInfo.WarrExpDate, tblCompInfo.MaintContract,
tblCompInfo.MaintExpDate,
tblCompInfo.DataID
FROM tblCompInfo;
Click to expand...


I think maybe your Link Master/Child Fields are wrong, but let's find out.
Here are some questions:

What are the names of the tables involved? It appears one is tblCompInfo.
The other, I gather, is a table of employees.

Is the recordsource of the main form the employees table?

What is the name of the primary key field of each table?

What is the relationship between the tables? Does one employee have many
computers? Is one computer used by many employees?

What fields in these tables links the two tables?

Have you formally defined a relationship between the tables, in the
Relationships window? If so, please describe it.
 
Dirk, sorry it took so long for my reply (had a meeting).

Okay, here are answers to your questions:

Table names: tblData (employee info) and tblCompInfo.

Main form resource: tblData

Primary keys: DataID for tblData and CompID for tblCompInfo

Relationship: One employee to many computers (never many employees to one
computer).

Fields linked: DataID

Relationship between tables: DataID

I see where my problem might be. There is no CompID in tblData and should
IDs be the link?

You're so helpful. Thank you again.
--
MB


Dirk Goldgar said:
MB said:
The Master and Child both say DataID. (Don't know if this is right.)

Here's the SQL of the query (called qryCompInfo)

SELECT tblCompInfo.PCName, tblCompInfo.Mfg, tblCompInfo.Model,
tblCompInfo.Specifications, tblCompInfo.[S/N], tblCompInfo.Monitor,
tblCompInfo.OS, tblCompInfo.PurchDate, tblCompInfo.PurchPrice,
tblCompInfo.WarrExpDate, tblCompInfo.MaintContract,
tblCompInfo.MaintExpDate,
tblCompInfo.DataID
FROM tblCompInfo;
Click to expand...


I think maybe your Link Master/Child Fields are wrong, but let's find out.
Here are some questions:

What are the names of the tables involved? It appears one is tblCompInfo.
The other, I gather, is a table of employees.

Is the recordsource of the main form the employees table?

What is the name of the primary key field of each table?

What is the relationship between the tables? Does one employee have many
computers? Is one computer used by many employees?

What fields in these tables links the two tables?

Have you formally defined a relationship between the tables, in the
Relationships window? If so, please describe it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
MB said:
Dirk, sorry it took so long for my reply (had a meeting).

Okay, here are answers to your questions:

Table names: tblData (employee info) and tblCompInfo.

Main form resource: tblData

Primary keys: DataID for tblData and CompID for tblCompInfo

Relationship: One employee to many computers (never many employees to one
computer).

Fields linked: DataID

Relationship between tables: DataID

I see where my problem might be. There is no CompID in tblData and should
IDs be the link?
Click to expand...


No, I don't think that's the problem. From the information you've given
above, DataID should be the linking field, just as you've shown, and you
wouldn't want a CompID in tblData.

Let's go back to what is making you think this isn't working. It seems to
me you said "the records that were already there are gone." If you can open
tblCompInfo and see records, but they aren't showing up in the (properly
linked subform), I'm guessing that those records were not properly stamped
with the DataID of the employee who "owns" them. This may be the result of
data entry that was made other than through the form/subform, or that was
made before the Link Master/Child Fields were properly set.

Look at tblCompInfo and see if there are DataID values in all the records.
If you do see values for them all, then I'm going to have to guess that
there's something wrong with the field types. So if that's the case, give
me the details of the DataID fields as defined in each table's Design View.
 
Yes, the records are indeed in tblCompInfo and there are DataID values in all
records.

The field types for tblData are:
DataID = Auto Number (Long Integer)
Other 3 fields (employee info) are text

Same for tblCompInfo:
CompID = Auto Number (Long Integer)
All other fields are text or date where appropriate

The records that are already there were entered before I changed some of the
fields into combo boxes. I need the field to become a list as the user fills
in the data, and it needs to be "auto-fill" so the user won't have to type in
the entire field.

Hope this helps.
 
MB said:
Yes, the records are indeed in tblCompInfo and there are DataID values in
all
records.

The field types for tblData are:
DataID = Auto Number (Long Integer)
Other 3 fields (employee info) are text

Same for tblCompInfo:
CompID = Auto Number (Long Integer)
All other fields are text or date where appropriate
Click to expand...

Are you saying that the DataID field in tblCompInfo is Text? That's clearly
wrong -- it should be Number, with Field Size set to Long Integer. What's
in that field now, numbers (stored as text), or employee names, or what?
 
I'm sorry, I mislead you. The field DataID is a number (long integer). Only
text fields are text, i.d., employee name, dept, div.
 
MB said:
I'm sorry, I mislead you. The field DataID is a number (long integer).
Only
text fields are text, i.d., employee name, dept, div.
Click to expand...


I just realized what the problem is! It's something you mentioned in your
very first post, that I overlooked when I spotted your problem with the
query. You wrote this:
The subform's Data Entry, Allow Additions and Allow Edits are set to yes
Click to expand...

But the Data Entry property is specifically intended to allow the entry of
new records only, and not display existing records. Set that property to
No, and see if your records appear.
 
MB said:
HOLY JAMOLIE, YOU ARE RIGHT!!!
Click to expand...

Just keep that poker face said:
Thank you Dirk! My form works and it's so cool thanks to you!
Click to expand...

You're welcome. I'm sorry I overlooked that at first -- we could have saved
the last 10 messages. Anyway, good luck with your database in the future!
 
I see where my problem might be. There is no CompID in tblData and should
IDs be the link?
Click to expand...

PMFJI but... you said at the top of the thread that the subform's Data Entry
property is Yes. Change it to No; the Data Entry property conceals all
existing records so that the only thing you can do is add new records.
 
John W. Vinson said:
PMFJI but... you said at the top of the thread that the subform's Data
Entry
property is Yes. Change it to No; the Data Entry property conceals all
existing records so that the only thing you can do is add new records.
Click to expand...


I wish I had your eyes, John! I overlooked that.
 
Back
Top