Cascading Combo Box

[Homegrownandy]

My query for displaying the available "batches" in a "Project" isn't working and I can't seem to figure out why.

I have an after update event, Following the tutorial here:

http://www.blueclaw-db.com/access_ev...fterupdate.htm

Private Sub Project_Name_AfterUpdate()
    Forms![Doc_CreatorF]![Project_Name].Requery
End Sub

I don't know how to just test that the form is updating though.

The criteria in the "Project_Name" field in the query is:

[Forms]![Doc_CreatorF]![Project_Name]

So from what I understand the results of this query should update when I select from the "Project_Name" combo box.

The second combo box uses the query to display results to pick from. But the query will never generate results.

Any ideas how I can troubleshoot this or do something different?

Thanks in advance. Andy.

Additionally - The first combo box is "Remembering the value to use later" not storing it.

All form names and references to combo boxes are correct. (I've started from scratch a few times and double checked).

Any other information that might be useful feel free to ask.

[CJ_London]

lots of info, but can't see how you are putting it together - for example

Private Sub Project_Name_AfterUpdate()
    Forms![Doc_CreatorF]![Project_Name].Requery
End Sub

is this requerying the project_name control in the current form a different one? - and if the current one, why are you requerying a control you have just updated?


Suggest start again and explain what you are trying to do and not what you are doing - as a minimum, include names of forms and relevant controls rather than using terms like 'the form'. Include rowsources for comboboxes and if that rowsource is a query, the sql to the query

[Homegrownandy]

I want a combo box which returns values depending on the selection in the first combo box. All of this is on one form. (any reference to form is "Doc_CreatorF") I select a value in the first combo box and in the second I want it to display everything under it.

EG. If project 1 was selected the second combo box should show all "batches" relating to project 1. (same throughout).

The reason the query needs to run again after update is because its dependant on the result from the combo box. So when the box is changed/updated the query needs to run again to pull records/values for the second combo box.

assuming the update works (I don't know how to test) the problem lies in the query. Its never returning any values.

here is the SQL

SELECT ProjectT.Job_Type, BatchT.Batch_Number
FROM SiteT INNER JOIN (ProjectT INNER JOIN (BatchT INNER JOIN JobT ON BatchT.[Batch ID] = JobT.Batch_ID) ON ProjectT.Project_ID = BatchT.Project_ID) ON SiteT.Site_ID = JobT.Site_ID
WHERE (((ProjectT.Job_Type)=[Forms]![Doc_CreatorF]![Project_Name]));

If you look at the tutorial I linked it shows the requery there. I have tried to copy the tutorial.

cheers, Andy.

[nick404]

Hm, this isn't how I have done cascading combo boxes before.

So if (cboProj) Project 1/2/3 needs to show batches1/2/3 in cboBatches let's call it (representing any amount of batches) then they have to be related in some table.

Then in your query behind cboBatches you want to have it contain fields Projects and Batches.
The criteria for projects is going to be Where [Forms]![Doc_CreatorF]![cboProj]

Just how I normally do casc. cbos...

[CJ_London]

I'll leave Nick404 to follow through, you haven't provided what I asked for so can't really help

[kagoodwin13]

Hm, this isn't how I have done cascading combo boxes before.

So if (cboProj) Project 1/2/3 needs to show batches1/2/3 in cboBatches let's call it (representing any amount of batches) then they have to be related in some table.

Then in your query behind cboBatches you want to have it contain fields Projects and Batches.
The criteria for projects is going to be Where [Forms]![Doc_CreatorF]![cboProj]

Just how I normally do casc. cbos...

In my experience, that is the best way to do cascading combo boxes in Access. Value from combo box 1 feeds into query, query determines values for combo box 2. The requery command helps if you update the value in combo box 1, but it takes both pieces to make the puzzle complete.

[nick404]

The requery command helps if you update the value in combo box 1, but it takes both pieces to make the puzzle complete.

Yes, I forgot the requery portion. Behind the first combo box in the AfterUpdate requery.

[Homegrownandy]

I'll leave Nick404 to follow through, you haven't provided what I asked for so can't really help

What did I not include that you asked for? I appreciate if that's how you feel it can be frustrating, but apply that advice to yourself. Its not just me that needs to be more clear.

cheers nick and kagoodwin, ill take a look at the suggestions.

[CJ_London]

I asked

is this requerying the project_name control in the current form a different one?

you answered - 'All of this is on one form' OK. I can work that out

I asked

- and if the current one, why are you requerying a control you have just updated?

no answer

I asked

as a minimum, include names of forms and relevant controls rather than using terms like 'the form'. Include rowsources for comboboxes and if that rowsource is a query, the sql to the query

you replied

select a value in the first combo box and in the second I want it to display everything under it. ....The reason the query needs to run again after update is because its dependant on the result from the combo box. So when the box is changed/updated the query needs to run again to pull records/values for the second combo box....the problem lies in the query.

For all I know the 'second' combobox rowsource has not been set or is referring to a different query, your 'requery' code appears to be requerying the wrong control (but without knowing why you are requerying that particular control, who knows?) You say the problem is with the query - why would you think that?

[Homegrownandy]

the "Project_Name" combo box needs to trigger the requery after update so the query has a value to work with. The query is dependant on the value from the "Project Name" combo box.

This could be where the problem lies.

Alternately, It might be the query itself. The reason I say this is because I can never get it to generate results (when I manually run it in design view.).

So either the reference from "project name" isn't there for the query to work. Or the query is wrong.

anything to do with combo box two is irrelevant at this stage, It cant display results/records that don't exist.

you say I'm requerying the wrong control (or possibly). Ill look into this; to be honest I don't understand this properly.

also I just noticed I linked the wrong tutorial. Which only added to the confusion,

http://www.blueclaw-db.com/comboboxl...pdown_list.htm
<this is the right one.

Ill have a look into the requery event.

[orange]

Lots of comboBox info

http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

http://www.techonthenet.com/access/comboboxes/

[Homegrownandy]

Thank you. Ill get to this when I get the chance.