Open Forms in Hidden Mode

[petersk]

Firstly I am an older person trying to teach myself to create a project
and teach myself Access VBA programming along the way.

I anticipate a number of problems I will need help with but here goes
with the first

From one form I want to open another form to extract data. I really

want to do this with the form Hidden but initially show the form open
so that I can easily check what is going on.

If I open the form using the DoCmd.OpenForm method with a Where
condition this works Ok even with the form Hidden. If however the
Where Condition is not satisfied the form opens with no data shown in
any of the fields. If I try to access the fields (to check if the
where condition has been met) I receive a Run Time error message error
'2427': You entered an expression that has no value.
My question is "How can I test to see if the Where Condition has been
satisfied and therefore take alternative action?.

I have tried to use an alternative by merely opening the form using
DoCmd.OpenForm without a Where Condition and then use DoCmd.FindRecord
based on the Where criteria. If this is not met the form remains on
the first record and it is easy to test to see if the criteria has been
met. The problem is that, although this works fine with the form open
normally as soon as the acHidden is used it behaves as if the form
isn't open at all.
What are the "rules" for Hidden forms?

The answers are probably simple but I am banging my head against a
brick wall.

Can anyone help ?

OldAlex

[Rick Brandt]

pe*****@cyllene.uwa.edu.au wrote:

Firstly I am an older person trying to teach myself to create a
project and teach myself Access VBA programming along the way.

I anticipate a number of problems I will need help with but here goes
with the first

From one form I want to open another form to extract data. I
really want to do this with the form Hidden but initially show the form open
so that I can easily check what is going on.

I assume you mean that the *second* form is the one you want to be hidden.
If I open the form using the DoCmd.OpenForm method with a Where
condition this works Ok even with the form Hidden. If however the
Where Condition is not satisfied the form opens with no data shown in
any of the fields. If I try to access the fields (to check if the
where condition has been met) I receive a Run Time error message error
'2427': You entered an expression that has no value.
My question is "How can I test to see if the Where Condition has been
satisfied and therefore take alternative action?.
Several ways. What is the code that raises this error? You can test the
NewRecord property of the second form. If that is true then the form opened
with zero records satisfyinh the WHERE clause. You could use DCount() to test
to see that you will get at least one record before you even attempt to open the
second form. That would not be my preference if the Record Sorce was large
since you are effectively querying the source twice.
I have tried to use an alternative by merely opening the form using
DoCmd.OpenForm without a Where Condition and then use DoCmd.FindRecord
based on the Where criteria. If this is not met the form remains on
the first record and it is easy to test to see if the criteria has
been met. The problem is that, although this works fine with the
form open normally as soon as the acHidden is used it behaves as if
the form isn't open at all.
What are the "rules" for Hidden forms?
I don't like that method (less efficient), but don't know of any reason why it
would behave differently on a hidden form.
The answers are probably simple but I am banging my head against a
brick wall.

IMO opening a form for the sole purpose of extracting lookup data from it is
unnecessary. You should just open a RecordSet so you can get at the data
directly. The extra overhead of a form is just not necessary. Using the DAO
object library that would look something like...

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

strSQL = "SELECT FieldName " & _
"FROM TableName " & _
"WHERE SomeField = SomeValue"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.EOF = True Then
'No matching records found (do whatever)
Else
'In here values can be extracted by...
SomeVariable = rs!FieldName
End If

rs.Close
Set rs = Nothing
Set db = Nothing


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com