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