Subform problem with LinkMasterFields

O

oldblindpew

I recently changed my main form's Recordsource from the table name, tblJob,
to a query:

SELECT tblJob.*, tblJob.tYear, tblJob.tIDJob FROM tblJob ORDER BY
tblJob.tYear DESC , tblJob.tIDJob DESC;

Now, when attempting to add a new record on the subform, I get this error:
The LinkMasterFields property setting has produced this error: 'The object
doesn't contain the Automation object 'tblJob."

There are any number of things I don't understand here.
1) Why does the query (generated via the Query Builder) contain four
references to tblJob? I mean, how many times do we have to say it? I also
don't understand the purpose or meaning of DESC .
2) After having specified, in the query, ALL fields from tblJob, why do we
have to call out certain fields individually by name from tblJob?
3) After this change of Recordsource, I noticed certain references in my
form to tIDJob were changed by Access to tblFirm.tIDJob.
4) I don't understand the quote marks and apostrophes in the error message.
5) As for the error itself, LinkMasterFields is supposed to be the field
name in the record source of the main form, and it certainly appears to be
present.
6) LinkMasterFields is supposed to match LinkChildFields, but now the one is
tblJob.tIDJob, and the other is just plain tIDJob. I don't know if this
constitutes a mismatch or not.
7) This is not really important, but the terminology should be either
Master/Slave or Parent/Child, not Master/Child.

Thanks for any help you can offer.
 
J

Jeanette Cunningham

Hi oldblindpew,
The query you are using selects everything from tblJob using the asterisk,
then you have asked it to select tblJob.tYear, tblJob.tIDJob as well.

Remove the 2 fields that are being used twice in the query-->
SELECT tblJob.* FROM tblJob ORDER BY
tblJob.tYear DESC , tblJob.tIDJob DESC;

The above should resolve the problems after you remove those extra
doubled-up field controls from the form/s.
You will only need one field/control for tblJob.tYear and one field/control
for tblJob.tIDJob.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
O

oldblindpew

Thanks Jeanette, by george it worked!

How (one may ask) did the double references get into my query? Easy. The
purpose of using a query instead of the table name for the record source was
in sorting the records by Year/Job in descending order, so the most recent
job would appear by default when the form opens. Knowing how tricky it can
be to write a query, I used the query builder. After picking "all records"
(the asterisk) I was faced with the question of the sort. The only way I
could see to do this was to pull down the field/s I wanted to sort by and
specify "Descending". In essence the query builder apparently forced me into
this problem. Is there some other way I could've / should've used the query
builder to avoid this trap??
--obp
 
J

Jeanette Cunningham

I always put only the fields I need in a query and never seem to use the *.
Sometimes the query builder will put a field in twice when the field is one
used for sorting.
It will make the second occurence of that field hidden by removing the tick
from the Show: row.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top