Getting to the chosen subform record

[senditontome]

Hi,
Creating databases was never in my job description but I've become
increasingly involved in this area and now really enjoy it. I've not
had any formal VBA training but picked up lots of excellent ideas and
tips from this NG.

I'm currently looking for a neater way of going directly to a
particlar record in a subform. This is for a database that keeps track
of clients and their correspondence (letters). Clients and letters are
in two linked tables.

The most commonly used form is the ( frmLetterDetail ) which has all
the clients details. Within this form is the subform (frmSubDetail)
which holds all that individual client's letters in a single form
layout.

The users of the database need frequently to go directly to the
details of a particular letter for a particular client. At present the
users enter the letter reference ID number in to a text box and an
action button initiates a search identifies the relevant client
(PersonID) and puts both these numbers in to a hidden form where they
can be picked up by the code.

The code is as follows:-

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmHidden"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Dim PersonID As Long
Dim TxtID As Long
Dim ID As Long

Dim frm As Form ' This selects the main form ie the form for
the client.

DoCmd.OpenForm "frmLetterDetai l", _
WhereCondition: ="PersonID = Forms!frmHidden .Form.TxtPerson ID"

' This now selects the line in the sub form which lists the
clients different letters

Set frm = Forms!FrmLetter Detail!FrmSubDe tail.Form
With frm.RecordsetCl one

.FindFirst "ID = " & Forms!frmHidden .TxtID
If .NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing

DoCmd.Close acForm, "frmHidden"

My query is....is there a way of doing this without using the hidden
form? Incidentally, the query behind the hidden form is:-
SELECT tblMain.ID, tblMain.PersonI D
FROM tblPersons RIGHT JOIN tblMain ON tblPersons.Pers onID =
tblMain.PersonI D
WHERE (((tblMain.ID)=[Forms]![FrmFront]![TxtGotoRefNo]));

Any advice would be much appreciated.

[senditontome]

I take it you have a main form bound to tblPersons (your clients), and a
subform bound to tblMain (your letters.)

Alan,

Thanks for getting back to me.

Yes, the above is 100% correct.
The unbound text box named TxtGotoRefNo is where the users can enter a
number that matches to the primary key of tblMain (ID. is actually on
another form called frnFront. This frmFront is the form that appears
when the DB opens and it is the main navigation/menu form where other
unbound text boxes permit different searches and various command
buttons bring up reports etc.

I have lots of free time on Easter Monday and will have a go with your
suggested code.

Neantime , many thanks.

Jim

[senditontome]

Allen, my apologies for the misspelling earlier.

I had a go with the air code. I had to add DAO to the reference
library and then it got part way through but the debug window came up
with error 2455 on the line:-

If Me.Dirty then Me.Dirty = False

I re-read your post and thought that this problem might be due to the
TxtGotoRefNo unbound text box being on frmFront which is unbound.

Would this be correct?

Jim

[Allen Browne]

If the message says that the form has no Dirty property, then it must be an
unbound form. If it is an unbound form, you can omit the line, because there
is no record to be saved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<se**********@g mail.comwrote in message
news:8a******** *************** ***********@i12 g2000prf.google groups.com...

Allen, my apologies for the misspelling earlier.

I had a go with the air code. I had to add DAO to the reference
library and then it got part way through but the debug window came up
with error 2455 on the line:-

If Me.Dirty then Me.Dirty = False

I re-read your post and thought that this problem might be due to the
TxtGotoRefNo unbound text box being on frmFront which is unbound.

Would this be correct?

Jim