Combo Box as record selection

[Tuckejam]

I have form that shows me all the information about each Chapter in my organization

I can move to different records (chapters) using the navigation buttons on the bottom (forward, Back, new)

and i have even programmed buttons on the form to do the same (next, previous, New)

but how can I turn the chapter name field into a combo box that will change, or select and show the information for the record selected.

(also, If I should be using different terms to ask this that would make it clearer what I am trying to say, please feel free to point it out)

Thanks everyone for you help

[Gicu]

You don't as trying to go to a different chapter will edit the current record. You have to add a new unbound combo box (usually in the form's header along with your other navigation buttons) that will search for the chapter selected (when you add a new combo box Access will prompt you if you want to to just that).
Cheers,

[ranman256]

the unbound cbo is used as a filter to show only those selected.
the bound cbo is what is assigned to the record.

to use as filter:

sub cboBox_afterupdate()
if isNull(cboBox) then
   me.filterOn = false
else
   me.filter = "[Field]='" & cboBox & "'"
   me.filterOn = true
endif
end sub

[Tuckejam]

I am working through a 10 hour video course on VBA so I will eventually have a better understanding but in the mean time while I am "Jumping ahead" I appreciate the help

I created a new combobox and linked it to "tbl_Chapters" and the field "Chapter Name" and selected remember the value for later use, and named the box "Chapter Name1"

then I tried pasting your code in the afterupdate section (in the vba module)

when I go to the form and select a chapter in the drop down box it errors and highlights the first line of code

I tried various things trying to make it work but don't yet understand it well enough to make it work


Private Sub Combo37_AfterUpdate() <---This line highlights yellow
Sub cboBox_afterupdate()
If IsNull(cboBox) Then
Me.FilterOn = False
Else
Me.Filter = "[Field]='" & cboBox & "'"
Me.FilterOn = True
End If
End Sub
End Sub


Thanks for the help

[Micron]

You have wrapped code in 2 beginning and 2 ending sub statements. Remove the last line and whichever of the first 2 is wrong.
If this control has a control source property value, then stop - you have it bound to a field and as was mentioned, it must be unbound. If it only has a row source property, then it is unbound.

[Gicu]

I thought you wanted to go to the selected charter record not filter by it. When you add a new combo box on the form you need to select the "Find a record on your form....." (last option), Select the Chapter field and you should be good to go. The code created by the wizard should look like this:

   ' Find the record that matches the control.
    Dim rs As Object


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

Cheers

[Tuckejam]

Micron - Yes, Fortunately I did understand that the code I pasted was wrapped twice (2 beginning, 2 end statements) - I had decided to past it that way to show what I was trying to edit to make work, unsure of which opening line to start with.

Thank you everyone for the help, but I think I am just not quite far enough along with my VBA to grasp it. once I finish my VBA Course I will check back and see if i can get it going.

Thanks again

[Tuckejam]

Ok Im still not getting it and hoping someone will walk me through it.

First I add a combo box to the header and chose "I want the combo box too get the values from another table or query" from the wizard
Then I select the table "tbl_Chapters" as the source of the values for the combo box

Then I select "Chapter Name" as the field of values I want to show in the combo box (and hide key column)

Then I have two choices "Remember the value for later use" OR "Store that value in this field"

And I chose "Remember value for later use"

And name the combo Box "Chapter Name1"


The combo box in the design view of the form says it is "unbound"

The properties for the combo box are:

Name: Combo47
Label: Chapter: Name1_Lable
Control Source:
Row Source: SELECT [tbl_Chapters].[ID], [tbl_Chapters].[Chapter Name] FROM [tbl_Chapters];
Row Source Type: Table/Query


When I try the code below, after selecting a record from the drop down box all the field in the for turn blank, and I have to close and reopen the form to get it to work/show anthing
(Note this form does also have a subform if that matters

Thank again for the help, it is truly appreciated


sub cboBox_afterupdate()
if isNull(Combo47) then
me.filterOn = false
else
me.filter = "[Chapter Name]='" & Combo47 & "'"
me.filterOn = true
endif
end sub

[Micron]

First, a combo doesn't have records per se, it has row values (and column).
Look at your sql for the combo. It returns 2 fields and their order dictates which order they appear in the combo.
Which combo column provides the value to the combo after a selection? What is the data type of that selection? What might those values be?
Then, what data type are you using for the filter in your code? What values are you looking for?

To fix the view when a filter messes up the form, just remove the filter. IIRC you can do that in the form navigation controls area (it will show 'filtered' or something like that) or remove it via the ribbon.

EDIT - I did have to make one assumption about your data when composing the above. If you're still stuck after this, I'll be more revealing and less about teaching because my assumption could be wrong.

[Gicu]

Try the code I gave you earlier:

 ' Find the record that matches the control.
    Dim rs As Object




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

If you want to use filters instead of navigating to the record you need to fix your code, the name of the control is cboBox yet you use Combo47 in your code.

Cheers,

[Micron]

the name of the control is cboBox yet you use Combo47 in your code.

Interesting. I saw that but just figured there were 2 combos but after reviewing, perhaps not.

[Gicu]

I agree, something looks odd as the above the code the OP mentions Name:combo47 but the event is for cboBox....

[Tuckejam]

Ha! as embarrassing as it is, that just shows how little I understand what I am doing.

That's me thinking you are generically referring to the name of the Combo Box, and me trying to put the actual name of the Combo Box in place of the generic reference.

THANK YOU Micron for "Teacher Mode" I am desperately trying to actually learn this stuff and not just drop in code. I'm working through all of that and will get back


this was the original code (from ranman256) that I was modifying
sub cboBox_afterupdate()
if isNull(cboBox) then
me.filterOn = false
else
me.filter = "[Field]='" & cboBox & "'"
me.filterOn = true
endif
end sub

[Micron]

You're welcome; and yes, the cboBox was just a pseudo name and you need to use the name of your control. If you click on the ellipses (...) in the property sheet (Event Tab) to start code for an event you won't get the name wrong.

[Tuckejam]

Hey Gicu, Sorry ii was not ignoring your approach, just trying not to do to much at once

your code (below, adjusted to fit Combo53) returns error - Runtime 3070, Microsoft Access Database engine does not recognize 'ChapterID' as valid field name or expression

Private Sub Combo53_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object


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





Still playing with your stuff Micron,
Right now Im trying to adjust the SQL via the query builder to match what I want, but starting to feel like Im on the wrong track