build a menu form

[northernbeaver]

I am trying to build a form that creates a command button for each employee in a table this is what I have so far.

Public Sub LoadMenu()
On Error GoTo Err_frmLoad_Click

'create a command button for each active employee listing 5 per row\
'that will open entry form and set the default employee to that employee

'declare dao envrionment and variables
Dim dbs As DAO.Database
Dim rstEmployee As DAO.Recordset
Dim frm As Form
Dim CurrentLeft As Double
Dim CurrentTop As Double
Dim ControlCount As Long
Dim frmWidth As Double
Dim frmHeight As Double
Dim ctlCommandBox As CommandButton

CurrentLeft = 0
CurrentTop = 0
ControlCount = 0

Set frm = Forms!frmMenu
Me.Width = 1.5
Me.Detail.Height = 0.3

frmWidth = frm.Width
frmHeight = Me.Detail.Height

Dim strSQL1 As String


strSQL1 = "select * from tblemployee where active = true"
Set dbs = CurrentDb()
Set rstEmployee = dbs.OpenRecordset(strSQL1)
On Error Resume Next
rstEmployee.MoveFirst
 On Error GoTo Err_frmLoad_Click
If Not (rstEmployee.BOF Or rstEmployee.EOF) Then
   
    Do Until rstEmployee.EOF
      Set ctlCommandBox = CreateControl(frm.Name, acCommandButton, acDetail, , , CurrentLeft, CurrentTop, 1.5, 0.3)
      ctlCommandBox.Caption = rstEmployee("firstname") & " " & rstEmployee("lastname")
      ControlCount = ControlCount + 1
      CurrentTop = CurrentTop + 5
      
        If ControlCount = 5 Then
            'move left over by 50
            CurrentLeft = CurrentLeft + 50
            CurrentTop = 0
            ControlCount = 0
            'make form wider
            frm.Width = frmWidth + 50
            
        End If
    rstEmployee.MoveNext
    Loop
    DoCmd.Restore
    frm.Requery
Else
    'no records found
End If

Err_frmLoad_Click:
    MsgBox Err.Description
    Exit Sub
End Sub

Now the error that im getting is " you must be in design view to add controls to forms" so how programaticly do I get into design view and then back?

 

[PHV]

You may try this:
DoCmd.RunCommand acCmdDesignView

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[northernbeaver]

Thanks PHV that worked like a charm!