Cascading Combo boxes query (i searched and can't figure this out)

[eggwater]

I've been given this job to do at work where I have to steamline this database (a flat table created by someone else) so that I can automatically export Standardised data to an all singing and dancing SAP system. I have done all the relatively hard work (with a little help from this Forum I might add)

OK - I work at a University we have two campuses and for each campus we have different places for students to live and these are (were) stored on my flat table database - I have a form (ExportForm) with a Combo Box for Location (ie. Campus 1 or 2) and a List box for AllocatedHall (i.e the residence).

Firstly can the List Box be updated as it is? (I believe the answer is no)

So I've made two new related tables:

tbl1 is Location and contains the fields: LocationNo & Location
tble is AccomodationLIst and contains: LocationNo, AccomodationNo, AccomodationCode, AccomodationName

there is a one to many relationship between LocationNo etc etc

On the form the source table is my flat table

The Combo box has the requery code:

Private Sub Location_AfterUpdate()
Me.Location.Requery
End Sub

and as the row source the query:

SELECT [Location].[LocationNo], [Location].[Location]
FROM Location;

The List Box has the following as the Row source the query:

SELECT [Accomodation_List].[LocationNo],
[Accomodation_List].[AccomodationNo], [Accomodation_List].
[AccomodationCode], [Accomodation_List].
[Accomodation_Name]
FROM Accomodation_List
WHERE ((([Accomodation_List].[Accomodation_Name])
=Forms![ExportForm]![ALLOCATED_HALL]));


When i try to get this to work I get blank in the AllocationHall list


Please help I have been pulling my hair out over this for too long - i have lookedat loads of different ways around it and they all keep doing the same

cheers

 

[eggwater]

hi eggwater

guess what you sorted it yrself you clever bastard

ha ha

*goes to pub for celebratory beer and dance with complete stranger*