Creating form from code

[Domac]

Hi ,

I am creating form from code using 'CreateForm' and 'CreateControl'
functions.
Everything works fine, as we plan to deploy MDE version soon, question is
will it work in MDE enviroment??

Thanks a lot.
Domac

[Allen Browne]

No. CreateForm won't work in an MDE.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Domac" <do...@domac.com> wrote in message
news:%23R5Wr1y...@TK2MSFTNGP02.phx.gbl...

[Domac]

Where to find list of MDE limitatons?

Thanks,
Domagoj

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
news:Oyx9$8yVHH...@TK2MSFTNGP05.phx.gbl...

[Domac]

Is there any way to create form and controls, wich is compatible with MDE?

[Douglas J. Steele]

There are some details at
http://msdn2.microsoft.com/en-us/library/aa189988(office.10).aspx (don't
work if you're not using Access 2002: that article applies to all versions)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Domac" <do...@domac.com> wrote in message

news:%23FDTf$zVHHA...@TK2MSFTNGP06.phx.gbl...

[Douglas J. Steele]

To be honest, it's very unusual to be creating forms and controls
programmatically in an application.

Why not explain why you feel it's necessary, and perhaps someone can suggest
an alternative?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Domac" <do...@domac.com> wrote in message

news:%236obkA0...@TK2MSFTNGP02.phx.gbl...

[David W. Fenton]

"Domac" <do...@domac.com> wrote in
news:#FDTf$zVHHA...@TK2MSFTNGP06.phx.gbl:

> Where to find list of MDE limitatons?

Well, if you'd bother to read the help file on what an MDE is, you
should have understood already *why* you can't create forms in an
MDE. Most of the "limitations" of an MDE flow directly from what an
MDE *is*, which is an Access project with the canonical code
stripped and only the compiled code left. This means that the Access
project is read-only throughout.

And all of that should be obvious from the definition of what an MDE
is. At least, for anyone who takes the time to think about it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Domac]

Here is situation :

Something about applivation : Application is developed for purpose of
manufacture menagment.
We are currently designing a module for check-list functionality, wich
consists of serie of properties and each of them is different data-type and
has different type of entry.
In practise , it looks like this:

PropertyName PropertyDataType
PropertyControl
WIDTH NUMBER TEXTBOX
HEIGHT NUMBER TEXTBOX
LENGHT NUMBER TEXTBOX
CHIPBOARD_DECOR TEXT COMBOBOX
CHIPBOARD_THICKNES NUMBER COMBOBOX

In real situation we have from 30 to 100 properties per product.
Idea is to enable design department to define properies and list of values
(for combobox properties) for each of product entity.
Different set of key properties is for wardrobe, table , laboratory table,
office closet.
So when sales personal create demand towards manufacturing department, they
must fill check-list to define each of key properties for product entity.
What is my problem?
I have to dynamically define a form depending on product entity user
selected, and i have achive it , but it must be compatible with MDE
deployment.
My solution is based on 'CreateForm' and 'CreateControl' functions, works
great ,each time i generate frmSubForm and display it throught SubForm
control.
Back few years, when I developed in VB6 i had comfort to dinamically create
controls from code using load function (if my memory works), in access i can
achive it only in design view using 'CreateControl' function.
Is there alternative ?
I have another idea, but I would like to avoide playing with visible
property.

Here is my code:

<CODE START>
On Error Resume Next

SubStavke.SourceObject = "frmProdajaPLIzradaSubPrazno"
DoCmd.DeleteObject acForm, "frmProdajaPLIzradaSub"

Dim frmTmp As Form

'Podesi svojstva forme u ovoj fazi
Set frmTmp = CreateForm

frmTmp.Section(acDetail).BackColor = Me.Section(acDetail).BackColor

Dim rstGrupe As DAO.Recordset

Dim ctlLabels() As Label
Dim ctlCombos() As ComboBox
Dim ctlTexts() As TextBox
Dim ctlLines() As Line

Dim lngLabelsCount As Long
Dim lngCombosCount As Long
Dim lngTextsCount As Long
Dim lngLinesCount As Long

Dim lngRazmak As Long 'Između kontrola!
Dim lngVertikalnoStanje As Long

Dim lngGrupeXOffset As Long
Dim lngSvojstvaXOffset As Long
Dim lngSvojstvaNazivWidth As Long

'Postavi uvlaku po x-u
lngGrupeXOffset = 500
lngSvojstvaXOffset = 500

'Postavi dimenzije
lngSvojstvaNazivWidth = 2500

'Vertikalni razmak između kontrola!
lngRazmak = 100

Dim strSvojstvaGrupe As String

strSvojstvaGrupe = "SELECT ProdajaPLSvojstvaGrupe_INT.SGrupaRb,
ProdajaPLSvojstvaGrupe_INT.SGrupaNaziv ,SGrupaId " & _
"FROM ProdajaPLSvojstvaGrupe_INT " & _
"GROUP BY ProdajaPLSvojstvaGrupe_INT.SGrupaRb,
ProdajaPLSvojstvaGrupe_INT.SGrupaNaziv,SGrupaId " & _
"ORDER BY ProdajaPLSvojstvaGrupe_INT.SGrupaRb;"

Set rstGrupe = CurrentDb.OpenRecordset(strSvojstvaGrupe, dbOpenSnapshot)

'Odma n a prvu grupu u listi
rstGrupe.MoveFirst

'Sve kreiraj pod generiranim imenom tipa "From1"

While Not rstGrupe.EOF = True

ReDim Preserve ctlLabels(lngLabelsCount)
ReDim Preserve ctlLines(lngLinesCount)
'Kreiraj kapcije za grupe svojstava
Set ctlLabels(lngLabelsCount) = CreateControl(frmTmp.Name, acLabel,
acDetail, , , lngGrupeXOffset, lngVertikalnoStanje, 1000, 300)
Set ctlLines(lngLinesCount) = CreateControl(frmTmp.Name, acLine,
acDetail, , , lngGrupeXOffset, lngVertikalnoStanje, 1000, 300)

'Svojstva labela!
ctlLabels(lngLabelsCount).Caption = rstGrupe!SGrupaNaziv
ctlLabels(lngLabelsCount).FontName = "Arial CE"
ctlLabels(lngLabelsCount).FontSize = 9
ctlLabels(lngLabelsCount).ForeColor = 16776960
ctlLabels(lngLabelsCount).FontBold = 800
ctlLabels(lngLabelsCount).SizeToFit
'Linija
ctlLines(lngLinesCount).Top = ctlLabels(lngLabelsCount).Top +
ctlLabels(lngLabelsCount).Height
ctlLines(lngLinesCount).Left = ctlLabels(lngLabelsCount).Left
ctlLines(lngLinesCount).Width = 5000
ctlLines(lngLinesCount).Height = 0
ctlLines(lngLinesCount).SpecialEffect = 2
'Pomakni brojače!
lngVertikalnoStanje = lngVertikalnoStanje + lngRazmak +
ctlLabels(lngLabelsCount).Height
lngLabelsCount = lngLabelsCount + 1
lngLinesCount = lngLinesCount + 1
'Pobroji svojstva za grupu sortirano po rb unosa!
Dim strSvojstvaZaGrupu As String
Dim rstSvojstvaZaGrupu As DAO.Recordset

strSvojstvaZaGrupu = "SELECT * FROM ProdajaPLSvojstva_INT WHERE
SvojstvoGrupaId=" & rstGrupe!SGrupaId & " ORDER BY SvojstvoRb"
Set rstSvojstvaZaGrupu = CurrentDb.OpenRecordset(strSvojstvaZaGrupu,
dbOpenSnapshot)

'Trći na prvi!
rstSvojstvaZaGrupu.MoveFirst

While Not rstSvojstvaZaGrupu.EOF = True
'NASLOVI SVOJSTAVA
'Prvo label zatim kontrola za unos!
ReDim Preserve ctlLabels(lngLabelsCount)

'Kreiraj kapcije za grupe svojstava
Set ctlLabels(lngLabelsCount) =
CreateControl(frmTmp.Name, acLabel, acDetail, , , lngSvojstvaXOffset,
lngVertikalnoStanje, lngSvojstvaNazivWidth, 300)

'Svojstva labela!
ctlLabels(lngLabelsCount).Caption =
rstSvojstvaZaGrupu!SvojstvoNaziv & " :"
ctlLabels(lngLabelsCount).FontName = "Arial CE"
ctlLabels(lngLabelsCount).FontSize = 9
ctlLabels(lngLabelsCount).ForeColor = RGB(255,
255, 255)
ctlLabels(lngLabelsCount).FontBold = 400
'ctlLabels(lngLabelsCount).SizeToFit
ctlLabels(lngLabelsCount).TextAlign = 3 'Right

'Pomakni brojače!
lngLabelsCount = lngLabelsCount + 1

'Kontrole za unos Select case
Select Case
(rstSvojstvaZaGrupu!SvojstvoUnosVrsta)

Case "UNOS"
'Textboxovi!
ReDim Preserve ctlTexts(lngTextsCount)

Set ctlTexts(lngTextsCount) =
CreateControl(frmTmp.Name, acTextBox, acDetail, , , lngSvojstvaXOffset,
lngVertikalnoStanje, 1500, 300)
ctlTexts(lngTextsCount).Left =
ctlLabels(lngLabelsCount - 1).Left + ctlLabels(lngLabelsCount - 1).Width +
50
ctlTexts(lngTextsCount).Width = 1500
ctlTexts(lngTextsCount).FontName =
"Courier New CE"
ctlTexts(lngTextsCount).FontSize = 9
ctlTexts(lngTextsCount).Name =
rstSvojstvaZaGrupu!SvojstvoNaziv

Select Case
(rstSvojstvaZaGrupu!SvojstvoTip)
Case "BROJ"
ctlTexts(lngTextsCount).DefaultValue
= 0
ctlTexts(lngTextsCount).Format =
"Standard"

Case "TEKST"
ctlTexts(lngTextsCount).DefaultValue
= ""
ctlTexts(lngTextsCount).Format =
""

End Select

lngTextsCount = lngTextsCount + 1

Case "LISTA"

'Textboxovi!
ReDim Preserve ctlCombos(lngCombosCount)

Set ctlCombos(lngCombosCount) =
CreateControl(frmTmp.Name, acComboBox, acDetail, , , lngSvojstvaXOffset,
lngVertikalnoStanje, 2000, 300)
ctlCombos(lngCombosCount).Left =
ctlLabels(lngLabelsCount - 1).Left + ctlLabels(lngLabelsCount - 1).Width +
50
ctlCombos(lngCombosCount).Width = 2000
ctlCombos(lngCombosCount).FontName =
"Courier New CE"
ctlCombos(lngCombosCount).FontSize = 9
ctlCombos(lngCombosCount).Name =
rstSvojstvaZaGrupu!SvojstvoNaziv
ctlCombos(lngCombosCount).RowSourceType
= "Table/Query"
ctlCombos(lngCombosCount).RowSource =
"SELECT Vrijednost FROM ProdajaPLSvojstvaListe_INT " & _
"WHERE
SvojstvoId=" & rstSvojstvaZaGrupu!SvojstvoId & " " & _
"ORDER
BY ItemId"

ctlCombos(lngCombosCount).LimitToList =
True

lngCombosCount = lngCombosCount + 1

End Select

'---------------------------------------------------------------------------------------------------------
lngVertikalnoStanje = lngVertikalnoStanje + lngRazmak +
ctlLabels(lngLabelsCount - 1).Height
rstSvojstvaZaGrupu.MoveNext

Wend

'----------------------------------------------------------------------

rstGrupe.MoveNext

Wend
'Finalne korekcije!

frmTmp.Section(acDetail).Height = frmTmp.Section(acDetail).Height + 200
frmTmp.DividingLines = False
frmTmp.NavigationButtons = False
frmTmp.RecordSelectors = False
frmTmp.ScrollBars = 2

Dim strFrmTmpName As String

strFrmTmpName = frmTmp.Name

DoCmd.Save acForm, frmTmp.Name
DoCmd.Close acForm, frmTmp.Name, acSaveYes
DoCmd.Rename "frmProdajaPLIzradaSub", acForm, strFrmTmpName

<CODE END>

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23noCTl0...@TK2MSFTNGP05.phx.gbl...

[Vladimír Cvajniga]

Is it possible to create an empty form (report) and use it somehow in MDE?
Ie. create controls, modufy class module code and run? IMHO, it would fix
some problems...

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku
news:Xns98E05B0C05D1Af9...@127.0.0.1...

[Vladimír Cvajniga]

IMHO, MS Acces limits are too low. 1000 objects and 2GB MDB file size is not
enough for large projects (nor programs, neither data storage!!!). Dynamic
objects (forms, reports, modules) or objects stored in a separate MDB could
solve this problem.

What I'm missing is a possibility to call a sub-project from the main
project. Main project should store common objects, functions & procedures,
sub-projects should store only its specific functions (procs, objects,
etc.).

Long time ago I was programming in Czech relation database system called PC
FAND. IMO, it was best DOS DB system ever. It's best feature was that it was
declaration-based. And it was possible to create REALLY HUGE structured
projects without any problem... well except RAM & HD limitations.

Vlado

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> píše v diskusním
příspěvku news:%23noCTl0...@TK2MSFTNGP05.phx.gbl...

[storrboy]

> IMHO, MS Acces limits are too low. 1000 objects and 2GB MDB file size is not
> enough for large projects (nor programs, neither data storage!!!).

Point me to a program (games don't count) that is over 2Gb in size
and
intended to be a desktop application. If you need more than 1000
objects
in one database file, you really shouldn't be designing it.

>Dynamic
> objects (forms, reports, modules) or objects stored in a separate MDB could
> solve this problem.

You just disputed your first argument.

> What I'm missing is a possibility to call a sub-project from the main
> project. Main project should store common objects, functions & procedures,
> sub-projects should store only its specific functions (procs, objects,
> etc.).

Because as others have pointed out, you haven't spent much time
reading
or planning.

[Vladimír Cvajniga]

Have you heard about accountings? Earnings? Properties? Complete information
systems that include oll of those plus many many more? :O

Or do you play games only? :D

Vlado

"storrboy" <stor...@sympatico.ca> píse v diskusním príspevku
news:1172243618.4...@a75g2000cwd.googlegroups.com...

[storrboy]

On Feb 23, 10:49 am, Vladimír Cvajniga <nos...@thank.you> wrote:
> Have you heard about accountings? Earnings? Properties? Complete information
> systems that include oll of those plus many many more? :O
>
> Or do you play games only? :D
>
> Vlado
>

> "storrboy" <storr...@sympatico.ca> píse v diskusním príspevkunews:1172243618.4...@a75g2000cwd.googlegroups.com...

>
> >> IMHO, MS Acces limits are too low. 1000 objects and 2GB MDB file size is
> >> not
> >> enough for large projects (nor programs, neither data storage!!!).
>
> > Point me to a program (games don't count) that is over 2Gb in size
> > and
> > intended to be a desktop application. If you need more than 1000
> > objects
> > in one database file, you really shouldn't be designing it.
>
> >>Dynamic
> >> objects (forms, reports, modules) or objects stored in a separate MDB
> >> could
> >> solve this problem.
>
> > You just disputed your first argument.
>
> >> What I'm missing is a possibility to call a sub-project from the main
> >> project. Main project should store common objects, functions &
> >> procedures,
> >> sub-projects should store only its specific functions (procs, objects,
> >> etc.).
>
> > Because as others have pointed out, you haven't spent much time
> > reading
> > or planning.

Or perhaps the designers of those upsize to something other than a
desktop application. Ask Microsoft, EBay or Indigo if they run off of
Access. There are bigger hammers for bigger jobs. Don't complain when
your 5 oz ball-peen dosen't drive a 12" stake.

[Tony Toews [MVP]]

Vladimír Cvajniga <nos...@thank.you> wrote:

>Is it possible to create an empty form (report) and use it somehow in MDE?
>Ie. create controls, modufy class module code and run? IMHO, it would fix
>some problems...

But you can't open an object in design view in an MDE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Vladimír Cvajniga]

I'm affraid you don't know what you're talking about... Sry, I must quit
that discussion.
V.

"storrboy" <stor...@sympatico.ca> píse v diskusním príspevku

news:1172246930....@a75g2000cwd.googlegroups.com...

[Vladimír Cvajniga]

Unfortunatelly, it's true. Can this be changed in future versions of Access?
What do you think?
Vlado

"Tony Toews [MVP]" <tto...@telusplanet.net> píse v diskusním príspevku
news:fsdut25m0nvu4vnh5...@4ax.com...

[Douglas J. Steele]

The most common solution is to create a form that has the maximum number of
textboxes that you'd need, and to toggle their visibility properties.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

(no private e-mails, please)

"Domac" <do...@domac.com> wrote in message

news:%23KTB4Y1...@TK2MSFTNGP03.phx.gbl...

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in
news:edwHLk1V...@TK2MSFTNGP06.phx.gbl:

> Is it possible to create an empty form (report) and use it somehow
> in MDE? Ie. create controls, modufy class module code and run?
> IMHO, it would fix some problems...

Create a generic form with controls and labels and at runtime, set
the recordsource, bind the controls to the appropriate fields,
change the labels and last position and resize the controls. That
will take care of your problem, though I can't for the life of me
figure out *why* you'd ever need to do such a thing.

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:#TwHBr1V...@TK2MSFTNGP05.phx.gbl:

> IMHO, MS Acces limits are too low. 1000 objects and 2GB MDB file
> size is not enough for large projects (nor programs, neither data
> storage!!!). Dynamic objects (forms, reports, modules) or objects
> stored in a separate MDB could solve this problem.

Or judicious use of forms and reports in a single Access project.

If you're really running into a problem like that, you can still use
library databases with your "extra" objects in those. It does cause
some issues for databound objects and can conceivable lead to
concurrency issues if you don't manage things correctly.

But I just don't see that the limits are problematic. I've never had
a front end over 50MBs (including embedded graphics!) nor one with
the number of objects that would come close to bumping up against
the limits.

Perhaps you're simply not re-using your forms and reports and
setting recordsources at runtime? If you do what lots of Access
novices do, and save a copy of a report for every single set of
criteria, you'll end up with way too many reports. I have plenty of
objects in my apps that dynamically change the recordsource at
runtime. Some even base themselves on different tables, depending on
the recordsource. And some hide/show/resize/move controls depending
on the context. I do this because I hate having two very similar
forms/reports that do slightly different things. I hate having to
maintain multiple versions of the same object, so instead, I make it
dynamic.

Perhaps that approach would relieve you of the need to have a
generic form.

[Vladimír Cvajniga]

As to me, I don't understand why MS has added those Create... functions to
Access. The only situation when I need to create a new form/report is when I
run out of resources (1000 objects). I NEVER use CreateForm/CreateReport.
When I'm in open MDB-project I often use design mode to create
reports/forms.

I have added "automatic reports" feature to my projects. They are based on
form's recordset. User has ability to select fields, sort, totals... plus
some more options (page breaks on group level, save/read automatic report
settings, etc.). To make it work I had to add a few template reports to each
of the project. Template reports contain "raw" controls and I "play games"
with this stuff.

The idea of automatic reports comes from Czech relational DB system PC FAND.
I just wonder why we don't have automatic reports in MS Access. :-/

BTW, I desperatly miss incremental search in MS Access, too. I had to create
one form plus some supporting code to have thic functionality in my
projects.

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E0B757679D7f9...@127.0.0.1...

[Vladimír Cvajniga]

Images

--------------------
Report templates:
http://img148.imagevenue.com/img.php?image=09743__AutoReport_Template1_418lo.jpg
http://img104.imagevenue.com/img.php?image=09787__AutoReport_Template2_454lo.jpg
http://img20.imagevenue.com/img.php?image=09776__AutoReport_Template3_418lo.jpg
http://img144.imagevenue.com/img.php?image=09754__AutoReport_WTemplate1_407lo.jpg
http://img21.imagevenue.com/img.php?image=09759__AutoReport_WTemplate2_327lo.jpg
http://img158.imagevenue.com/img.php?image=09789__AutoReport_WTemplate3_401lo.jpg

User interface form:
http://img137.imagevenue.com/img.php?image=09765__AutoReport_frm1_582lo.jpg
http://img14.imagevenue.com/img.php?image=09771__AutoReport_frm2_315lo.jpg

The report:
http://img161.imagevenue.com/img.php?image=09748__AutoReport_The_Report_401lo.jpg

--------------------

Incremental search:
http://img165.imagevenue.com/img.php?image=09782__IncrementalSearch_481lo.jpg

--------------------

I hope that now it'll be very easy to understand the ideas... and that MS
guys will do their best ASAP! ;-)

Vlado

"Vladimír Cvajniga" <nos...@thank.you> píse v diskusním príspevku
news:OoEuw5%23VHH...@TK2MSFTNGP04.phx.gbl...

[Vladimír Cvajniga]

In fact, as to front-end file size, there no storage problem... except 1000
objects limit!
But 2GB for data storage is too low... :-(

Also, I'd like to store common functions in main project where I'd create an
interface for calling sub-projects.

My A97 largest project so far is nearly 50MB (FE), but it contains ~800
class-module objects (forms, reports & code modules).

I re-use objects (as I did years ago in PC FAND), ei. I don't use different
form/report for different recordsets... I couln't live without that. ;-)
And without that I should have exceeded Access limits long time ago.

There are many "dynamics" in my projects. A few weeks ago I've started a new
structured project and it surely will exceed current Access limits.

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E0B85C43EF4f9...@127.0.0.1...

[Vladimír Cvajniga]

How do I use "library databases"? Never heard of this function.

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku
news:Xns98E0B85C43EF4f9...@127.0.0.1...

[storrboy]

On Feb 24, 8:28 am, Vladimír Cvajniga <nos...@thank.you> wrote:
> How do I use "library databases"? Never heard of this function.
>

That would be one of the games some of us play.
The idea is to use a database like a DLL, OCX or addin.

Take a bunch of the DB objects you use often and put them all together
in a database of thier own. Reference this new one in other projects
and use it's public objects and functions. It takes a bit of planning
but it can cut the size of a project and make your most used stuff
more portable. I believe one drawback is that both dbs must be in the
same format - mdb or mde. I don't think you can mix them.

[Vladimír Cvajniga]

I'm affraid you're wrong. MDBs can't be handled as DLLs or OCXs. I have no
experience with add-ins... but I think add-in would not meet my needs.

Vlado

"storrboy" <stor...@sympatico.ca> píse v diskusním príspevku
news:1172335758.6...@j27g2000cwj.googlegroups.com...

[Vladimír Cvajniga]

I have tried to reference to an MDB and I will test some tricks ASAP.
TYVM for the idea! It's new to me - haven't read about it in any Access
book. There are many restrictions when referencing to an MDB (MDE?) but it
should help a little. It will really need some planning...

Vlado
"Vladimír Cvajniga" <nos...@thank.you> píse v diskusním príspevku
news:%2309NRNE...@TK2MSFTNGP02.phx.gbl...

[storrboy]

On Feb 24, 1:41 pm, Vladimír Cvajniga <nos...@thank.you> wrote:
> I'm affraid you're wrong. MDBs can't be handled as DLLs or OCXs. I have no
> experience with add-ins... but I think add-in would not meet my needs.
>
> Vlado
>

> "storrboy" <storr...@sympatico.ca> píse v diskusním príspevkunews:1172335758.6...@j27g2000cwj.googlegroups.com...

> On Feb 24, 8:28 am, Vladimír Cvajniga <nos...@thank.you> wrote:
>
> > How do I use "library databases"? Never heard of this function.
>
> That would be one of the games some of us play.
> The idea is to use a database like a DLL, OCX or addin.
>
> Take a bunch of the DB objects you use often and put them all together
> in a database of thier own. Reference this new one in other projects
> and use it's public objects and functions. It takes a bit of planning
> but it can cut the size of a project and make your most used stuff
> more portable. I believe one drawback is that both dbs must be in the
> same format - mdb or mde. I don't think you can mix them.

Put some public functions in a DB, create a refernce in another and
call those functions. The comparison was in setting the reference and
using it's contents, not in specific usages.

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:OoEuw5#VHHA...@TK2MSFTNGP04.phx.gbl:

> I don't understand why MS has added those Create... functions to
> Access.

So you can create a database in code.

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:OoEuw5#VHHA...@TK2MSFTNGP04.phx.gbl:

> The idea of automatic reports comes from Czech relational DB
> system PC FAND. I just wonder why we don't have automatic reports
> in MS Access. :-/

The form wizard can create "automatic" reports. You can print a
table view, with lots of room for formatting if you want.

> BTW, I desperatly miss incremental search in MS Access, too. I had
> to create one form plus some supporting code to have thic
> functionality in my projects.

What is "incremental" search?

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:OCNxze$VHHA...@TK2MSFTNGP02.phx.gbl:

> Incremental search:
> http://img165.imagevenue.com/img.php?image=09782__IncrementalSearch
> _481lo.jpg

It's extremely easy to implement that in code. It's easy enough to
do with the built-in tools -- the FIND dialog has different sets for
whole field match, start of field, or anywhere in field; plus you
can type "H*" and find all recordsbeginning with H. And filtering
works the same way, with the ability to use the * wildcard (or ? or
@) in the built-in filtering mechanism without any need to code for
it.

That said, I wouldn't want to have to train my users on how to use
that generalized tool. Instead I'd do what you've done and program a
specific interface for them to accomplish what they need to do.
That's not an issue of built-in features being included or lacking
-- it's just a matter of choosing whether you want to train your
users on the built-in features or build something customized
specifically for their needs.

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:uN6Pao$VHHA...@TK2MSFTNGP06.phx.gbl:

> In fact, as to front-end file size, there no storage problem...
> except 1000 objects limit!

I've never gotten anywhere close to that in any of my projects.

> But 2GB for data storage is too low... :-(

Then don't use Jet to store your data -- use SQL Server or some
other server database.

> Also, I'd like to store common functions in main project where I'd
> create an interface for calling sub-projects.

Not sure what you mean, but you can do this quite with a library
database.

> My A97 largest project so far is nearly 50MB (FE), but it contains
> ~800 class-module objects (forms, reports & code modules).

Do you actually have code in all the forms/reports that have
modules? You might want to uncheck the HasModule property of
forms/reports that have no code. Dunno if this will reduce your
object count or not, but it's there.

> I re-use objects (as I did years ago in PC FAND), ei. I don't use
> different form/report for different recordsets... I couln't live
> without that. ;-) And without that I should have exceeded Access
> limits long time ago.
>
> There are many "dynamics" in my projects. A few weeks ago I've
> started a new structured project and it surely will exceed current
> Access limits.

I think you *must* be doing something wrong as I can't conceive of a
project that needs that many objects.

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:#09NRNEW...@TK2MSFTNGP02.phx.gbl:

> I'm affraid you're wrong. MDBs can't be handled as DLLs or OCXs. I
> have no experience with add-ins... but I think add-in would not
> meet my needs.

No, you're wrong. Like a DLL, you can use a library MDB/MDE as a
library of functions that can be called from another project by
setting a reference to it.

In regard to OCX's (or ActiveX controls), the comparison is not
quite so close, but you can have forms and reports in a library
database and use them from a database that has a reference to the
library database (or just calls the library databse directly). The
Access wizards are delivered in this format and the forms you see
onscreen when running wizards are not stored in your own MDB.

The Access Developers Handbook has extensive coverage of how to do
this kind of thing.

[Vladimír Cvajniga]

We are talking about forms and reports: CreateForm, CreateReport,
CreateControl, CreateReportControl. I could easily live without those
because I don't need them at all. I wonder if anybody uses these functions.

CreateDatabase, CreateTable, etc., is totally different kind of stuff. I use
them quite often.

Vlado
"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E1D45AC2FDCf9...@127.0.0.1...

[Vladimír Cvajniga]

Users edit recordsets based on queries. I never give user an option to edit
a table in table view. I'd like automatic report based on current form's
recordset. Imagine recordset for the following form:

http://img165.imagevenue.com/img.php?image=09782__IncrementalSearch_481lo.jpg

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E1D4833C6f99...@127.0.0.1...

[Vladimír Cvajniga]

There's some info about Incremental find @ Wikipedia:
http://en.wikipedia.org/wiki/Incremental_find.

Database incremental search:
http://www.usabilityfirst.com/glossary/term_918.txl
http://www.codeproject.com/vb/net/Incremental_search.asp

You could fine more about incremental search in Google:
http://www.google.co.uk/search?hl=en&q=what+is+%22incremental+search%22&meta=

Also see
http://img165.imagevenue.com/img.php?image=09782__IncrementalSearch_481lo.jpg.

I have to try Pinnacle Studio or Windows Screen Recorder so that I can
create some videos...

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E1D4833C6f99...@127.0.0.1...

[Vladimír Cvajniga]

There are plenty of forms/reports which have HasModule set to FALSE.

Example to call a sub-project in PC FAND:
Call(SubProject)
or
Call(SubProject,SpecificProcedureOrFunction)
The second one is simply SUPERB!!! In Access: I think it can be done via
reference to an appropriate MDE, but I think you must specify all data
connections to each sub-projects' databases from main project :-(.

In MS Access terms:
- I have a main project (Main.mde) for commonc functions & a dashboard
- I have several sub-projects (Sub1.mde, Sub2.mde, Sub3.mde) for specific
tasks
- I'd like to use Call "Sub1.mde" (imagine some kind of dashboard) from
within Main.mde; any sub-project can recognize & use all functions declared
in Main.mde... as well as tables (data connections), forms, reports, etc.
...

> I've never gotten anywhere close to that in any of my projects.

Have you ever programmed accountings & taxes & earnings & properties (&
more)? Can you imagine all of these in one project?

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E1D57E28C18f9...@127.0.0.1...

[Vladimír Cvajniga]

TYVM for your respond. I'll try to get The Access Developers Handbook. And
I'll try some referencing to see how it works. I'm affraid it will not cover
all my needs but I'd better try.
IMHO, all the developers' stuff (basic & advanced) should be a part of MS
Access Help system! :-/

Vlado

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku
news:Xns98E1D5EFDA977f9...@127.0.0.1...

[Vladimír Cvajniga]

Will surely try referencing! TYVM for driving me right way.

Vlado

"storrboy" <stor...@sympatico.ca> píse v diskusním príspevku

news:1172359585.6...@m58g2000cwm.googlegroups.com...

[Tony Toews [MVP]]

Vladimír Cvajniga <nos...@thank.you> wrote:

>Unfortunatelly, it's true. Can this be changed in future versions of Access?
>What do you think?

Highly unlikely. Not being able to open an object in design view is
why MDEs even exist.

[Tony Toews [MVP]]

Vladimír Cvajniga <nos...@thank.you> wrote:

>As to me, I don't understand why MS has added those Create... functions to
>Access.

MS needed those tools themselves for creating forms and reports using
the wizards. So they just made those available to the public.

[Tony Toews [MVP]]

Vladimír Cvajniga <nos...@thank.you> wrote:

>How do I use "library databases"? Never heard of this function.

See my Add-in Tips, Hints and Gotchas page at
http://www.granite.ab.ca/access/addins.htm

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:#TFm0MLW...@TK2MSFTNGP05.phx.gbl:

> We are talking about forms and reports: CreateForm, CreateReport,
> CreateControl, CreateReportControl. I could easily live without
> those because I don't need them at all. I wonder if anybody uses
> these functions.
>
> CreateDatabase, CreateTable, etc., is totally different kind of
> stuff. I use them quite often.

Well, it wasn't designed specifically for *you*. I'm sure there are
commands I never use that you use all the time (and vice versa), but
I'm not calling for the elimination of those commands just because I
don't use them.

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:#BKB0MLW...@TK2MSFTNGP05.phx.gbl:

> Users edit recordsets based on queries. I never give user an
> option to edit a table in table view. I'd like automatic report
> based on current form's recordset.

You can create a datasheet form that replicates the table view and
gives you the ability to do all sorts of additional things
(everything you can do in a form).

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:OXmr4hM...@TK2MSFTNGP06.phx.gbl:

> Example to call a sub-project in PC FAND:
> Call(SubProject)
> or
> Call(SubProject,SpecificProcedureOrFunction)
> The second one is simply SUPERB!!! In Access: I think it can be
> done via reference to an appropriate MDE, but I think you must
> specify all data connections to each sub-projects' databases from
> main project :-(.

No, actually, you don't need to do that at all. I call the Zoom
wizard in my apps (the same one that is called with Shift F2) and I
do it with no reference set using Application.Run.

> In MS Access terms:
> - I have a main project (Main.mde) for commonc functions & a
> dashboard - I have several sub-projects (Sub1.mde, Sub2.mde,
> Sub3.mde) for specific tasks
> - I'd like to use Call "Sub1.mde" (imagine some kind of dashboard)
> from within Main.mde; any sub-project can recognize & use all
> functions declared in Main.mde... as well as tables (data
> connections), forms, reports, etc.

Investigate Application.Run.

>> I've never gotten anywhere close to that in any of my projects.
> Have you ever programmed accountings & taxes & earnings &
> properties (& more)? Can you imagine all of these in one project?

I've dealt with parts of that and still can't imagine how you'd need
that mean objects.

[Vladimír Cvajniga]

Project scenario (based on file system, ie. sub-project is in an appropriate
sub-dir, but it can be re-directed through so called Catalog; as it works in
PC FAND !!!):
http://img7.imagevenue.com/img.php?image=84348_Project_122_146lo.jpg

If MS Access had this functionality I wouldn't need ANY references to
library databases... neither I would need ANY add-in!

I think that Application.Run can't do anything like that because it runs as
a separate task, ie. it can't see superior functions in main project.

Vlado

P.S. In PC FAND you can call a simple procedure in any sub-project from main
project! When you Call(SubProject,Procedure) it opens the sub-project. But
it runs only a bit of it's code.
Unfortunatelly, PC FAND has no Windows version. If PC FAND was transformed
for Windows I would never try MS Access.

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E2C2F463F5Cf9...@127.0.0.1...

[Vladimír Cvajniga]

I think I was right. I've tried Application.Run according to MS Access 2002
help. Application runs as a separate task. :-(

Vlado

"Vladimír Cvajniga" <nos...@thank.you> píse v diskusním príspevku
news:eHiQ9BZ...@TK2MSFTNGP03.phx.gbl...

[Vladimír Cvajniga]

One more thing:

When a sub-project is called both main project & sub-project should run as
one task. They should perform as one joined project! This is what you might
not understand. PC FAND's philosophy is a bit diffrent from MS Access's one.
:-)

Vlado

"Vladimír Cvajniga" <nos...@thank.you> píse v diskusním príspevku
news:eHiQ9BZ...@TK2MSFTNGP03.phx.gbl...

[James A. Fortune]

Vladimír Cvajniga wrote:
> We are talking about forms and reports: CreateForm, CreateReport,
> CreateControl, CreateReportControl. I could easily live without those
> because I don't need them at all. I wonder if anybody uses these functions.
>
> CreateDatabase, CreateTable, etc., is totally different kind of stuff. I
> use them quite often.

I am in the middle of using them to create a wizard to generate search
forms automatically right now. I want the wizard to allow the user to
select an Access table and some options and use the selections to create
three forms, e.g., for tblEmail frmEmailSearch, frmEmailResults,
frmEmailInfo. The first form is unbound and is used to create a dynamic
sql string that can be used to populate a read-only bound results form.
Once a record is selected from the results form, an info form (bound
or unbound depending on the selection) can be used to edit the record.
I'm almost done with the controls for the first search form. I have to
decide what to do if the table has more than 80 fields since non-date
fields are placed vertically and there's an integer twips limit on the
size of the form. Instead of using an API function so get text widths I
assume that field names are Camel Case (the user can resize the controls
afterward otherwise). I set up two textboxes for each date field. The
idea is to limit the date field to that range, but to select the exact
date if only the first textbox is filled in. I'll probably adjust the
basic idea for things like Y/N fields, but that will be after the basics
are in place. Here's the code so far:

Option Compare Database
Option Explicit

Private Sub cmdCreateSearchForms_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String
Dim tdf As TableDef
Dim idx As Index
Dim fld As Field
Dim ctl As Control
Dim strFieldNames() As String
Dim intFieldTypes() As Integer
Dim intFieldActSize() As Integer
Dim intNonKeyFields As Integer
Dim intI As Integer
Dim intJ As Integer
Dim strField As String
Dim boolFieldFound As Boolean
Dim strPrimaryKeyField As String
Dim strTable As String
Dim strForm As String
Dim ctnr As Container
Dim doc As Document
Dim boolSearchFormFound As Boolean
Dim boolResultsFormFound As Boolean
Dim boolInfoFormFound As Boolean
Dim Response As Variant
Dim strPrompt As String
Dim strTitle As String
Dim frmSearch As Form
Dim frmResults As Form
Dim frmInfo As Form
Dim strSearchForm As String
Dim strResultsForm As String
Dim strInfoForm As String
Dim strCreatedForm As String
Const FORMWIDTH = 7
Const VERTSPACING = 360
Const NORMALWEIGHT = 400
Const SEMIBOLD = 600
Const TEXTALIGNLEFT = 1
Const TEXTALIGNCENTER = 2
Const TEXTALIGNRIGHT = 3
Const SHADOWED = 4
Const NORMALBACKSTYLE = 1
Dim lngFormHeightTwips As Long
Dim intDateFields As Integer
Dim intNonDateFields As Integer
Dim lngTitleTwipsL As Long
Dim lngTitleTwipsT As Long
Dim lngTitleTwipsW As Long
Dim lngTitleTwipsH As Long
Dim lngCommandNewTwipsL As Long
Dim lngCommandNewTwipsT As Long
Dim lngCommandNewTwipsW As Long
Dim lngCommandNewTwipsH As Long
Dim lngCommandGoTwipsL As Long
Dim lngCommandGoTwipsT As Long
Dim lngCommandGoTwipsW As Long
Dim lngCommandGoTwipsH As Long
Dim lngCommandExitTwipsL As Long
Dim lngCommandExitTwipsT As Long
Dim lngCommandExitTwipsW As Long
Dim lngCommandExitTwipsH As Long
Dim lngLabelsRef0TwipsL As Long
Dim lngLabelsRef0TwipsT As Long
Dim lngLabelsRef0TwipsW As Long
Dim lngLabelsRef0TwipsH As Long
Dim lngComboboxRef0TwipsL As Long
Dim lngComboboxRef0TwipsT As Long
Dim lngComboboxRef0TwipsW As Long
Dim lngComboboxRef0TwipsH As Long
Dim dblLabelMaxW As Double
Dim dblDateLabelMaxW As Double
Dim dblComboboxMaxW As Double
Dim lngDateLabelDRTwipsL As Long
Dim lngDateLabelDRTwipsT As Long
Dim lngDateLabelDRTwipsW As Long
Dim lngDateLabelDRTwipsH As Long
Dim lngDateLabelFldTwipsL As Long
Dim lngDateLabelFldTwipsT As Long
Dim lngDateLabelFldTwipsW As Long
Dim lngDateLabelFldTwipsH As Long
Dim lngDateLabelStartTwipsL As Long
Dim lngDateLabelStartTwipsT As Long
Dim lngDateLabelStartTwipsW As Long
Dim lngDateLabelStartTwipsH As Long
Dim lngDateLabelEndTwipsL As Long
Dim lngDateLabelEndTwipsT As Long
Dim lngDateLabelEndTwipsW As Long
Dim lngDateLabelEndTwipsH As Long
Dim lngDateLabelRef0TwipsL As Long
Dim lngDateLabelRef0TwipsT As Long
Dim lngDateLabelRef0TwipsW As Long
Dim lngDateLabelRef0TwipsH As Long
Dim lngNudgeDateLabelDown As Long
Const CharToInches = 0.08

If IsNull(cbxTableName.Value) Then
MsgBox ("No table has been selected.")
Exit Sub
End If
'Check for the existence of a primary key
Set MyDB = CurrentDb
Set tdf = MyDB.TableDefs(cbxTableName.Value)
strPrimaryKeyField = ""
For Each idx In tdf.Indexes
If idx.Primary = True Then
'Make sure the type of the primary key is Long
'and that it is on a single field
strField = Right(idx.Fields, Len(idx.Fields) - 1)
boolFieldFound = False
For Each fld In tdf.Fields
If fld.Name = strField Then
boolFieldFound = True
strPrimaryKeyField = strField
Exit For
End If
Next fld
If boolFieldFound = True Then
If tdf.Fields(strField).Type <> dbLong Then
MsgBox ("The primary key must have a Long data type.")
Set tdf = Nothing
Set MyDB = Nothing
Exit Sub
End If
Else
MsgBox ("The primary key must be on a single field.")
Set tdf = Nothing
Set MyDB = Nothing
Exit Sub
End If
Exit For
End If
Next idx
Set tdf = Nothing
'Allocate VERTSPACING for total number of Non-Date and Non-PK fields
unless there are lots of date fields
intDateFields = 0
intNonDateFields = 0
Set tdf = MyDB.TableDefs(cbxTableName.Value)
'I should set up an array to store the field names and their types.
intNonKeyFields = tdf.Fields.Count - 1
ReDim strFieldNames(intNonKeyFields)
ReDim intFieldTypes(intNonKeyFields)
ReDim intFieldActSize(intNonKeyFields)

For Each fld In tdf.Fields
If fld.Name <> strPrimaryKeyField Then
If fld.Type = dbDate Then
intDateFields = intDateFields + 1
Else
intNonDateFields = intNonDateFields + 1
End If
strFieldNames(intDateFields + intNonDateFields) = fld.Name
intFieldTypes(intDateFields + intNonDateFields) = fld.Type
'Get the actual max size if it's a text field, otherwise use fld.Size
If fld.Type = dbText Then
strSQL = "SELECT Max(Len(Nz(" & fld.Name & ", ''))) AS MaxLen
FROM " & cbxTableName.Value & ";"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
intFieldActSize(intDateFields + intNonDateFields) = 0
If MyRS.RecordCount > 0 Then
MyRS.MoveFirst
intFieldActSize(intDateFields + intNonDateFields) = MyRS("MaxLen")
End If
MyRS.Close
Set MyRS = Nothing
Else
intFieldActSize(intDateFields + intNonDateFields) = fld.Size
End If
End If
Next fld
Set tdf = Nothing
'MsgBox ("Date Fields: " & intDateFields)
'MsgBox ("Non-Date Fields: " & intNonDateFields)
'Determine how large vertically to make the form; allow 1/2" for title
and lower command buttons
'Allow two lines for the Date label and sublabels
If intDateFields + 2 > intNonDateFields Then
lngFormHeightTwips = (intDateFields + 2) * VERTSPACING + 1440 + 0.5 *
1440 + 0.25 * 1440
Else
lngFormHeightTwips = intNonDateFields * VERTSPACING + 1440 + 0.5 *
1440 + 0.25 * 1440
End If
strTable = cbxTableName.Value
If Left(strTable, 3) = "tbl" Then
If Len(strTable) > 3 Then
strTable = Right(strTable, Len(strTable) - 3)
End If
End If
'Check for pre-existing forms: e.g., frmMyTableSearch (tblMyTable)
boolSearchFormFound = False
boolResultsFormFound = False
boolInfoFormFound = False
strSearchForm = "frm" & strTable & "Search"
strResultsForm = "frm" & strTable & "Results"
strInfoForm = "frm" & strTable & "Info"
For Each ctnr In MyDB.Containers
If ctnr.Name = "Forms" Then
For Each doc In ctnr.Documents
Select Case doc.Name
Case strSearchForm: boolSearchFormFound = True
Case strResultsForm: boolResultsFormFound = True
Case strInfoForm: boolInfoFormFound = True
End Select
If boolSearchFormFound = True And boolResultsFormFound = True And
boolInfoFormFound = True Then Exit For
Next doc
End If
Next ctnr
If boolSearchFormFound = True Then
strPrompt = strSearchForm & " already exists. Delete?"
strTitle = "Action Confirmation"
Response = MsgBox(strPrompt, vbOKCancel, strTitle)
If Response <> vbOK Then
Set MyDB = Nothing
Exit Sub
End If
DoCmd.DeleteObject acForm, strSearchForm
End If
If boolResultsFormFound = True Then
strPrompt = strResultsForm & " already exists. Delete?"
strTitle = "Action Confirmation"
Response = MsgBox(strPrompt, vbOKCancel, strTitle)
If Response <> vbOK Then
Set MyDB = Nothing
Exit Sub
End If
DoCmd.DeleteObject acForm, strResultsForm
End If
If boolInfoFormFound = True Then
strPrompt = strInfoForm & " already exists. Delete?"
strTitle = "Action Confirmation"
Response = MsgBox(strPrompt, vbOKCancel, strTitle)
If Response <> vbOK Then
Set MyDB = Nothing
Exit Sub
End If
DoCmd.DeleteObject acForm, strInfoForm
End If
DoEvents
Set frmSearch = CreateForm()
'Set basic form properties
frmSearch.HasModule = True
frmSearch.ViewsAllowed = 1 'Form
frmSearch.ScrollBars = 2 'Vertical Only
frmSearch.RecordSelectors = False
frmSearch.NavigationButtons = False
frmSearch.DividingLines = False
frmSearch.ShortcutMenu = False
frmSearch.Width = FORMWIDTH * 1440
frmSearch.Section(acDetail).Height = lngFormHeightTwips
strCreatedForm = frmSearch.Name
'Add Controls and code to the newly created form after it has been renamed
DoCmd.Close acForm, strCreatedForm, acSaveYes
Do While IsFormOpen(strCreatedForm)
DoEvents
Loop
Set frmSearch = Nothing
DoCmd.Rename strSearchForm, acForm, strCreatedForm
DoEvents
DoCmd.OpenForm strSearchForm, acDesign
DoEvents
lngTitleTwipsL = 2.5 * 1440
lngTitleTwipsT = 0.25 * 1440
lngTitleTwipsW = 2 * 1440
lngTitleTwipsH = 0.25 * 1440
lngCommandNewTwipsW = 0.875 * 1440
lngCommandNewTwipsH = 0.25 * 1440
lngCommandNewTwipsT = lngTitleTwipsT
lngCommandNewTwipsL = Int((lngTitleTwipsL - lngCommandNewTwipsW) / 2)
Set ctl = CreateControl(strSearchForm, acLabel, , "lblTitle", ,
lngTitleTwipsL, lngTitleTwipsT)
Call SetControlProperties(ctl, "lblTitle", 12, SEMIBOLD,
TEXTALIGNCENTER, lngTitleTwipsW, lngTitleTwipsH, "Search Form")
Set ctl = Nothing
Set ctl = CreateControl(strSearchForm, acCommandButton, acDetail, ,
"New", lngCommandNewTwipsL, lngCommandNewTwipsT)
Call SetControlProperties(ctl, "cmdNew", 10, SEMIBOLD, ,
lngCommandNewTwipsW, lngCommandNewTwipsH, "New")
Set ctl = Nothing
'Do any non-date fields first
'Allocate 1.05" for every 10 characters at 10 point
dblLabelMaxW = GetMaximumLabelSize(strFieldNames(), intFieldTypes(), 10,
"NonDate") 'Characters
'Convert to inches
dblLabelMaxW = dblLabelMaxW * CharToInches
lngLabelsRef0TwipsL = 0.5 * 1440
lngLabelsRef0TwipsT = 0.75 * 1440
lngLabelsRef0TwipsW = dblLabelMaxW * 1440
lngLabelsRef0TwipsH = 0.8 * VERTSPACING
dblComboboxMaxW = GetMaximumTextFieldSize(intFieldTypes(),
intFieldActSize(), 20) 'Characters
'Convert to inches
dblComboboxMaxW = dblComboboxMaxW * CharToInches
lngComboboxRef0TwipsL = lngLabelsRef0TwipsL + lngLabelsRef0TwipsW + 0.25
* 1440
lngComboboxRef0TwipsT = 0.75 * 1440
lngComboboxRef0TwipsW = dblComboboxMaxW * 1440
lngComboboxRef0TwipsH = 0.8 * VERTSPACING
If intNonDateFields > 0 Then
For intI = 1 To intNonKeyFields
If intFieldTypes(intI) <> dbDate Then
'Set up the label
Set ctl = CreateControl(strSearchForm, acLabel, , "lbl" &
strFieldNames(intI), , lngLabelsRef0TwipsL, lngLabelsRef0TwipsT + (intI
- 1) * VERTSPACING)
Call SetControlProperties(ctl, "lbl" & strFieldNames(intI), 10,
NORMALWEIGHT, TEXTALIGNRIGHT, lngLabelsRef0TwipsW, lngLabelsRef0TwipsH,
strFieldNames(intI))
'Set up the combobox
Set ctl = CreateControl(strSearchForm, acComboBox, , , ,
lngComboboxRef0TwipsL, lngComboboxRef0TwipsT + (intI - 1) * VERTSPACING)
Call SetControlProperties(ctl, "cbx" & strFieldNames(intI), 10,
NORMALWEIGHT, TEXTALIGNLEFT, lngComboboxRef0TwipsW, lngComboboxRef0TwipsH)
ctl.Properties("RowSource") = "SELECT DISTINCT " &
strFieldNames(intI) & " FROM " & cbxTableName.Value & " WHERE " &
strFieldNames(intI) & " IS NOT NULL ORDER BY " & strFieldNames(intI) & ";"
End If
Next intI
End If
'If date fields exist, set up some labels and textboxes for them
dblDateLabelMaxW = GetMaximumLabelSize(strFieldNames(), intFieldTypes(),
10, "Date") 'Characters
'Convert to inches
dblDateLabelMaxW = dblDateLabelMaxW * CharToInches
lngDateLabelDRTwipsL = lngComboboxRef0TwipsL + lngComboboxRef0TwipsW +
(0.5 + dblDateLabelMaxW) * 1440
lngDateLabelDRTwipsT = lngComboboxRef0TwipsT
lngDateLabelDRTwipsW = 1800
lngDateLabelDRTwipsH = 288
lngDateLabelFldTwipsL = lngComboboxRef0TwipsL + lngComboboxRef0TwipsW +
0.5 * 1440
lngDateLabelFldTwipsT = lngComboboxRef0TwipsT
lngDateLabelFldTwipsW = dblDateLabelMaxW * 1440
lngDateLabelFldTwipsH = 288
lngDateLabelStartTwipsL = lngComboboxRef0TwipsL + lngComboboxRef0TwipsW
+ (dblDateLabelMaxW + 0.8) * 1440
lngDateLabelStartTwipsT = lngComboboxRef0TwipsT
lngDateLabelStartTwipsW = 864
lngDateLabelStartTwipsH = 288
lngDateLabelEndTwipsL = lngComboboxRef0TwipsL + lngComboboxRef0TwipsW +
(dblDateLabelMaxW + 1.6) * 1440
lngDateLabelEndTwipsT = lngComboboxRef0TwipsT
lngDateLabelEndTwipsW = 864
lngDateLabelEndTwipsH = 288
lngDateLabelRef0TwipsL = lngComboboxRef0TwipsL + lngComboboxRef0TwipsW +
0.5 * 1440
lngDateLabelRef0TwipsT = lngComboboxRef0TwipsT
lngDateLabelRef0TwipsW = dblDateLabelMaxW * 1440
lngDateLabelRef0TwipsH = 0.8 * VERTSPACING
lngNudgeDateLabelDown = 30
If intDateFields > 0 Then
'Set up the four labels
'Date Ranges
Set ctl = CreateControl(strSearchForm, acLabel, , "lbl_DateRanges", ,
lngDateLabelDRTwipsL, lngDateLabelDRTwipsT)
Call SetControlProperties(ctl, "lbl_DateRanges", 10, SEMIBOLD,
TEXTALIGNCENTER, lngDateLabelDRTwipsW, lngDateLabelDRTwipsH, "Date
Ranges", NORMALBACKSTYLE, , SHADOWED, , 2)
'Field
Set ctl = CreateControl(strSearchForm, acLabel, , "lbl_Field", ,
lngDateLabelFldTwipsL, lngDateLabelFldTwipsT + VERTSPACING)
Call SetControlProperties(ctl, "lbl_Field", 8, SEMIBOLD,
TEXTALIGNCENTER, lngDateLabelFldTwipsW, lngDateLabelFldTwipsH, "Field",
NORMALBACKSTYLE, , SHADOWED, , 2)
'Starting
Set ctl = CreateControl(strSearchForm, acLabel, , "lbl_Starting", ,
lngDateLabelStartTwipsL, lngDateLabelStartTwipsT + VERTSPACING)
Call SetControlProperties(ctl, "lbl_Starting", 8, SEMIBOLD,
TEXTALIGNCENTER, lngDateLabelStartTwipsW, lngDateLabelStartTwipsH,
"Starting", NORMALBACKSTYLE, , SHADOWED, , 2)
'Ending
Set ctl = CreateControl(strSearchForm, acLabel, , "lbl_Ending", ,
lngDateLabelEndTwipsL, lngDateLabelEndTwipsT + VERTSPACING)
Call SetControlProperties(ctl, "lbl_Ending", 8, SEMIBOLD,
TEXTALIGNCENTER, lngDateLabelEndTwipsW, lngDateLabelEndTwipsH, "Ending",
NORMALBACKSTYLE, , SHADOWED, , 2)
intJ = 0 'Keep track of jth date field
For intI = 1 To intNonKeyFields
If intFieldTypes(intI) = dbDate Then
intJ = intJ + 1
'Set up the label
Set ctl = CreateControl(strSearchForm, acLabel, , "lbl_" &
strFieldNames(intI), , lngDateLabelRef0TwipsL, lngDateLabelRef0TwipsT +
(intJ + 1) * VERTSPACING + lngNudgeDateLabelDown)
Call SetControlProperties(ctl, "lbl_" & strFieldNames(intI), 8,
NORMALWEIGHT, TEXTALIGNRIGHT, lngDateLabelRef0TwipsW,
lngDateLabelRef0TwipsH, strFieldNames(intI), , , , , 2)
'Set up the date textboxes
Set ctl = CreateControl(strSearchForm, acTextBox, , , ,
lngDateLabelStartTwipsL, lngDateLabelRef0TwipsT + (intJ + 1) * VERTSPACING)
Call SetControlProperties(ctl, "txt" & strFieldNames(intI) &
"Start", 10, NORMALWEIGHT, TEXTALIGNLEFT, lngDateLabelStartTwipsW,
lngDateLabelRef0TwipsH)
Set ctl = CreateControl(strSearchForm, acTextBox, , , ,
lngDateLabelEndTwipsL, lngDateLabelRef0TwipsT + (intJ + 1) * VERTSPACING)
Call SetControlProperties(ctl, "txt" & strFieldNames(intI) &
"End", 10, NORMALWEIGHT, TEXTALIGNLEFT, lngDateLabelStartTwipsW,
lngDateLabelRef0TwipsH)
End If
Next intI
Set ctl = Nothing
End If
'Set up the final two command buttons
'The bottom 1/2" was saved for them, so for 1/4" height command buttons
centered vertically:
'top = lngFormHeightTwips - 0.375 * 1440
lngCommandGoTwipsL = lngTitleTwipsL - 0.75 * 1440 + lngTitleTwipsW / 2
lngCommandGoTwipsT = lngFormHeightTwips - 0.375 * 1440
lngCommandGoTwipsW = lngCommandNewTwipsW
lngCommandGoTwipsH = lngCommandNewTwipsH
lngCommandExitTwipsL = lngTitleTwipsL + 0.75 * 1440 + lngTitleTwipsW / 2
lngCommandExitTwipsT = lngCommandGoTwipsT
lngCommandExitTwipsW = lngCommandNewTwipsW
lngCommandExitTwipsH = lngCommandNewTwipsH
Set ctl = CreateControl(strSearchForm, acCommandButton, acDetail, ,
"Go", lngCommandGoTwipsL, lngCommandGoTwipsT)
Call SetControlProperties(ctl, "cmdGo", 10, SEMIBOLD, ,
lngCommandGoTwipsW, lngCommandGoTwipsH, "Go")
Set ctl = CreateControl(strSearchForm, acCommandButton, acDetail, ,
"Exit", lngCommandExitTwipsL, lngCommandExitTwipsT)
Call SetControlProperties(ctl, "cmdExit", 10, SEMIBOLD, ,
lngCommandExitTwipsW, lngCommandExitTwipsH, "Exit")
Erase strFieldNames
Erase intFieldTypes
Erase intFieldActSize
DoCmd.Restore
DoCmd.Close acForm, strSearchForm, acSaveYes
DoEvents
Set MyDB = Nothing
End Sub

Private Sub cmdExit_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Function GetMaximumLabelSize(strNames() As String, intTypes() As
Integer, intMinimum As Integer, strType) As Integer
Dim lngI As Long
Dim intMax As Integer

intMax = intMinimum
For lngI = 1 To UBound(strNames())
If strType = "Date" Then
If intTypes(lngI) = dbDate Then
If Len(strNames(lngI)) > intMax Then
intMax = Len(strNames(lngI))
End If
End If
Else
If intTypes(lngI) <> dbDate Then
If Len(strNames(lngI)) > intMax Then
intMax = Len(strNames(lngI))
End If
End If
End If
Next lngI
GetMaximumLabelSize = intMax
End Function

Private Function GetMaximumTextFieldSize(strTypes() As Integer,
strSizes() As Integer, intMinimum As Integer) As Integer
Dim lngI As Long
Dim intMax As Integer

intMax = intMinimum
For lngI = 1 To UBound(strTypes())
If strTypes(lngI) = dbText Then
If strSizes(lngI) > intMax Then
intMax = strSizes(lngI)
End If
End If
Next lngI
GetMaximumTextFieldSize = intMax
End Function

Private Sub Form_Load()
Dim MyDB As Database
Dim tdf As TableDef
Dim strList As String

'Create the list of tables
strList = ""
Set MyDB = CurrentDb
For Each tdf In MyDB.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
strList = strList & tdf.Name & ";"
End If
Next tdf
Set MyDB = Nothing
If Len(strList) > 0 Then strList = Left(strList, Len(strList) - 1)
cbxTableName.RowSourceType = "Value List"
cbxTableName.RowSource = strList
End Sub

Public Function IsFormOpen(strForm As String) As Boolean
Dim frmX As Form

IsFormOpen = False
For Each frmX In Forms
If frmX.Name = strForm Then
IsFormOpen = True
Exit For
End If
Next frmX
End Function

Private Sub SetControlProperties(ctl As Control, Optional strName As
String, Optional intFontSize As Integer, Optional intFontWeight As
Integer, Optional intTextAlign As Integer, Optional lngWidthTwips As
Long, Optional lngHeightTwips As Long, Optional strCaption As String,
Optional intBackStyle As Integer, Optional lngBackColor As Long,
Optional intSpecialEffect As Integer, Optional lngBorderColor As Long,
Optional intBorderWidth As Integer)
If Not IsMissing(strName) Then ctl.Properties("Name") = strName
If intFontSize <> 0 Then ctl.Properties("FontSize") = intFontSize
If intFontWeight <> 0 Then ctl.Properties("FontWeight") = intFontWeight
If intTextAlign <> 0 Then ctl.Properties("TextAlign") = intTextAlign
If lngHeightTwips <> 0 Then ctl.Properties("Height") = lngHeightTwips
If lngWidthTwips <> 0 Then ctl.Properties("Width") = lngWidthTwips
If strCaption <> "" Then ctl.Properties("Caption") = strCaption
If intBackStyle <> 0 Then ctl.Properties("BackStyle") = intBackStyle
If lngBackColor <> 0 Then ctl.Properties("BackColor") = lngBackColor
If intSpecialEffect <> 0 Then ctl.Properties("SpecialEffect") =
intSpecialEffect
If lngBorderColor <> 0 Then ctl.Properties("BorderColor") = lngBorderColor
If intBorderWidth <> 0 Then ctl.Properties("BorderWidth") = intBorderWidth
End Sub

James A. Fortune
MPAP...@FortuneJames.com

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:OKtrWWZW...@TK2MSFTNGP04.phx.gbl:

> I think I was right. I've tried Application.Run according to MS
> Access 2002 help. Application runs as a separate task.

Not if you're running a wizard/library database. For instance, to
call the Zoom box from the Utility wizard, you call this:

Application.Run("UTILITY.BuilderZoom", strObjName, _
strCtlName, strCurrValue)

(with appropriate values for the arguments, of course).

It doesn't run out of process, but within the current instance of
Access. Of course, you are also passing the data in and the control
names, and internally the code is doing what is necessary to update
the control you called it from.

But it *definitely* works. In fact, it's how Access launches its
wizards (which are really just MDEs written in VBA and Access).

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:ud1fKrZW...@TK2MSFTNGP05.phx.gbl:

> When a sub-project is called both main project & sub-project
> should run as one task.

When Access uses its built-in wizards and when I call the ZoomBox
function in the Utility wizard, it does not run out of process. Same
for when you create a reference to a library database.

So, I really don't know what you're talking about.

[Vladimír Cvajniga]

I'll have to check and see how I can handle database access. I think I
must define connections for all tables in main project instead of handling
them seperatly in "sub-project" FE DBs. I have tried to call a public sub in
a referenced MDB (sub-project) to run sub-project's main form but I've got
problems with data connections and with file system based operations. It
seems that all necessary files in sub-project's directory (external EXEs,
local DBs, etc.) should be moved to main project directory... due to CurDir,
CurrentDb.Name, and similar functions.
In other words: sub-project runs OK if it's run separately. From main
project I can't easily call a public function in a referenced MDB to run
the sub-project. It seems it's necessary to make many changes in sub-project
so that it could be run from main project.

Main project: c:\Documents and Settings\Vlado\Dokumenty\Dashboard.mdb.
Sub-project (FE): c:\Documents and Settings\Vlado\Dokumenty\_Moje
projekty\KEO\EO11.mdb
Sub-project FE handles two main databases, one on MySQL-server, another one
is BE MDB for EO11's data. I use several temporary databases for different
tasks which reside in EO11.mdb's dir ()c:\Documents and
Settings\Vlado\Dokumenty\_Moje projekty\KEO\.

EO11.mdb run fine if it's run separately.

1) I have added a public procedure to one of sub-project's modules:
Public Sub RunProject()
DoCmd.OpenForm "frm_Login"
End Sub

... and a function for testing:
Public Function fncCurrentDBname()
MsgBox CurrentDb.Name
End Function

2) I have created a new "main" project and added a reference to a
sub-project MDB.

3) I have created a "dashboard" form in main project with two buttons:
Private Sub btnEO11_Click()
EO11.modMain.RunProject
End Sub

It should open EO11.mdb's (there are some functions that work with
CurrentDB.Name on a start-up). See below.

Private Sub btnTest_Click()
EO11.modMain.fncCurrentDBname
End Sub

When I click btnTest I get a message-box with the following path:
c:\Documents and Settings\Vlado\Dokumenty\Dashboard.mdb

It's a path of main project MDB! But I'd expect
c:\Documents and Settings\Vlado\Dokumenty\_Moje projekty\KEO\EO11.mdb

So... I'm affraid I can't use this scenario. I may be doing something wrong.
But I can't imagine that should have to re-programm all file-system based
stuff. :O

And more... when I click btnEO11 I get run-time error 2485 concerning
menubar talking something about macro: Microsoft Access couldn't find macro
HobbyMenu.
- HobbyMenu is EO11's menubar.
- I never use macros.

Don't understand what I'm doing wrong. But I'm sure I can't use any
file-system-based functions in sub-project.

Vlado

P.S. Thenk you for your time.

"David W. Fenton" <XXXu...@dfenton.com.invalid> píse v diskusním príspevku

news:Xns98E3936E87B22f9...@127.0.0.1...

[David W. Fenton]

Vladimír Cvajniga <nos...@thank.you> wrote in

news:eusYrae...@TK2MSFTNGP06.phx.gbl:

> So... I'm affraid I can't use this scenario. I may be doing
> something wrong.

You can set the recordsource of the form loaded from a library
database at runtime, using the "IN 'database.mdb'" clause to set
which database the tables are drawn from. You would do this in the
form's OnOpen event. You wouldn't have any linked tables in your
library database at all.

I suggest that you attempt to acquire the Access Developers Handbook
which covers the whole process of using library databases in great
detail.