Searching Form and Subform

[George]

Dear friends,

I have a form frmCases (from tblCases) and a subform frmMessagesSub (from
tblMessages). The two tables are linked (one to many) using the CaseNummber.

A field in the tblCases is called CaseCode, also in the related tblMessages
the same field (CaseCode) is found.

I need in my Form to enter a CaseCode and then to search the MainForm and
also the subForm for that CaseCode?

Any help will be highly appreciated.

Thanking in advance

GeorgeCY

 

[Beetle]

You could, for example, put an unbound text box (we'll call it
txtCaseSearch) in, say, the header of your form. In the After Update
event of this text box you would use code like;

Private Sub txtCaseSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "CaseCode = """ & Me.txtCaseSearch & """"
If .NoMatch Then
MsgBox "No matching Case Code found"
Else
Me.BookMark = .BookMark
End If
End With

End Sub

The above assumes that your Case Code is a text field. If it is a numeric
field then the first line in the With block should be;

.FindFirst "CaseCode = " & Me.txtCaseSearch

If you have the appropriate Master/Child link for your Form/Subform, then
the subform will automatically display any messages associated with the
selected case.

 

[George]

Thanks a lot for your reply,

I tried it and it seems that works fine search the main form only (I have a
tabbed form - the first tab is from the main table and the second - using a
subform from tblMessages).

If I type abc in the unbound you sugested I need to search in the main form
if the instance appears or even in the subform.

Ο χÏήστης "Beetle" έγγÏαψε:

 

[Beetle]

Maybe I'm not following. I thought your main form is to display Cases
and your subform is to display any messages related to a particular
case. I also assumed that CaseCode was the field that defines the
relationship between the tables.

Is that not correct?

 

[George]

Thanks again,

My main form displays Cases and has also a field called CaseCode (is not the
primary key, the primary key is is the CaseNumber).

In the subform there is also another field called CaseCode which is not the
Primary key.

I know that this is not a well normariled database but I have to use it as
it is.

Ο χÏήστης "Beetle" έγγÏαψε: