Typo3 Developer: MS Access Tutorial - 4
Create a button to show usergroups stored in the fe_users:usergroup field of the Typo3 database.
Add a Command button to the form above the textbox. When the wizard appears, click "Cancel" to exit the wizard. Right click on the Command button to view the button's properties. Click on the "All" tab. Name the button "RefreshUsergroups" (without spaces) and set the caption to "Show Usergroups".
|
 |
In the properties panel, set the On Click event to Event Procedure and click the VB Editor button to edit the code. We are going to create three functions that will display the user's usergroup-list in the UsergroupList textbox.
Function 1. dehex: converts the usergroup-list from hexadecimal to ASCII values.
Public Function dehex(str As String) As String
' converts hexadecimal values into ASCII
Dim I As Long
For I = 1 To Len(str) Step 2
dehex = dehex & Chr( _
InStr(1, "0123456789ABCDEF", Mid(str, I, 1)) * 16 - 16 _
+ InStr(1, "0123456789ABCDEF", Mid(str, I + 1, 1)) - 1)
Next I
End Function
Function 2. ShowUsergroups: connects to the website fe_users:usergroups field using ADO and the MyOLEDB Provider.
Public Function ShowUsergroups()
' queries usergroup OLE field of website and converts it from
' hexadecimal. result in text box.
Dim groupsAsHex As String
Dim groupsAsASCII As String
Dim con As ADODB.Connection 'connect using ADO and MySqlPov
' which are both OLE-friendly
Set con = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
con.Open "Provider=MySqlProv;Data Source=typo3db_sitedb;
Password=xxxxx;User ID=typo3db_access;Location=NN.NN.NN.NN"
rs.Open "SELECT usergroup FROM fe_users WHERE
(((fe_users.uid)=" & [uid] & "));", con
groupsAsHex = rs!usergroup 'the result is a hexadecimal string
groupsAsASCII = dehex(groupsAsHex) 'call dehex function
[UsergroupList] = groupsAsASCII
AvailableUsergroups.Enabled = True 'enable the
'AvailableUsergroups listbox
End Function
Function 3. RefreshUsergroups_Click: Calls the ShowUsergroups function when the button is clicked.
Private Sub RefreshUsergroups_Click()
' on click calls the ShowUsergoups function
On Error GoTo Err_RefreshUsergroups_Click
Call ShowUsergroups
Exit_RefreshUsergroups_Click:
Exit Sub
Err_RefreshUsergroups_Click:
MsgBox Err.Description
Resume Exit_RefreshUsergroups_Click
End Sub
Add a label from the Toolbox with some instructions, and the usergroup management section of our form should look like this:
|
 |
Draw and color another rectangle. This will contain a listbox from which we can select available usergroups displayed with their uids and titles.
From the Toolbox draw a listbox on top of the rectangle. In the List Box Wizard set the listbox to draw from the fe_groups table.
|
 |
|
 |
Set the listbox to display the uid field and title field from the fe_groups table sorted in ascending order by uid.
|
 |
|
 |
Uncheck the Hide key column checkbox.
|
 |
Select the uid as the unique identifier for the records.
|
 |
We don't want to use the listbox to update records to Typo3 directly, so set the listbox to Remember the value for later use.
|
 |
Name the listbox label "AvailableUsergroups" and finish the wizard.
Right-click the listbox to view its properties. Make sure the name field shows the name to be "AvailableUsergroups".
Set the Multi Select property to "Extended". This will allow us to select multiple rows by holding down the Ctl key.
We need to add some code to the listbox, so set the After Update event to Event Procedure and click the VB Editor button.
|
 |
In the VB Editor create two functions for the listbox.
Function 1. AllSelectedData: displays the value of the selected row in the listbox by appending it to the list in the UsergroupList textbox.
Function 2. AvailableUsergroups_AfterUpdate: Calls the function AllSelectedData when selections are made to the AvailableUsergroups listbox.
Public Function AllSelectedData()
' displays value of selected row in list box by appending it in
' text box UsergroupList
Dim ctl As Control
Dim varItm As Variant, intI As Integer
Dim newGroup As Variant
Dim uText As Variant
Dim firstChar
Set ctl = AvailableUsergroups
For Each varItm In ctl.ItemsSelected
For intI = 0 To ctl.ColumnCount - 2 ' change integer to
' target uid or title
uText = ctl.Column(intI, varItm) 'selected row's index
'and verbage
Next intI
newGroup = newGroup & "," & uText 'append to variable.
'sep by comma.
firstChar = Left(newGroup, 1) ' return the first character
' if first char is comma remove it
If (firstChar = ",") Then newGroup =
Replace(newGroup, ",", "", 1, 1)
UsergroupList = newGroup 'add to text box
UpdateUsergroups.Enabled = True 'enable btn
Next varItm
End Function
Private Sub AvailableUsergroups_AfterUpdate()
' list box selections trigger function AllSelectedData
Call AllSelectedData
End Sub
Toggle back to MS Access. Add a label with instructions and to the rectangle, and this part of our form should look like the following.
|
 |