Showing Message Box and results

G

Gnerks

I have 2 forms. The first has a combo box the user uses to select an AFS
(field) and a command button to go to the next form. The command button was
set up via the Command Button Wizard for Form Operations - Open Form - Open
and find specific data to display. With the specific data being the AFS
selected on form 1 matching (if it exists) on form 2. If it doesn't exist, I
want a message box to come up that says "There is no matching AFS in this
form" vbOKOnly and then allow the user to select another AFS. The code
generated by using the Command Button Wizard is as follows. Where I I insert
the MsgBox call?

Option Compare Database
Option Explicit


Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
Private Sub cboAFSselect_AfterUpdate()
Me!txtAFS = Me!cboAFSselect.Column(1)
End Sub

Private Sub cmdM4Details1_Click()
On Error GoTo Err_cmdM4Details1_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

stDocName = "frmM4_Details_PAM"

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'If stLinkCriteria <> "[AFS]=" & "'" & Me![cboAFSselect] & "'" then
'Msg = "Manfor does not have a UTC for this AFS. Do you want to
continue ?" ' Define message.
'Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
'Title = "No MANFOR UTC" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'If Response = vbYes Then ' User chose Yes.
' MyString = "Yes" ' Perform some action.
'Else ' User chose No.
' MyString = "No" ' Perform some action.
'End If

'End If



Exit_cmdM4Details1_Click:
Exit Sub


Err_cmdM4Details1_Click:
MsgBox Err.Description
Resume Exit_cmdM4Details1_Click

End Sub
 
G

Graham Mandeno

As you have it at the moment, you need to check the existence of the [AFS]
value in your table BEFORE you open the form. Something like this:

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
If DCount( "*", "YourTableName", stLinkCriteria ) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
' display your message
End If

However, since the filter selection is coming from a combo box, would it not
be better to list in the combo box only those AFS values that have
corresponding records in the table?
--
Hope this helps!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gnerks said:
I have 2 forms. The first has a combo box the user uses to select an AFS
(field) and a command button to go to the next form. The command button
was
set up via the Command Button Wizard for Form Operations - Open Form -
Open
and find specific data to display. With the specific data being the AFS
selected on form 1 matching (if it exists) on form 2. If it doesn't
exist, I
want a message box to come up that says "There is no matching AFS in this
form" vbOKOnly and then allow the user to select another AFS. The code
generated by using the Command Button Wizard is as follows. Where I I
insert
the MsgBox call?

Option Compare Database
Option Explicit


Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
Private Sub cboAFSselect_AfterUpdate()
Me!txtAFS = Me!cboAFSselect.Column(1)
End Sub

Private Sub cmdM4Details1_Click()
On Error GoTo Err_cmdM4Details1_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

stDocName = "frmM4_Details_PAM"

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'If stLinkCriteria <> "[AFS]=" & "'" & Me![cboAFSselect] & "'" then
'Msg = "Manfor does not have a UTC for this AFS. Do you want to
continue ?" ' Define message.
'Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
'Title = "No MANFOR UTC" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'If Response = vbYes Then ' User chose Yes.
' MyString = "Yes" ' Perform some action.
'Else ' User chose No.
' MyString = "No" ' Perform some action.
'End If

'End If



Exit_cmdM4Details1_Click:
Exit Sub


Err_cmdM4Details1_Click:
MsgBox Err.Description
Resume Exit_cmdM4Details1_Click

End Sub
Click to expand...
 
G

Gnerks

Graham

You are right that would make sense - however, each form working from a
separate table. I tried your suggestion and nothing changed, still need to
an idea where to put the MsgBox so that if the second table does not have the
AFS selected from the first table's combo box, the message will appear that
it is not there.

I appreciate your help.

Gnerks

Graham Mandeno said:
As you have it at the moment, you need to check the existence of the [AFS]
value in your table BEFORE you open the form. Something like this:

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
If DCount( "*", "YourTableName", stLinkCriteria ) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
' display your message
End If

However, since the filter selection is coming from a combo box, would it not
be better to list in the combo box only those AFS values that have
corresponding records in the table?
--
Hope this helps!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gnerks said:
I have 2 forms. The first has a combo box the user uses to select an AFS
(field) and a command button to go to the next form. The command button
was
set up via the Command Button Wizard for Form Operations - Open Form -
Open
and find specific data to display. With the specific data being the AFS
selected on form 1 matching (if it exists) on form 2. If it doesn't
exist, I
want a message box to come up that says "There is no matching AFS in this
form" vbOKOnly and then allow the user to select another AFS. The code
generated by using the Command Button Wizard is as follows. Where I I
insert
the MsgBox call?

Option Compare Database
Option Explicit


Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
Private Sub cboAFSselect_AfterUpdate()
Me!txtAFS = Me!cboAFSselect.Column(1)
End Sub

Private Sub cmdM4Details1_Click()
On Error GoTo Err_cmdM4Details1_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

stDocName = "frmM4_Details_PAM"

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'If stLinkCriteria <> "[AFS]=" & "'" & Me![cboAFSselect] & "'" then
'Msg = "Manfor does not have a UTC for this AFS. Do you want to
continue ?" ' Define message.
'Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
'Title = "No MANFOR UTC" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'If Response = vbYes Then ' User chose Yes.
' MyString = "Yes" ' Perform some action.
'Else ' User chose No.
' MyString = "No" ' Perform some action.
'End If

'End If



Exit_cmdM4Details1_Click:
Exit Sub


Err_cmdM4Details1_Click:
MsgBox Err.Description
Resume Exit_cmdM4Details1_Click

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

So, are you saying that the DCount function IS returning a value greater
than zero? If so, then there ARE records in the table with the selected AFS
value. If not, your MsgBox gets displayed.

I don't understand where the problem is. In what way is it not working?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Gnerks said:
Graham

You are right that would make sense - however, each form working from a
separate table. I tried your suggestion and nothing changed, still need
to
an idea where to put the MsgBox so that if the second table does not have
the
AFS selected from the first table's combo box, the message will appear
that
it is not there.

I appreciate your help.

Gnerks

Graham Mandeno said:
As you have it at the moment, you need to check the existence of the
[AFS]
value in your table BEFORE you open the form. Something like this:

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
If DCount( "*", "YourTableName", stLinkCriteria ) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
' display your message
End If

However, since the filter selection is coming from a combo box, would it
not
be better to list in the combo box only those AFS values that have
corresponding records in the table?
--
Hope this helps!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gnerks said:
I have 2 forms. The first has a combo box the user uses to select an
AFS
(field) and a command button to go to the next form. The command
button
was
set up via the Command Button Wizard for Form Operations - Open Form -
Open
and find specific data to display. With the specific data being the
AFS
selected on form 1 matching (if it exists) on form 2. If it doesn't
exist, I
want a message box to come up that says "There is no matching AFS in
this
form" vbOKOnly and then allow the user to select another AFS. The code
generated by using the Command Button Wizard is as follows. Where I I
insert
the MsgBox call?

Option Compare Database
Option Explicit


Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
Private Sub cboAFSselect_AfterUpdate()
Me!txtAFS = Me!cboAFSselect.Column(1)
End Sub

Private Sub cmdM4Details1_Click()
On Error GoTo Err_cmdM4Details1_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

stDocName = "frmM4_Details_PAM"

stLinkCriteria = "[AFS]=" & "'" & Me![cboAFSselect] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'If stLinkCriteria <> "[AFS]=" & "'" & Me![cboAFSselect] & "'" then
'Msg = "Manfor does not have a UTC for this AFS. Do you want to
continue ?" ' Define message.
'Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define
buttons.
'Title = "No MANFOR UTC" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'If Response = vbYes Then ' User chose Yes.
' MyString = "Yes" ' Perform some action.
'Else ' User chose No.
' MyString = "No" ' Perform some action.
'End If

'End If



Exit_cmdM4Details1_Click:
Exit Sub


Err_cmdM4Details1_Click:
MsgBox Err.Description
Resume Exit_cmdM4Details1_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform Activation 0
Excelsend method inconsistent using Access 2003 and 2007 0
Msg Box with Conditions and display a value from form on message 4
Beep in a MsgBox 2
Multiple VByesno Message boxes no working 2
how to store user responses 2
cursor won't move to selection 2
how to add a textbox if user chooses yes in response to msgbox. 0

Top