Re: Somebody Please Help Me

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <oldblindpew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C0833D03-1B94-47C0-9C8E-0548EC9C3102@xxxxxxxxxxxxxxxx
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" <oldblindpew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:659A2BAD-A8DD-451F-A752-DB25488F17F8@xxxxxxxxxxxxxxxx
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
<oldblindpew@xxxxxxxxxxxxxxxxxxxxxxxxx> 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]





.

Relevant Pages

  • Re: Somebody Please Help Me
    ... In any case, while the dialog form is open Me refers to it, so Me.lstFirm would refer to lstFrm in the dialog form. ... Dim rst As DAO.Recordset ... 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. ... Single stepping confirms that Set rst = Me.RecordsetClone generates the ...
    (microsoft.public.access.forms)
  • RE: How to export data from a query to a .xlsx file.
    ... That is a lot of overhead for just dumping a query to a spreadsheet. ... You should reference a worksheet with the ... Dim XL as object ... set rst = new recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: storing SQL result into a VB variable?
    ... as I said earlier you can do this using either ADO or DAO. ... you'll need a reference to the Microsoft ActiveX Data Objects ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset("SELECT YourField FROM YourTable WHERE ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenRecordset - Error 3061
    ... I did indeed reference a control within a form and your advice below ... Set rst = dbsCurrent.OpenRecordset ... Dim rst As DAO.Recordset ... Dim mSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: storing SQL result into a VB variable?
    ... then the approach Brendan outlined is the only option. ... you'll need a reference to the Microsoft ActiveX Data Objects ... >> Dim rst As DAO.Recordset ... >> Set rst = db.OpenRecordset("SELECT YourField FROM YourTable WHERE ...
    (microsoft.public.access.modulesdaovba)