Somebody Please Help Me

When "everything works perfectly", instead of making the control invisible,
make its dimensions 1x1 pixel. Problem solved. Everything works perfectly.

UpRider

"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.

On Thu, 5 Mar 2009 22:29:36 -0700, "UpRider" wrote:

When "everything works perfectly", instead of making the control invisible,
make its dimensions 1x1 pixel. Problem solved. Everything works perfectly.


Don't forget to set its Tabstop property to No as well.
--

John W. Vinson [MVP]

You said something about openning a dialog form. I bet your dialog form has
no record source that's why it failed. You can't bind a recordsetclone if you
have nothing in the form.

You might want is to refer to a form that has the recordsetclone.

Set frm as Form

Set frm = Forms!YourFromName

Set rst = frm.Recordsetclone

AccessVandal already wrote what I would have suggested. I generally search
using a combo box in the header, so I completely forgot that you are
searching from a dialog form. Another point is that you may find you need
to keep the dialog form open but hidden. In the main form's Close event you
could have:
DoCmd.Close acForm, "DialogFormName"

The Me prefix references the current form. It is not transferred when that
form closes. In any case, while the dialog form is open Me refers to it, so
Me.lstFirm (or Me!lstFirm) would refer to lstFrm in the dialog form. To
refer to another object, as suggested:

Dim frm as Form
Dim rst As DAO.Recordset
Dim lngSelect As Long

Set frm = Forms!YourFromName

'Check to see if no selection was made.
If IsNull(frm.lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
Exit Sub
End If

'Store the selection (firm ID number) in a variable.
lngSelect = frm.lstFirm

'Close the dialog form to switch back to the main form.
Me.Visible = False

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

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

Rather than opening another form you could maybe have the list box in the
header, and make it visible when you click the command button. Or you could
place the list box in the form header and make that visible.

Me.FormHeader.Visible = True

or to toggle it:

Me.FormHeader.Visible = Not Me.FormHeader.Visible

In any case, you will probably want to hide it in the form's Current event.

Just another option to consider. Remember that when referencing one form or
report from code in another form you need to use the full Forms!FormName
reference.


"oldblindpew" wrote in message
...
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]

Hallelujah! It's finally working! Thank you, Thank you, Thank you! There
is no pleasure so keen as the sudden cessation of great pain. No matter how
rank the piece of junk you're working on, once you get it fixed and running
you start to develop that warm glow. Now I can join the other Stepford Wives
who sing the praises of Access...Not. ;-)

Notice the solution involves hard-coding a form name into my procedure,
which means this procedure is now married to that one form. I had been using
the same procedure to find firms for several different forms. I now need to
either a) make duplicate copies of this procedure, each differing only with
regard to the form name, or b) find a way to pass the form name to the
procedure, or c) avoid opening a dialog form by putting list boxes in all my
forms, so that "Me" can be used to create recordset clones for differnt forms.

Thanks again for bearing with me through this.

"BruceM" wrote:

AccessVandal already wrote what I would have suggested. I generally search
using a combo box in the header, so I completely forgot that you are
searching from a dialog form. Another point is that you may find you need
to keep the dialog form open but hidden. In the main form's Close event you
could have:
DoCmd.Close acForm, "DialogFormName"

The Me prefix references the current form. It is not transferred when that
form closes. In any case, while the dialog form is open Me refers to it, so
Me.lstFirm (or Me!lstFirm) would refer to lstFrm in the dialog form. To
refer to another object, as suggested:

Dim frm as Form
Dim rst As DAO.Recordset
Dim lngSelect As Long

Set frm = Forms!YourFromName

'Check to see if no selection was made.
If IsNull(frm.lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
Exit Sub
End If

'Store the selection (firm ID number) in a variable.
lngSelect = frm.lstFirm

'Close the dialog form to switch back to the main form.
Me.Visible = False

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

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

Rather than opening another form you could maybe have the list box in the
header, and make it visible when you click the command button. Or you could
place the list box in the form header and make that visible.

Me.FormHeader.Visible = True

or to toggle it:

Me.FormHeader.Visible = Not Me.FormHeader.Visible

In any case, you will probably want to hide it in the form's Current event.

Just another option to consider. Remember that when referencing one form or
report from code in another form you need to use the full Forms!FormName
reference.


"oldblindpew" wrote in message
...
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]

The first argument for OpenForm is the form name. After that there are
several others, including OpenArgs. OpenArgs is used to pass a value to
another form.

Dim strFormName as String
strFormName = Me.Form.Name

DoCmd.OpenForm "frmSearch", , , , , , strFormName

or to simplify if this is the only argument after the form name:

DoCmd.OpenForm "frmSearch", OpenArgs:=strFormName

Note that this code is exactly as it appears here. Do not substitute for
Form.Name or anything like that. The idea is that you are passing the name
of the calling form (the one from which you open the search form) to the
searchform. As long as the search form is open you can refer to its
OpenArgs value:

Instead of:

Set frm = Forms!YourFromName

try:

Set frm = Forms(strFormName)

The code up to that point:

Dim frm as Form
Dim rst As DAO.Recordset
Dim lngSelect As Long
Dim strArgs as String

strArgs = Me.OpenArgs

Set frm = Forms(strArgs)

The rest of the code is as before.

As a caveat, I will not have a chance to test this in your exact situation.
It should work, but I can't say I remember every detail, or that I have
explained it clearly. If it does not work you may do best to start another
thread, showing all of the relevant code you have so far. It would be
better to do it that way than to reference this thread. I've been taking
part in this thread and am starting to get a bit disoriented myself.

Note that frmSearch will retain the OpenArgs value as long as it (frmSearch)
is open. If you are using frmSearch from another form (Form2) you will need
to close frmSearch so you can pass the OpenArgs value from Form2 to
frmSearch; otherwise frmSeqrch will retain the original OpenArgs value.

"oldblindpew" wrote in message
news
Hallelujah! It's finally working! Thank you, Thank you, Thank you!
There
is no pleasure so keen as the sudden cessation of great pain. No matter
how
rank the piece of junk you're working on, once you get it fixed and
running
you start to develop that warm glow. Now I can join the other Stepford
Wives
who sing the praises of Access...Not. ;-)

Notice the solution involves hard-coding a form name into my procedure,
which means this procedure is now married to that one form. I had been
using
the same procedure to find firms for several different forms. I now need
to
either a) make duplicate copies of this procedure, each differing only
with
regard to the form name, or b) find a way to pass the form name to the
procedure, or c) avoid opening a dialog form by putting list boxes in all
my
forms, so that "Me" can be used to create recordset clones for differnt
forms.

Thanks again for bearing with me through this.

"BruceM" wrote:

AccessVandal already wrote what I would have suggested. I generally
search
using a combo box in the header, so I completely forgot that you are
searching from a dialog form. Another point is that you may find you
need
to keep the dialog form open but hidden. In the main form's Close event
you
could have:
DoCmd.Close acForm, "DialogFormName"

The Me prefix references the current form. It is not transferred when
that
form closes. In any case, while the dialog form is open Me refers to it,
so
Me.lstFirm (or Me!lstFirm) would refer to lstFrm in the dialog form. To
refer to another object, as suggested:

Dim frm as Form
Dim rst As DAO.Recordset
Dim lngSelect As Long

Set frm = Forms!YourFromName

'Check to see if no selection was made.
If IsNull(frm.lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
Exit Sub
End If

'Store the selection (firm ID number) in a variable.
lngSelect = frm.lstFirm

'Close the dialog form to switch back to the main form.
Me.Visible = False

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

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

Rather than opening another form you could maybe have the list box in the
header, and make it visible when you click the command button. Or you
could
place the list box in the form header and make that visible.

Me.FormHeader.Visible = True

or to toggle it:

Me.FormHeader.Visible = Not Me.FormHeader.Visible

In any case, you will probably want to hide it in the form's Current
event.

Just another option to consider. Remember that when referencing one form
or
report from code in another form you need to use the full Forms!FormName
reference.


"oldblindpew" wrote in message
...
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]