Listbox Help

[GendoPose]

Hi All,

2 questions;

Firstly, is it possible to hide unchecked values in a listbox? I have a user with several roles and I want to only show the ticked roles in the listbox.

Secondly, can you create a hyperlink on listbox values? i.e, if I click on "Manager" in the roles listbox, it follows that to another form and opens the record about managers?

Thanks

 

[pr2-eugin]

Firstly, is it possible to hide unchecked values in a listbox? I have a user with several roles and I want to only show the ticked roles in the listbox.

Yes, Edit the RowSource of the ListBox.

Secondly, can you create a hyperlink on listbox values? i.e, if I click on "Manager" in the roles listbox, it follows that to another form and opens the record about managers?

If it is to do with opening Forms, it will not be Hyperlink, it is merely a function call. You would use DoCmd.OpenForm method, and yes it is possible.

 

[GendoPose]

Yes, Edit the RowSource of the ListBox.
If it is to do with opening Forms, it will not be Hyperlink, it is merely a function call. You would use DoCmd.OpenForm method, and yes it is possible.

Any help on how to do that? Brain's gone dead and I really cannot see it.

Also, co you do the OpenForm command based on each value in the list box, as that's what I'm after?

 

[pr2-eugin]

I am not sure how your table is structured, in my DB I have an Agent table where I have a column called activeEmp, so if I need to list only people who are active in the company (or in other words, who are only ticked). I just use the following as the RowSource of the listbox,

SELECT Agents.a_ID, Agents.aName
FROM Agents
WHERE Agents.activeEmp = True;

If your listbox has an ID, by which a record is determined, you normally use the WHERE condition of the DoCmd.OpenForm method, Paul has a good explanation : http://baldyweb.com/wherecondition.htm

 

[MarlaC]

Include whatever the checkbox field is in your query for the rowsource for the list box, and put True in the criteria row beneath that field.

As far as opening the form, more information is needed. Are you opening the same form regardless of selection in the list box and want the form's data filtered, or do you want a different form to open depending on the user's selection in the list box? Also, do you want to allow multi-select, so that users can have, say, data on Managers and Assistant Managers displayed in a form, or forms for Managers and Associates?

 

[GendoPose]

I am not sure how your table is structured, in my DB I have an Agent table where I have a column called activeEmp, so if I need to list only people who are active in the company (or in other words, who are only ticked). I just use the following as the RowSource of the listbox,

SELECT Agents.a_ID, Agents.aName
FROM Agents
WHERE Agents.activeEmp = True;

If your listbox has an ID, by which a record is determined, you normally use the WHERE condition of the DoCmd.OpenForm method, Paul has a good explanation : http://baldyweb.com/wherecondition.htm

I changed from a listbox to seperate textboxes, it's easier for now.

I've been using the WHERE condition of the OpenForm method, however it filters the records to that one record and when I turn the filter off, it goes straight back to the first record, how can I turn the filter off and stay on the record I've navigated to?

 

[pr2-eugin]

Did you check the continuation of the link? But I want to have all the records still available in the second form

 

[GendoPose]

Did you check the continuation of the link? But I want to have all the records still available in the second form

I can't for the life of me seem to get this to work.

My code is this;

Private Sub Name1_DblClick(Cancel As Integer)
Dim rs As Object
Dim lngBookmark As Long
    lngBookmark = Me.OriginalID
    DoCmd.OpenForm "frmMyForm"
    Set rs = Forms!MyForm.RecordsetClone
    rs.FindFirst "NewID =" & lngBookmark
End Sub

Any ideas where I've gone wrong?

OriginalID is the ID on the first form, NewID is the ID on the form I want to open and navigate to a record.

 

[pr2-eugin]

So what has happened to the rest of the code?

 Forms!frmMyForm.Bookmark = rs.Bookmark

 

[GendoPose]

So what has happened to the rest of the code?

 Forms!frmMyForm.Bookmark = rs.Bookmark

Sorry, I read that line as optional on baldyweb.

Thanks, brain is feeling very slow today!

 

[pr2-eugin]

Only opening to a NewRecord if a match was not found is optional, the Else part should be present. You can still have something like..

Private Sub Name1_DblClick(Cancel As Integer)
    Dim rs As Object, lngBookmark As Long
    
    lngBookmark = Me.OriginalID
    DoCmd.OpenForm "frmMyForm"
    Set rs = Forms!MyForm.RecordsetClone
    rs.FindFirst "NewID =" & lngBookmark
    Forms!frmMyForm.Bookmark = rs.Bookmark
    
[COLOR=Blue]    If rs.NoMatch Then
        MsgBox "The data does not seem to exisit, Please try again", vbInformation
    Else[/COLOR]
        Forms!frmEmployeesDetail.Bookmark = rs.Bookmark
[COLOR=Blue]    End If [/COLOR]
    Set rs = Nothing
End Sub