Listbox Lunacy

[Synergy.ron@gmail.com]

I have a Membersip app. There is a membership form based on members, and there is a Listbox (LstMembers) based on a Query based on Members.

a) If I 'screw around with creatin and deleting the listbox (using the wizzard) I can sometimes get it to work. Otherwise, it just shows the listbox without benefit of events.


as usual your thoughts are appreciated,


Attachment 46121Attachment 46121Attachment 46121

[Bob Fitz]

a) If I 'screw around with creatin and deleting the listbox (using the wizzard) I can sometimes get it to work.

What result exactly, would you consider to be working correctly?

[June7]

What is purpose of these 2 listboxes? Why is one listbox bound to field and other has expression in ControlSource?

Binding listbox to autonumber field makes no sense. Cannot update an autonumber field.

I think need to blank both ControlSource properties.

Listbox Click event errors if form is on New Record so deal with that. Reference listbox value instead IDMember field: rstMembers.Index = Me.LstMembers

Now the error is "Object variable or With block variable not set." on the rstMembers.

Have you step debugged?

[orange]

Ron,

You are discussing HOW you have done something. Readers need to know WHAT you are attempting to automate.
Please tell us about the process(es) involved, and show us some sample data before your "automation" and some showing us your expected "correct" output.

[Synergy.ron@gmail.com]

sorry for the brevity. Ii am trying to get the listbox to be a navigation control in the Members form. i.e, click a lst control and navigate to the right record in Members.

[June7]

So did you try suggestions in post 2?

[Micron]

Maybe

Private Sub LIST_Members_DblClick(Cancel As Integer)
Me.Filter = "IDmember = " & Me.List_Members.Column(0)
Me.FilterOn = True
End Sub

but I'm not seeing the point of reproducing the same data 3 ways on the same form. Is this just a learning exercise?
I seem to recall that there might be an implication of just changing a filter value without turning it off first but I can't recall what that was about and I'd never find it now.

[mike60smart]

Ron

Your Members form makes no sense whatsoever.

I believe the layout in the attached is what you are after.

This allows you to search for a Member using the Combobox in the Header of the Form.

[kd2017]

There's a lot of stuff going on here that's generally not recommended.

1. I see a lot of lookup fields within your table. This is a no-no http://access.mvps.org/access/lookupfields.htm

2. The members table has a subdatasheet setup to a table that doesn't exist and is causing issues. The first thing I do when creating a table is explicitly set the subdatasheet property to [None] https://www.fmsinc.com/microsoftacce...heet/index.htm

3. Inconsistent name formatting. Some fields use camel case, others use reverse camel case (?), some use underscores, some use spaces. I'd pick just one, either camel case or underscores, and stick with it throughout your app. Don't use spaces or special characters. https://en.wikipedia.org/wiki/Camel_case

mike60smart showed an example of how navigate to records in your form using a combobox with a simple macro. Most of us generally work in vba rather than macros, that said here is vba code written by the access hall of famer David Fenton to accomplish the some thing mike has shown. This is the code I use. This could be used for a listbox as well.

Private Sub cmbFind_AfterUpdate()
    If IsNull(Me!cmbFind) Then Exit Sub
    
    With Me.RecordsetClone
        .FindFirst "[InventoryID] = " & Me!cmbFind
        If Not .NoMatch Then
            If Me.Dirty Then Me.Dirty = False
            Me.Bookmark = .Bookmark
        Else
            ' put your not found code here, but you really shouldn't need it
        End If
    End With
End Sub

https://stackoverflow.com/questions/...obox-in-access

[Synergy.ron@gmail.com]

The goal is: the member list shows in the list box 2) selection of a member causes that members detail fields to populate. Sounds simple but I am having a hard time with this. (I suspect I need to set the rowsource in onload() and then populate using on click() event. Question...Can the listboxbox be unbound while the rest are bound? thank you.

[Synergy.ron@gmail.com]

The goal is: the member list shows in the list box 2) selection of a member causes that members detail fields to populate. Sounds simple but I am having a hard time with this. (I suspect I need to set the rowsource in onload() and then populate using on click() event. Question...Can the listboxbox be unbound while the rest are bound? thank you

[June7]

The goal is: the member list shows in the list box 2) selection of a member causes that members detail fields to populate. Sounds simple but I am having a hard time with this. (I suspect I need to set the rowsource in onload() and then populate using on click() event. Question...Can the listboxbox be unbound while the rest are bound? thank you

Yes, listbox can be unbound. Any control can be unbound while other controls are bound. However, if you use code to populate unbound control, all records will show the same info.

Sounds to me like you need an unbound control in form header for input of filter criteria then apply filter to form. Review http://allenbrowne.com/ser-62.html

[Synergy.ron@gmail.com]

Attached is my progress so far. The Form fields still resist acceptance of a value from the listbox????? i appreciate you comments and observations on what I am not getting right. thanks.




Attachment 46159
Attachment 46159

[ssanfu]

Design issues:

You have spaces in field names
you have calculated fields (should be done in a query)
you have a field name with a colon in it table -> LodgeInfo Field -> : odgeNum
you have at least 10 Look up fields
Type is a reserved word (2 tables) (also, type of what??) and shouldn't be used as an object name



The form frmMembers now is functional (I think).
I changed the form record source, the query "qry_Mem_nav" and the list box AfterUpdate code.
I changed some text box control sources.

[mike60smart]

Hi
Can you explain the purpose of this form layout?
It just appears to be a duplication of data displayed on 1 Form.