Find feature

[Tara]

I have a form (frmClientInfo) that displays basic client information...name,
address, etc. On the form, I used the wizard to create a search button so
that the user could enter a client's last name and easily find the record
they wanted. Now, I'm being asked to add a second command button that will
search through the Client's children and then pull up the correct Client
information. In this way, if a child's name was known but not the parent's
name, the correct parent (Client) information would be retrieved. The
child's information is held in a separate table (tblChildInfo) and is linked
to the Client table via a second table (tblCaseInfo) with an ID Number. Can
a search like this be done from the frmClientInfo form? How would I code
this?

Thanks!

 

[Ken Snell MVP]

You need to give us information about how your table(s) is/are structured.
Do you store parent and child information in the same table? in separate
tables? If in separate tables, how are the tables related?

 

[Tara]

Child and parent info are in different tables - tblChildInfo and
tblClientInfo. They are linked via a 3rd table (tblCaseInfo) which contains
both the ClientID number from tblClient and a CaseID number that is also in
tblChildInfo.

 

[Mike Painter]

I have a form (frmClientInfo) that displays basic client
information...name, address, etc. On the form, I used the wizard to
create a search button so that the user could enter a client's last
name and easily find the record they wanted. Now, I'm being asked to
add a second command button that will search through the Client's
children and then pull up the correct Client information. In this
way, if a child's name was known but not the parent's name, the
correct parent (Client) information would be retrieved. The child's
information is held in a separate table (tblChildInfo) and is linked
to the Client table via a second table (tblCaseInfo) with an ID
Number. Can a search like this be done from the frmClientInfo form?
How would I code this?

Thanks!

You may want to look at self referencing tables
http://www.databasedev.co.uk/self-join_query.html

Adding the ClientID and ChildID to the caseInfo should give you a many to
many relationship (it will if I understand you correctly).
http://www.databasedev.co.uk/many_to_many_example.html

 

[Ken Snell MVP]

So we'd need to search the tblChildInfo table to find a matching name, look
at the CaseID value for that record, then search the tblClientInfo table for
that CaseID value, and look at the Client info for that record.

Tell us the SQL statement for the form's Record Source query (if it's a
table name or a query name, tell us the fields that are in the table or
query). Also, is the form a Single Form view or a Continuous Forms view.

We'll need that info to provide a more specific suggestion for the VBA code
that will do what you seek.

 

[Tara]

Thanks for getting back to me Ken. Sounds like you understand exactly what
I'm looking for!

Here's the Record Source for tblClient.

SELECT tblClient.* FROM tblClient WHERE (((tblClient.HOME)=-1 Or
(tblClient.NFSF)=-1)) ORDER BY [ClientLast];

The fields in tblClient are: ClientID, ClientFirst, ClientLast,
ClientAddress, ClientCity, ClientZip, ClientPhone, ClientDOB,
ClientEthnicity, HOME, NFSF.

 

[Ken Snell MVP]

Here is generic code that you can run in the event procedure for the Click
event of a command button (replace the generic names of controls with the
real names):

Private Sub NameOfButton_Click()
Dim lngClientID As Long, lngCaseID As Long
Dim strChildName As String

strChildName = Nz(Me.NameOfTextBox.Value, "")

If IsNull(DLookup("CaseID", "tblChildInfo", _
"ChildNameFieldName Like ''*" & strChildInfo _
* "*'")) = False Then
lngCaseID = DLookup("CaseID", "tblChildInfo", _
"ChildNameFieldName Like ''*" & strChildInfo _
* "*'")
lngClientID = DLookup("ClientID", "tblCaseInfo", _
"CaseID = " & lngCaseID)
With Me.RecordsetClone
.MoveFirst
.FindFirst "ClientID = " & lngClientID
If .NoMatch = False Then
Me.Bookmark = .Bookmark
Else
MsgBox "Client not found.", vbOK, "Not Found"
End If
End With
Else
MsgBox "Child name not found.", vbOK, "Not Found"
End If

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks for getting back to me Ken. Sounds like you understand exactly
what
I'm looking for!

Here's the Record Source for tblClient.

SELECT tblClient.* FROM tblClient WHERE (((tblClient.HOME)=-1 Or
(tblClient.NFSF)=-1)) ORDER BY [ClientLast];

The fields in tblClient are: ClientID, ClientFirst, ClientLast,
ClientAddress, ClientCity, ClientZip, ClientPhone, ClientDOB,
ClientEthnicity, HOME, NFSF.

So we'd need to search the tblChildInfo table to find a matching name,
look
at the CaseID value for that record, then search the tblClientInfo table
for
that CaseID value, and look at the Client info for that record.

Tell us the SQL statement for the form's Record Source query (if it's a
table name or a query name, tell us the fields that are in the table or
query). Also, is the form a Single Form view or a Continuous Forms view.

We'll need that info to provide a more specific suggestion for the VBA
code
that will do what you seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Tara]

Thanks so much for the help Ken!

Here is generic code that you can run in the event procedure for the Click
event of a command button (replace the generic names of controls with the
real names):

Private Sub NameOfButton_Click()
Dim lngClientID As Long, lngCaseID As Long
Dim strChildName As String

strChildName = Nz(Me.NameOfTextBox.Value, "")

If IsNull(DLookup("CaseID", "tblChildInfo", _
"ChildNameFieldName Like ''*" & strChildInfo _
* "*'")) = False Then
lngCaseID = DLookup("CaseID", "tblChildInfo", _
"ChildNameFieldName Like ''*" & strChildInfo _
* "*'")
lngClientID = DLookup("ClientID", "tblCaseInfo", _
"CaseID = " & lngCaseID)
With Me.RecordsetClone
.MoveFirst
.FindFirst "ClientID = " & lngClientID
If .NoMatch = False Then
Me.Bookmark = .Bookmark
Else
MsgBox "Client not found.", vbOK, "Not Found"
End If
End With
Else
MsgBox "Child name not found.", vbOK, "Not Found"
End If

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks for getting back to me Ken. Sounds like you understand exactly
what
I'm looking for!

Here's the Record Source for tblClient.

SELECT tblClient.* FROM tblClient WHERE (((tblClient.HOME)=-1 Or
(tblClient.NFSF)=-1)) ORDER BY [ClientLast];

The fields in tblClient are: ClientID, ClientFirst, ClientLast,
ClientAddress, ClientCity, ClientZip, ClientPhone, ClientDOB,
ClientEthnicity, HOME, NFSF.

So we'd need to search the tblChildInfo table to find a matching name,
look
at the CaseID value for that record, then search the tblClientInfo table
for
that CaseID value, and look at the Client info for that record.

Tell us the SQL statement for the form's Record Source query (if it's a
table name or a query name, tell us the fields that are in the table or
query). Also, is the form a Single Form view or a Continuous Forms view.

We'll need that info to provide a more specific suggestion for the VBA
code
that will do what you seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Child and parent info are in different tables - tblChildInfo and
tblClientInfo. They are linked via a 3rd table (tblCaseInfo) which
contains
both the ClientID number from tblClient and a CaseID number that is
also
in
tblChildInfo.

:

You need to give us information about how your table(s) is/are
structured.
Do you store parent and child information in the same table? in
separate
tables? If in separate tables, how are the tables related?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a form (frmClientInfo) that displays basic client
information...name,
address, etc. On the form, I used the wizard to create a search
button
so
that the user could enter a client's last name and easily find the
record
they wanted. Now, I'm being asked to add a second command button
that
will
search through the Client's children and then pull up the correct
Client
information. In this way, if a child's name was known but not the
parent's
name, the correct parent (Client) information would be retrieved.
The
child's information is held in a separate table (tblChildInfo) and
is
linked
to the Client table via a second table (tblCaseInfo) with an ID
Number.
Can
a search like this be done from the frmClientInfo form? How would I
code
this?

Thanks!