Form On Load Code

[Gus]

Hello,

I am trying to create an event where the code will look for no results after
a query. If there are not results I want a prompt to ask the user to add
Yes/No message box. Once they select either yes or no then I want the forms
to open. I hope someone can help.

Private Sub Form_Load()
Dim rst As DAO.Recordset
'Specifies to User if no record found then add
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then
Dim Response As Integer
Response = MsgBox("Agreement Not Found. Please make sure it was typed in
Correctly. You may need to add the Record. Do you want to Add?", vbYesNo,
"Continue")
If Response = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End Sub

My code currently is not working.
Thanks.

 

[fredg]

Hello,

I am trying to create an event where the code will look for no results after
a query. If there are not results I want a prompt to ask the user to add
Yes/No message box. Once they select either yes or no then I want the forms
to open. I hope someone can help.

Private Sub Form_Load()
Dim rst As DAO.Recordset
'Specifies to User if no record found then add
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then
Dim Response As Integer
Response = MsgBox("Agreement Not Found. Please make sure it was typed in
Correctly. You may need to add the Record. Do you want to Add?", vbYesNo,
"Continue")
If Response = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End Sub

My code currently is not working.
Thanks.

For one thing your code is missing an End If.

Use the Form's Open event instead.
I've simplified the coding a bit for you here.

Private Sub Form_Open(Cancel as Integer)
If Me.RecordsetClone.Recordcount = 0 then
Cancel = True
If MsgBox(MsgBox("Agreement Not Found. Please make sure it was typed
in Correctly. You may need to add the Record. Do you want to Add?",
vbYesNo, "Continue") = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End If
End Sub

 

[Gus]

Hi Fredg,

Thanks for the help. I get a Syntax error pointing to the code below.

If MsgBox(MsgBox("Agreement Not Found. Please make sure it was typed
in Correctly. You may need to add the Record. Do you want to Add?",
vbYesNo, "Continue") = vbYes Then

 

[fredg]

Hi Fredg,

Thanks for the help. I get a Syntax error pointing to the code below.

If MsgBox(MsgBox("Agreement Not Found. Please make sure it was typed
in Correctly. You may need to add the Record. Do you want to Add?",
vbYesNo, "Continue") = vbYes Then

Too many MsgBoxes! I copied and pasted incorrectly. Sorry.
Should be....

If MsgBox("Agreement Not Found..... etc...)

 

[Gus]

Fred,

This is what I have and still get a compile error syntax error on the same
code line.

Thanks.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
If MsgBox("Agreement Not Found. Please make sure it was typed
in Correctly. You may need to add the Record. Do you want to Add?",
vbYesNo, "Continue") = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End If
End Sub

 

[fredg]

Fred,

This is what I have and still get a compile error syntax error on the same
code line.

Thanks.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
If MsgBox("Agreement Not Found. Please make sure it was typed
in Correctly. You may need to add the Record. Do you want to Add?",
vbYesNo, "Continue") = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End If
End Sub

** snipped

When reading messages from newsgroups you must always be aware of
improper word wrap.
I would suspect that email word wrap is causing this problem.

This part ...
If MsgBox("Agreement Not Found. Please make sure it was typed
in Correctly. You may need to add the Record. Do you want to Add?",
vbYesNo, "Continue") = vbYes Then

must be all on one line.

If you want to shorten the code line width, you must use the
continuation characters which are Space Underscore.
For example to shorten the above code width:

If MsgBox("Agreement Not Found. Please make sure it was " _
& "typed in Correctly. You may need to add the Record. " _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then

Now when that message appears, the text is going to be wide,
I would suggest you enter a new line in the above so that it appears
neater on the screen. Something like this:

If MsgBox("Agreement Not Found." & vbNewLine _
& "Please make sure it was typed in Correctly." & vbNewLine _
& "You may need to add the Record." & vbNewLine _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then

The above message will appear on 4 shorter lines.

 

[Gus]

Hi Fred,

Sorry, but still error message Run Time Error '424' Object Required. It
points to this line DoCmnd.OpenForm "New_Agreement_Number_Log_Form".

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
If MsgBox("Agreement Not Found." & vbNewLine _
& "Please make sure it was typed in Correctly." & vbNewLine _
& "You may need to add the Record." & vbNewLine _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End If
End Sub

Thanks.

 

[fredg]

Hi Fred,

Sorry, but still error message Run Time Error '424' Object Required. It
points to this line DoCmnd.OpenForm "New_Agreement_Number_Log_Form".

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
If MsgBox("Agreement Not Found." & vbNewLine _
& "Please make sure it was typed in Correctly." & vbNewLine _
& "You may need to add the Record." & vbNewLine _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then
DoCmnd.OpenForm "New_Agreement_Number_Log_Form"
Else
Docmd.OpenForm "Agreement_Search_and_Check_Out_Form"
End If
End If
End Sub

Thanks.

:
** snipped **

At least we're moving along towards the end of the code. <g>

So do you have a form named exactly:
New_Agreement_Number_Log_Form

There is nothing wrong with the syntax of the OpenForm method you are
using.

I hate using spaces or underscores in object names. Too many chances
for typing/spelling errors. NewAgreementNumberLogForm is just as easy
to read (at least to me), though I would never use that long a name
either.

Let's try changing the name of the form to "AForm".
Then change the code to
DoCmd.OpenForm "AForm"

Does that work?

 

[Gus]

Hi Fred,

Thank you, so much. The NewAgreementLog Form now comes up, but when
selecting no the form AgreementSearch will not. Any suggestion why that may
be. I am not sure if it has anything to do with the fact that the code
resides in the On Open event of the AgreementSearch Form. Any ideas? Actually
when selecting no on the messagebox it does no action.

Thanks.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
If MsgBox("Agreement Not Found." & vbNewLine _
& "Please make sure it was typed in Correctly." & vbNewLine _
& "You may need to add the Record." & vbNewLine _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then
Docmd.OpenForm "NewAgreementLog"
Else
Docmd.OpenForm "AgreementSearch"
End If
End If
End Sub

 

[fredg]

Hi Fred,

Thank you, so much. The NewAgreementLog Form now comes up, but when
selecting no the form AgreementSearch will not. Any suggestion why that may
be. I am not sure if it has anything to do with the fact that the code
resides in the On Open event of the AgreementSearch Form. Any ideas? Actually
when selecting no on the messagebox it does no action.

Thanks.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
If MsgBox("Agreement Not Found." & vbNewLine _
& "Please make sure it was typed in Correctly." & vbNewLine _
& "You may need to add the Record." & vbNewLine _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then
Docmd.OpenForm "NewAgreementLog"
Else
Docmd.OpenForm "AgreementSearch"
End If
End If
End Sub

Nowhere did you mention that the (AgreementSearch) form was the same
form the code was on.
The form is already opening when this code is being run.
All you need do is Cancel = True to not open the form (which we
already have in the If statement, so do nothing for the false part of
the If statement and the AgreementSearch form will continue to open.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then

If MsgBox("Agreement Not Found." & vbNewLine _
& "Please make sure it was typed in Correctly." & vbNewLine _
& "You may need to add the Record." & vbNewLine _
& "Do you want to Add?", vbYesNo, "Continue") = vbYes Then
Cancel = True
Docmd.OpenForm "NewAgreementLog"
End If
End If
End Sub

You will notice the only time you receive the message is if the record
count is 0.
The message will appear asking if you wish to add a new record.
If Yes is selected, the current form will not open (Cancel = True),
but the NewAgreementLog form will.
If No is selected, the current form AgreementSearch will open (showing
no records of course) because Cancel was not set to true in the False
part of the code.
If the form's recordcount is greater than 0, the form opens.

I'm glad to see you shortened the form names. >