Using a Combo Box to find a Record

  • Thread starter Thread starter adrian007uk
  • Start date Start date
A

adrian007uk

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
 
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 said:
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.


adrian007uk said:
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
Click to expand...
Click to expand...
 
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

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


adrian007uk said:
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 said:
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.


adrian007uk said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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

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


adrian007uk said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

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


adrian007uk said:
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 said:
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

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


adrian007uk said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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

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


adrian007uk said:
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 said:
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

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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 here: 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

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


adrian007uk said:
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 said:
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

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


adrian007uk said:
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

:

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

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


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

:

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.


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 here: 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

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


adrian007uk said:
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 said:
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

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


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

:

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

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


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

:

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.


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
Click to expand...
Click to expand...
Click to expand...
 
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

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


adrian007uk said:
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 said:
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 here: 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

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


adrian007uk said:
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.

:

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

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


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

:

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

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


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

:

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.


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
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen

I have checked the forms Data Entry properties and the FilterOnLoad
property. These were all correct. However i have managed to get the form to
work! I am not sure why and was wondering if you could explain it to me. It
may be something very simple that i have overlooked. Here goes. My tables
again (from left to right):

TblArtists
ArtistID (PK) (Autonumber)
Artist_Name

TblTracks
TrackID (PK) (Autonumber)
Track_Name
ArtistID (Number)
RecordingID (Number)

TblRecordings
RecordingID (PK) (Autonumber)
Title
Date
Number of Tracks

I made the query and added the fields i wanted. I played around with which
table fields such as ArtistID would come from(e.g., TblArtists or TblTracks).
As explained i encountered problems with the code you gave me, displaying
the records and not working once i saved the form.

I got the form to work when i added the TrackID from TblTracks. Is this the
reason the form now works? Due to adding this field every track on the
record is displayed as opposed to just each record but it works. I am unsure
why as TrackID is a primary key but is not directly linked to TblArtists or
TblRecords.

Is there any way on opening the form that the combo box is blank and no
records are shown? Is there also a way to make sure no data can be added to
the combo box or to any of the fields? I just want this form to view records
not add, change or delete the.

Many thanks for the help so far.

Adrian

Allen Browne said:
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

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


adrian007uk said:
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 said:
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 here: 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

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


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.

:

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

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


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

:

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

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


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

:

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.


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
Click to expand...
Click to expand...
Click to expand...
 
To prevent the user changing the value in the controls on the form, set
their Locked property to Yes.

To load the form so that it initially displays no records, give it a filter
that no records match, e.g.:

Private Sub Form_Load()
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
 
Thanks for the help. Do you know how i would stop the form/combo box showing
duplicate entries?

Thanks

Adrian
 
Thanks for the help. Do you know how i would stop the form/combo box showing
duplicate entries?
Click to expand...

Base the Combo Box on a query with its Unique Values property set to Yes.
 
I have not had chance to try it out yet.

I think i have figured out why i was having so much trouble initially with
the form. In the record source of the form, it shows it is based on a query,
but at the end it has "WHERE ArtisitID=3". I removed this and everything
seems to work but it is showing duplicate enteries. I am hoping the advice
John and yourself has given me will sort this last problem out.

Adrian
 
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




Allen Browne said:
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.


adrian007uk said:
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
Click to expand...
Click to expand...
 
Back
Top