Trying to Identify A "Form Wizard" Object Type? (1 Viewer)

[ytene]

Hello everyone, sorry for the fact that this is likely a pretty silly question...

If I have an Access table and want to have a Form which references the table fields, I can run the Access Form Wizard, which gives me a very elegant little pop-up window including two columns, "Available Fields" and "Selected Fields", and I can move table elements between these two collections...

Please see the attached PNG for a screen scrape of the Wizard concerned.

My silly question is - can anyone please tell me the name of the Control object (if one exists) that could be used to implement this in one of my forms?

A guess a prudent follow-up question would be to ask for confirmation that my intended use is appropriate... I have an Access-based application which will support multiple users, each of whom may be entitled to operate within the application using one or more pre-defined roles (i.e. "Data Entry", "Data Verify", etc).

In my database I will have 3 tables: one for Users, one for Roles and a link table. I would like to be able to allow an Operator to select a User [from a Combo Box] and then be presented with a form which resembles the screen image. My idea would be to allow the operator to select from the "general bucket" of all available roles, and pick one or more to be assigned to the given user ID...

The solution used by the integrated Access form wizard would be perfect. I can't see it in any of the "basic" Form primitives. I've looked through the ActiveX controls and the only one that looked remotely appropriate was "Microsoft External Item Picker", but that doesn't look right when I try to include it.

I've tried searching for hints based on "Form Wizard", but so far without success.

Any suggestions on a good approach for this would be very much aprpeciated.

Thank you.

 

[pbaldy]

Would this work for you?

http://www.baldyweb.com/MultiselectAppend.htm

What you describe is possible, but to my knowledge there is no built-in version of it, you'd have to build it yourself.

 

[isladogs]

If you go into the VBE whilst the wizard is still open you will see all the wizard items listed including (somewhere) the one you did as a screenshot.

Unfortunately for you, and deliberately by Microsoft, you can neither see the code used in the VBE nor the actual form design in the nav pane

So you'll just have to create your own form to mimic the wizard

 

[ytene]

Thank you !!! I think it is worth a try!!!

Before I posted my original request for help - i.e. first thing this morning - I'd pinged a more experienced friend for his advice. He responded with a link to this page:-

http://www.worldbestlearningcenter.com/index_files/Access-vba-loop-for-each.htm

which looks to replicate the functionality of that MS Wizard quite closely - even delivering more functionality that I need...

So between your kind reply and the above link, I might be in luck...

I'm going to tinker a bit and see where I get.

Thank you!

 

[isladogs]

Thanks for the link - I like it!

 

[ytene]

I just wanted to add a brief footnote for anyone tempted to try the sample solution at "worldbestlearningcenter.com", which is that namely there are a series of bugs in the on-screen text...

1. The author mixes the case between cbdData and CboData for the Combo box.
2. The code assumes that the 2 list boxes created on the form will be named List0 and List1, but this will not always be the case. List0 should be on the left.
3. The method showfield() needs to have an extra line inserted inside the For Each loop, just before the AddItem line, which needs to read,

List0.RowSourceType = "Value List"

4. When all this is done, and the form works as expected, You will see a list of tables in yor Combo Box, then for the selected row of the Combo Box you will see all the fields of that identified table in your List0 List box. You can move them one at a time or in bulk to the List1 item... However, the bottom 2 of the 4 buttons [to unselect content] will remain inactive until *all* the content has been moved left-to-right, from List0 to List1.

I haven't figured out where that bug resides as yet. If anyone wants to beat me to it, I'd be grateful!!!

 

[jdraw]

You may find this older free video by Datapig useful

 

[ytene]

OK, this is really (really) weird. I just took another look at the sample code from "worldbestlearningcenter.com" to see if I could spot an easy fix to the problem with having the action buttons enabled/disabled correctly (based on the population of content in the two list boxes).

There is a method offered for the sample form, CmdAddOne_Click(), which gets invoked when the "Add One" button is pressed. In original form, the method looks like this:-

Private Sub CmdAddOne_Click()

Dim lstdata0 As String
List1.RowSourceType = "Value List"
lstdata0 = List0.ItemData(List0.ListIndex)
Me.List1.AddItem lstdata0
SelectedItem1
Me.List0.RemoveItem (List0.ListIndex)
​

If Me.List0.ListCount = 0 Then

List1.SetFocus
Me.cmdaddone.Enabled = False
Me.cmdaddall.Enabled = False
Me.CmdBackOne.Enabled = True
Me.CmdBackAll.Enabled = True
​

End If
SelectedItem
​

End Sub


Looking at the code, the gap in the logic occurs when the number of items in List0 is checked against zero. Obviously, this is because both List0 and List1 can have non-zero values at a time when we would want both sets of buttons to be active. So ... I changed the logic of this Sub until it looks like this:-


Private Sub CmdAddOne_Click()

Dim lstdata0 As String
List1.RowSourceType = "Value List"
lstdata0 = List0.ItemData(List0.ListIndex)
Me.List1.AddItem lstdata0
SelectedItem1
Me.List0.RemoveItem (List0.ListIndex)

If Me.List0.ListCount > 0 Then

Me.CmdAddOne.Enabled = True
Me.CmdAddAll.Enabled = True
​

Else

List1.SetFocus
Me.CmdAddOne.Enabled = False
Me.CmdAddAll.Enabled = False
​

End If
If Me.List1.ListCount > 0 Then

Me.CmdBackOne.Enabled = True
Me.CmdBackAll.Enabled = True
​

Else

Me.CmdBackOne.Enabled = False
Me.CmdBackAll.Enabled = False
​

End If
SelectedItem
​

End Sub

Obviously this is very crude in execution, but it should provide a slightly more complete set of logic tests each time that someone pushes the "Add One" button. After making this change, I was expecting to see slightly different performance when I clicked the "Add One" button - but otherwise no other changes...

What I actually get is a completely functional form... In other words, all four buttons now work exactly as I would expect them to, even though I have only changed the logic for one of them. That doesn't make any sense, but I am sitting here looking at a working form!

Can anyone point to my stupidity and tell me what I missed (not that I'm complaining, you understand).

Unfortunately, this is only a part of the solution to my original challenge. If you recall my original post in this thread, I would like to use this particular control paradigm to help me manage the contents of what is essentially a "link table"...

Currently my plan is:-

1. Run a query that returns a list of all Roles that the nominated user does not have access to. Populate the "List0" form object with this content.
2. Run a second query that returns a list of all Roles that the nominated user *does* have access to. Populate the "List1" form object with this content.
3. Let the operator adjust role alignment using the controls on the form we've been experimenting with, but, in my "tweaked" version of the form, perhaps I can extend the logic in the i.e. "CmdAddOne" and "CmdBackOne" buttons that creates a link record [for the Add Button] or removes a link record [for the Back] button.
4. That leaves me with the task of creating a "BulkAdd" and a "BulkRemove" method - should not be too difficult - I can use a ForEach loop to iterate through the available roles, and trap the error if I try and create a record that already exists...
5. Final wrap-up will be to implement logic that warns the operator if the "Save" is implemented whilst the User has no group membership at all - because this will prevent the User from logging in at all...

There is one more feature which I need to add, which is to be able to designate a "Default Role"... Not sure if I am going to try and do that on this form or set up a dedicated one for that. On this form I'd need to find a way of populating another Combo box with whatever the contents of the List1 box happened to be at a given point in time... Again I can do this by extending the Add and Back methods.

Ah well, something to experiment with.

I would be incredibly grateful if anyone has any suggestions to make regarding elegant/efficient implementations of any of this.

Thank you.