Combobox in Subdatasheet

[Wigi]

Hi all

I'm pretty newbie with Access, so bare with me... I'm using the fairly known
Access database, available here at the Newsgroups, for my CD collection. I
imported all tracks, CD's and artists.

In that database, there's a form for recordings (CDs), with a subdatasheet
to view and enter the tracks of that CD.

My question is this: since I have a separate table for the tracks (track ID,
track title, duration), can't I have a combobox in the subdatasheet to pick
from the tracklist? Right now it's showing the track ID, not the title.

Thanks in advance!

 

[Ken Snell \(MVP\)]

Yes. You need to change the combobox's RowSource query and other properties
of the combo box.

But first, are you using a form to do the data entry? Or are you entering
data directly into the table?

 

[Wigi]

Hi Ken

Thank you for your answer.

Form or table? Well that's the difficult point here I guess. Let me explain.

I started with an Excel sheet with all the tracks, artists and albums. 1 row
per track:

Column A: track title
Column B: artist
Column C: name of the CD
Column D: track number on that CD
Column E: name of the CD where the track appears as well
Column F: track number on that CD
Column G: name of the CD where the track appears once again
Column H: track number on that CD
....

Some tracks appear on 5 CD's.

Then I discovered Access and Arvin's nice database.

I imported the cells from Excel into the tables. ONLY after that, I found
that one should use the subdatasheet to enter the titles...

Summarizing, I should have used the form's subdatasheet, but I didn't. We're
talking about 14,000 tracks, 5000 artists and 400 CD's.

Thanks

 

[Wigi]

Hi Ken

I've tried many things with the Rox source, but I'm not having success.

If you could have a look at it, I uploaded the file to my website:

http://users.skynet.be/fa436118/wim/xlwdfiles/MusicCatalog1.zip

The purpose is to have a combobox in the recordings subform. In 2nd column,
I'd like to be able to type in the track title, choosing from a drop down.
The number that is appearing now, is correct, only that needs to be lookup in
the table Tracks. And also, I want only 1 column with the titles, not the
numbers, if possible. Don't bother about the name errors and all other
errors, I know how to fix them).

The problem is that I need to refer to the table Tracks, while the subform
seems to always refer to Tracks Versions.

Thank you for your time and knowledge.

 

[Ken Snell \(MVP\)]

If I'm understanding correctly, what you need to do is modify the Column
Counts and Column Widths properties of the combo box.

You want a query that looks like this for the combo box's Row Source query:

SELECT TrackID, TrackName
FROM Tracks
ORDER BY TrackName;

Set the Bound Column property to 1. Set the Column Counts property to 2. Set
the Column Widths property to this string:
0";2"


This will hide the column that holds the TrackID value, and the combo box
will show just the track title. However, the combo box will store the
TrackID value into the table on which the subform is based.
--

Ken Snell
<MS ACCESS MVP>

 

[Wigi]

Thanks for the answer.

But it's not working yet. I set the properties as you instructed me to do.

Here are the properties I don't feel sure about:

Control Source:

[Tracks]!TrackID

Row Source:

SELECT TrackID, TrackName FROM Tracks ORDER BY TrackName;

When I click to have a drop down in the column, I get a popup box asking to:

Enter parameter value: TrackName

I don't get a dropdown with track names.

Thanks.

 

[Ken Snell \(MVP\)]

I was using a generic name "TrackName". Replace that with the real name of
the field that contains the name of the track.

Also, why is the Control Source pointing to the Tracks table if you're using
the Tracks table to just store the list of tracks? Shouldn't it be pointing
to a table that serves as the junction table to match a CD with specific
Tracks?

Post the tables and fields in those tables so that we can see what your data
structure looks like.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the answer.

But it's not working yet. I set the properties as you instructed me to do.

Here are the properties I don't feel sure about:

Control Source:

[Tracks]!TrackID

Row Source:

SELECT TrackID, TrackName FROM Tracks ORDER BY TrackName;

When I click to have a drop down in the column, I get a popup box asking
to:

Enter parameter value: TrackName

I don't get a dropdown with track names.

Thanks.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

If I'm understanding correctly, what you need to do is modify the Column
Counts and Column Widths properties of the combo box.

You want a query that looks like this for the combo box's Row Source
query:

SELECT TrackID, TrackName
FROM Tracks
ORDER BY TrackName;

Set the Bound Column property to 1. Set the Column Counts property to 2.
Set
the Column Widths property to this string:
0";2"


This will hide the column that holds the TrackID value, and the combo box
will show just the track title. However, the combo box will store the
TrackID value into the table on which the subform is based.

 

[Wigi]

Hi Ken

OK, I didn't see the names where generic, since the names used are quite
similar. Here are the main tables and fields:

TABLE Tracks

TrackID - Autonumber
TrackTitle - Text
RecordingArtistID - Number

TABLE Tracks Versions

ID - Autonumber
TrackTitle - Number (Combobox, Table/Query, Row Source: Tracks, 1, 1)
RecordingArtistID - Number
RecordingID - Number
TrackNumber - Number
TABLE Tracks Versions

TABLE Recordings

RecordingID - Autonumber
RecordingTitle - Text
RecordingArtistID - Number
MusicCategoryID - Number
....

TABLE Recording Artists

RecordingArtistID - Autonumber
RecordingArtistName - Text

TABLE Music Categories

MusicCategoryID - AutoNumber
MusicCategory - Text


The subform in the FORMS Recordings: (holding tracks)

Column 1:
Name: TrackNumber
Control Source: TrackNumber

Column 2: (the important one)
Name: TrackID
Control Source: [Tracks]!TrackID
Row Source Type: Table/Query
Row Source: SELECT TrackID, TrackName FROM Tracks ORDER BY TrackName;


That's it. Thanks again for your efforts. Much appreciated.

 

[Ken Snell \(MVP\)]

Your data setup is not making sense to me. From what you've posted, you're
using the Tracks table to get a list of TrackID values, and then you're
trying to store that TrackID value back into the TrackID field of the Tracks
table. That makes no sense to me?

I don't see any table that is storing the data to show which TrackID values
go with a single CD; I assume that you're wanting to set up the database to
be able to show which tracks are on each CD? What is the purpose of the
Recordings form to which you're referring -- is the main form's recordsource
data the individual CDs?

I apologize, but I am unable to provide better suggestions until I have a
better understanding of the database's purpose and structure.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken

OK, I didn't see the names where generic, since the names used are quite
similar. Here are the main tables and fields:

TABLE Tracks

TrackID - Autonumber
TrackTitle - Text
RecordingArtistID - Number

TABLE Tracks Versions

ID - Autonumber
TrackTitle - Number (Combobox, Table/Query, Row Source: Tracks, 1, 1)
RecordingArtistID - Number
RecordingID - Number
TrackNumber - Number
TABLE Tracks Versions

TABLE Recordings

RecordingID - Autonumber
RecordingTitle - Text
RecordingArtistID - Number
MusicCategoryID - Number
...

TABLE Recording Artists

RecordingArtistID - Autonumber
RecordingArtistName - Text

TABLE Music Categories

MusicCategoryID - AutoNumber
MusicCategory - Text


The subform in the FORMS Recordings: (holding tracks)

Column 1:
Name: TrackNumber
Control Source: TrackNumber

Column 2: (the important one)
Name: TrackID
Control Source: [Tracks]!TrackID
Row Source Type: Table/Query
Row Source: SELECT TrackID, TrackName FROM Tracks ORDER BY TrackName;


That's it. Thanks again for your efforts. Much appreciated.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

I was using a generic name "TrackName". Replace that with the real name
of
the field that contains the name of the track.

Also, why is the Control Source pointing to the Tracks table if you're
using
the Tracks table to just store the list of tracks? Shouldn't it be
pointing
to a table that serves as the junction table to match a CD with specific
Tracks?

Post the tables and fields in those tables so that we can see what your
data
structure looks like.

 

[Wigi]

Hi Ken

Well, it's not just tracks and recordings as in the original file of Arvin
Meyer. There's a table in between:

(1) Tracks
(2) Tracks Versions, storing TrackID and RecordingID
(3) Recordings

Can't I retrieve the CD contents in this way? I have an ID for each time a
track appears on a CD, and that CD's ID number as well in the same table (2).

The zipfile I uploaded

http://users.skynet.be/fa436118/wim/xlwdfiles/MusicCatalog1.zip

is ca. 600 KB large. Would it help if I cut it down to a small sample, for
you to have a look at it?

If that's impossible, I guess I better start from scratch but I'm not very
keen on that.

Thanks again.

 

[Ken Snell \(MVP\)]

OK, so the subform should be based on the [Tracks Versions] table, not the
[Tracks] table.

Make the subform's RecordSource query be a query based on the [Tracks
Versions] table, then set the Control Source of the combo box (the one with
TrackID values) to just TrackID (not Tracks!TrackID).

 

[Wigi]

Hi

I see what you're aiming at, that's clear to me, but the syntax is killing me.

For the subform, I have:

Source Object: Recordings Subform
Link Chield Fields: RecordingID
Link Master FIelds: RecordingID

For the combobox, I have:

Control Source: TrackID
Row Source Type: Table/Query
Row Source: SELECTTrackID,[TrackName FROM Tracks Versions ORDER BY TrackName;

I tried numerous other things that I could think of, to no avail

Thanks.

 

[Ken Snell \(MVP\)]

Your form structure is not valid. The main form should be based on the
Recordings table, and the subform should be based on the Track Versions
table, using the RecordingID field for the LinkChildFields and
LinkMasterFields properties of the subform control.

What is the Record Source property of the main form? What is it for the
subform's Source Object form?

--

Ken Snell
<MS ACCESS MVP>

Hi

I see what you're aiming at, that's clear to me, but the syntax is killing
me.

For the subform, I have:

Source Object: Recordings Subform
Link Chield Fields: RecordingID
Link Master FIelds: RecordingID

For the combobox, I have:

Control Source: TrackID
Row Source Type: Table/Query
Row Source: SELECTTrackID,[TrackName FROM Tracks Versions ORDER BY
TrackName;

I tried numerous other things that I could think of, to no avail

Thanks.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

OK, so the subform should be based on the [Tracks Versions] table, not
the
[Tracks] table.

Make the subform's RecordSource query be a query based on the [Tracks
Versions] table, then set the Control Source of the combo box (the one
with
TrackID values) to just TrackID (not Tracks!TrackID).