Help to cancel Load event

[Paul3rd]

Hello, I have written the following code to cancel a load event under certain
circumstances. The code will fire the MsgBox but I can't figure out how to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub

 

[Dirk Goldgar]

Hello, I have written the following code to cancel a load event under
certain
circumstances. The code will fire the MsgBox but I can't figure out how to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub

A form's Load event can't be canceled, though its Open event can. What's in
Text41 that can tell you whether the load event should be (theoretically)
canceled? If you can't put this logic in the Open event, would it suffice
just to close the form in the Load event, if it shouldn't be opened?

 

[Paul3rd]

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.
I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?
Paul

Hello, I have written the following code to cancel a load event under
certain
circumstances. The code will fire the MsgBox but I can't figure out how to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub

A form's Load event can't be canceled, though its Open event can. What's in
Text41 that can tell you whether the load event should be (theoretically)
canceled? If you can't put this logic in the Open event, would it suffice
just to close the form in the Load event, if it shouldn't be opened?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

That doesn't really make sense.

The order in which the Form events fire when you open a form are:

Open >> Load >> Resize >> Activate >> Current

What code did you put into the Open event? What leads you to believe that
the Current and Load events are firing first?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.
I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?
Paul

Hello, I have written the following code to cancel a load event under
certain
circumstances. The code will fire the MsgBox but I can't figure out how
to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub

A form's Load event can't be canceled, though its Open event can. What's
in
Text41 that can tell you whether the load event should be (theoretically)
canceled? If you can't put this logic in the Open event, would it
suffice
just to close the form in the Load event, if it shouldn't be opened?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Paul3rd]

Hello Douglas,
I put the same code in the Open event, but, because no value is passed to
the text box until the Load event I couldn't cancel the Open event.
On the underlying table ("ApptDis") is a number field which is either 0 or
1, it is 1 if another user has the form ("ApptDis") open.
Is there a way for the OnOpen event to check the value of a table field
before the Load event?
Paul

That doesn't really make sense.

The order in which the Form events fire when you open a form are:

Open >> Load >> Resize >> Activate >> Current

What code did you put into the Open event? What leads you to believe that
the Current and Load events are firing first?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.
I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?
Paul

Hello, I have written the following code to cancel a load event under
certain
circumstances. The code will fire the MsgBox but I can't figure out how
to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub


A form's Load event can't be canceled, though its Open event can. What's
in
Text41 that can tell you whether the load event should be (theoretically)
canceled? If you can't put this logic in the Open event, would it
suffice
just to close the form in the Load event, if it shouldn't be opened?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.

How could that be? The Open event is always the first to fire. It's
possible to have code in the Open event that forces the Load and maybe the
Current events to fire before the Open event procedure is done, but the Open
event will always fire first, even if it doesn't finish first.

On the other hand, if there's always a 1 in text41, and you intend to cancel
or close the form if Text41 = 1, then I don't see why you would be opening
the form in the first place. So there's clearly something I don't
understand about what you're doing here.

I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?

That code might look something like this:

'----- start of modified code -----
Private Sub Form_Load()

Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.Close acForm, Me.Name, acSaveNo
Else
strCriteria = "[ApptDate] = #" & Format(Date, "mm/dd/yyyy") & "#"

With Me.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End If

End Sub
'----- end of modified code -----

I took the liberty of simplifying your original code somewhat, and
correcting a couple of minor errors. Note the use of the .NoMatch property
instead of .EOF to test for an unsuccessful FindFirst. The .EOF property is
not the correct property to use in a Jet database.

 

[Douglas J. Steele]

You can open a recordset to get at the data, or you can use DLookup.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas,
I put the same code in the Open event, but, because no value is passed to
the text box until the Load event I couldn't cancel the Open event.
On the underlying table ("ApptDis") is a number field which is either 0 or
1, it is 1 if another user has the form ("ApptDis") open.
Is there a way for the OnOpen event to check the value of a table field
before the Load event?
Paul

That doesn't really make sense.

The order in which the Form events fire when you open a form are:

Open >> Load >> Resize >> Activate >> Current

What code did you put into the Open event? What leads you to believe that
the Current and Load events are firing first?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the
Load
event seem to happen first.
I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?
Paul

:

Hello, I have written the following code to cancel a load event
under
certain
circumstances. The code will fire the MsgBox but I can't figure out
how
to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub


A form's Load event can't be canceled, though its Open event can.
What's
in
Text41 that can tell you whether the load event should be
(theoretically)
canceled? If you can't put this logic in the Open event, would it
suffice
just to close the form in the Load event, if it shouldn't be opened?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Klatuu]

As Douglas said, either a recordset or a DLookup. I prefer the latter:

Private Sub Form_Open(Cancel As Integer)

If DLookup("[FieldName]", "ApptDis") = 1 Then
Cancel = True
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Hello Douglas,
I put the same code in the Open event, but, because no value is passed to
the text box until the Load event I couldn't cancel the Open event.
On the underlying table ("ApptDis") is a number field which is either 0 or
1, it is 1 if another user has the form ("ApptDis") open.
Is there a way for the OnOpen event to check the value of a table field
before the Load event?
Paul

That doesn't really make sense.

The order in which the Form events fire when you open a form are:

Open >> Load >> Resize >> Activate >> Current

What code did you put into the Open event? What leads you to believe that
the Current and Load events are firing first?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.
I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?
Paul

:

Hello, I have written the following code to cancel a load event under
certain
circumstances. The code will fire the MsgBox but I can't figure out how
to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub


A form's Load event can't be canceled, though its Open event can. What's
in
Text41 that can tell you whether the load event should be (theoretically)
canceled? If you can't put this logic in the Open event, would it
suffice
just to close the form in the Load event, if it shouldn't be opened?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Paul3rd]

Dirk,
I put the same code in the Open event, but, because no value is passed to
the text box until the Load event I couldn't cancel the Open event.
On the underlying table ("ApptDis") is a number field which is either 0 or
1, it is 1 if another user has the form ("ApptDis") open.
Is there a way for the OnOpen event to check the value of a table field
before the Load event?
Paul

Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.

How could that be? The Open event is always the first to fire. It's
possible to have code in the Open event that forces the Load and maybe the
Current events to fire before the Open event procedure is done, but the Open
event will always fire first, even if it doesn't finish first.

On the other hand, if there's always a 1 in text41, and you intend to cancel
or close the form if Text41 = 1, then I don't see why you would be opening
the form in the first place. So there's clearly something I don't
understand about what you're doing here.

I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?

That code might look something like this:

'----- start of modified code -----
Private Sub Form_Load()

Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.Close acForm, Me.Name, acSaveNo
Else
strCriteria = "[ApptDate] = #" & Format(Date, "mm/dd/yyyy") & "#"

With Me.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End If

End Sub
'----- end of modified code -----

I took the liberty of simplifying your original code somewhat, and
correcting a couple of minor errors. Note the use of the .NoMatch property
instead of .EOF to test for an unsuccessful FindFirst. The .EOF property is
not the correct property to use in a Jet database.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Paul3rd]

Thanks to everyone!
This solution worked just fine!
Private Sub Form_Open(Cancel As Integer)
Dim mySQL As String
Dim InUse As Integer
InUse = DLookup("[InUse]", "ApptDis", "[ApptDate] = Date()")
If InUse = 1 Then
MsgBox ("OK so far")
DoCmd.CancelEvent
Else

and then the SQL runs.....

As Douglas said, either a recordset or a DLookup. I prefer the latter:

Private Sub Form_Open(Cancel As Integer)

If DLookup("[FieldName]", "ApptDis") = 1 Then
Cancel = True
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Hello Douglas,
I put the same code in the Open event, but, because no value is passed to
the text box until the Load event I couldn't cancel the Open event.
On the underlying table ("ApptDis") is a number field which is either 0 or
1, it is 1 if another user has the form ("ApptDis") open.
Is there a way for the OnOpen event to check the value of a table field
before the Load event?
Paul

That doesn't really make sense.

The order in which the Form events fire when you open a form are:

Open >> Load >> Resize >> Activate >> Current

What code did you put into the Open event? What leads you to believe that
the Current and Load events are firing first?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for replying so quickly Dirk,
An integer "1" is in the Text41 control.
I tried the code in the On Open event but the Current event and the Load
event seem to happen first.
I'd like to just close the form from the load event and go back to the
Switchboard1 form. Could you recommend code for that?
Paul

:

Hello, I have written the following code to cancel a load event under
certain
circumstances. The code will fire the MsgBox but I can't figure out how
to
cancel the Load event.
Any help would be greatly appreciated.
Paul
Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

If Me.Text41.Value = 1 Then
MsgBox ("Form Currently in use...try again later")
DoCmd.CancelEvent
Else
strCriteria = "[ApptDate] = #" & Date & "#"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub


A form's Load event can't be canceled, though its Open event can. What's
in
Text41 that can tell you whether the load event should be (theoretically)
canceled? If you can't put this logic in the Open event, would it
suffice
just to close the form in the Load event, if it shouldn't be opened?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks to everyone!
This solution worked just fine!
Private Sub Form_Open(Cancel As Integer)
Dim mySQL As String
Dim InUse As Integer
InUse = DLookup("[InUse]", "ApptDis", "[ApptDate] = Date()")
If InUse = 1 Then
MsgBox ("OK so far")
DoCmd.CancelEvent
Else

and then the SQL runs.....

I'm glad you found a solution. Just as a note, setting the Open event's
Cancel argument to True, as in Klatuu's example, is a better way of
cancelling the event than DoCmd.CancelEvent.