Selective Field (list on either side - like access)

[Maillme]

Hi there,

is it possible to create this kind of form within a form in access - and then filter basedon the criteria on one of the sides?

http://img442.imageshack.us/img442/9977/formuq7.jpg

e.g. i want to list all courses an employee has been on, so i want to "move" the course to the right hand side, then run a report on these courses, showing this person on the report.

many thanks for any help,
Neil

 

[TheAceMan1]

How are ya Maillme . . .

Yes its possible, but you could acomplishment the same thing with a single [blue]multi-select listbox[/blue]. You'd enumerate selections using the [blue]ItemsSelected[/blue] property!

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Maillme]

i Ace man,

thanks for your reply - i had thought about a multiselect list, but this isnt going to be any good for the "not so database literate" that will be using it,

hence why i want to use this one - any tutorials out there?

thanks again,
Neil

 

[ZOR]

Maybe I have not woken up yetm but can you not do it with two listboxes side by side. Don't know if this FAQ helps

http://www.tek-tips.com/faqs.cfm?fid=6326

Regards

 

[JoeAtWork]

Yes, with two listboxes as Zor said. There's probably no tutorial out there for this specific solution, since this type of dialog is rather simple for a professional programmer.

 

[Maillme]

thanks zor,

However - how do i actually use this code?

E.g. - wher eon my form does it go?

thanks,
Neil

 

[JoeAtWork]

Huh, I should have checked Zor's link, apparently somebody did write a tutorial on this.

E.g. - wher eon my form does it go?

The instructions are right in the comments of the code.

 

[Maillme]

Hi there,

I'm working through this list code, can someone explain what this statement means:

Ensure that you have a reference to DAO

many thanks - as you can probably ghuess im no coder!

thanks alot for all your valuable help!

Neil

 

[Maillme]

Also - the following line of code is causing me problems:

"Private WithEvents mCmdMoveFromListOneToListTwo As Access.CommandButton"

help!!

thanks again,
Neil

 

[Maillme]

for get last comment - i had the class named incorrectly

Neil

 

[Maillme]

oops - no i never, still same error!

sorry bout all this,

thanks,
Neil

 

[MajP]

What is the error. I wrote this code so hopefully non-coders could use it. So this will be a code test on how clear my instructions are.

If you follow the directions and name everything the exact way you should be able to drop the class code into a class module and the form code into the form and all should work.

What is your error? Can not help without the error code and description

 

[Maillme]

Hi MajP,

First of all thanks alot for going to the effort of this code - it really helps people like me!

Second, apologies for not including the error!:

"Compile Error: Only valid in Object Module"

Naming:

My lists are named:

ListBoxOne
ListBoxTwo

My buttons are named:

CmdBtnMoveFromListOneToListTwo
CmdBtnMoveAllFromListOneToListTwo
CmdBtnMoveFromListTwoToListOne
CmdBtnMoveAllFromListTwoToListOne

Form Name:

frm_test_list

Many thanks,
Neil

 

[MajP]

The code needs to be placed in a "Class Module" not in a standard module. From either the database window or the vba window choose "Insert", "Class Module" from the toolbar. You can not choose "New" from the module tab because that will produce a "standard module"

 

[Maillme]

Hi MajP,

thanks for that - still no joy though

I have changed my buttons and lists to the following:

cmdOne
cmdTwo

lstOne
lstTwo

I get no error - but i dont get any of my values shifting from one list to the other (i have one list populated from another table).

thanks again for all your help,
Neil

 

[MajP]

can you post your code from your form? It should look something like.

Option Compare Database
Option Explicit
Public ftl As FromToList

Private Sub Form_Load()
   Set ftl = New FromToList
   Set ftl.ListBoxOne = Me.lstOne
   Set ftl.ListBoxTwo = Me.lstTwo
   Set ftl.CmdBtnMoveFromListOneToListTwo = Me.cmdOne
   Set ftl.CmdBtnMoveAllFromListOneToListTwo = Me.cmdTwo
   Set ftl.CmdBtnMoveFromListTwoToListOne = Me.cmdThree
   Set ftl.CmdBtnMoveAllFromListTwoToListOne = Me.cmdFour
End Sub

If you have done the above and do not get an error, then there is something in my code

 

[Maillme]

It works a treat now thank you very much! 2 things though im looking for guidance on:

a) I have populated my list (list one) from a table, but when i 'move' this over, it moves the ID number from this table, so, instead of "Session 1" i get "1".

b) How can i now run a query on the values in one of my lists - e.g. return all the records that have "session 1" populated on thr right hand list?


thanks again,
Neil

 

[MajP]

build a funtion called inList. It returns true if the ID you pass it is in the listbox.

Public Function inList(theID As Variant) As Boolean
On Error GoTo errLbl:
  'put your names here
  Const ListName As String = "lstTwo"
  Const intColumn As Integer = 0
  Const FormName As String = "frmBulkSchedule"
  '
  
  Dim lstBox As Access.ListBox
  Set lstBox = Forms(FormName).Controls(ListName)
  
  Dim intCounter As Integer
  If IsNull(theID) Then
    Exit Function
  End If
  For intCounter = 0 To lstBox.ListCount - 1
    Debug.Print lstBox.Column(intColumn, intCounter)
    If theID = lstBox.Column(intColumn, intCounter) Then
      inList = True
    End If
  Next intCounter
  Exit Function
errLbl:
  If Not Err.Number = 2501 Then
    MsgBox Err.Number & " " & Err.Description
  End If
End Function

now in a query assume the ID field in your table is sessionID then something like

SELECT sessionID otherFields from tblSessions where inList([sessionID) = true

In my example the first column of the listbox (index of 0) contains the values that I am searching for.