Somebody Please Help Me

Oops, I TOTALLY forgot about the FindRecord you are using and yes it does
depend on which control has the focus. I would suggest you then readh
Bruce's replies because he does offer suggestions/other options.

And to reinterate what Bruce is sying, while there is always more than one
way to skin a cat, your way is not based on the way Access DOES work. While
I too get upset that things don't always work they I think they SHOULD work,
I recognize that trying to teach the pig to dance only aggravates me AND the
pig (and the pig still won't dance). Please look at what Bruce has
suggested and see if there is something there that will work for you.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
Gina,
Thanks for your patience and generosity.
Let me say, again, the vital bit of code here is DoCmd.FindRecord. Now,
I'm
not saying this is the best command to accomplish my task, but it just
seemed
to me the most likely candidate. This command, or method, seems to depend
for its operation on the control in the form that has focus. I thought I
had
made this quite clear in my prior posts. FindRecord has to know which
column
(field) to search, and the way it knows is by the control in the form that
has focus, which is bound to the appropriate field in the table. If the
correct contol does not have focus, FindRecord is looking in the wrong
column
and therefore does not find a match on the value it was sent in search of.
This is why the cursor has to be moved to the right control (text box)
before
using FindRecord. Therefore if I want to use FindRecord to search for a
Firm
by Firm ID, I must have that field visibly represented on my form, and it
must have focus.

"Gina Whipp" wrote:

Why are you trying to move the cursor to a hidden control... Just make
it
Tab Stop = No and make no refrence to move ot it, it is not neccessary.

Does this database have any data? I'd be happy to take a look for FREE,
as
this should work, before you go crazy and not after!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
Gina Whipp,
I had considered trying what you suggest, but just didn't believe such
a
goofy contrivance should be necessary. However, in desperation I
finally
did
try it, and guess what? Microsoft Access cannot move to a hidden
control!
In a way I'm glad about this because I don't think is would make sense
to
be
able to move the cursor to a hidden control.

I made the control visible, and now everything works perfectly. Except
I
do
not want to have to see the Firm ID!!! This is so crazy...when I
originally
built the Firms table, I devised a key field based on an alphabetical
naming
convention to uniquely identify each firm. In time, however, I came to
accept what I read about how much better it is to let Access take care
of
the
key field via autonumbering, so I got rid of my text-based key and
replaced
it with an autonumber key. Thus I went to the trouble of switching to
autonumber, but am not really gaining the benefit from it!!

From my point of view, I keep throwing easy pitches, and Access keeps
striking out. I know there are answers out there, but an answer you
can't
find and use and master with reasonable time and effort isn't an answer
at
all.

"Gina Whipp" wrote:

oldblindpew,

Then why not add the key and make it invisible? It doesn't have to
show
to
work...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
Gina,
Thanks for replying. My list box is already set up as you describe.
When
I
wrote of passing either the Firm Name to a sting variable or
conversely,
the
Firm ID to a long integer variable, this involved changing the bound
column
to either 2 or 1, respectively.

I think the problem has to do with the fact that my Firms
maintenance
form
is focussed on the Firm Name. DoCmd.FindRecord seems unable to
search
the
Firm ID field, but only the Firm Name field, due to this implied
focus.

If I were to add a Firm ID text box to my maintenance form, and then
go
to
that control prior to running DoCmd.FindRecord, I have no doubt it
would
work. But the whole idea of having a surrogate autonumbering key is
that
it
should lurk in the background without the user having to see it or
know
about
it.

Another solution would be to alter the names of firms to eliminate
any
duplicates, but monkeying with the names just to accomodate the
lameness
of
the database system is unacceptable. In essence, this would change
the
Firm
Name into the real key field. If so, why have a surrogate key?

I'm sure there is an answer here somewhere; I can't be the first
person
to
want to do this. I'm just amazed that finding a way to perform so
basic a
database operation could ever be this difficult and time-consuming.

"Gina Whipp" wrote:

oldblindpew,

You would need to use the Firm's unique ID and use the same thing
in
the
list box, which ight be why it didn't work before because of the
way
the
list box was set up. Try setting up the list box as 2 columns and
make
the
first one 0" width but set the bound column as 1.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in
message
...
What kind of a database cannot retrieve a record and show it on a
form?

Here's the flow: The user opens a form for Firms maintenance. On
this
form
is a textbox which displays the name of the firm. Next to this
textbox
is
a
command button with a picture of binoculars on it. When you click
on
the
button, a dialog form is opened containing a list box showing all
the
firms
in alphabetical order. The user finds the firm he is interested
in,
and
selects it. At this point the dialog box closes and the selected
firm's
record appears on the underlying form, but this last bit doesn't
work
for
duplicate firm names. Access retrieves only the first instance
of
the
selected firm, regardless of which instance the user selects.
Here
is
the
code behind the event:

Option Compare Database 'Use database order for string
comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm

' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm

' Close the dialog form to switch back to the main form.
DoCmd.Close

' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it
shouldn't.
DoCmd.GoToControl "txbFirmName"

' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect

' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"

Exit_OkFindFirm:
On Error Resume Next
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

Originally, I put the firm's name into a string variable
(strSelect)
and
did
DoCmd.FindRecord strSelect. This works, but not for duplicate
firm
names.

Next I tried passing the firm's unique ID number to a long
integer
variable.
This did not work at all, doubtless because FindRecord was
searching
the
firm name field for the firm ID number, which of course isn't
there.

Next, I tried to force FindRecord to look in all fields for a
match
to
the
firm ID. I didn't much care for this approach, but it didn't
work
anyway,
so
that was okay.

The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the
Firms
table
wasn't open.

My brain hurts.

I am not very familiar with Seek, so I am not the person to advise you about
it. However, from what I have seen it takes more coding than my suggested
method. If you seek simplicity the easiest thing is to use the combo box
(or list box) wizard. Follow the prompts to have it locate a selected
record. When it is done, take a look at the code. It is quite similar to
mine, except that it has:

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I'm not sure why I omitted the EOF (end of file) test from my code, but I
haven't had any problems in quite a number of applications. Maybe it's
because I got the code from a newsgroup posting rather than by way of the
wizard.

One of the problems I have with the wizard is that it gives the control a
default name, so if I want to use a naming convention I need to change the
control name in the code, then go back to the Property Sheet and change the
name there, then add the Event again. It will find the code, but the extra
step is needed to link the renamed control to the code. Still, the wizard
can be useful.

By the way, I didn't realize GoToRecord requires the control to have the
focus. I don't know if you could reference a list box column as I suggested
in the previous posting, but I really do think the RecordSetClone/Bookmark
way is quite tidy, and it works very quickly. It may seem like Access needs
to do a lot, but at millions or billions of clock cycles per second Access
can get a lot done in just about no time at all.

I can't explain RecordSetClone very well except to say I understand it to be
copy of the recordset that can be searched independently of the form. Once
that is done, the RecordSetClone bookmark, which is an internal number
identifying the record (and which I understand to be the same in the
recordset and the clone), is matched with the Recordset bookmark. Since the
Recordset is used by the form, the form takes you to that record.

It sounds quite esoteric, but is really very down-to-earth once you get to
know it.

"oldblindpew" wrote in message
news
BruceM,
Thanks for your very kind response.
I am not familiar with GoToRecord either. As stated, I only tried it
because I couldn't get FindRecord to work.
I don't doubt that my approach has been unconventional, but that is only
because I do not know what the conventional approach is. I developed my
application largely by copying and modifying stuff from sample databases.
I
have two books, one is Access 200 Programming from the Ground Up, by Whil
Hentzen, which I assure you is very well-thumbed, and more recently,
Access
2007 VBA Programmer's Reference, which is a bit over my head, and not
helped
by the numerous typos I keep finding. In addition, there is of course the
sometimes-helpful Microsoft F1 Help. Despite all this I have not really
come
upon a "conventional" method for finding a record and displaying it to a
form, which is pretty stunning since that is what databases are supposed
to
do.
I am only just now beginning to get the impression that the conventional
answer may involve Cloned RecordSets and Bookmarks, but these are
completely
new concepts to me and sound over-complicated for the simple task I'm
trying
to accomplish.
This morning I have gained some ground by abandoning FindRecord in favor
of
Seek. Could you tell me, once I've found the desired record via Seek, how
to
make that record the current record so it appears on the form?
Thanks

"BruceM" wrote:

I'm not sure I'm following allo of this, but you can reference any column
in
a combo box or list box row source:
Me.lstFirm.Column(1) references the second column in the list box
(numbering
is zero-based in this case). If you simply reference the list box you
reference the bound column. There is no need to change the bound column.

I am not familiar with GoToRecord. When I use an unbound combo box (or
list
box) to search, its After Update code I use is something like this:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[FirmID] = " & Me.lstFirm
Me.Bookmark = rs.Bookmark

This assumes FirmID is a number field (including autonumber). If you
search
by firm name and there are duplicates you may not end up at the record
you
want no matter the method. All of this assumes the list box row source
Gina
described (two columns, with the unique number field in the first column
and
the firm name in the second). BTW, the first column is 1 on the Property
Sheet, but it is 0 in code. As described, a reference to the list box
references the bound column. Me.lstFirm references the bound column.

You can refer to any field in a form's record source, but until you go to
the record you don't have that option. That is why the reference

To move the cursor to a control (not a field, which is in a table or
query):
Me.ControlName.SetFocus

You refer to moving the cursor first to a "field", then to the command
button in the same code. You can move the focus, but of course the
command
button won't accept a cursor, unless I misunderstand something in what
you
are saying.

Just as an observation, your frustration is apparent and in some ways
understandable, but many developers have achieved very good results with
Access databases. There is a rather steep learning curve, to be sure,
but
there is also a lot of free information and many sample databases to help
guide the learning process. Your approach to this problem has been
somewhat
unconventional, so it is hard to see how you have come to the conclusion
that Access is "lame". Some people may perceive that as a chip on your
shoulder, and be reluctant to attempt a reply for that reason.

There are some good articles and tips on this page:
http://allenbrowne.com/tips.html
On the right side of the page are links to many more sites.



"oldblindpew" wrote in message
...
Gina,
Thanks for replying. My list box is already set up as you describe.
When
I
wrote of passing either the Firm Name to a sting variable or
conversely,
the
Firm ID to a long integer variable, this involved changing the bound
column
to either 2 or 1, respectively.

I think the problem has to do with the fact that my Firms maintenance
form
is focussed on the Firm Name. DoCmd.FindRecord seems unable to search
the
Firm ID field, but only the Firm Name field, due to this implied focus.

If I were to add a Firm ID text box to my maintenance form, and then go
to
that control prior to running DoCmd.FindRecord, I have no doubt it
would
work. But the whole idea of having a surrogate autonumbering key is
that
it
should lurk in the background without the user having to see it or know
about
it.

Another solution would be to alter the names of firms to eliminate any
duplicates, but monkeying with the names just to accomodate the
lameness
of
the database system is unacceptable. In essence, this would change the
Firm
Name into the real key field. If so, why have a surrogate key?

I'm sure there is an answer here somewhere; I can't be the first person
to
want to do this. I'm just amazed that finding a way to perform so
basic a
database operation could ever be this difficult and time-consuming.

"Gina Whipp" wrote:

oldblindpew,

You would need to use the Firm's unique ID and use the same thing in
the
list box, which ight be why it didn't work before because of the way
the
list box was set up. Try setting up the list box as 2 columns and
make
the
first one 0" width but set the bound column as 1.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
What kind of a database cannot retrieve a record and show it on a
form?

Here's the flow: The user opens a form for Firms maintenance. On
this
form
is a textbox which displays the name of the firm. Next to this
textbox
is
a
command button with a picture of binoculars on it. When you click on
the
button, a dialog form is opened containing a list box showing all
the
firms
in alphabetical order. The user finds the firm he is interested in,
and
selects it. At this point the dialog box closes and the selected
firm's
record appears on the underlying form, but this last bit doesn't
work
for
duplicate firm names. Access retrieves only the first instance of
the
selected firm, regardless of which instance the user selects. Here
is
the
code behind the event:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm

' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm

' Close the dialog form to switch back to the main form.
DoCmd.Close

' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it
shouldn't.
DoCmd.GoToControl "txbFirmName"

' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect

' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"

Exit_OkFindFirm:
On Error Resume Next
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

Originally, I put the firm's name into a string variable (strSelect)
and
did
DoCmd.FindRecord strSelect. This works, but not for duplicate firm
names.

Next I tried passing the firm's unique ID number to a long integer
variable.
This did not work at all, doubtless because FindRecord was searching
the
firm name field for the firm ID number, which of course isn't there.

Next, I tried to force FindRecord to look in all fields for a match
to
the
firm ID. I didn't much care for this approach, but it didn't work
anyway,
so
that was okay.

The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the
Firms
table
wasn't open.

My brain hurts.

I did not say that Access relied on goofy contrivances. I said it would be a
goofy contrivance if you had to put an unwanted textbox on a form and then
hide it, just to get desired information from a known record to diplay on the
form. This would be a goofy contrivance because it could not possibly be
telling Access anything that Access did not already know. I further said
that I did not believe such a contrivance should be necessary, and of course
it is not.

"BruceM" wrote:

Access does not rely on "goofy contrivances", but rather on procedures and
methods with which you may not be familiar. You seem to think Access
"should" do things the way you want rather than the way it is designed to
work. There certainly are some things about Access that need improvement,
but it is well able to do what I think you have described. For one thing,
as Gina said there is no reason to move out of the control. I outlined in
another post in this thread some ways of doing what I think you are trying
to do.

"oldblindpew" wrote in message
...
Gina Whipp,
I had considered trying what you suggest, but just didn't believe such a
goofy contrivance should be necessary. However, in desperation I finally
did
try it, and guess what? Microsoft Access cannot move to a hidden control!
In a way I'm glad about this because I don't think is would make sense to
be
able to move the cursor to a hidden control.

I made the control visible, and now everything works perfectly. Except I
do
not want to have to see the Firm ID!!! This is so crazy...when I
originally
built the Firms table, I devised a key field based on an alphabetical
naming
convention to uniquely identify each firm. In time, however, I came to
accept what I read about how much better it is to let Access take care of
the
key field via autonumbering, so I got rid of my text-based key and
replaced
it with an autonumber key. Thus I went to the trouble of switching to
autonumber, but am not really gaining the benefit from it!!

From my point of view, I keep throwing easy pitches, and Access keeps
striking out. I know there are answers out there, but an answer you can't
find and use and master with reasonable time and effort isn't an answer at
all.

"Gina Whipp" wrote:

oldblindpew,

Then why not add the key and make it invisible? It doesn't have to show
to
work...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
Gina,
Thanks for replying. My list box is already set up as you describe.
When
I
wrote of passing either the Firm Name to a sting variable or
conversely,
the
Firm ID to a long integer variable, this involved changing the bound
column
to either 2 or 1, respectively.

I think the problem has to do with the fact that my Firms maintenance
form
is focussed on the Firm Name. DoCmd.FindRecord seems unable to search
the
Firm ID field, but only the Firm Name field, due to this implied focus.

If I were to add a Firm ID text box to my maintenance form, and then go
to
that control prior to running DoCmd.FindRecord, I have no doubt it
would
work. But the whole idea of having a surrogate autonumbering key is
that
it
should lurk in the background without the user having to see it or know
about
it.

Another solution would be to alter the names of firms to eliminate any
duplicates, but monkeying with the names just to accomodate the
lameness
of
the database system is unacceptable. In essence, this would change the
Firm
Name into the real key field. If so, why have a surrogate key?

I'm sure there is an answer here somewhere; I can't be the first person
to
want to do this. I'm just amazed that finding a way to perform so
basic a
database operation could ever be this difficult and time-consuming.

"Gina Whipp" wrote:

oldblindpew,

You would need to use the Firm's unique ID and use the same thing in
the
list box, which ight be why it didn't work before because of the way
the
list box was set up. Try setting up the list box as 2 columns and
make
the
first one 0" width but set the bound column as 1.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
What kind of a database cannot retrieve a record and show it on a
form?

Here's the flow: The user opens a form for Firms maintenance. On
this
form
is a textbox which displays the name of the firm. Next to this
textbox
is
a
command button with a picture of binoculars on it. When you click on
the
button, a dialog form is opened containing a list box showing all
the
firms
in alphabetical order. The user finds the firm he is interested in,
and
selects it. At this point the dialog box closes and the selected
firm's
record appears on the underlying form, but this last bit doesn't
work
for
duplicate firm names. Access retrieves only the first instance of
the
selected firm, regardless of which instance the user selects. Here
is
the
code behind the event:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm

' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm

' Close the dialog form to switch back to the main form.
DoCmd.Close

' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it
shouldn't.
DoCmd.GoToControl "txbFirmName"

' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect

' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"

Exit_OkFindFirm:
On Error Resume Next
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

Originally, I put the firm's name into a string variable (strSelect)
and
did
DoCmd.FindRecord strSelect. This works, but not for duplicate firm
names.

Next I tried passing the firm's unique ID number to a long integer
variable.
This did not work at all, doubtless because FindRecord was searching
the
firm name field for the firm ID number, which of course isn't there.

Next, I tried to force FindRecord to look in all fields for a match
to
the
firm ID. I didn't much care for this approach, but it didn't work
anyway,
so
that was okay.

The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the
Firms
table
wasn't open.

My brain hurts.

My misunderstanding of what you wrote. In fact, Access does require a
hidden text box in reports to accomplish what can be done by referencing a
field in a form. Seems absurd to me, but that is the case with some
features of just about any large-scale software. For that matter, people
who use my small-scale Access applications sometimes ask why I did something
a certain way, to which I can only reply that I thought it was how they
wanted to use the program. Of course, I can usually change it pretty
readily to fit actual user needs.

"oldblindpew" wrote in message
...
I did not say that Access relied on goofy contrivances. I said it would be
a
goofy contrivance if you had to put an unwanted textbox on a form and then
hide it, just to get desired information from a known record to diplay on
the
form. This would be a goofy contrivance because it could not possibly be
telling Access anything that Access did not already know. I further said
that I did not believe such a contrivance should be necessary, and of
course
it is not.

"BruceM" wrote:

Access does not rely on "goofy contrivances", but rather on procedures
and
methods with which you may not be familiar. You seem to think Access
"should" do things the way you want rather than the way it is designed to
work. There certainly are some things about Access that need
improvement,
but it is well able to do what I think you have described. For one
thing,
as Gina said there is no reason to move out of the control. I outlined
in
another post in this thread some ways of doing what I think you are
trying
to do.

"oldblindpew" wrote in message
...
Gina Whipp,
I had considered trying what you suggest, but just didn't believe such
a
goofy contrivance should be necessary. However, in desperation I
finally
did
try it, and guess what? Microsoft Access cannot move to a hidden
control!
In a way I'm glad about this because I don't think is would make sense
to
be
able to move the cursor to a hidden control.

I made the control visible, and now everything works perfectly. Except
I
do
not want to have to see the Firm ID!!! This is so crazy...when I
originally
built the Firms table, I devised a key field based on an alphabetical
naming
convention to uniquely identify each firm. In time, however, I came to
accept what I read about how much better it is to let Access take care
of
the
key field via autonumbering, so I got rid of my text-based key and
replaced
it with an autonumber key. Thus I went to the trouble of switching to
autonumber, but am not really gaining the benefit from it!!

From my point of view, I keep throwing easy pitches, and Access keeps
striking out. I know there are answers out there, but an answer you
can't
find and use and master with reasonable time and effort isn't an answer
at
all.

"Gina Whipp" wrote:

oldblindpew,

Then why not add the key and make it invisible? It doesn't have to
show
to
work...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
Gina,
Thanks for replying. My list box is already set up as you describe.
When
I
wrote of passing either the Firm Name to a sting variable or
conversely,
the
Firm ID to a long integer variable, this involved changing the bound
column
to either 2 or 1, respectively.

I think the problem has to do with the fact that my Firms
maintenance
form
is focussed on the Firm Name. DoCmd.FindRecord seems unable to
search
the
Firm ID field, but only the Firm Name field, due to this implied
focus.

If I were to add a Firm ID text box to my maintenance form, and then
go
to
that control prior to running DoCmd.FindRecord, I have no doubt it
would
work. But the whole idea of having a surrogate autonumbering key is
that
it
should lurk in the background without the user having to see it or
know
about
it.

Another solution would be to alter the names of firms to eliminate
any
duplicates, but monkeying with the names just to accomodate the
lameness
of
the database system is unacceptable. In essence, this would change
the
Firm
Name into the real key field. If so, why have a surrogate key?

I'm sure there is an answer here somewhere; I can't be the first
person
to
want to do this. I'm just amazed that finding a way to perform so
basic a
database operation could ever be this difficult and time-consuming.

"Gina Whipp" wrote:

oldblindpew,

You would need to use the Firm's unique ID and use the same thing
in
the
list box, which ight be why it didn't work before because of the
way
the
list box was set up. Try setting up the list box as 2 columns and
make
the
first one 0" width but set the bound column as 1.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in
message
...
What kind of a database cannot retrieve a record and show it on a
form?

Here's the flow: The user opens a form for Firms maintenance. On
this
form
is a textbox which displays the name of the firm. Next to this
textbox
is
a
command button with a picture of binoculars on it. When you click
on
the
button, a dialog form is opened containing a list box showing all
the
firms
in alphabetical order. The user finds the firm he is interested
in,
and
selects it. At this point the dialog box closes and the selected
firm's
record appears on the underlying form, but this last bit doesn't
work
for
duplicate firm names. Access retrieves only the first instance
of
the
selected firm, regardless of which instance the user selects.
Here
is
the
code behind the event:

Option Compare Database 'Use database order for string
comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm

' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm

' Close the dialog form to switch back to the main form.
DoCmd.Close

' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it
shouldn't.
DoCmd.GoToControl "txbFirmName"

' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect

' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"

Exit_OkFindFirm:
On Error Resume Next
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

Originally, I put the firm's name into a string variable
(strSelect)
and
did
DoCmd.FindRecord strSelect. This works, but not for duplicate
firm
names.

Next I tried passing the firm's unique ID number to a long
integer
variable.
This did not work at all, doubtless because FindRecord was
searching
the
firm name field for the firm ID number, which of course isn't
there.

Next, I tried to force FindRecord to look in all fields for a
match
to
the
firm ID. I didn't much care for this approach, but it didn't
work
anyway,
so
that was okay.

The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the
Firms
table
wasn't open.

My brain hurts.

Access is buggy as hell.

It crashes, it has a poor design-- it just doesn't work for most
serious users (who just want a product that 'just works')

Stop making excuses for Jet-- if it's a bug it sucks and it should be
fixed

-Aaron


On Mar 4, 10:25*am, "BruceM" wrote:
Access does not rely on "goofy contrivances", but rather on procedures and
methods with which you may not be familiar. *You seem to think Access
"should" do things the way you want rather than the way it is designed to
work. *There certainly are some things about Access that need improvement,
but it is well able to do what I think you have described. *For one thing,
as Gina said there is no reason to move out of the control. *I outlined in
another post in this thread some ways of doing what I think you are trying
to do.

"oldblindpew" wrote in message

...

Gina Whipp,
I had considered trying what you suggest, but just didn't believe such a
goofy contrivance should be necessary. *However, in desperation I finally
did
try it, and guess what? *Microsoft Access cannot move to a hidden control!
In a way I'm glad about this because I don't think is would make sense to
be
able to move the cursor to a hidden control.

I made the control visible, and now everything works perfectly. *Except I
do
not want to have to see the Firm ID!!! *This is so crazy...when I
originally
built the Firms table, I devised a key field based on an alphabetical
naming
convention to uniquely identify each firm. *In time, however, I came to
accept what I read about how much better it is to let Access take care of
the
key field via autonumbering, so I got rid of my text-based key and
replaced
it with an autonumber key. *Thus I went to the trouble of switching to
autonumber, but am not really gaining the benefit from it!!

From my point of view, I keep throwing easy pitches, and Access keeps
striking out. *I know there are answers out there, but an answer you can't
find and use and master with reasonable time and effort isn't an answer at
all.

"Gina Whipp" wrote:

oldblindpew,

Then why not add the key and make it invisible? *It doesn't have to show
to
work...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
Gina,
Thanks for replying. *My list box is already set up as you describe.
When
I
wrote of passing either the Firm Name to a sting variable or
conversely,
the
Firm ID to a long integer variable, this involved changing the bound
column
to either 2 or 1, respectively.

I think the problem has to do with the fact that my Firms maintenance
form
is focussed on the Firm Name. *DoCmd.FindRecord seems unable to search
the
Firm ID field, but only the Firm Name field, due to this implied focus.

If I were to add a Firm ID text box to my maintenance form, and then go
to
that control prior to running DoCmd.FindRecord, I have no doubt it
would
work. *But the whole idea of having a surrogate autonumbering key is
that
it
should lurk in the background without the user having to see it or know
about
it.

Another solution would be to alter the names of firms to eliminate any
duplicates, but monkeying with the names just to accomodate the
lameness
of
the database system is unacceptable. *In essence, this would change the
Firm
Name into the real key field. *If so, why have a surrogate key?

I'm sure there is an answer here somewhere; I can't be the first person
to
want to do this. *I'm just amazed that finding a way to perform so
basic a
database operation could ever be this difficult and time-consuming.

"Gina Whipp" wrote:

oldblindpew,

You would need to use the Firm's unique ID and use the same thing in
the
list box, which ight be why it didn't work before because of the way
the
list box was set up. *Try setting up the list box as 2 columns and
make
the
first one 0" width but set the bound column as 1.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
What kind of a database cannot retrieve a record and show it on a
form?

Here's the flow: The user opens a form for Firms maintenance. On
this
form
is a textbox which displays the name of the firm. Next to this
textbox
is
a
command button with a picture of binoculars on it. When you click on
the
button, a dialog form is opened containing a list box showing all
the
firms
in alphabetical order. The user finds the firm he is interested in,
and
selects it. At this point the dialog box closes and the selected
firm's
record appears on the underlying form, but this last bit doesn't
work
for
duplicate firm names. *Access retrieves only the first instance of
the
selected firm, regardless of which instance the user selects. *Here
is
the
code behind the event:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
* *On Error GoTo Err_OkFindFirm

' Check to see if no selection was made.
* *If IsNull(Me!lstFirm) Then
* *MsgBox "Make a selection or click Cancel", , "No Selection"
* *GoTo Exit_OkFindFirm
* *End If

' Store the selection in a variable.
* *Dim lngSelect As Long
* *lngSelect = Me!lstFirm
* *'Dim strSelect As String
* *'strSelect = Me!lstFirm

' Close the dialog form to switch back to the main form.
* *DoCmd.Close

' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it
shouldn't.
* *DoCmd.GoToControl "txbFirmName"

' Find the selected record.
* *'DoCmd.FindRecord lngSelect, , , , , acAll
* *'DoCmd.FindRecord strSelect
* *DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect

' Move the cursor back to the search button.
* *DoCmd.GoToControl "cmdSearchFirm"

Exit_OkFindFirm:
* *On Error Resume Next
* *Exit Sub

Err_OkFindFirm:
* *MsgBox "Error #: " & Err & Chr(13) & Err.Description
* *Resume Exit_OkFindFirm

End Sub

Originally, I put the firm's name into a string variable (strSelect)
and
did
DoCmd.FindRecord strSelect. *This works, but not for duplicate firm
names.

Next I tried passing the firm's unique ID number to a long integer
variable.
This did not work at all, doubtless because FindRecord was searching
the
firm name field for the firm ID number, which of course isn't there.

Next, I tried to force FindRecord to look in all fields for a match
to
the
firm ID. *I didn't much care for this approach, but it didn't work
anyway,
so
that was okay.

The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. *This gave me an error message stating that the
Firms
table
wasn't open.

My brain hurts.

BruceM,
Thanks again for your patience.

I think I am getting close to a solution.

FindRecord, despite its promising sound, is limited by its need for a
visible control on the form, bound to the search field, and having focus.
The Seek method, despite its speed and efficiency, doesn't work because there
is apparently no way to set the form's Bookmark property to the current table
record. I'm a bit amazed that the above two methods have not been enhanced
by now to make them more useful.

That leaves the Find method, which is reportedly slow, and requires the use
of the Recordset Clone, which isn't exactly intuitive and looks suspiciously
like a workaround.

I have code from Allen Browne to make a combo box work as a record-picking
device, but I do not want to use this approach, because I see this as an
improper use of the control. A combo box is for entering data into a field.
I do not want the user confused by having it sometimes used for picking a
record. Instead, I want to stick with my search button (binoculars) which
opens a dialog form containing a list box.

I am having a bit of trouble getting this to work, though, and I think it
has to do with with focus and orientation. I get an error message stating
that an object I am referencing is either closed or doesn't exist. I suspect
that after I capture the value of the Firm ID and close the dialog form to
revert back to the mainenance form, Access is getting lost at this point. I
will get into debugging, but perhaps you could spot the problem more quickly.
Here is the code:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
'Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm
Dim rst As DAO.Recordset

'Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

'Store the selection (firm ID number) in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm

'Close the dialog form to switch back to the main form.
DoCmd.Close

'Find the selected record.
Set rst = Me!RecordsetClone
rst.FindFirst "aIDFirm = " & lngSelect

'Check the result
If rst.NoMatch Then
MsgBox "Record not found."
Else
Me!Bookmark = rst.Bookmark
End If

Exit_OkFindFirm:
On Error Resume Next
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub


"BruceM" wrote:

I am not very familiar with Seek, so I am not the person to advise you about
it. However, from what I have seen it takes more coding than my suggested
method. If you seek simplicity the easiest thing is to use the combo box
(or list box) wizard. Follow the prompts to have it locate a selected
record. When it is done, take a look at the code. It is quite similar to
mine, except that it has:

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I'm not sure why I omitted the EOF (end of file) test from my code, but I
haven't had any problems in quite a number of applications. Maybe it's
because I got the code from a newsgroup posting rather than by way of the
wizard.

One of the problems I have with the wizard is that it gives the control a
default name, so if I want to use a naming convention I need to change the
control name in the code, then go back to the Property Sheet and change the
name there, then add the Event again. It will find the code, but the extra
step is needed to link the renamed control to the code. Still, the wizard
can be useful.

By the way, I didn't realize GoToRecord requires the control to have the
focus. I don't know if you could reference a list box column as I suggested
in the previous posting, but I really do think the RecordSetClone/Bookmark
way is quite tidy, and it works very quickly. It may seem like Access needs
to do a lot, but at millions or billions of clock cycles per second Access
can get a lot done in just about no time at all.

I can't explain RecordSetClone very well except to say I understand it to be
copy of the recordset that can be searched independently of the form. Once
that is done, the RecordSetClone bookmark, which is an internal number
identifying the record (and which I understand to be the same in the
recordset and the clone), is matched with the Recordset bookmark. Since the
Recordset is used by the form, the form takes you to that record.

It sounds quite esoteric, but is really very down-to-earth once you get to
know it.

"oldblindpew" wrote in message
news
BruceM,
Thanks for your very kind response.
I am not familiar with GoToRecord either. As stated, I only tried it
because I couldn't get FindRecord to work.
I don't doubt that my approach has been unconventional, but that is only
because I do not know what the conventional approach is. I developed my
application largely by copying and modifying stuff from sample databases.
I
have two books, one is Access 200 Programming from the Ground Up, by Whil
Hentzen, which I assure you is very well-thumbed, and more recently,
Access
2007 VBA Programmer's Reference, which is a bit over my head, and not
helped
by the numerous typos I keep finding. In addition, there is of course the
sometimes-helpful Microsoft F1 Help. Despite all this I have not really
come
upon a "conventional" method for finding a record and displaying it to a
form, which is pretty stunning since that is what databases are supposed
to
do.
I am only just now beginning to get the impression that the conventional
answer may involve Cloned RecordSets and Bookmarks, but these are
completely
new concepts to me and sound over-complicated for the simple task I'm
trying
to accomplish.
This morning I have gained some ground by abandoning FindRecord in favor
of
Seek. Could you tell me, once I've found the desired record via Seek, how
to
make that record the current record so it appears on the form?
Thanks

"BruceM" wrote:

I'm not sure I'm following allo of this, but you can reference any column
in
a combo box or list box row source:
Me.lstFirm.Column(1) references the second column in the list box
(numbering
is zero-based in this case). If you simply reference the list box you
reference the bound column. There is no need to change the bound column.

I am not familiar with GoToRecord. When I use an unbound combo box (or
list
box) to search, its After Update code I use is something like this:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[FirmID] = " & Me.lstFirm
Me.Bookmark = rs.Bookmark

This assumes FirmID is a number field (including autonumber). If you
search
by firm name and there are duplicates you may not end up at the record
you
want no matter the method. All of this assumes the list box row source
Gina
described (two columns, with the unique number field in the first column
and
the firm name in the second). BTW, the first column is 1 on the Property
Sheet, but it is 0 in code. As described, a reference to the list box
references the bound column. Me.lstFirm references the bound column.

You can refer to any field in a form's record source, but until you go to
the record you don't have that option. That is why the reference

To move the cursor to a control (not a field, which is in a table or
query):
Me.ControlName.SetFocus

You refer to moving the cursor first to a "field", then to the command
button in the same code. You can move the focus, but of course the
command
button won't accept a cursor, unless I misunderstand something in what
you
are saying.

Just as an observation, your frustration is apparent and in some ways
understandable, but many developers have achieved very good results with
Access databases. There is a rather steep learning curve, to be sure,
but
there is also a lot of free information and many sample databases to help
guide the learning process. Your approach to this problem has been
somewhat
unconventional, so it is hard to see how you have come to the conclusion
that Access is "lame". Some people may perceive that as a chip on your
shoulder, and be reluctant to attempt a reply for that reason.

There are some good articles and tips on this page:
http://allenbrowne.com/tips.html
On the right side of the page are links to many more sites.



"oldblindpew" wrote in message
...
Gina,
Thanks for replying. My list box is already set up as you describe.
When
I
wrote of passing either the Firm Name to a sting variable or
conversely,
the
Firm ID to a long integer variable, this involved changing the bound
column
to either 2 or 1, respectively.

I think the problem has to do with the fact that my Firms maintenance
form
is focussed on the Firm Name. DoCmd.FindRecord seems unable to search
the
Firm ID field, but only the Firm Name field, due to this implied focus.

If I were to add a Firm ID text box to my maintenance form, and then go
to
that control prior to running DoCmd.FindRecord, I have no doubt it
would
work. But the whole idea of having a surrogate autonumbering key is
that
it
should lurk in the background without the user having to see it or know
about
it.

Another solution would be to alter the names of firms to eliminate any
duplicates, but monkeying with the names just to accomodate the
lameness
of
the database system is unacceptable. In essence, this would change the
Firm
Name into the real key field. If so, why have a surrogate key?

I'm sure there is an answer here somewhere; I can't be the first person
to
want to do this. I'm just amazed that finding a way to perform so
basic a
database operation could ever be this difficult and time-consuming.

"Gina Whipp" wrote:

oldblindpew,

You would need to use the Firm's unique ID and use the same thing in
the
list box, which ight be why it didn't work before because of the way
the
list box was set up. Try setting up the list box as 2 columns and
make
the
first one 0" width but set the bound column as 1.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
What kind of a database cannot retrieve a record and show it on a
form?

Here's the flow: The user opens a form for Firms maintenance. On
this
form
is a textbox which displays the name of the firm. Next to this
textbox
is
a
command button with a picture of binoculars on it. When you click on
the
button, a dialog form is opened containing a list box showing all
the
firms
in alphabetical order. The user finds the firm he is interested in,
and
selects it. At this point the dialog box closes and the selected
firm's
record appears on the underlying form, but this last bit doesn't
work
for
duplicate firm names. Access retrieves only the first instance of
the
selected firm, regardless of which instance the user selects. Here
is
the
code behind the event:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm

' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm

' Close the dialog form to switch back to the main form.
DoCmd.Close

' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it
shouldn't.
DoCmd.GoToControl "txbFirmName"

' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect

' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"

Exit_OkFindFirm:
On Error Resume Next
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

Originally, I put the firm's name into a string variable (strSelect)
and
did
DoCmd.FindRecord strSelect. This works, but not for duplicate firm
names.

Next I tried passing the firm's unique ID number to a long integer
variable.
This did not work at all, doubtless because FindRecord was searching
the
firm name field for the firm ID number, which of course isn't there.

Next, I tried to force FindRecord to look in all fields for a match
to
the
firm ID. I didn't much care for this approach, but it didn't work
anyway,
so
that was okay.

The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the

On Thu, 5 Mar 2009 07:21:01 -0800, oldblindpew
wrote:

That leaves the Find method, which is reportedly slow, and requires the use
of the Recordset Clone, which isn't exactly intuitive and looks suspiciously
like a workaround.

Unless you have a HUGE dataset, and a very accurate stopwatch, you'll never
notice the difference in performance between SEEK and FIND. And the
RecordsetClone is perfectly standard practice. If the name of the object puts
you off... well, Microsoft has some odd naming conventions even for things
which work just fine.

Try it. It really actually does work.
--

John W. Vinson [MVP]

John,
I have to laugh to keep from crying. Please read my post. I have already
determined that FindRecord doesn't work and Seek doesn't work, so now I'm
trying Find, with RecordSetClone and Bookmark. I Am Trying It, But It Isn't
Working. I posted a copy of my code, and indicated what the error message is
and where I think the problem may lie. I'm sure you could look at my code
and tell me immediately what I've got wrong.

Meanwhile, I've been trying to debug my code by single-stepping thru it and
am about to go berserk because I can't make THAT work! My information says
you position your cursor and press F8. All I get is the computer generated
"clunk" noise, the sound of a user beating his virtual head against a virtual
wall. Please overlook my hyperventilating and toss me a hint or two. Thanks.



"John W. Vinson" wrote:

On Thu, 5 Mar 2009 07:21:01 -0800, oldblindpew
wrote:

That leaves the Find method, which is reportedly slow, and requires the use
of the Recordset Clone, which isn't exactly intuitive and looks suspiciously
like a workaround.

Unless you have a HUGE dataset, and a very accurate stopwatch, you'll never
notice the difference in performance between SEEK and FIND. And the
RecordsetClone is perfectly standard practice. If the name of the object puts
you off... well, Microsoft has some odd naming conventions even for things
which work just fine.

Try it. It really actually does work.
--

John W. Vinson [MVP]

I think the problem is that you are using the bang (!) instead of the dot
for Me.RecordsetClone and Me.Bookmark Here is an explanation of the two:
http://my.advisor.com/doc/05352

As long as aIDFirm is a number field it should work. It looks like it is,
but I am not certain.

For stepping through code, set a breakpoint by clicking the vertical bar at
the left of the code window, next to a line of code (other than one starting
with Dim, and maybe a few other exceptions). It should place a dot in the
vertical bar, and highlight the line of code. For instance, click next to
Set rst = Me.RecordsetClone. Now go to the form and attempt to run the code
by using the combo box to find a record. When the code reaches the
breakpoint it will pause, and you will see the code window. That's where
you use F8 to step through it one line at a time (or use F5 to skip to the
end or to the next break point).


"oldblindpew" wrote in message
...
John,
I have to laugh to keep from crying. Please read my post. I have already
determined that FindRecord doesn't work and Seek doesn't work, so now I'm
trying Find, with RecordSetClone and Bookmark. I Am Trying It, But It
Isn't
Working. I posted a copy of my code, and indicated what the error message
is
and where I think the problem may lie. I'm sure you could look at my code
and tell me immediately what I've got wrong.

Meanwhile, I've been trying to debug my code by single-stepping thru it
and
am about to go berserk because I can't make THAT work! My information
says
you position your cursor and press F8. All I get is the computer
generated
"clunk" noise, the sound of a user beating his virtual head against a
virtual
wall. Please overlook my hyperventilating and toss me a hint or two.
Thanks.



"John W. Vinson" wrote:

On Thu, 5 Mar 2009 07:21:01 -0800, oldblindpew
wrote:

That leaves the Find method, which is reportedly slow, and requires the
use
of the Recordset Clone, which isn't exactly intuitive and looks
suspiciously
like a workaround.

Unless you have a HUGE dataset, and a very accurate stopwatch, you'll
never
notice the difference in performance between SEEK and FIND. And the
RecordsetClone is perfectly standard practice. If the name of the object
puts
you off... well, Microsoft has some odd naming conventions even for
things
which work just fine.

Try it. It really actually does work.
--

John W. Vinson [MVP]

Thank you, BruceM.

I had already tried changing the bangs into dots, although I did not think
a) that I had it wrong, and b) that it would make any difference. It did not
make any difference.

I followed your instructions and got the step-thru to work. Its funny my
reference book said nothing about having to insert a break point to force the
execution into break mode. It just said put your cursor where you want and
press F8 to begin.

Single stepping confirms that Set rst = Me.RecordsetClone generates the
error. "Me" refers to the dialog form initially, and when that is closed the
firm form becomes the active form, "Me" should refer to it, but it isn't
working. "Me" has to be the object in the expression that is either closed
or doesn't exist. Weird.

"BruceM" wrote:

I think the problem is that you are using the bang (!) instead of the dot
for Me.RecordsetClone and Me.Bookmark Here is an explanation of the two:
http://my.advisor.com/doc/05352

As long as aIDFirm is a number field it should work. It looks like it is,
but I am not certain.

For stepping through code, set a breakpoint by clicking the vertical bar at
the left of the code window, next to a line of code (other than one starting
with Dim, and maybe a few other exceptions). It should place a dot in the
vertical bar, and highlight the line of code. For instance, click next to
Set rst = Me.RecordsetClone. Now go to the form and attempt to run the code
by using the combo box to find a record. When the code reaches the
breakpoint it will pause, and you will see the code window. That's where
you use F8 to step through it one line at a time (or use F5 to skip to the
end or to the next break point).


"oldblindpew" wrote in message
...
John,
I have to laugh to keep from crying. Please read my post. I have already
determined that FindRecord doesn't work and Seek doesn't work, so now I'm
trying Find, with RecordSetClone and Bookmark. I Am Trying It, But It
Isn't
Working. I posted a copy of my code, and indicated what the error message
is
and where I think the problem may lie. I'm sure you could look at my code
and tell me immediately what I've got wrong.

Meanwhile, I've been trying to debug my code by single-stepping thru it
and
am about to go berserk because I can't make THAT work! My information
says
you position your cursor and press F8. All I get is the computer
generated
"clunk" noise, the sound of a user beating his virtual head against a
virtual
wall. Please overlook my hyperventilating and toss me a hint or two.
Thanks.



"John W. Vinson" wrote:

On Thu, 5 Mar 2009 07:21:01 -0800, oldblindpew
wrote:

That leaves the Find method, which is reportedly slow, and requires the
use
of the Recordset Clone, which isn't exactly intuitive and looks
suspiciously
like a workaround.

Unless you have a HUGE dataset, and a very accurate stopwatch, you'll
never
notice the difference in performance between SEEK and FIND. And the
RecordsetClone is perfectly standard practice. If the name of the object
puts
you off... well, Microsoft has some odd naming conventions even for
things
which work just fine.

Try it. It really actually does work.
--

John W. Vinson [MVP]