Thinking about an unbound data entry form...

[Fred Boer]

Hello:

I'd like some advice! I am thinking about possibly using an unbound form for
data entry, and I don't know if it would be a good idea or not.

This is for a library database. Currently I have a bound data entry form
with 3 subforms. I use a tab control, with book information on the first
tab, an author information subform on the second tab, a subject heading
subform on a third tab, and a series subform on the fourth tab. (See below
for table structures.)

The current data entry process involves entering all "book" information into
the main form. After this record is saved, I can then enter Author, Subject
and Series information. This works well enough, I suppose, and the tab
control makes it nice and tidy. However, this data entry process is somewhat
cumbersome and non-intuitive. When cataloguing a book, one would naturally
expect to enter the title, then author, then subject, then series, then book
details. This sequence is not possible, however, because the record for book
information must be completed and saved before entering Author, Subject,
etc.

I was thinking that maybe if I had an unbound data entry form, I could enter
all the data in whatever sequence I liked, and then, on saving the record, I
could take the data, create the main record, save it, then insert the
related information. However, I have never used an unbound data entry form,
and don't know if this is a reasonable idea. Advice?

One particular issue might revolve around books with multiple authors. The
unbound form would have to permit the entry of more than one author name. I
suppose multiple author names might be stored in a temporary table?

Thanks!
Fred Boer

Tbl_Library

Book_ID
Title
Publisher
Format
Binding
Dewey #
etc...

Tbl_Author

Author_ID
AuthorFirstName
AuthorLastName
etc.

Tbl_BookAuthor

Book_ID
Author_ID

And similar junction tables for Subject and Series headings.

 

[Klatuu]

Fred,

I typically use unbound forms for data entry. They do take a lot more VBA
coding that bound forms. As I read your post, I was thinking this is not so
hard. Based on your description, I am guessing you have a book table, an
author table, a series table, and a subject table.

In this case, you would need to put code in your app to query the
appropriate table (Subject) to see if it existed or not and take the
appropriate action. The difficulty came in when you talked about multiple
authors. What I have done, is still use the subforms with datasheet layout
where there is more than one child, with an ADD command button. The only
issue here is that you would still need whatever values required to to link
the records. If because they way you want to enter the data does not support
that, then a tempory table would be the way to go.

For performance sake and to prevent conflicts in a multi user environment, I
put the temporary tables like that in the front end. I also delete the data
before I start a new parent, and after I am finished with that parent for
safety sake, and to keep the fe size down. I also always do a compact on
close to keep the slop to a minimum.

 

[Dirk Goldgar]

Hello:

I'd like some advice! I am thinking about possibly using an unbound
form for data entry, and I don't know if it would be a good idea or
not.

This is for a library database. Currently I have a bound data entry
form with 3 subforms. I use a tab control, with book information on
the first tab, an author information subform on the second tab, a
subject heading subform on a third tab, and a series subform on the
fourth tab. (See below for table structures.)

The current data entry process involves entering all "book"
information into the main form. After this record is saved, I can
then enter Author, Subject and Series information. This works well
enough, I suppose, and the tab control makes it nice and tidy.
However, this data entry process is somewhat cumbersome and
non-intuitive. When cataloguing a book, one would naturally expect to
enter the title, then author, then subject, then series, then book
details. This sequence is not possible, however, because the record
for book information must be completed and saved before entering
Author, Subject, etc.

I was thinking that maybe if I had an unbound data entry form, I
could enter all the data in whatever sequence I liked, and then, on
saving the record, I could take the data, create the main record,
save it, then insert the related information. However, I have never
used an unbound data entry form, and don't know if this is a
reasonable idea. Advice?

One particular issue might revolve around books with multiple
authors. The unbound form would have to permit the entry of more than
one author name. I suppose multiple author names might be stored in a
temporary table?

Thanks!
Fred Boer

Tbl_Library

Book_ID
Title
Publisher
Format
Binding
Dewey #
etc...

Tbl_Author

Author_ID
AuthorFirstName
AuthorLastName
etc.

Tbl_BookAuthor

Book_ID
Author_ID

And similar junction tables for Subject and Series headings.

Hi, Fred -

It's perfectly reasonable, in principle, to use unbound forms for data
entry. However, your need to record a one-to-many relationship makes
this more complicated. Klatuu's suggestion of using a form and subforms
bound to temporary or "work" tables is probably the way I would go, *if*
I didn't want to use forms bound directly to the real tables. Note that
the work tables don't have to have relationships enforced, so you can
let the user enter data freely in all the forms and then, when the "make
it so" button is clicked, validate everything, fix up the linking
fields, and copy the records into the real tables.

On the other hand, you may be able to avoid the need to take this
approach, and stick with the bound forms you're currently using, if you
relax the requirement that all fields on the main, "book information"
form be filled in before entering records in the subform. After all,
all you really need is for there to *be* a main form record that can be
saved. Do you have an absolute requirement that all the book details be
entered before the book record is saved? What's to stop you from
setting up a form and subforms that will let you enter just the title of
the book, then the authors (forcing the Tbl_Library record to be saved
with the title), then the subjects and series information, and then
continuing to fill in the rest of the book details? Sure, you end up
with the Tbl_Library record being saved twice -- once incomplete, once
complete -- but where's the harm in that?

If you have a requirement that you cannot have a record in the table
without all those details, you won't be able to get use the database
engine to enforce it, using this approach, because you have to allow
some of those fields to be Null when the record is first saved. That is
a drawback, since I prefer to have the database engine enforce business
rules where possible. However, you can have your form validate the
table in its Unload event, and/or in its Current event, bringing you
back to edit any record that is incomplete, thus enforcing the business
rule in code, allowing you to be a bit more flexible at some small cost
in reliability.

 

[Fred Boer]

Thanks! I think I see what you mean about the need for temporary table(s). I
am also beginning to see that this approach would involve "a lot more VBA"
coding. Might be entertaining to try it and see how it works.

Cheers!
Fred

 

[Fred Boer]

Hi Dirk:

I've spent a few hours experimenting with an unbound form since I originally
posted. I've begun to create an unbound data entry form, using a temp table
for Author entry. I'm able to enter book information, and multiple authors.
I haven't gotten to the point of actually creating the "validating, fixing
and copying" code yet, but I'm beginning to see that there will a certain
level of complexity to that process that I'd have to work through.

Your comments about "relaxing" the rules on the main form provide an
interesting alternative. On consideration, I think that forcing all fields
to be filled before saving the record really isn't absolutely necessary in
my situation. I believe that when I created the form originally, I enforced
completely filling the form because I thought that was how it was "supposed"
to be done, and I saw lots of examples and discussion around accomplishing
this task.

I also think that the "small cost in reliability" isn't an issue, really,
given the nature of the application. As you say, database engine enforcement
is preferable, but perhaps not essential here.

I think I will attempt to implement a data entry form based on your
suggestions. I'll post back if I have further questions. I still want to try
an unbound form eventually since it looks like an interesting approach and
would be a useful learning experience, but I want to see if I can do it your
way first.

Many thanks!
Fred

 

[Fred Boer]

Dear Dirk:

I've created a data entry form and it appears to be working just fine. I've
run into a problem: I want to create a process that will run when my
application opens. This code will detect records with missing data, and
offer the user the chance to edit the offending records. I want the code to
run using an event from the main application form. I am stuck on one thing:
I cannot manipulate the visibility of the main form properly. I want the
code to run, and if the user chooses to edit the records, I want to hide the
main form, and make the editing form visible. (All my forms are pre-loaded
as hidden forms during the startup of the application.)

I believe that it is the case that you cannot hide a form if it has the
focus, so I have tried setting the focus to another form and then making the
main form invisible.

1. If I have code in an event on the main form can I do this? I suspect that
I can't since it doesn't work. Does the second line of code pull the focus
back to Frm_Main?

Forms!Frm_Child.cboTitle.setfocus
Forms!Frm_Main.visible=False

2. If I can't do this, how can I run this process from the main form? Or is
there a better way?

Thanks!
Fred

P.S. Code below:

Dim sSQL As String
Dim sMsg As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCount As Long

On Error GoTo ErrorHandler

sSQL = "SELECT Count(*) AS CountOfBookID " & _
"FROM Tbl_Library LEFT JOIN Tbl_BookAuthor " & _
"ON Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID " & _
"WHERE Tbl_BookAuthor.Book_ID Is Null " & _
"OR Tbl_Library.NumberofVolumes Is Null " & _
"OR Tbl_Library.VolumeNumber Is Null " & _
"OR Tbl_Library.MediaFormat_ID Is Null " & _
"OR Tbl_Library.Location_ID Is Null " & _
"OR Tbl_Library.Binding_ID Is Null " & _
"OR Tbl_Library.Status_ID Is Null "


Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
lngCount = rs.Fields(0)
rs.Close

If lngCount > 0 Then

' Build a suitable message.
If lngCount = 1 Then
sMsg = "There is a title"
Else

sMsg = "There are " & lngCount & " titles"
End If

sMsg = sMsg & " missing required information in the table! Do you
wish to edit "

If lngCount = 1 Then
sMsg = sMsg & "this record?"
Else

sMsg = sMsg & "these records?"
End If

' Display the message and find out user's wish.
If MsgBox(sMsg, vbYesNo + vbQuestion + vbDefaultButton2, "W. Ross
Macdonald School Library") = vbYes Then
' The user wants to edit the records.
' Open the form hidden, set its recordsource, then show it.


DoCmd.OpenForm "Frm_LibraryDataEntry", WindowMode:=acHidden

With Forms!Frm_LibraryDataEntry

.RecordSource = "SELECT Tbl_Library.*,Tbl_BookAuthor.Book_ID
" & _
"FROM Tbl_Library LEFT JOIN Tbl_BookAuthor "
& _
"ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID " & _
"WHERE Tbl_BookAuthor.Book_ID Is Null " & _
"OR Tbl_Library.NumberofVolumes Is Null " &
_
"OR Tbl_Library.VolumeNumber Is Null " & _
"OR Tbl_Library.MediaFormat_ID Is Null " & _
"OR Tbl_Library.Location_ID Is Null " & _
"OR Tbl_Library.Binding_ID Is Null " & _
"OR Tbl_Library.Status_ID Is Null "
.Visible = True

End With

Forms!Frm_LibraryDataEntry.cbotitle.setfocus ' These two lines are
intended to be used when code runs from
Forms!Frm_Main.Visible=False ' an event on
main form, to move focus, enabling
End If
' the main form to be hidden - doesn't work...
End If


ExitPoint:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
If Err.Number = 3024 Or Err.Number = 3044 Then
Call fRefreshLinks
Else
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint:
End If


End Sub

 

[Dirk Goldgar]

Dear Dirk:

I've created a data entry form and it appears to be working just
fine. I've run into a problem: I want to create a process that will
run when my application opens. This code will detect records with
missing data, and offer the user the chance to edit the offending
records. I want the code to run using an event from the main
application form. I am stuck on one thing: I cannot manipulate the
visibility of the main form properly. I want the code to run, and if
the user chooses to edit the records, I want to hide the main form,
and make the editing form visible. (All my forms are pre-loaded as
hidden forms during the startup of the application.)

I believe that it is the case that you cannot hide a form if it has
the focus, so I have tried setting the focus to another form and then
making the main form invisible.

No, you can hide a form even if it has the focus. All that happens is
the focus goes to the next visible form. If no form is visible, then no
form gets the focus.

1. If I have code in an event on the main form can I do this? I
suspect that I can't since it doesn't work. Does the second line of
code pull the focus back to Frm_Main?

Forms!Frm_Child.cboTitle.setfocus
Forms!Frm_Main.visible=False

Tsk, Fred! You're doing that thing where you say "it doesn't work",
without saying in what way it doesn't work. I thought you knew better

2. If I can't do this, how can I run this process from the main form?
Or is there a better way?

Your code is setting the focus to a particular control on Frm_Child, not
to Frm_Child itself, and I don't think that's going to make Frm_Child
visible. How about this:

Me.Visible = False
With Forms!Frm_Child
.SetFocus
!cboTitle.SetFocus
End With

? That's assuming the code is running on Frm_Main. I don't know
whether you need to set the focus to cboTitle or not; that would depend
on your application.

 

[Fred Boer]

Hi Dirk:

I'm annoying my son by "playing" on the computer while he has to empty the
dishwasher... <g>

With respect to setting the focus to the form itself (as per below), I found
the passage from Help (on the Set Focus topic) I was thinking of :

"If a form contains controls for which the Enabled property is set to True,
you can't move the focus to the form itself. You can only move the focus to
controls on the form. In this case, if you try to use SetFocus to move the
focus to a form, the focus is set to the control on the form that last
received the focus."

Thanks!
Fred

P.S. Been trumped by my other son. He has homework that requires the
computer...

 

[Fred Boer]

Hi Dirk:

No, you can hide a form even if it has the focus. All that happens is
the focus goes to the next visible form. If no form is visible, then no
form gets the focus.

Hmm. Well, if no other form was visible, then, would the result be the form
I was attempting to hide would remain visible? I'm pretty sure, though, that
the "editing" form is visible at the time I try to hide the main form...

Tsk, Fred! You're doing that thing where you say "it doesn't work",
without saying in what way it doesn't work. I thought you knew better
than that. <g>

Sorry! "It doesn't work" = Frm_Main remains visible and retains the focus.
The "editing form" (Frm_LibraryDataEntry), is open, and has the correct
recordset, but is hidden behind the maximized Frm_Main. ( I *do* know
better.. but, in my first attempt to describe this, my post seemed hideously
long. This was an less successful attempt at a more, um, concise
explanation... )

Your code is setting the focus to a particular control on Frm_Child, not
to Frm_Child itself, and I don't think that's going to make Frm_Child
visible. How about this:

Me.Visible = False
With Forms!Frm_Child
.SetFocus
!cboTitle.SetFocus
End With

? That's assuming the code is running on Frm_Main. I don't know
whether you need to set the focus to cboTitle or not; that would depend
on your application.

I will try this tomorrow, thank you. I thought that if I tried a "SetFocus"
against the name of a form itself, it wouldn't work if any of the controls
on the form were enabled. Thus, I thought I had to set focus to a specific
control instead. Tonight I might have time to review the help..

Thanks for your help, Dirk!

Fred

 

[Dirk Goldgar]

Hi Dirk:

I'm annoying my son by "playing" on the computer while he has to
empty the dishwasher... <g>

With respect to setting the focus to the form itself (as per below),
I found the passage from Help (on the Set Focus topic) I was thinking
of :

"If a form contains controls for which the Enabled property is set to
True, you can't move the focus to the form itself. You can only move
the focus to controls on the form. In this case, if you try to use
SetFocus to move the focus to a form, the focus is set to the control
on the form that last received the focus."

Yes, that's right. But if the form itself isn't currently active,
SetFocus to the form will make it active, unhiding it if necessary. I
take it you've figured this out.

P.S. Been trumped by my other son. He has homework that requires the
computer...

I guess you're going to have to get a new computer. What a shame.

 

[Fred Boer]

Dear Dirk:



Well, it still “isn’t working”. At the risk of trying your patience, I
think I’ll try describing the situation in more detail, in the hopes that if
I’ve failed to mention something significant, I might include it by
accident.



I have created a new data entry form, and it appears to work properly. This
new data entry form has looser restrictions on data integrity, so I decided
to create a process that would, on opening my application, run a check for
records with missing data, and offer the user the chance to edit the
offending records.



Opening my application involves two forms:



Frm_Splash – Presents a splash screen and pre-loads a number of forms. These
forms are opened hidden. Does not pre-load Frm_LibraryDataEntry, the new
data entry form. Does includes Frm_Hidden, which sets up a persistent LDB
connection to back end tables.



Frm_Main - the main application form. It includes a clock which uses the
timer event. (Note: I have disabled/completely removed the code in the timer
event during testing since I thought it might affect things.)



I want to run the code using an event on Frm_Main, as the application opens.
When the user chooses to edit the offending records, Frm_LibraryDataEntry is
opened properly and its recordset is loaded correctly. But it remains
behind and obscured by the maximized Frm_Main. No matter what I try to do, I
cannot cause the main form to become hidden and therefore reveal
Frm_LibraryDataEntry. Frm_Main remains visible, maximized, and retains the
focus.



There are a number of forms open at this point. If I manually minimize
Frm_Main, Frm_LibraryDataEntry is revealed. If I then manually minimize
Frm_LibraryDataEntry, a form called Frm_LibraryDataEdit is revealed. (NOTE:
This is odd... it should be hidden - I need to check on this...) Other forms
are open but hidden. (Confirmed by running Isloaded in the immediate
window.)



I have tried placing this code in the Open, Activate, Load and Current event
of Frm_Main.

Note: The code below performs correctly when used from a command button on
Frm_Main, but not when run from a Frm_Main event.



Private Sub Command51_Click()

Dim sSQL As String
Dim sMsg As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCount As Long

On Error GoTo ErrorHandler


sSQL = "SELECT Count(*) AS CountOfBookID " & _
"FROM Tbl_Library LEFT JOIN Tbl_BookAuthor " & _
"ON Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID " & _
"WHERE Tbl_BookAuthor.Book_ID Is Null " & _
"OR Tbl_Library.NumberofVolumes Is Null " & _
"OR Tbl_Library.VolumeNumber Is Null " & _
"OR Tbl_Library.MediaFormat_ID Is Null " & _
"OR Tbl_Library.Location_ID Is Null " & _
"OR Tbl_Library.Binding_ID Is Null " & _
"OR Tbl_Library.Status_ID Is Null "


Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
lngCount = rs.Fields(0)
rs.Close



If lngCount > 0 Then

' Build a suitable message.
If lngCount = 1 Then
sMsg = "There is a title"
Else

sMsg = "There are " & lngCount & " titles"
End If

sMsg = sMsg & " missing required information in the table! Do you
wish to edit "

If lngCount = 1 Then
sMsg = sMsg & "this record?"
Else

sMsg = sMsg & "these records?"
End If

' Display the message and find out user's wish.
If MsgBox(sMsg, vbYesNo + vbQuestion + vbDefaultButton2, "W. Ross
Macdonald School Library") = vbYes Then
' The user wants to edit the records.
' Open the form hidden, set its recordsource, then show it.
DoCmd.OpenForm "Frm_LibraryDataEntry", WindowMode:=acHidden

With Forms!Frm_LibraryDataEntry
.RecordSource = "SELECT Tbl_Library.*,Tbl_BookAuthor.Book_ID
" & _
"FROM Tbl_Library LEFT JOIN Tbl_BookAuthor "
& _
"ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID " & _
"WHERE Tbl_BookAuthor.Book_ID Is Null " & _
"OR Tbl_Library.NumberofVolumes Is Null " &
_
"OR Tbl_Library.VolumeNumber Is Null " & _
"OR Tbl_Library.MediaFormat_ID Is Null " & _
"OR Tbl_Library.Location_ID Is Null " & _
"OR Tbl_Library.Binding_ID Is Null " & _
"OR Tbl_Library.Status_ID Is Null "
.Visible = True
End With


'NOTE: I added the following code as per your suggestion with no
visible effect...



' Forms!Frm_Main.Visible = False
' With Forms!Frm_LibraryDataEntry
' .SetFocus

' !cbotitle.SetFocus
' End With

End If
End If


ExitPoint:
Set rs = Nothing
Set db = Nothing
Exit Sub



ErrorHandler:
If Err.Number = 3024 Or Err.Number = 3044 Then
Call fRefreshLinks
Else
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint:
End If

End Sub

 

[Dirk Goldgar]

Dear Dirk:

Well, it still "isn't working". At the risk of trying your
patience, I think I'll try describing the situation in more detail,
in the hopes that if I've failed to mention something significant, I
might include it by accident.

I have created a new data entry form, and it appears to work
properly. This new data entry form has looser restrictions on data
integrity, so I decided to create a process that would, on opening my
application, run a check for records with missing data, and offer the
user the chance to edit the offending records.

Opening my application involves two forms:

Frm_Splash - Presents a splash screen and pre-loads a number of
forms. These forms are opened hidden. Does not pre-load
Frm_LibraryDataEntry, the new data entry form. Does includes
Frm_Hidden, which sets up a persistent LDB connection to back end
tables.

Frm_Main - the main application form. It includes a clock which uses
the timer event. (Note: I have disabled/completely removed the code
in the timer event during testing since I thought it might affect
things.)

I want to run the code using an event on Frm_Main, as the application
opens. When the user chooses to edit the offending records,
Frm_LibraryDataEntry is opened properly and its recordset is loaded
correctly. But it remains behind and obscured by the maximized
Frm_Main. No matter what I try to do, I cannot cause the main form to
become hidden and therefore reveal Frm_LibraryDataEntry. Frm_Main
remains visible, maximized, and retains the focus.

There are a number of forms open at this point. If I manually minimize
Frm_Main, Frm_LibraryDataEntry is revealed. If I then manually
minimize Frm_LibraryDataEntry, a form called Frm_LibraryDataEdit is
revealed. (NOTE: This is odd... it should be hidden - I need to check
on this...) Other forms are open but hidden. (Confirmed by running
Isloaded in the immediate window.)

Did you know that you can click Window -> Unhide... to see a list of all
hidden windows? That may make your development process quicker than
having to bring up the Immediate window and enter an IsLoaded
expression.

I have tried placing this code in the Open, Activate, Load and
Current event of Frm_Main.

Now I understand what the problem is. Hiding or keeping the focus from
a form when it is first opened is actually rather difficult (unless it's
opened hidden). It's harder than it should be, because Access assumes
that you opened the form because you want to show it. TTBOMK (and I've
tinkered with this before), there's no event that fires immediately upon
opening that will keep the form from becoming visible and getting the
focus. However, you can use the Timer event to hide the form or set the
focus to another form.

Let's suppose you were not currently using the Timer event at all (I
know you said you are, but for this simple example, suppose you aren't).
Then you could initially have the TimerInterval set to 0 in Design View,
and have code like this on the form:

'----- start of example module code #1 -----
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

' ... miscellaneous other code ...

' Display the message and find out user's wish.
If MsgBox( ... ) Then
' The user wants to edit the records.
' Open the form hidden, set its recordsource, then show it.
DoCmd.OpenForm "Frm_LibraryDataEntry", WindowMode:=acHidden

With Forms!Frm_LibraryDataEntry
.RecordSource = ...
.Visible = True
End With

Me.TimerInterval = 1 ' turn on the timer
' Note: the form won't actually be visible until the
' Timer event fires.

End If

End Sub

Private Sub Form_Timer()

Me.TimerInterval = 0 ' turn off the timer

' If Frm_LibraryDataEntry is open, set the focus to it.
If IsLoaded("Frm_LibraryDataEntry") Then
Forms!Frm_LibraryDataEntry.SetFocus
End If

End Sub
'----- end of example module code #1 -----

You say you already have a Timer event procedure. That being the case,
you'll have to overload the Timer event to take care of this business as
well as doing what it's currently doing. You may have a
1000-millisecond TimerInterval, if you're just using it to run a clock,
but that would make Frm_LibraryDataEntry slow to appear. To cope with
that, you might modify the TimerInterval on the form's first opening,
and then in the Timer event after the first firing. You'd also need a
module-level flag to keep the Timer event from setting the focus over
and over again while Frm_LibraryDataEntry is open. So the modified code
might look something like this:

'----- start of example module code #2 -----
Option Compare Database
Option Explicit

Dim mblnShowDataEntry As Boolean

Private Sub Form_Open(Cancel As Integer)

' ... miscellaneous other code ...

' Display the message and find out user's wish.
If MsgBox( ... ) Then
' The user wants to edit the records.
' Open the form hidden, set its recordsource, then show it.
DoCmd.OpenForm "Frm_LibraryDataEntry", WindowMode:=acHidden

With Forms!Frm_LibraryDataEntry
.RecordSource = ...
.Visible = True
End With

mblnShowDataEntry = True
Me.TimerInterval = 1 ' set minimum timer interval
' Note: the form won't actually be visible until the
' Timer event fires.

End If

End Sub

Private Sub Form_Timer()

' If we need to show Frm_LibraryDataEntry,
' set the focus to it and reset the timer.

If mblnShowDataEntry Then
mblnShowDataEntry = False
Me.TimerInterval = 1000 ' reset the timer
Forms!Frm_LibraryDataEntry.SetFocus
End If

' ... other code for Timer event ...

End Sub
'----- end of example module code #2 -----

Note: in these code examples, I just set the focus to
Frm_LibraryDataEntry, without actually hiding the main form. If you
want to make the main form invisible also -- Me.Visible = False --- you
could do that.

 

[Fred Boer]

Hi Dirk!

Did you know that you can click Window -> Unhide... to see a list of all
hidden windows? That may make your development process quicker than

No, I didn't! Thanks for telling me... Debugging during development is one
of my weakest skills; I'm certain I'm not using all of the available tools,
and I suspect I am using the ones I know about less than efficiently. I
dream of standing behind a real Access developer and watching him or her
using the VBA IDE, and seeing a pro manage a debugging session - I figure
I'd learn so much about how to effectively use the available tools...

Ah, yes, my chief weakness is debugging.... Debugging, and writing code.
Two...., *two* of my weaknesses are debugging, writing code, and table
design..... Three! Three of my weaknesses are debugging, writing code, table
design... and understanding relationships......hmmm..... Amongst my
weaknesses are such diverse elements as debugging, writing code, table
design, understanding relationships, hopeless inefficiency, and an almost

Now I understand what the problem is. Hiding or keeping the focus from
a form when it is first opened is actually rather difficult (unless it's
opened hidden). It's harder than it should be, because Access assumes
that you opened the form because you want to show it.

How logical of Access to think I want to show the form I'm opening! <g> When
you put it that way, I see the problem more clearly. I need to think of form
events more in terms of "all of these things happen just in opening the
form". In my mind, I was thinking of the process more like.. "Ok, Access,
open this form, and then when you are all done opening the form, I want you
to do this..."; in other words: give me an event that runs during the
opening of the form that occurs once the form is open. Obviously
wrongheaded.

Thank you for the timer code. At first glance I believe I understand the
concept. However, it seems a bit complex for what I really want to do.
Really, I just want the "clean up" code to run periodically without having
to force the user to push a button to make it happen. I might be better off
trying to run the code on opening the data entry form... . The code would
run, and then depending on the user's choice, the data entry form's
recordsource could be set to edit "offending" records or to simply enter new
records. So, every time the user wants to enter records, I'd offer a chance
to clean up missing data. Not quite as good as running the code every time
the application runs, but perhaps good enough.... Just thinking off top of
my head here...

Anyway, I really appreciate your help, Dirk! I *do* find it a bit of a
challenge when I think I'm trying to do something that I feel is
conceptually quite simple, but turns out to be harder than I expect!

Cheers!
Fred

 

[Dirk Goldgar]

Ah, yes, my chief weakness is debugging.... Debugging, and writing
code. Two...., *two* of my weaknesses are debugging, writing code,
and table design..... Three! Three of my weaknesses are debugging,
writing code, table design... and understanding
relationships......hmmm..... Amongst my weaknesses are such diverse
elements as debugging, writing code, table design, understanding
relationships, hopeless inefficiency, and an almost fanatical
devotion to the Pope... <g>
ROFL

How logical of Access to think I want to show the form I'm opening!
<g> When you put it that way, I see the problem more clearly. I need
to think of form events more in terms of "all of these things happen
just in opening the form". In my mind, I was thinking of the process
more like.. "Ok, Access, open this form, and then when you are all
done opening the form, I want you to do this..."; in other words:
give me an event that runs during the opening of the form that occurs
once the form is open. Obviously wrongheaded.

For what it's worth, I think this is a design defect. It makes sense
that you should be able to make the form invisible in the form's Open
event, to keep it from appearing on the screen. But it's clear that the
OpenForm process won't pay any attention to the Visible property. It's
going to open that form and show it, no matter what you do.
Interestingly, if you have the form open in design view and then switch
to form view, the open event fires and setting the Visible property in
that event works.

Thank you for the timer code. At first glance I believe I understand
the concept. However, it seems a bit complex for what I really want
to do.

It comes under the heading of "kludge", all right.

Really, I just want the "clean up" code to run periodically
without having to force the user to push a button to make it happen.
I might be better off trying to run the code on opening the data
entry form... . The code would run, and then depending on the user's
choice, the data entry form's recordsource could be set to edit
"offending" records or to simply enter new records. So, every time
the user wants to enter records, I'd offer a chance to clean up
missing data. Not quite as good as running the code every time the
application runs, but perhaps good enough.... Just thinking off top
of my head here...

Yes, that would be a possibility. Another possibility is to do it when
the application is closing, as a hidden form is unloaded. If you cancel
the form's Unload event, you can keep the application from closing.
However, doing it then might lead to the user almost always refusing to
take the time to do the cleanup.

Or maybe your splash form could make the check and decide whether to
open the main form or the data entry/fixup form. Or you could just open
the data entry/fixup form from the splash form, with a parameter that
tells it to make the check in its open event. If there's nothing to fix
up, or the user doesn't want to do it, it would just cancel the open
event and open the main form. Otherwise, it would just let itself
finish opening, and only open the main form when it closes.

Anyway, I really appreciate your help, Dirk! I *do* find it a bit of a
challenge when I think I'm trying to do something that I feel is
conceptually quite simple, but turns out to be harder than I expect!

I think we all know that feeling.

 

[Dirk Goldgar]

Ah, one of my favourite things about these newsgroups... people are
guaranteed to understand my references! You've no idea how often I've
tried some of these things with my students..... but they've never
experienced the classics!

I run into the same thing with my students. They give me odd looks, and
I just sigh and mutter something about "... puppies!".

 

[Fred Boer]

Hi Dirk:

ROFL

Ah, one of my favourite things about these newsgroups... people are
guaranteed to understand my references! You've no idea how often I've tried
some of these things with my students..... but they've never experienced the
classics!

Interestingly, if you have the form open in design view and then switch
to form view, the open event fires and setting the Visible property in
that event works.

Yes, I think I saw that behaviour! I thought, yipee! I got it! But, as you
say, it was only when going from design view.

Thanks for your other suggestions. I think I'll mull them all over and
fiddle with each in turn.

I think we all know that feeling.

And that *is* a comfort!

Cheers!
Fred

 

[Fred Boer]

Hi Dirk:

Just to wrap this up:

Today I tried using the splash screen as you suggested, and it works
perfectly and was easy to implement. Problem solved!

Thanks!
Fred