error message with dependent combo boxes

[hockey8837]

I have made a form for my DB which I've built two combo boxes, one dependent upon the other.

the first combo box's row source draws a select query:

SELECT tlkpFunding.TargetPlantingDate FROM tlkpFunding GROUP BY tlkpFunding.TargetPlantingDate;

it has a macro in the ON CHANGE event:

Set Warnings-No
Open query- qryZipBasedOffDate, Datasheet, Edit
Close - Query, qryZipBasedOffDate, Prompt '"qryZipBasedOffDate is a make table query-it populates ztblZip"'
Close - Table, ztblZip, Prompt
Set Warnings-No

the second combo box's row source is:

SELECT ztblZip.LocationZip FROM ztblZip;

My basic goal was to be able to select a target planting date, and limit the list in the zip code combo box to only those locations with that target date.

This works great except for one thing- say the user makes a selection of a target date, then selects an appropriate zip, then, changes their mind and wants to pick a different date. When the user goes back to click the 'target date' combo the following error message appears:

"The database engine could not lock table 'ztblZip' because it is already in use by another person or process."

Why am I getting this, and how can I change/fix it??

I've attached a thinned down copy of my db with the pertinent tables/queries/forms (the reports it generates are not necessary)...

 

[the_net_2.0]

the reason you're getting the error message is because your macro is attempting to run a make-table query, and it needs the zipcode table to do so, but it is already locked by the form because the combo box is querying it via the sql statement.

I don't understand why the make table query is even in there.

 

[hockey8837]

the reason you're getting the error message is because your macro is attempting to run a make-table query, and it needs the zipcode table to do so, but it is already locked by the form because the combo box is querying it via the sql statement.

I don't understand why the make table query is even in there.

Well, I'd put the make table query there to be able to limit the zip codes for cbozip to pull data from, and I was having major issues getting it to work any other way. Since, I need the data stored or saved somewhere long enough for the user to select the other combo boxes on the page and have the versatility to go back and forth, I thought maybe this would work well enough.

I'm sure this isn't the best way to do this, but it's what I managed to 'hack' together for the time being. I'm open to better suggestions!

 

[DCrake]

Do a search on cascading combo boxes, you don't need to keep running make table queries constantly. This will eventually bloat your database.

 

[hockey8837]

Do a search on cascading combo boxes, you don't need to keep running make table queries constantly. This will eventually bloat your database.

Ok, I'll take a look, thanks!

 

[hockey8837]

Hi,
So I followed Example 2: A Single Row Source Table from: http://www.fontstuff.com/access/acctut10.htm

It seems to be working now, except it looks like it's returning ALL records in the cbo, not unique records. I changed it to 'select distinct' for cboZip, yet I'm still getting duplicate zip codes showing up in the combo box. How can I fix this?

I've attached the db with the changes.