Using a Combo Box to find a Record

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" wrote:

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

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

--
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 managed to get the form to work! ...

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.

adrian007uk wrote:
Hi Allen
Is there any way on opening the form that the combo box is blank and no
records are shown?

This question has two parts.
1. When you first open the form, the combo box is always blank. If it is not,
than you have a bound control. Change it to unbound. (your control
ControlSource is blank in the properties tab "Data")

2. If you want no record shown, just set the Form's property Data Entry = Yes.
However, you next question is.....

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.

If you want to prevent Add, Edit, and Delete on the form, set the Form's
properties
1. Data Entry = Yes
2. Allow Edits = No
3. Allow Deletions = No
4. Allow Additions = No

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200909/1

Edits: Data Entry should be No.

AccessVandal wrote:
1. Data Entry = No
2. Allow Edits = No
3. Allow Deletions = No
4. Allow Additions = No

snip...

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200909/1

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

Thanks

Adrian

"AccessVandal via AccessMonster.com" wrote:

Edits: Data Entry should be No.

AccessVandal wrote:
1. Data Entry = No
2. Allow Edits = No
3. Allow Deletions = No
4. Allow Additions = No

snip...

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200909/1

On Mon, 7 Sep 2009 15:25:01 -0700, adrian007uk
wrote:

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

Base the Combo Box on a query with its Unique Values property set to Yes.
--

John W. Vinson [MVP]

Did John's suggestion on the query properties work for you?

adrian007uk wrote:
Thanks for the help. Do you know how i would stop the form/combo box showing
duplicate entries?

Thanks

--
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

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

"AccessVandal via AccessMonster.com" wrote:

Did John's suggestion on the query properties work for you?

adrian007uk wrote:
Thanks for the help. Do you know how i would stop the form/combo box showing
duplicate entries?

Thanks

--
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

As to why you're having dulplicates entry, I don't know. Remember we can't
see your DB.

Perhaps it was due to incorrect relationships, indexes, and etc. or just
simply a dual entry in your table.

It is better that you show us your query string. Go to your query for the
form and combobox and open the query editor in design mode. In the left top
of your menu icon "View", click the tiny down triangle/arrow, select the "SQL
View". Copy and paste that into your Internet browser when you're replying.

According to your tables, there should be only one Primary key indexed.

TblArtists
ArtistID (PK) 'indexed no dulplicates
Artist_Name

TblTracks
TrackID (PK) 'indexed no dulplicates
Track_Name
ArtistID
RecordingID

TblRecordings
RecordingID (PK) 'indexed no dulplicates
Title
Date
Number of Tracks

If a single table query on TblArtists "Select * From TblArtists" has
dulplicates, it means that you have two or more entry in your table and
you'll have to correct your data.

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

--
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

Hi John

This solved the problem thanks.

Adrian

"John W. Vinson" wrote:

On Mon, 7 Sep 2009 15:25:01 -0700, adrian007uk
wrote:

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

Base the Combo Box on a query with its Unique Values property set to Yes.
--

John W. Vinson [MVP]