Query based on combo box

[hawkins]

I have a query that uses a combo box in a subform as a criteria to select a specific record. The combo box is in the subform, ctrYearlySubForm, and the combo box is Yearcmb. The year is stored in a different table then what my query subform is in if that makes a difference?The query works correctly if i manually enter in the year but I have tried : =[Forms]![Main].[ctrYearlySubForm].[Form].[Yearcmb] and it still does not select the correct year/any year at all. The query is also based on a name but the query already correctly works with that, any suggestions that I may be doing incorrectly? Thank you for your help in advance!

[June7]

Is the combobox multi-column? Which is the bound column? Show the RowSource SQL of the combobox. If it is not multi-column, your syntax should work. So should: [Forms]![Main]![ctrYearlySubForm]![year field name]

[hawkins]

When I set up the query subform it asked to do it for every Name so the name is working correctly and its only single column. Here's the row source for the year combobox: SELECT Yearly.FdrandYearID, Yearly.YearRecorded, Yearly.Name2ID FROM Yearly WHERE (((Yearly.Name2ID)=[Forms]![Main]![Namescmb])) ORDER BY Yearly.YearRecorded;

[hawkins]

Here is my database, I figured it would be easier to see it. The query I am working with is DemandJanuaryForm and the subform is DemandJanuaryForm subform. I have it set it with values so for name=3HT12F1 or 1 in the Month-year table, you will be able to get 2009 and 2010 data from the Month-Year table. Thank you for your help!

[June7]

The 4 Demand... queries have a criteria that references Forms!Main.YearlySubForm.Form.Yearcmb

Remove it or change to Forms!Main!Yearcmb

[hawkins]

for some reason that did not work. It still does not select the correct year/any year at all.

[June7]

I made those changes and it does work.

Selection in Namescmb runs AfterUpdate event which moves to first record with the NameID and requeries Yearcmb, Capcmb, Yearscmb.

What do you want to happen?

[hawkins]

I am wanting the demandJanuaryFOrm subform to update with the values from a chosen name and year, so its not just dependent on the name but the year as well. The query, DemandJanuaryForm, already gets the correct name and month but the year isnt working correctly. I went into datasheet view for the subform and would only update the name but nothing with the year. There is only data for 2010 but that was the year I was trying to get to work. Sorry for any confusion and I truly appreciate this help!

[June7]

Okay, understand better now. Revise my earlier instructions. Instead of referencing the combobox on the subform as criteria in the queries, reference the YearRecorded field/textbox in ctrYearlySubForm: [Forms]![Main]![ctrYearlySubForm]![YearRecorded]

And now I wonder why Forms!Main!Yearcmb did not trigger input prompt, Yearcmb is on subform not Main, odd.

[hawkins]

Oh ok, that makes sense but for some reason im still not getting anything :S

[June7]

I was seeing the subforms requery sometimes but not always. Problem is sequence of code execution. With referencing YearRecorded textbox/field (need to lock against editing and tab stop no?) must update YearlySubForm then requery the other subforms. Modify Sub Yearcmb_AfterUpdate() to:
RunCommand acCmdSaveRecord
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "FdrandYearID=" & Me.Yearcmb
Me.Bookmark = rs.Bookmark
rs.Close
[Forms]![Main].[ctrYearlySubForm].Requery
[Forms]![Main].[CtrDemandJanuarysubForm].Requery
[Forms]![Main].[CtrDemandAprilSubForm].Requery
[Forms]![Main].[ctrDemandJulySubForm].Requery
[Forms]![Main].[CtrDemandOctSubForm].Requery

Also, the Main form does open to an existing record. Might want a locked textbox on Main to show the Name (actually, you have one there, make it visible and locked, tab stop no). I normally make locked textboxes transparent and use white backcolor for editable controls. And change the RowSource of Yearcmb to reference NameID instead of the combobox so that the list will be initially populated when the form first opens.

Each FdrName will have only 4 records for each year? Did you go with 4 subforms for the Demand queries just so will always be 4 lines showing?