Displaying Text From Lookup Table

[JamesJoey]

I have a continuous form based on a table that is for my movie library.
Each movie is assigned a movie type so I can filter the records from a combo box based on the lookup table.

The lookup table has two fields: MovieTypeID and MovieType.

I want to display MovieType in a column on the continuous form but I need to base the form on two tables, I believe.

Any help will be appreciated,
James

 

[jdraw]

Post the sql for the form's record source.
Show us the design of your tables.

 

[JamesJoey]

SELECT * FROM tblDvd ORDER BY DvdMovieTypeID, DvdMovieTitle;

Not sure what you mean by design of the form.

 

[jdraw]

Try

SELECT tbldvd.*
	,tblLookup.MovieType
FROM tblDvd
INNER JOIN tblLookup ON tbldvd.DvdMovieTypeID = tblLookup.MovieTypeId
ORDER BY DvdMovieTypeID
	,DvdMovieTitle;

Try this as a query and see if you get the values you need.

For the form, you'll want to set the combo row source using the Lookup table.

You'd then --in the afterUpdate of the combo event- you'd add a where clause to the record source of the form and requery.

Me.Recordource ="SELECT tbldvd.*,tblLookup.MovieType" _
 & " FROM tblDvd INNER JOIN tblLookup ON " _
 & " tbldvd.DvdMovieTypeID = tblLookup.MovieTypeId " _
 & " WHERE tbldvd.dvdMovieTypeID ='" &  me.combo & "'"  _
 & " ORDER BY DvdMovieTypeID	,DvdMovieTitle;"

Me.Requery

 

[JamesJoey]

I can't make heads or tails of inner joins so I attached the pertinent objects.

View attachment Dvd.zip

 

[jdraw]

I'm not sure of all the things you are doing, but I made a copy of your database and modified it
as DvdMoviesJ.accdb

 

[JamesJoey]

Yes, that's what I want.

I didn't think that it would be so complicated (at least for me).


Thanks much,
James

 

[jdraw]

Glad it helps.
Complicated is relative. I always work from a clear description of the issues/opportunities.
It gets complicated when you get a working/operational database (or mostly operational) and you aren't fully aware of the requirements nor the coding to date. It becomes a hunt through code......

Anyway, good luck with your project.

 

[JamesJoey]

I found something out.

If using a split form and I unhide the DvdMovieTypeID field, the datasheet displays the DvdMovieType value.

Why I have no idea.