Idiot Check

J

JAMES FENNEL

I've got a database that tracks the use of Respirators on my ship. I've been
slowly updating an older existing database and trying to improve the
functionality of the forms. What I'm working on now is an Idiot Check to
check whenever a respirator is checked out a query is run to see if that
respirator number is already checked out. If so, a form opens that tells the
operator that number respirator is checked out. I've tried an IF..Then
statement but I get a Object Unknown response to the following statement:

Private Sub RespiratorNumber_LostFocus()
DoCmd.OpenQuery "Doubles", acViewNormal
If Me!RespiratorNumber = Doubles.RespiratorNumber and (Doubles.DateOut is
not Null and Doubles.DateIn is Null) then
DoCmd.OpenForm "Respirator Pop-Up", acNormal, , , acFormEdit, acWindowNormal
DoCmd.Close acQuery, "Doubles"

End Sub

Please Help ME!!!! What am I doing wrong and is there an easier way to do it?
James
 
D

Dirk Goldgar

JAMES FENNEL said:
I've got a database that tracks the use of Respirators on my ship.
I've been slowly updating an older existing database and trying to
improve the functionality of the forms. What I'm working on now is
an Idiot Check to check whenever a respirator is checked out a query
is run to see if that respirator number is already checked out. If
so, a form opens that tells the operator that number respirator is
checked out. I've tried an IF..Then statement but I get a Object
Unknown response to the following statement:

Private Sub RespiratorNumber_LostFocus()
DoCmd.OpenQuery "Doubles", acViewNormal
If Me!RespiratorNumber = Doubles.RespiratorNumber and
(Doubles.DateOut is not Null and Doubles.DateIn is Null) then
DoCmd.OpenForm "Respirator Pop-Up", acNormal, , , acFormEdit,
acWindowNormal DoCmd.Close acQuery, "Doubles"

End Sub

Please Help ME!!!! What am I doing wrong and is there an easier way
to do it? James
Click to expand...

OpenQuery opens a query in datasheet view but doesn't give your code any
access to the fields in the query. I don't know what the query actually
selects, but you may find that the DLookup function can be used in this
situation:

Private Sub RespiratorNumber_AfterUpdate()

If Not IsNull( _
DLookup("RespiratorNumber", "Doubles", _
"RespiratorNumber=" & Me!RespiratorNumber & _
" and (DateOut is not Null and DateIn is Null") _
) _
Then
DoCmd.OpenForm "Respirator Pop-Up"
End If

End Sub

That assumes that RespiratorNumber is a numeric field, not text.
 
J

John Vinson

I've got a database that tracks the use of Respirators on my ship. I've been
slowly updating an older existing database and trying to improve the
functionality of the forms. What I'm working on now is an Idiot Check to
check whenever a respirator is checked out a query is run to see if that
respirator number is already checked out.
Click to expand...

Dirk's suggestion of using DLookUp is one way - but I'd suggest not
showing the user respirators that aren't available AT ALL!

You should be able to base your Form on a query selecting only
checked-in respirators. Something like

SELECT <whatever>
FROM Respirators
WHERE RespiratorNumber NOT IN
(SELECT Doubles.RespiratorNumber FROM Doubles WHERE (Doubles.DateOut
is not Null and Doubles.DateIn is Null);


John W. Vinson[MVP]
 
Top