Showing a form object

[hbrems]

Hi,

I'm trying to create a new instance of a form, add a property (class object) to it and make it visible.

The best I've done is the code below. But this only shows the form for as long as the code runs. In other words, the form becomes visible and disappears again immediately.

Private Sub btnEditCountry_Click()

Dim c As clsCountry

On Error GoTo ErrorHandler
    
    ' Instantiate country object
    Set c = New clsCountry
    
    ' Set country properties
    c.CountryID = 5
    c.CountryName = "Belgium"
    c.ShortCode = "BE"
        
    ' Open edit form
    With New Form_frmEditCountry
        .Country = c
        .Visible = True
    End With

Exit Sub

ErrorHandler:
    MsgBox "Something went wrong: " + Err.Description
    
End Sub

I don't see any form.Show() method. I don't think DoCmd.OpenForm() is an option unless something like this

Dim f As Form
Set f = New Form
DoCmd.OpenForm f

But that doesn't work of course. Any other ideas?

 

[DCrake]

So basically you have a form and you want to open it with a predefined selection made in a control?

What is the intention of the exercise?

 

[hbrems]

Hi and thanks for taking interest.

The idea is that if I would select multiple countries (fe. from a listbox), I'd be able to show multiple instances of the same form, but each with their own country objects in the background.

On each 'frmEditCountry' form I'd be able to edit the country details and call the object's "Update" method which will do the necessary to store the updated country in the table.

Sample code of 'frmEditCountry' form:

Private pCountry As clsCountry

' Country GET
Public Property Get Country() As clsCountry
    Country = pCountry
End Property

' Country SET
Public Property Let Country(Country As clsCountry)
    Set pCountry = Country
End Property

Private Sub btnSave_Click()
     pCountry.Update
End Sub

I hope that it makes sense. I know what I'm trying to do is probably not "the Access way", but I'm just experimenting with classes and objects.

 

[vbaInet]

Think - scope of form variable.

Edit: This was in response to your original post by the way.

 

[DCrake]

If I remember correctly MS has a solutions accdb/mdb that shows how to open multiple instances of the same form. Have a look on their KB for it.

 

[hbrems]

Think - scope of form variable.

That's the essence of the question. How to make the form exist beyond the scope of the sub.

Well, here's what I've come up with. In the main form I've added a collection to hold the newly created editForms

Private editForms As Collection

Private Sub btnEditCountry_Click()

Dim c As clsCountry
Dim f As Form_frmEditCountry

On Error GoTo ErrorHandler
    
    ' Instantiate country object    
    ' Set country properties
    
    ' Open edit form
    Set f = New Form_frmEditCountry
    f.Country = c
    f.Initialize ' This is a sub that will populated the fields based on the object
    f.Visible = True
    editForms.Add f

End Sub

 

[vbaInet]

Yep, you're on track.

How's that working for you so far?

Having different instances of a form just for updating purposes seems like an overkill. Have you thought about looping through the listbox and run an UPDATE query to get it to update per country?

 

[hbrems]

How's that working for you so far? Having different instances of a form just for updating purposes seems like an overkill.

I'm just toying with objects and new ways of doing things. I thinking: should my form really make sql statements? Depends on the situation of course, but in the first place I see a form's purpose as a means to interact with the user.

In this case my form allows the user to select countries and show edit forms. The edit form receives the country as an object and stores it in a private member.

When the user makes changes to the text boxes on the edit form he is actually making immediate changes to the object.

When the user clicks on the 'Save' button he actually calls the update() method of the object.

The update method of the object check if the information in the object is complete. Then the object talks to a repository object which generates SQL statements to update the table in the DB. (Since a country cannot update a database, a country repository object however can).

In summary:
- Form interacts with user
- Objects hold information and have their own methods
- Repositories store and retreive info from the DB

Does this sound crazy?

 

[vbaInet]

Sounds plausible but to be honest, having several instances of a form pop-up as a means of editting data will look too cumbersome on the screen display and they may end up getting confused. A neat form with a subform, where the subform is used for editting a country - one at a time - seems to be a more intuitive interface.

If it was going to be two or three instances of the form then it may work.

- Repositories store and retreive info from the DB

Note here that in a multi-user environment any changes made by another user will not reflect in the values the class objects hold. Take this scenario:
1. Record A is opened by User 1
2. Record A is opened by User 2
3. Record A is deleted by User 1
4. Record A is editted and saved by User 2 - error occurs

I suppose you can trap this DAO error.

Does this sound crazy?

Yes! Just joking.

 

[hbrems]

Having several instances of a form pop-up as a means of editing data will look too cumbersome on the screen display.

Haha, don't focus so much on how I'm updating my silly countries. In this example the selection might be limited to 1 country at a time (how often does a country name change anyway). But I'm sure you can imagine situations where opening similar forms with different data behind might be useful. For example when tracking stock information, or comparing 2 cars you'd like to buy... Stuff like that.

Note here that in a multi-user environment any changes made by another user will not reflect in the values the class objects hold.

Isn't that true for any object oriented application?

 

[vbaInet]

But I'm sure you can imagine situations where opening similar forms with different data behind might be useful. For example when tracking stock information, or comparing 2 cars you'd like to buy... Stuff like that.

If you look at Comparison websites, what you have is one window with the information of both (i.e. if comparing two) cars laid side-by-side. We can mimic this layout by using subforms which becomes visible when necessary. Remember, a subform is also an instance of a form.

Isn't that true for any object oriented application?

VBA does support object orientation but not fully. It's just in there for completeness. With bound forms the Engine handles updating of data and hence, displays new information when needed. You could try and replicate this by creating a Refresh function that refreshes the record every 60 seconds for example. But will it be worth the hassle?