Error when recordsource set using code

  • Thread starter Thread starter Fred Boer
  • Start date Start date
F

Fred Boer

Hello!

I have a form that I use in two different situations. Normally the form uses
a saved query as its recordsource. In this situation "me.book_ID" is used
without error in some code.

In another situation I load a different recordsource using code. In this
case, the use of "me.book_ID" causes an error: 2465.. can't find field. I've
checked that the field is actually present in the recordset, and if I create
a bound textbox to this field, it is loaded with the correct value. I
suppose a simple solution is to refer to an invisible textbox in both cases,
but I'm curious why it doesn't work to refer to the field when the recordset
isn't a saved query.

Thanks!
Fred Boer
 
Well, I've worked away on this for a few hours and I *think* I understand
what the problem is:

The saved query contains a field called Book_ID. The recordsource loaded via
code also contains a field called Book_ID. However, because there is a Left
Join in this recordsource, Access prefixes the field with the table name,
like so: Tbl_Library.Book_ID. Naturally, I can't refer to this field as
Me.Book_ID, cause that ain't what it's called!

1. I've solved the problem (I think...) by creating a new form
(Frm_MissingDataEdit) which is a clone of Frm_LibraryDataEntry, with
appropriately edited code.

2. Is there a way you can think of that I could use only one form and still
manage loading a recordset in code? I can't see how I can, because of the
Book_ID/Tbl_LibraryBookID issue, but y'all are a lot more clever than me! ;)

Thanks!
Fred
 
2. Is there a way you can think of that I could use only one form and
still manage loading a recordset in code? I can't see how I can,
because of the Book_ID/Tbl_LibraryBookID issue, but y'all are a lot
more clever than me! ;)
Click to expand...

Alias the field in both queries:

SELECT This.Book_ID AS MainBookID,
That.Book_ID AS MissingBookID,
OtherFields
FROM This LEFT JOIN That
ON etc etc


In general I don't see why one would want fields at both ends of a join
to appear in a query, so why don't you just drop the one you don't want?
Then the one remaining field will be identifiable by its ordinary name.

Hope that helps


Tim F
 
Dear Tim:

Told you that you were cleverer than me! :)

Using an alias is a good idea; I wish I'd thought of that myself. However,
your second point provided an even easier solution; I dropped the second
Book_ID from the query, and ended up with only one Book_ID field, avoiding
the problem altogether! It never occurred to me to do that - I guess I just
thought that I had to have both in the query to be able to make the query
work. Actually, to be honest, I suppose there wasn't a lot of thinking on my
part at all! <g>

I appreciate your help, Tim.

Fred
 
I dropped the second
Book_ID from the query, and ended up with only one Book_ID field,
avoiding the problem altogether! It never occurred to me to do that -
I guess I just thought that I had to have both in the query to be able
to make the query work. Actually, to be honest, I suppose there wasn't
a lot of thinking on my part at all!
Click to expand...

Actually, most of us (well, alright then, I do!) get well snarfed up on
whether to include the FK or the PK in queries.

If you don't include the FK, you can't change it to point to a completely
different foreign record: e.g. change the Tutor assigned to a Class.

If you don't include the PK, you can't edit the foreign record: e.g.
correcting the spelling of the Tutor's name.

On the other hand, getting the wrong one can affect the updateability of
the whole query, in various and darkly-secret ways! I don't believe that
anyone completely understands those!

Glad it worked out for you
All the best


Tim F
 
Back
Top