Combo Box replacing navigation button (record selector)

[hawkins]

The project I am working on has a list of 345 feeders(names) that have both static information and changing information depending on the year. I have a table with names that is related to a table with the names and years with more information with it. I have created a combo box in a form that one can either enter in a new year by clicking the next record button on the navigation bar or I can go either hit forwards or backwards on the navigation bar to change inbetween the years for a specific feeder. I was wondering how I could get rid of the navigation bar and use the combo box to choose from already entered years for specific names or enter in a new year to create a new record for that year... To look up a name selectedfrom a combo box then look up all the years under that name and inserts those years and an add new year option to the combo box for Year.


*** I have since been able to get the combo box to fill with only the first name entered. I used [Forms]![Main]![Namescmb]. I have been trying to use this to update the combo:
Private Sub NamecmbAfterUpdate()
Me.Yearcmb = Null
Me.Yearcmb.Requery
Me.Yearcmb = Me.Yearcmb.ItemData(0)
End Sub
The two combo boxes are in different forms, I was wondering if this is what is affecting the results?

Let me know if the description is too vague, I am new to access and do not have much knowledge about it. Any suggestions will be greatly appreciated.

[June7]

Private Sub Namescmb_AfterUpdate()
Form_formname.Yearcmb.Requery
Form_formname.Yearcmb = Form_formname.Yearcmb.ItemData(0)
End Sub

[hawkins]

thank you for the response!for some darn reason that didnt work. here is exactly what ive done so far and I would have uploaded the file but it is too large to attach.
Yearcmb is the year combo
Namecmb is the name combo
I first changed the row source for yearcmb under names2ID to [Forms]![Main]![Namecmb]. names2ID is related to the names list. After that I could recieve the first set of years but after changing the name it will not update. I then entered:
Private Sub Namecmb_AfterUpdate()
Form_YearlySubForm.Yearcmb.Requery
Form_YearlySubForm.Yearcmb = Form_YearlySubForm.Yearcmb.ItemData(0)
End Sub

YearlySubForm being the subform of main where the year is located. This code did not change anything when it was entered.
I was hoping that when the year is chosen the rest of the material under the yearly updates are loaded with that years data. The year being more of a record changer depending on the selected year.

[June7]

Okay, form/subform, not two independent open forms. Referencing subform controls is actually a little more complicated because have to 'go through' the subform container. Need to give the container a name different from the form, like ctrYearlySubForm. Since the code is behind the main form, try: Me.subformcontainername.Form.controlname

[hawkins]

Thank you for the response, where exactly would I put Me.YearlySubForm.Main.Yearcmb? I am just confusing myself with this thing... I have also found that instead of the row source for yearcmb under names2ID to be [Forms]![Main]![Namecmb] this site suggested to use: SELECT YearRecorded FROM YearlySubForm WHERE Name2ID=[Namecmb]; because it is a subform

I am not completely sure if that is correct because I get a syntax error when I try to use it.

[June7]

Yes, this is getting muddy. Clarify.

Main form is named Main?
Subform is named YearlySubForm?
Subform container is named _____?
Namescmb is combobox on main form?
Yearcmb is combobox on subform?

As stated, subform container control must have name different from its subform, like ctrYearlySubForm.

Code in the Namescmb AfterUpdate event would be (do NOT replace .Form with .Main):
Private Sub Namescmb_AfterUpdate()
Me.ctrYearlySubForm.Form.Yearcmb.Requery
Me.ctrYearlySubForm.Form.Yearcmb = Me.ctrYearlySubForm. Form.Yearcmb.ItemData(0)
End Sub

The RowSource for Yearcmb refers to Namescmb for its criteria:
SELECT YearRecorded FROM tablename WHERE Name2ID=Forms!Main!Namescmb;

[hawkins]

I have changed the subform container control name to ctrYearlySubForm but when I put into the row source: SELECT YearRecorded FROM Yearly WHERE Name2ID=Forms!Main!Namescmb; or the same without the ; at the end I still get a error message saying"The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses." I am wondering if I am putting that code in the correct place too, i put it under the Name2ID column.
Thank you, I truly appreciate the help!

[June7]

That SELECT sql goes in the RowSource property of Yearcmb combobox, not in table/query and certainly not under the Name2ID column.

[hawkins]

thanks for the help, ok that makes sense, i kept hitting the (...) to go to the other screen and not just typing it into there. For some reason it still isnt working, could I by any chance send it to you? i feel like that might be easier to figure out what i keep doing wrong.

[June7]

Yes, seeing is often better. Attach to post, zip if necessary, or upload to fileshare site such as box.net and post link to file. Either way, remove confidential data and run Compact & Repair first.

[hawkins]

Here's the zip, im hoping it works but just let me know if it doesnt. You are a life saver!

[June7]

Yearcmb cannot be a bound control if you want to use it as a search tool by selecting from its list. Otherwise you are simply changing the year value in the field for that record. Unbind this control and create a textbox on the form to show the year for the current record. Need to include FdrandYearID field in the RowSource query.

The Namescmb AfterUpdate event is set to [Embedded Macro], needs to be [Event Procedure]. Again, add a textbox to show the Name value for current record.

RecordSource for YearlySubForm can simply be Yearly.
RecordSource for CapacitorBankSubForm can simply be CapacitorBank.

The NotInList event for Yearcmb needs to be renamed if you still want to use it.

Code for the Namescmb and Yearcmb AfterUpdate events ([Event Procedure] in the Events property):

Private Sub Namescmb_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "NameID=" & Me.Namescmb
Me.Bookmark = rs.Bookmark
rs.Close
Me.ctrYearlySubForm.Form.Yearcmb.Requery
Me.Namescmb = Null
End Sub

Private Sub Yearcmb_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "FdrandYearID=" & Me.Yearcmb
Me.Bookmark = rs.Bookmark
Me.Yearcmb = Null
rs.Close
End Sub

[hawkins]

thank you, when I put in those afterupdates, there is a Run-time error '3077': Syntax error (missing operator) in expression. The error says its in rs.FindFirst "FdrandYearID=" & Me.Yearcmb. The second combo, yearcmb now updates with the values of the years for a specific name but does not go to the record but I am guessing the error is the problem.

[June7]

I don't know. It works for me. Double check that you followed my instructions correctly. So the the FindFirst in the other event is working?

[hawkins]

I tried to re-open the zip I sent you and change the suggestions you had but I still do not get the correct thing. I may just be missing something really small.