Okay, Juile, that approach should work, so let's pin down what's going on.
1. You have this code in the DblClick event procedure of the UnitID control
in the subform. At the top of the module this code is in, make sure you have
the line:
Option Explicit
This will cause Access to show you any misspelled or undeclared variables.
2. From the code window, choose Compile on the Debug menu. Fix any problems,
and repeat until it compiles okay. (No complaints, but Compile dims on the
menu.)
3. Still in the code window, under:
Tools | Options | General
set Error Trapping to:
Break on Unhandled Errors
This ensures Access notifies you if things go wrong.
4. Double-check that the UnitData form is not already open - not even in
design view.
5. Use the code suggested previously.
After running it, open the Immediate Window (Ctrl+G), and look at the output
there to identify if the criteria looks correct, and if the form's filter is
correct.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
JulieL said:
I'm not sure I do any more!
OK. I have a form called PortfolioData. This form has a subform which is
just a list of units in the portfolio, containing their unique
identifier -
UnitID - and the unit address. Some portfolios have only one unit, but
some
might have up to 50.
I want to open another form called UnitData at the correct record by
double-clicking on the UnitID, in the subform. What happens when I try
this
is that the form UnitData opens perfectly, but only at the first record in
the record set. If for example I have UnitIDs ranging from 001/001 to
001/007, if I were to double click on, say, 001/005, the UnitData form
opens
but only at 001/001. If I were to try the same thing for a different
portfolio, say one beginning 002/001 UnitData still opens at the record
for
001/001.
I can open the form but not in the right place and I cannot see why not.
--
Cheers
Julie
Allen Browne said:
Then I don't understand your question.
If the form you are trying to open does not have a UnitID field, and it
does
not have a subform containing a UnitID field, I don't understand how you
can
open the form to the matching UnitID.
No the Unitid is in the subform of a completely different form.
--
Cheers
Julie
:
If the UnitID is in a subform of the target form (not in the target
form
itself), you will need to DLookup() the matching key value in the
target
form, and then use that vaue in your stLinkCritiera.
For example, if the UnitData form is bound to the Product table (with
ProductID primary key), and its subform is bound to the Unit table
(with
a
ProductID foreign key), you would need to get the ProductID fro the
particular unit:
Dim varProductID As Variant
varProductID = DLookup("UnitID", "Unit", "UnitID = " & [UnitID])
Then open the form to that product (since it doesn't have the unit.)
Thanks once again for your efforts. Sorry I didn't make myself clear
about
the form/subform. The subform is on a form called portfolio data.
Each
portfolio can have multiple units, so the subform simply lists the
units.
The
idea is that if information about individual units is needed, by
double-clicking it on the subform the unit data form will pop up at
the
correct unit.
I've just tried your code with the same result. It runs perfectly
happily
and opens the form unit data, but still at the first record. I am
just
about
at my wits' end!
--
Cheers
Julie
:
UnitID is in a *subform* on the form you are opening?
If so, that approach won't work.
You cannot use a WhereCondition for OpenForm if the field you are
filtering
on is not even in the form's RecordSource.
If the UnitData form does have UnitID in its fields (not in its
subform's
fields), the code should work. Debug.Print might help you pin down
what's
going on, e.g.:
Dim stDocName As String
Dim stLinkCriteria As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.[Unit ID]) Then
MsgBox "Choose a unit."
Else
stDocName = "UnitData"
stLinkCriteria = "[UnitID] = """ & Me![Unit ID] & """"
Debug.Print strLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
Debug.Print Forms(stDocName).Filter
End If
Thanks for this Allen. However, the UnitID is a text field and I
have
tried
including the line to save the record before opening the form,
but
to
no
avail. Although the correct form opens, it still simply opens at
the
very
first record. UnitID field is on a subform, which is simply a
list
of
the
units available.
--
Cheers
Julie
:
If UnitID is a Number field (not a Text field), drop the extra
quotes:
stLinkCriteria = "[UnitID]=" & Me![Unit ID]
If the form is already open, the criteria won't work.
You may need to save the current record before you open the
other
form:
If Me.Dirty Then Me.Dirty = False
Hi
This is the first time I've done this so I hope I've included
everything
needed.
I'm using Access 2002. I want to open a form (UnitData) at a
specific
record by double clicking on its name (UnitID). It is opening
the
required
form, but at the first record, which is not necessarily the
one I
want
to
find. This is the code I am using. Can anybody tell me what is
wrong?
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "UnitData"
stLinkCriteria = "[UnitID]=" & "'" & Me![Unit ID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
