Want to Display Most Recently Entered Record in Access 2003 Language Learning App

[Mike]

Hi all,

I'm new to Access. I've got a good bit of VBA under my belt, but I've
been using it in Excel, so I'm a bit lost in all this new
functionality.

Anyway, I'm trying to build an application to help me study Romanian.
I have a table, Phrases! as follows:

Phrases
Phrase I AutoNumber unique identifier
LearningSession Number convenient way to group phrases for study
Entered Date/Time the date and time the phrase was entered
Source Memo textbook, page number, etc... (or "Mom said it")
Romanian Memo the phrase in Romanian
English Memo the phrase in English

I want there to be a locked text box or something like that on my
Phrase Input form that shows the Romanian field from the record I most
recently entered. (This helps me find my place again if I get
distracted or need to stop in the middle of keying in a vocabulary
list.)

My first thought was to write a query on Phrases! and link the Control
Source of a text box on my form to the query. The query works fine-I
just looked up a nested SQL walk-through:

LastEnteredPhrase
SELECT Phrases.Romanian AS MostRecent
FROM Phrases
WHERE (((Phrases.Entered) In (SELECT Max(Phrases.Entered) FROM
Phrases)));

But when I try to link the Control Source to a form (for which the
Record Source is the Phrases! table) I get the #NAME? error.

I don't think the form could be confused about the object references
since I renamed the Romanian attribute to MostRecent:Romanian in
Design View.

And I know I'm not making any typos because I'm using the expression
writer to generate the references. I've tried:

=LastEnteredPhrase!MostRecent

and

=DLookUp(LastEnteredPhrase!MostRecent, LastEnteredPhrase!)

which is corrected to

=DLookUp(LastEnteredPhrase!MostRecent, LastEnteredPhrase!
MostRecent)

Can anyone help me?

Thanks,

Mike

 

[Mike]

Hi all,

I'm new to Access. I've got a good bit of VBA under my belt, but I've
been using it in Excel, so I'm a bit lost in all this new
functionality.

Anyway, I'm trying to build an application to help me study Romanian.
I have a table, Phrases! as follows:

Phrases
Phrase I AutoNumber unique identifier
LearningSession Number convenient way to group phrases for study
Entered Date/Time the date and time the phrase was entered
Source Memo textbook, page number, etc... (or "Mom said it")
Romanian Memo the phrase in Romanian
English Memo the phrase in English

I want there to be a locked text box or something like that on my
Phrase Input form that shows the Romanian field from the record I most
recently entered. (This helps me find my place again if I get
distracted or need to stop in the middle of keying in a vocabulary
list.)

My first thought was to write a query on Phrases! and link the Control
Source of a text box on my form to the query. The query works fine-I
just looked up a nested SQL walk-through:

LastEnteredPhrase
SELECT Phrases.Romanian AS MostRecent
FROM Phrases
WHERE (((Phrases.Entered) In (SELECT Max(Phrases.Entered) FROM
Phrases)));

But when I try to link the Control Source to a form (for which the
Record Source is the Phrases! table) I get the #NAME? error.

I don't think the form could be confused about the object references
since I renamed the Romanian attribute to MostRecent:Romanian in
Design View.

And I know I'm not making any typos because I'm using the expression
writer to generate the references. I've tried:

=LastEnteredPhrase!MostRecent

and

=DLookUp(LastEnteredPhrase!MostRecent, LastEnteredPhrase!)

which is corrected to

=DLookUp(LastEnteredPhrase!MostRecent, LastEnteredPhrase!
MostRecent)

Can anyone help me?

Thanks,

Mike

sorry... futzed something up trying to post =o( does this mean no one
will help me?

 

[Rob Parker]

Hi Mike,

Well, multiple posts from the original poster in a short time is likely to
alienate at least some possible responders. I'd suggest, within the Access
newsgroups, that you should not repost for at least a day (usually someone
will respond in much less time than that), and if you must report, start a
new thread with the word "Repost" in the subject line.

With that out of the way ...

On a quick browse of your problem, it seems likely that you've encountered a
very common problem, particularly if you originally created your textbox
control by dragging a field onto the form. That will generate a ontrol with
the same name as its Control Source, since Access can't figure out if you
are referring to the control or the field. If you then change the Control
Source to an expression which uses the same source, you will get the #Name
error which you describe. The solution is simply to rename the textbox
control, such as txtControlSource. A further recommendation is to use a
standard naming convention for everything in your database; the commonest
one is LNC (Leszynski Naming Convention).

HTH,

Rob

 

[Rob Parker]

Oops - hideous edit in one of those paragraphs has scrambled my meaning.
For the third para, try this:

On a quick browse of your problem, it seems likely that you've encountered a
very common problem, particularly if you originally created your textbox
control by dragging a field onto the form. That will generate a control
with the same name as its Control Source. If you then change the Control
Source to an expression which uses the same source, you will get the #Name
error which you describe, since Access can't figure out if you are referring
to the control or the field. The solution is simply to rename the textbox
control, such as txtControlSource. A further recommendation is to use a
standard naming convention for everything in your database; the commonest
one is LNC (Leszynski Naming Convention).

Rob

 

[Mike]

Oops - hideous edit in one of those paragraphs has scrambled my meaning.
For the third para, try this:

On a quick browse of your problem, it seems likely that you've encountered a
very common problem, particularly if you originally created your textbox
control by dragging a field onto the form. That will generate a control
with the same name as its Control Source. If you then change the Control
Source to an expression which uses the same source, you will get the #Name
error which you describe, since Access can't figure out if you are referring
to the control or the field. The solution is simply to rename the textbox
control, such as txtControlSource. A further recommendation is to use a
standard naming convention for everything in your database; the commonest
one is LNC (Leszynski Naming Convention).

Rob









- Show quoted text -

Rob,

Thanks so much for reading through! I honestly don't know how I
managed to post that message 3 times at 10:14 PM! I think my browser
was taking its time refreshing and I clicked Send a few too many times
only to find the alignment (I copied from a Word doc) scrambled...
fixed it afterwards hoping to make it more readable for posting number
4... whoops...

Anyway, for the issue itself, I'm still not sure what's wrong. I
created the textbox by dragging one out of the toolbox, so it was
originally an unbound control with the name TextBox11, which isn't
anywhere close to the expressions I've entered for Control Source. Any
other ideas?

Also, I really appreciate the LNC naming convention tip! "Best
practices" is very much at the top of my things-to-learn list. On the
subject: Can you tell me whether DLookup or writing a query to
populate the box or some other approach would be the "standard"
solution for this requirement?

And also, thank you for the netiquette tip. Another question on the
subject? Is it considered gauche to respond to the personal e-mail of
someone who replies to your posting? (Ahem, yeah, that's just you so
far )

Thanks again for your help. Best,

Mike

 

[Guest]

I can't add anything to what Rob said except that when you post, it takes
5-15 minutes before it shows up.

 

[Rob Parker]

Hi Mike,

Since the problem seems not to be a duplicate names issue (BTW, that will
occur if there is any control with the same name as a field name, not just
the control bound to the field), it's probably due to a syntax error
somewhere. The most obvious is that you've omitted the quotes in your
dlookup statement. Assuming that your query is named LastEnteredPhrase (I'd
have named it qryLastEnteredPhrase), then the following should work in the
unbound textbox:
=dlookup("MostRecent","LastEnteredPhrase")

Using a dlookup to get a field/value from a table/query which is not the
form's recordsource is a fairly standard technique. Sometimes you may be
able to include that field in the form's recordsource, but that may make the
query non-updateable and then the form will not be able to be used for data
entry/editing - usually not what is acceptable ;-)

As for your netiquette question: within the microsoft.public newsgroups, the
standard practice is to post/reply only to the newsgroup; in that way,
everything is available for anyone to see, and find when searching.
Occassionally (if there's a particularly complicated issue) a discussion
will be taken off-line if a responder offers to accept a copy of the
problematic database to look at directly, but that's pretty rare. And when
it does occur, someone will (or should) post the final solution so that
others may see it. And one other netiquette point in these groups (which
happens often, but not always) is to post back a "that worked" (or, much
more rarely, "that didn't fix it") so that browsers/searchers will know
whether a posted solution works.

HTH,

Rob

 

[Mike]

Hi Mike,

Since the problem seems not to be a duplicate names issue (BTW, that will
occur if there is any control with the same name as a field name, not just
the control bound to the field), it's probably due to a syntax error
somewhere. The most obvious is that you've omitted the quotes in your
dlookup statement. Assuming that your query is named LastEnteredPhrase (I'd
have named it qryLastEnteredPhrase), then the following should work in the
unbound textbox:
=dlookup("MostRecent","LastEnteredPhrase")

Using a dlookup to get a field/value from a table/query which is not the
form's recordsource is a fairly standard technique. Sometimes you may be
able to include that field in the form's recordsource, but that may make the
query non-updateable and then the form will not be able to be used for data
entry/editing - usually not what is acceptable ;-)

As for your netiquette question: within the microsoft.public newsgroups, the
standard practice is to post/reply only to the newsgroup; in that way,
everything is available for anyone to see, and find when searching.
Occassionally (if there's a particularly complicated issue) a discussion
will be taken off-line if a responder offers to accept a copy of the
problematic database to look at directly, but that's pretty rare. And when
it does occur, someone will (or should) post the final solution so that
others may see it. And one other netiquette point in these groups (which
happens often, but not always) is to post back a "that worked" (or, much
more rarely, "that didn't fix it") so that browsers/searchers will know
whether a posted solution works.

HTH,

Rob











- Show quoted text -

Hi Rob,

Thanks so much for responding! Funny (sort of) story: I had started
writing a post back about how I'd tried this so many times, so many
ways, I'd lost track, but I was sure I had the syntax right. Then I
figured I better at least enter the most recent attempt at least to
serve as a basis for further discussion. So I went in, re-entered the
DLookup, which I'd deleted before out of frustration (I would've
copied and pasted your example directly, but I'd changed the naming
according to LNC), and wouldn't you know it, it works just fine! No
idea what syntax error I'd been making, but man am I glad I can
finally get off the ground with this!

Thanks again!

Mike