Code help please

[Anthonydb]

I am using a form and a subform, on the main form I have a cmdfind. I want the find button to search a table (Tape3) that is displayed as the subform. I am looking to search by multiple criteria but I can't even get the button and form to interact so I've deleted all code and I am looking to start from scratch. I initially want to search by the tbl field Serial Number. Also, each time the main form is opened, I want the subform to start off as blank. I tried SQL and I just wasn't doing something right. Here are the parameters:

The table is Tape3
The search criteria in the table is Serial Number
The subform should be totally independent of the main form.

Any assistance is highly appreciated.

 

[jaydwest]

A good way to do what you want to do is with filters. Set the Recordsource of the subform to be to full recordset (Tape3).

On the Current Event of the Parent Form enter the following code

Dim frmSubform as form

Set frmSubform = Me![<SubformControlName>].Form

frmSubform.Filter = "Serial Number = 'XXXXX'"
frmSubform.FilterOn = true

This will display a blank subform, assuming "XXXX" is not a valid subform. Also assumes Serial Number is text not numeric. There may be some flutter on the screen when you open the form because Access first wants to display all records, then the filter is applied. This is usually not to bad but if it bothers you, you can turn echo off while the form is loading.

On the Click Event of your Search Button enter the following code

frmSubform.Filter = "Serial Number = '" & Me![SearchField] & "'"
frmSubform.FilterOn = true

This will apply the filter to your subform and display the records (if any are found).

Try this and if you have any questions, let me know.

 

[Anthonydb]

I'm receiving a run time error 424 object required. This is the line that is highlighted:

frmSubform.Filter = "Serial Number = '" & Me![txtSearchResults] & "'"

 

[jaydwest]

Try this

frmSubform.Filter = "[Serial Number] = '" & Me![txtSearchResults] & "'"

Also are there ever any apostrophes in the txtSearchResults? If yes you will want to add Chr$(34) before and after the field, as shown below.

frmSubform.Filter = "Serial Number = " & Chr$(34) & Me![txtSearchResults] & Chr$(34)

 

[jaydwest]

Sorry, make the last line

frmSubform.Filter = "[Serial Number] = " & Chr$(34) & Me![txtSearchResults] & Chr$(34)

 

[Anthonydb]

No I'm getting the highlighting of the line with no error msg.

Here is the code that I have applied:
Keep in mind that 2 of those lines are commented out.

Private Sub cmdFind_Click()

frmSubform.Filter = "[Serial Number] = '" & Me![txtSearchResults] & "'"
'frmSubform.Filter = "[Serial Number] = " & Chr$(34) & Me![txtSearchResults] & Chr$(34)
'frmSubform.Filter = "Serial Number = '" & Me![txtSearchResults] & "'"
frmSubform.FilterOn = True


End Sub


Private Sub Form_Current()

Dim frmSubform As Form

Set frmSubform = Me![Tape_2].Form

frmSubform.Filter = "Serial Number = 'XXXXX'"
frmSubform.FilterOn = True

End Sub

 

[jaydwest]

Don't you just love Access. Sometimes a line that was highlighted while debugging doesn't get cleared. Try exiting the DBS and starting it again. If the line is still highlighted, click on [Reset] button or Run | Reset on the Menu Bar.

 

[Anthonydb]

You got me, that didn't help either. The highlighter went back to the same line. Also I'm getting an error saying that access doesn't recognize the expression (which is the subform) Tape 2.