Combo Box as record selection

[Tuckejam]

$#!T I broke it

Some how I lost all my links to the table

and field names in the form now show #Name?

I have my backup at home, but any thoughts on what I did to cause this?

[Gicu]

You get the error because your unique ID is simply called ID, not ChapterID (per your post below):

Row Source: SELECT [tbl_Chapters].[ID], [tbl_Chapters].[Chapter Name] FROM [tbl_Chapters];

SO the code should say:

' Find the record that matches the control.
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Nz(Me![Combo53], 0)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Note that I modified the code a bit from the original (which was created by the Access combo box wizard) to declare rs as a recordset and removed the Str (as I believe your ID is numeric).

The two sets of code would have totally different effects on your form: the filter one from ranman will basically apply a filter so your form will show Record 1 of 1 (Filtered) on the bottom navigation buttons bar. The one I suggested will navigate to the searched record without applying a filter (so you would see Record 52 of 100 for example).

Cheers,

[Tuckejam]

Gicu,

Thank you for clarifying the different approaches, and you are correct in that I defiantly want to use your approach (this way after using the combo box to find a record, the next and previous buttons would still work)
(still want to work through the other to understand and learn but will tackle your approach first)

You get the error because your unique ID is simply called ID, not ChapterID (per your post below):

Yeah that was me trying to change it to make it work, sorry for the inconsistency in information.

But let me try this to make sure I know where we are,

Dim rs as DAO.Recordset <--- This declares "rs" as a variable of the "DAO.Recordset" type.

Set rs = Me.Recordset.Clone <--- This takes the "RecordsetClone" Property of the form and assigns that information to the "rs" variable

rs.FindFirst "[ID] = " & Nz(Me![Combo53], 0) <-------I understand this in the big picture sense but not as thoroughly as I would like

I think I understand that we are using the "FindFirst" method of the "rs" variable to find a criteria.

Super simplified we are saying "Find in [ID] that matches what is in [Combo53]"

and we are saying "[ID] = " and I think this is refering to the "ID" field in the tbl_chapters table


and Ill pause here and make sure I get this before I go any further.

I thank you again for you time, I know how frustrating it can be to explain something to someone repeatedly

[Tuckejam]

UPDATE: Might be getting closer or I might be on the wrong train


I know I had used the "LIKE" in some of my training classes so I started playing around and got past the 3070 error

Now when ever I select anything in the ComboBox it automatically changes to the first record OHRMC-01

Like a said I'm probably on the wrong train, but I thought I would share what I came up with, and ask why we are not using LIKE in the code you gave me?

Private Sub Combo51_AfterUpdate()
Dim rs As DAO.Recordset


Set rs = Me.Recordset.Clone


rs.FindFirst "[Chapter Name] Like '& Str(Nz(Me![Combo51]))'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

[Gicu]

Because you want to find the record you selected in the combo box which is a specific one, not "like" one. If you want like you might as well use a filter.

Now for the code. You changed the code again and you are trying to "find" a chapter name

rs.FindFirst "[Chapter Name] Like '& Str(Nz(Me![Combo51]))'"

but your combo is bound to the ID field. So if your Chapter Name="A New Start" and its ID =1 what the combo passes to the code is 1 not "A New Start".
If you start with a bound form when you add a new combobox you should be presented with this options:

If you select the third one and follow the prompts the wizard should build the code for you. If you create the combo manually your combo should have its first (and the bound) column set to ID, second column Chapter Name then any others you might choose to show; the column widths property should say 0";3";.... (which is a semi-colon separated list with the first column=ID being hidden), column number property should match the number of columns listed in the column width.

Cheers,
Vlad

[Tuckejam]

Ok, well, thata the problem.

In 50 plus attempts at this I have never once had that third option as a possible selection.

I have only ever been offered the first two as my choices.

[Tuckejam]

I just created a new form using the form wizard button, added a combo box and bam that third option shows up and work right away....

I created my form with starting with a blank form and adding fields to it using the add existing Fields button.

So for the record

when using the Form Wizard the record source becomes: tbl_Chapters

When starting with a blank form the record source is:

SELECT tbl_Chapters.[Chapter Name], tbl_Chapters.[Chapter Chair], tbl_Chapters.[Chapter Chair Phone], tbl_Chapters.[Chapter Chair Email], tbl_Chapters.[Chapter Area], tbl_Chapters.[Chapter Outreach], tbl_Chapters.[Chapter Charter Date], tbl_Chapters.[House Group Email], tbl_Chapters.[Chapter FIN] FROM tbl_Chapters;

I'm strongly considering deleting Access from my hard drive and going back to the warm safe flat world of Excel

I apologize for the amount of everyone's time I wasted on this

[Micron]

but your combo is bound to the ID field. So if your Chapter Name="A New Start" and its ID =1 what the combo passes to the code is 1 not "A New Start".

Therein lies the answers to the direction I was trying to steer you in post 9. I can't see where you ever addressed those points but it was your answer about 12 posts ago.
Good luck & don't give up on Access. Excel isn't better - it's just a different tool. They just look similar.