Using a Combo Box to find a Record

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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
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

Hi Allen

I have followed your instructions but i am getting a box that says enter
parmeter value on the form and then says to enter ArtistID. I can't get the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
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

When Access requests a parameter, it means that it can't find the name you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have followed your instructions but i am getting a box that says enter
parmeter value on the form and then says to enter ArtistID. I can't get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
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

Hi Allen

I have managed to 'sort of' get the combo box working. It shows the records
for only 1 artist. The remainder of the time the form stays blank (i.e., i
select an artist nothing happens, select another nothing, select one and it
shows the records associated with it but only that artist).

When i look at the query i can see all of the data but it is not showing up
on the form! Also i have some random numbers in the combo box as an option
to select! The numbers are three 1's and a 2 then the artists shown in
alphabetical order.

Adrian

"Allen Browne" wrote:

When Access requests a parameter, it means that it can't find the name you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have followed your instructions but i am getting a box that says enter
parmeter value on the form and then says to enter ArtistID. I can't get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
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

The symptoms you describe suggest that there are multiple issues here.

If you have the RowSource correct, then you must have an artist in your
table with the name 111 or something.

It also sounds like you have an issue here with data types. Examine the
tables. If the ArtistID in the artist table is any kind of number (including
AutoNumber), then the matching field for AritstID in the other table must
also be a number.

Make sure you have created a relationship between these tables (using the
Relationships window), *and* that you checked the box for "Relational
Integrity." If you try to check the RI box and there are errors, sort out
those issues first: it will help to get this issue solved.

Also, check that the combo you are using to filter the form is *unbound*
(nothing in its Control Source property.) And set its Format property to:
General Number
so Access understands its data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have managed to 'sort of' get the combo box working. It shows the
records
for only 1 artist. The remainder of the time the form stays blank (i.e.,
i
select an artist nothing happens, select another nothing, select one and
it
shows the records associated with it but only that artist).

When i look at the query i can see all of the data but it is not showing
up
on the form! Also i have some random numbers in the combo box as an
option
to select! The numbers are three 1's and a 2 then the artists shown in
alphabetical order.

Adrian

"Allen Browne" wrote:

When Access requests a parameter, it means that it can't find the name
you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have followed your instructions but i am getting a box that says
enter
parmeter value on the form and then says to enter ArtistID. I can't
get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
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

Hi Allen

Thanks for all your help so far. I have gone through your list. The random
numbers were in the artists table for some reason so i managed to get rid of
them.

I examined the tables and all 'auto numbers' matched up with 'numbers' in
the corresponding tables. There was one difference where i had RecordID in
one table and RecordingID in another. I have now corrected this.

I had all the relationships correct and had ticked for 'Relational
Integrity' (i have all the boxes ticked, coulkd this be an issue?).

The combo box is unbound. I have tried creating it from the wizard and from
scratch but i am still having problems. The format of the combo box is set
to general number Since i corrected the 'RecordID/RecordingID' issue i am
now having a problem with:

Me.Filter = "ArtistID = " & Me.cboFindArtist

and sometimes:

Me.FilterOn = True

Other issues i should mention is for the 'artists' field in the tracks table
i have made a 'not in list' combo box to add artists when required. Could
this be the problem? I am using access 2007.

"Allen Browne" wrote:

The symptoms you describe suggest that there are multiple issues here.

If you have the RowSource correct, then you must have an artist in your
table with the name 111 or something.

It also sounds like you have an issue here with data types. Examine the
tables. If the ArtistID in the artist table is any kind of number (including
AutoNumber), then the matching field for AritstID in the other table must
also be a number.

Make sure you have created a relationship between these tables (using the
Relationships window), *and* that you checked the box for "Relational
Integrity." If you try to check the RI box and there are errors, sort out
those issues first: it will help to get this issue solved.

Also, check that the combo you are using to filter the form is *unbound*
(nothing in its Control Source property.) And set its Format property to:
General Number
so Access understands its data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have managed to 'sort of' get the combo box working. It shows the
records
for only 1 artist. The remainder of the time the form stays blank (i.e.,
i
select an artist nothing happens, select another nothing, select one and
it
shows the records associated with it but only that artist).

When i look at the query i can see all of the data but it is not showing
up
on the form! Also i have some random numbers in the combo box as an
option
to select! The numbers are three 1's and a 2 then the artists shown in
alphabetical order.

Adrian

"Allen Browne" wrote:

When Access requests a parameter, it means that it can't find the name
you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have followed your instructions but i am getting a box that says
enter
parmeter value on the form and then says to enter ArtistID. I can't
get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
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

So, the primary keys are autonumbers, and you have the relationships defined
with Referential integrity. The cascading boxes you checked in relationships
won't cause the problem he cascading updates are meaningless (since the
autonumbers never update); cascading deletes may be dangerous (i.e. if you
delete an artist or record, it automatically deletes all their tracks too.)

It should work as suggested. Are you still getting the "parameter error"
message? If it's just that you are not getting the other records then
something else is wrong. For example, if you have Martin Buble in tblArtists
twice, you will have 2 different autonumbers for him, and if you pick the
wrong one, you won't get the matching tracks shown.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
news
Hi Allen

Thanks for all your help so far. I have gone through your list. The
random
numbers were in the artists table for some reason so i managed to get rid
of
them.

I examined the tables and all 'auto numbers' matched up with 'numbers' in
the corresponding tables. There was one difference where i had RecordID
in
one table and RecordingID in another. I have now corrected this.

I had all the relationships correct and had ticked for 'Relational
Integrity' (i have all the boxes ticked, coulkd this be an issue?).

The combo box is unbound. I have tried creating it from the wizard and
from
scratch but i am still having problems. The format of the combo box is
set
to general number Since i corrected the 'RecordID/RecordingID' issue i am
now having a problem with:

Me.Filter = "ArtistID = " & Me.cboFindArtist

and sometimes:

Me.FilterOn = True

Other issues i should mention is for the 'artists' field in the tracks
table
i have made a 'not in list' combo box to add artists when required. Could
this be the problem? I am using access 2007.

"Allen Browne" wrote:

The symptoms you describe suggest that there are multiple issues here.

If you have the RowSource correct, then you must have an artist in your
table with the name 111 or something.

It also sounds like you have an issue here with data types. Examine the
tables. If the ArtistID in the artist table is any kind of number
(including
AutoNumber), then the matching field for AritstID in the other table must
also be a number.

Make sure you have created a relationship between these tables (using the
Relationships window), *and* that you checked the box for "Relational
Integrity." If you try to check the RI box and there are errors, sort out
those issues first: it will help to get this issue solved.

Also, check that the combo you are using to filter the form is *unbound*
(nothing in its Control Source property.) And set its Format property to:
General Number
so Access understands its data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have managed to 'sort of' get the combo box working. It shows the
records
for only 1 artist. The remainder of the time the form stays blank
(i.e.,
i
select an artist nothing happens, select another nothing, select one
and
it
shows the records associated with it but only that artist).

When i look at the query i can see all of the data but it is not
showing
up
on the form! Also i have some random numbers in the combo box as an
option
to select! The numbers are three 1's and a 2 then the artists shown in
alphabetical order.

Adrian

"Allen Browne" wrote:

When Access requests a parameter, it means that it can't find the name
you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have followed your instructions but i am getting a box that says
enter
parmeter value on the form and then says to enter ArtistID. I can't
get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in
message
...
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

Hi Allen

This just gets stranger and stranger. I have managed to get the combo
box/form working. However, once i save the form and go back to it, it does
not work. I can select an artist but no records show up! When i first
create it everything works fine.

Adrian

"Allen Browne" wrote:

So, the primary keys are autonumbers, and you have the relationships defined
with Referential integrity. The cascading boxes you checked in relationships
won't cause the problem he cascading updates are meaningless (since the
autonumbers never update); cascading deletes may be dangerous (i.e. if you
delete an artist or record, it automatically deletes all their tracks too.)

It should work as suggested. Are you still getting the "parameter error"
message? If it's just that you are not getting the other records then
something else is wrong. For example, if you have Martin Buble in tblArtists
twice, you will have 2 different autonumbers for him, and if you pick the
wrong one, you won't get the matching tracks shown.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
news
Hi Allen

Thanks for all your help so far. I have gone through your list. The
random
numbers were in the artists table for some reason so i managed to get rid
of
them.

I examined the tables and all 'auto numbers' matched up with 'numbers' in
the corresponding tables. There was one difference where i had RecordID
in
one table and RecordingID in another. I have now corrected this.

I had all the relationships correct and had ticked for 'Relational
Integrity' (i have all the boxes ticked, coulkd this be an issue?).

The combo box is unbound. I have tried creating it from the wizard and
from
scratch but i am still having problems. The format of the combo box is
set
to general number Since i corrected the 'RecordID/RecordingID' issue i am
now having a problem with:

Me.Filter = "ArtistID = " & Me.cboFindArtist

and sometimes:

Me.FilterOn = True

Other issues i should mention is for the 'artists' field in the tracks
table
i have made a 'not in list' combo box to add artists when required. Could
this be the problem? I am using access 2007.

"Allen Browne" wrote:

The symptoms you describe suggest that there are multiple issues here.

If you have the RowSource correct, then you must have an artist in your
table with the name 111 or something.

It also sounds like you have an issue here with data types. Examine the
tables. If the ArtistID in the artist table is any kind of number
(including
AutoNumber), then the matching field for AritstID in the other table must
also be a number.

Make sure you have created a relationship between these tables (using the
Relationships window), *and* that you checked the box for "Relational
Integrity." If you try to check the RI box and there are errors, sort out
those issues first: it will help to get this issue solved.

Also, check that the combo you are using to filter the form is *unbound*
(nothing in its Control Source property.) And set its Format property to:
General Number
so Access understands its data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have managed to 'sort of' get the combo box working. It shows the
records
for only 1 artist. The remainder of the time the form stays blank
(i.e.,
i
select an artist nothing happens, select another nothing, select one
and
it
shows the records associated with it but only that artist).

When i look at the query i can see all of the data but it is not
showing
up
on the form! Also i have some random numbers in the combo box as an
option
to select! The numbers are three 1's and a 2 then the artists shown in
alphabetical order.

Adrian

"Allen Browne" wrote:

When Access requests a parameter, it means that it can't find the name
you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have followed your instructions but i am getting a box that says
enter
parmeter value on the form and then says to enter ArtistID. I can't
get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in
message
...
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

Something else is going on. Perhaps you have the form's Data Entry property
set to Yes, so it doesn't show existing records. Or perhaps FilterOnLoad
property is Yes so it runs a prior query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

This just gets stranger and stranger. I have managed to get the combo
box/form working. However, once i save the form and go back to it, it
does
not work. I can select an artist but no records show up! When i first
create it everything works fine.

Adrian

"Allen Browne" wrote:

So, the primary keys are autonumbers, and you have the relationships
defined
with Referential integrity. The cascading boxes you checked in
relationships
won't cause the problem he cascading updates are meaningless (since
the
autonumbers never update); cascading deletes may be dangerous (i.e. if
you
delete an artist or record, it automatically deletes all their tracks
too.)

It should work as suggested. Are you still getting the "parameter error"
message? If it's just that you are not getting the other records then
something else is wrong. For example, if you have Martin Buble in
tblArtists
twice, you will have 2 different autonumbers for him, and if you pick the
wrong one, you won't get the matching tracks shown.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
news
Hi Allen

Thanks for all your help so far. I have gone through your list. The
random
numbers were in the artists table for some reason so i managed to get
rid
of
them.

I examined the tables and all 'auto numbers' matched up with 'numbers'
in
the corresponding tables. There was one difference where i had
RecordID
in
one table and RecordingID in another. I have now corrected this.

I had all the relationships correct and had ticked for 'Relational
Integrity' (i have all the boxes ticked, coulkd this be an issue?).

The combo box is unbound. I have tried creating it from the wizard and
from
scratch but i am still having problems. The format of the combo box is
set
to general number Since i corrected the 'RecordID/RecordingID' issue i
am
now having a problem with:

Me.Filter = "ArtistID = " & Me.cboFindArtist

and sometimes:

Me.FilterOn = True

Other issues i should mention is for the 'artists' field in the tracks
table
i have made a 'not in list' combo box to add artists when required.
Could
this be the problem? I am using access 2007.

"Allen Browne" wrote:

The symptoms you describe suggest that there are multiple issues here.

If you have the RowSource correct, then you must have an artist in
your
table with the name 111 or something.

It also sounds like you have an issue here with data types. Examine
the
tables. If the ArtistID in the artist table is any kind of number
(including
AutoNumber), then the matching field for AritstID in the other table
must
also be a number.

Make sure you have created a relationship between these tables (using
the
Relationships window), *and* that you checked the box for "Relational
Integrity." If you try to check the RI box and there are errors, sort
out
those issues first: it will help to get this issue solved.

Also, check that the combo you are using to filter the form is
*unbound*
(nothing in its Control Source property.) And set its Format property
to:
General Number
so Access understands its data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in message
...
Hi Allen

I have managed to 'sort of' get the combo box working. It shows the
records
for only 1 artist. The remainder of the time the form stays blank
(i.e.,
i
select an artist nothing happens, select another nothing, select one
and
it
shows the records associated with it but only that artist).

When i look at the query i can see all of the data but it is not
showing
up
on the form! Also i have some random numbers in the combo box as an
option
to select! The numbers are three 1's and a 2 then the artists shown
in
alphabetical order.

Adrian

"Allen Browne" wrote:

When Access requests a parameter, it means that it can't find the
name
you
are looking for.

Perhaps your form's source table/query doesn't have an ArtistID
field?
Perhaps you don't have a text box with that name on your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in
message
...
Hi Allen

I have followed your instructions but i am getting a box that
says
enter
parmeter value on the form and then says to enter ArtistID. I
can't
get
the
records to show up when i select an artist from the combo box.

Adrian

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"adrian007uk" wrote in
message
...
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