Using a Combo Box to find a Record

[merry_fay]

Hi Allen,

I've followed your instructions for my database with the criteria as such:
Column Count 1
Column Widths 1 (I couldn't see the values when it was 0!)
Name CurrYear
Row Source SELECT [3- Unit Cost Override Form Query].Year
FROM [3- Unit Cost Override Form Query]
GROUP BY [3- Unit Cost Override Form Query].Year
ORDER BY [3- Unit Cost Override Form Query].Year;

& set the After Update [Event Procedure] to:

Private Sub CurrYear_AfterUpdate()
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.CurrYear) Then
Me.FilterOn = False
Else
Me.Filter = "Year= " & Me.CurrYear.Value
Me.FilterOn = True
End If
Me.CurrYear = Null

End Sub

The Record source for the form is the query [3- Unit Cost Override Form Query]

When I open the form, the combo box is blank & all the records are showing,
but when I drop down on the combo box & select a year, it comes up with the
error message:

Run time error '2001':
You cancelled the previous operation

When I click on debug, it highlights the line:
Me.Filter = "Year= " & Me.CurrYear.Value

Do you have any suggestions why it's doing this?

Thanks

1. Create a query that uses the tblRecordings and tblTracks tables. Output
the fields you need for your form, including the ArtistID. Save the query.

2. Create a continuous view form, so it shows one record per row.

3. In the Form Header section of this form, add an unbound combo box where
the user can choose the artist they are interested in. Give it these
properties:
Column Count 2
Column Widths 0
Name cboFindArtist
Row Source SELECT ArtistID, Artist_Name
FROM tblArtsts
ORDER BY Artist_Name;
After Update [Event Procedure]

4. Click the Built button (...) beside the After Update property.
Access opens the code window.
Set up the code as follows:

Private Sub cboFindArtist_AfterUpdate
If Me.Dirty Then Me.dirty = False
If IsNull(Me.cboFindArtist) Then
Me.FilterOn = False
Else
Me.Filter = "ArtistID = " & Me.cboFindArtist
Me.FilterOn = True
End If
Me.cboFindArtist = Null
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have 3 tables namely:

TblArtists
ArtistID (PK)
Artist_Name

TblTracks
TrackID (PK)
Track_Name
ArtistID
RecordingID

TblRecordings
RecordingID (PK)
Title
Date
Number of Tracks

What i want to do is use a combo box to select an 'Artist' and disply all
'Records' associated with the artist. If the artist was in the 'Records'
table i'm sure it would be simple but because 'Artists' is in a seperate
table i cannot figure out how do make it work.

Could somebody please help me? Do i need to make a sepeate query? Shold
the combo box be bound or unbound?

Thanks