Showing a form

[Barguast]

Urgh, surely this is a simple question, but after hours of trial and error / fruitless internet searching, I got no idea where I'm going wrong. All I want to do is load a form in VBA under MS Access...

Dim f As Form_f_employeeSelect
Set f = New Form_f_employeeSelect
    
f.Show vbModal, Me

Dim f As New Form_f_employeeSelect
    
f.Show vbModal, Me

Form_f_employeeSelect.Show vbModal, Me

None of those seem to work for me. I used to be pretty good with VB6, but this completely simple problem has me stumped.

[kaffenils]

Docmd.OpenForm "f_employeeSelect"

[dannymking]

Try..

DoCmd.OpenForm "f_employeeSelect", acNormal , or whatever the form is called in the access objects window..

To set it to be modal set that in the design view of the form, under the other tab in the properties window

[Barguast]

Thank you both. I take that is basically the only way to do this? You can't just open a form as you would in VB6?

[RobDog888]

Nope, that is the way to do it for an Access Form.

[kaffenils]

Thank you both. I take that is basically the only way to do this? You can't just open a form as you would in VB6?

There is one other way that I know of. You can declare a public variable of type Form.

public frm as Form

Then you can set frm to a new instance of an existing form.

set from= New Form_f_employeeSelect

The reason the frm variable has to be public is that if it is private or just dimmed within a function or sub it will be destroyed when the sub/function exists or the object holding the private variable is closed.

This method can be used to create multiple instances of the same form, even displaying different data. To do this you declare a public array of type form.

public frm() as Form

You will need to create a function that keeps track of how many items there are in the array so that when you add a new instance of a form to the array you know what position to put it in. When you need to reference a form, e.g. to set recordsource, you reference the form from the array.

frm(3).form.recordsource="select * from sometable"

[Barguast]

I've tried kaffenils example and I'm still having problems...

Public m_employeeSelect As Form

Private Sub cmdSelectEmployees_Click()

    Set m_employeeSelect = New Form_f_employeeSelect
    m_employeeSelect.Show vbModal

End Sub

When I run that, I get an 'Application-defined or object-defined error' run-time error on the 'm_employeeSelect.Show vbModal' line.

The reason I want to use this method is that I still want to be able to access the form after it has been closed so I can get some information about the state it was left in when it was closed (in this case, which employees were selected in a ListView control). As far as I can see you can't do this with the DoCmd.OpenForm method. Or can you?

[Barguast]

OK, looks like I might have to explain my situation better.

Basically, the form I want to load shows a list of names (with checkboxes) that are loaded from the underlying Access database. Once the user has selected the names, the form will close. However, since the form will still have the information of which names are selected, I still want to be able to access the form after it has closed. For example:

'Show the form and wait for it to close
f_employeeSelect.Show vbModal

'If the form was cancelled then exit
if (f_employeeSelect.CancelSelected ()) Then Exit Sub

'Get the list of names selected
Dim c as Collection
c = f_employeeSelect.GetNameList ()

As should be demonstrated by the above code, I simply want to be able to access the functions of the form after it has been closed so I can extract the information entered by the user whilst it was open.

Is this possible? Or am I just going about it all wrong?

[kaffenils]

An Access form doesn't have a Show method. Instead you must set the Visible property to True.

Public m_employeeSelect As Form
 
Private Sub cmdSelectEmployees_Click()
 
    Set m_employeeSelect = New Form_f_employeeSelect
    m_employeeSelect.Visible=True
End Sub

If you want to hide it you simply set the Visible property to False. Then you can still access it's properties and controls. Don't close it (which is done by setting the public variable to nothing) as the form and it's controls no longer will be accessible.

Set m_employeeSelect = Nothing

[kaffenils]

As should be demonstrated by the above code, I simply want to be able to access the functions of the form after it has been closed so I can extract the information entered by the user whilst it was open.

Is this possible? Or am I just going about it all wrong?

If you don't need to open multiple instances of a form, then there is no need to open it with the metod I descibed above (the one with a public variable holding the form).
To open the form once (as a dialog), you use

docmd.OpenForm "f_employeeSelect",,,,,acDialog

This will cause the code that opens the form to halt until the form is closed. The problem is that you cannot access the form after it is closed, but you can access it if hidden (Visible=False). The way to do it is to create a Ok and a Cancel button on the modal form. When the user clicks Cancel you close the form (Docmd.Close acForm, me.name) and when the user clicks Ok you hide the Form (Me.Visible=True).
The code that opened the form will now keep running, and the first thing you do is to check if the form f_employeeSelect is still open (which means that the user clicked Ok). To check if a form is open you use the following code

If SysCmd(acSysCmdGetObjectState, acForm, "f_employeeSelect") > 0 Then
    c = form_f_employeeSelect.GetNameList() ' I don't know what this function do...
    ' We must also close the form
    docmd.close acForm,"f_employeeSelect"
end if