Best way to go about this?

[bennyB]

Hi.

i have two tables storing meeting and meeting agenda details, and i have created a form and subform based on these (linked on meeting number).

the meeting_agenda table has a field in it called Premises_Name, which as you would expect links to the Premises table and displays the names the premises which have liquor permits.

however, i have another table called "kava" which has a list of places with kava permits.

on the subform (meeting_agenda), i want it so that when you select the topic (liquor/kava), from a drop down box, it changes the source in the Premises_Name field. (either displays the ones with kava or liquor permits).

i tried to set up a select/case statement and change the row source such as:

Case "liquor"
Premises_Name.RowSource = "SELECT DISTINCTROW [Premises].[Premises_Number], [Premises].[Premises Name] FROM [Premises];"

and something similar for the kava case. but i get an error sometimes.

when i first load up the form and select liquor from the topic and then move to the premises_name field and select the drop down box, i get
"The Microsoft Jet database engine could not find the input table or query
'~sq_cMeeting_Agenda Subform~sq_cPremises_Name'. Make sure it exists and that its name is spelled correctly."

when i view the subform by itself (not as part of the mainform) i can select the topic and correctly select the premises_name. (row source changes as it should), but when i view it as part of the mainform (as a subform) i get that error.

also - the field premises_name has the following properties:
Row Source Type - Table/Query
Row Source - SELECT DISTINCTROW [Premises].[Premises_Number], [Premises].[Premises Name] FROM [Premises];

so when i select the topic "kava" the drop down box (when the form is viewed by itself) the drop down box displays the correct values, but when i select one (e.g. the first one) it corresponds to the first record in the premises table, not the first one in the kava premises table

is this to do with the fact that the field itself has an intial row source?

how do you think the best way about doing this? am i on the right track or whether i should start something completely new?

appreciate the advice
BB

 

[DES]

Sounds to me like you have over thought this one.

In your premises table you should have a field that will contain either the value "Liquor" or "Kava". This will get rid of the "Kava" table.

You then make a combo on the form with a list containing both Kava and Liquor.

Next create a query from the Premises table that uses the combo as criteria for the field for Liquor/Kava. Use this query as your recordsource for the sub form.

Then on the combo set it to requery that recordsource after the combo updates.

If you have problems with this process drop me a line and send me a sample - I will return it to you working, with an explaination of the changes.

 

[bennyB]

Hi Des

i think that i know what you are talking about, but i still think that there are going to be some problems, due to the way that the tables are set up.

see the Premises table contains those premises that have a liquor license, and the primary key is their liquor license number.

the kava areas, are just names of areas which kava is allowed (primary key is an autonumber), so the tables arent of the same style.

as the database is quite large, i tried deleting all of the records, and compacting and then zipping but its still >200kb.

is there a chance that i could grab your email address and send you a copy to look at and see whether you can see a way of doing this?

thanks for all your time
BB

 

[DES]

email: technoweenie@sympatico.ca

Don't mind looking at it for you