Opening a form in a library and retrieving a returned value

[KitaYama]

I'm studying the possible ways of adding an accdb (or accde) as a library to an existing database, opening a form that's located in the library, and returning the contents of a textbox on the form to the main database.

Any kind of reading material, advice or sample database is much appreciated.

What I've done so far:
1- I wasn't able to find a way to open the form directly from the main database. Is it possible?
2- I added a function to the library that opens the form. Then called this function from the main database. But now I'm stuck on how to return the value of a textbox to main database.
3- Because of the nature of the process, I can not use openform with acDialog parameter to freeze the code and then get the value on its close event.

Thanks again.

 

[MarkK]

If your library contains a function that returns a reference to the form, then consumers of your library can manipulate that reference.

From a public function in your library, you can return a public class that raises events. That class can open the form, handle that form's events, and then raise events in your FE that return the values or properties of that form. Then consumers of your library can handle those events.

 

[tvanstiphout]

Here is a quick and dirty example.
Compile testLib to .accde.
Open testApp, and fix up the reference to that library.
Run _modTestOnly.test1Form

 

[KitaYama]

and then raise events in your FE that return the values or properties of that form

I think it's the key to my problem. Finding a way that a class raise an event if FE.
Thanks. I'll research on this.

 

[KitaYama]

Here is a quick and dirty example.
Compile testLib to .accde.
Open testApp, and fix up the reference to that library.
Run _modTestOnly.test1Form

thanks.
Let me see what have you done.

 

[KitaYama]

Here is a quick and dirty example.
Compile testLib to .accde.
Open testApp, and fix up the reference to that library.
Run _modTestOnly.test1Form

@tvanstiphout
Million thanks. That works just fine.

 

[cheekybuddha]

Here is a link to some code that you can run, so that classes in your library can be instantiated using the 'New' keyword in you application code.

 

[KitaYama]

Here is a link to some code that you can run, so that classes in your library can be instantiated using the 'New' keyword in you application code.

Thanks. I'll check them too.

 

[DickyP]

All I'd say is that they are rereferred to as Code libraries for a reason. The received wisdom (according to the three Access tomes I still have specifically state this) is that only code Modules and Classes should be in Library database. Of course you can add forms, but to do so implies you are doing something application specific in a generic source.

 

[KitaYama]

All I'd say is that they are rereferred to as Code libraries for a reason. The received wisdom (according to the three Access tomes I still have specifically state this) is that only code Modules and Classes should be in Library database. Of course you can add forms, but to do so implies you are doing something application specific in a generic source.

While I understand your concern, but imagine you have an utility that you use in all your applications across the organization. Let's say a Calendar.
With every development of this utility, you have to copy the necessary form and modules to all your databases. If you can add it to a lib, you have only one place to update.
As I said in my first post, at this stage, it's only for educational purpose.
Out of interest, is there any specific reason that a library must only contains Modules and Classes?

thanks.

 

[GPGeorge]

While I understand your concern, but imagine you have an utility that you use in all your applications across the organization. Let's say a Calendar.
With every development of this utility, you have to copy the necessary form and modules to all your databases. If you can add it to a lib, you have only one place to update.
As I said in my first post, at this stage, it's only for educational purpose.
Out of interest, is there any specific reason that a library must only contains Modules and Classes?

thanks.

The only problem I can see with this approach is that any subscriber accdb which uses that common form has to conform to its functionality. If the library form changes in any relevant way, all subscriber accdbs have to be modified to match. I suppose you can guard against that with appropriate design, but speaking from personal experience, it is a factor to be accounted for.

 

[KitaYama]

I suppose you can guard against that with appropriate design

Yes, that's the main key to success.

 

[MajP]

This is relatively simple to do. Simply put a function in the library that opens the form and returns the value once the form is closed. Or do as Mark says.

 

[KitaYama]

Simply put a function in the library that opens the form and returns the value

That was my first impression, but later it appeared to be not possible. (or at least with my knowledge.)
As I said, the form can not be opened as AcDialogue to pause the code.
So the function is called, the form opens, and the function runs to the end, and the form stays open.
At this step, there is nothing that the function can return, because still the user has done nothing in the form.

@tvanstiphout solution in #3 showed me how to use a class to solve the problem.

 

[MajP]

Simple example of opening a Calendar in the external db.

Function in library. (The calendar has an OK and Cancel. The OK button hides the form, cancel closes it. This idea works on any pop up)

Public Function GetMonthYear(Optional DefaultYear As Integer, Optional DefaultMonth As Integer, Optional YearRangeStart As Integer, Optional YearRangeEnd As Integer) As Date
  If DefaultYear = 0 Then DefaultYear = Year(Date)
  If DefaultMonth = 0 Then DefaultMonth = Month(Date)
  If YearRangeStart = 0 Then YearRangeStart = 2000
  If YearRangeEnd = 0 Then YearRangeEnd = 2050
  DoCmd.OpenForm "monthyearpicker", , , , , acDialog, DefaultYear & ";" & DefaultMonth & ";" & YearRangeStart & ";" & YearRangeEnd & ";"
  If CodeProject.AllForms("monthYearpicker").IsLoaded Then
    GetMonthYear = Forms("monthyearpicker").txtYearMonth
    DoCmd.Close acForm, "monthYearPicker"
  End If
End Function

Super easy to return value from external popups using this method.

 

[KitaYama]

Simple example of opening a Calendar in the external db.

Function in library. (The calendar has an OK and Cancel. The OK button hides the form, cancel closes it. This idea works on any pop up)

Public Function GetMonthYear(Optional DefaultYear As Integer, Optional DefaultMonth As Integer, Optional YearRangeStart As Integer, Optional YearRangeEnd As Integer) As Date
  If DefaultYear = 0 Then DefaultYear = Year(Date)
  If DefaultMonth = 0 Then DefaultMonth = Month(Date)
  If YearRangeStart = 0 Then YearRangeStart = 2000
  If YearRangeEnd = 0 Then YearRangeEnd = 2050
  DoCmd.OpenForm "monthyearpicker", , , , , acDialog, DefaultYear & ";" & DefaultMonth & ";" & YearRangeStart & ";" & YearRangeEnd & ";"
  If CodeProject.AllForms("monthYearpicker").IsLoaded Then
    GetMonthYear = Forms("monthyearpicker").txtYearMonth
    DoCmd.Close acForm, "monthYearPicker"
  End If
End Function

Super easy to return value from external popups using this method.

Thanks for your solution. I'm at home and don't have Access here.
I'll take a look tomorrow morning when I'm back to work.

But from your code above, as I explained, I can not use acDialog to open the form.
thanks for your time.

 

[MajP]

Standard method in library to open any form
See both the dialog and non dialog versions.

Public Function openForm(formName, Optional view As AcFormView = acNormal, Optional filterName, Optional whereCOndition, Optional datamode As AcFormOpenDataMode = acFormPropertySettings, Optional windowMode As AcWindowMode = acWindowNormal, Optional openArgs) As Access.Form
    DoCmd.openForm formName, view, filterName, whereCOndition, datamode, windowMode, openArgs
    Set openForm = Forms(formName)
End Function

Non dialog version

Public WithEvents FrmMonthYear As Access.Form
'****************************************************************************************************************************************************************
'-----------------------------------------------------------------------------------   No Dialog   -------------------------------------------------------------
'*****************************************************************************************************************************************************************
Private Sub cmdLibraryNoDialog_Click()
Set FrmMonthYear = calendarlibrary.OpenForm("MonthYearPicker_NoDialog")
FrmMonthYear.OnClose = "[Event Procedure]"
End Sub

Private Sub FrmMonthYear_Close()
  'Allows for OK / Cancel. Put a property in the class if the OK button was selected
If FrmMonthYear.ok_Selected Then 'custom property
   Me.txtMonth = FrmMonthYear.txtYearMonth
   Me.txtYear = FrmMonthYear.txtYearMonth
End If
End Sub

I added a property in the form to check if OK was selected.[/ICODE]

 

[Josef P.]

An ‘EventBridge’ would allow late binding.

Class:

Public Event Commit(ByVal NewValue As Variant)
Public Event Canceled()

Public Sub RaiseCommit(ByVal NewValue As Variant)
   RaiseEvent Commit(NewValue)
End Sub

Public Sub RaiseCanceled()
   RaiseEvent Canceled
End Sub

Init a reference from "App" to "Lib". The lib code call the methods RaiseCommit/RaiseCanceled. "App" is listen to events.

 

[DickyP]

A number of thoughts on putting forms in Library Databases, a bit randomly presented;

Generally:

First of all - if it works for you great, but you won't find me doing it.

Secondly remember Forms are classes. Indeed in Access 95 for members with long enough memories we used hidden forms as classes, until they were introduced properly in Access 97. If you use the code alluded to by Kitayama above to expose classes in Libraries (which I've used for years) you can open the form in your library by merely instancing it.

Next the advice in my various Access tomes suggest that library databases should be co-located with the main FE.

Lastly you'll find that if you use a .accde as your library you'll have to recompile your front end anyway if you produce a new version of the Library.

In The particular case cited, I'd be intrigued to know why the form can't be opened in dialog mode? Makes things difficult to return any meaningful value if you are using the form in an asynchronous manner, as your code that called it will have moved on. Indeed you could have multiple version of the Library form open at once.

 

[KitaYama]

if it works for you great, but you won't find me doing it.

I expected a technical reason. It seems that's a matter of personal preference.

Next the advice in my various Access tomes suggest that library databases should be co-located with the main FE.

Will I be faced problems if I use the add-in located in a server, where all FEs can use it?
If there's any problem in this, I can save the add-in with FE, then change FE's update procedure to update the add-in for each user too.

Lastly you'll find that if you use a .accde as your library you'll have to recompile your front end anyway if you produce a new version of the Library.

I'll check this. Since I was testing accdb add-ins, a re-opening of the FE seemed to be enough.
Thanks for the warning.

I'd be intrigued to know why the form can't be opened in dialog mode?

Many reasons can be thought.
Since a form is a class, you may need to do some sort of tasks in this class after being instantiated (opened)
Filling some public properties (set/let/get) to let the form does its job, setting default values for some controls, changing its record source, moving to a specific record, changing controls properties, adding conditional formatting via code, .....
Someone with your long carrier in Access, can think of more reasons.

Of course some of above cases can be solved by concatenating the necessary parts into a string and passing it as OpenArg to the class, but then since a string is passed, you have trouble with data types, and you also need to parse the concatenated string to isolate the necessary parts and work on them.

Join -> Pass -> dissolve -> use.
To me the first three steps can be omitted. Simply open the form in non-dialoge mode, then do what ever you like with the opened form.


Thanks for sharing your thoughts.