• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

Linked criteria between two fields on a form

Hi all,

Not quite sure whether my title is appropriate but my problem is as follows:

I have a form with two particular object fields that are related in the following manner:

Object 1 is a combo which selects an entry and the second object is a text box where I will input a date.


The combo can select from four different items but I what I want it to do is that if any three particular items of the four is selected then an entry has/must  go in the date field and if it is not then some of message should appear.

The Access version I'm using is A97 and A03.

Grateful for assistance
0
PipMic
Asked:
PipMic
  • 9
  • 6
1 Solution
 
Dale FyeCommented:
Generally, I would use the Form_Before Update event to test this.

Private Sub Form_BeforeUpdate(Cancel as Integer)

    Select Case NZ(me.cbo_yourComboName, 0)
         Case 0      'no entry
              msgbox "Make a selection from the dropdown list"
              me.cbo_yourComboName.SetFocus
              Cancel = true
          Case 1     'use the value where you don't require an entry in the text box
              'do nothing
          Case Else
               If Len(me.txt_yourDateField & "") = "" Then
                   msgbox "Enter a date in the date field!"
                   me.txt_yourDateField.setfocus
                   Cancel = true
               End if
      End Select

End Sub
0
 
PipMicAuthor Commented:
Hi,

Not quite sure.... I am attaching a sample.

I would want that before I close the form that the routine will check the following :

If the combo selected is Active that no date is required. Anything else selected requires a date. If no date has been selected then a msg box should appear requesting its entry and if this is ignored say twice then the form would close and a prompt saying that the record would not be saved.

Hope this helps..
EE-V2.mdb
0
 
PipMicAuthor Commented:
Hi,

The sample sent is in A97.....(forgot to mention)

Thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
hnasrCommented:
cboBox: make it 2 columns
entry1    1
entry2    2
entry3    0
entry4    3
cboBox.Column(1) gives 0,1,2, or 3 - 0 may be used to show message.

You use the select case to test the vlaue of cboBox.Column(1) selected as shown by fyed above.
0
 
Dale FyeCommented:
The Form_BeforeUpdate event fires before any updates are written to your table, which means that it also fires before your form is closed.  I'm assuming that there must be an entry in the combo box.

Unfortunately, I cannot open your database because of security rules, but here is the way I would modify my earlier code based on your feedback:
Private Sub Form_BeforeUpdate(Cancel as Integer)

    Select Case NZ(me.cbo_yourComboName, "Empty")
         Case "Empty"      'no entry
              msgbox "Make a selection from the dropdown list"
              me.cbo_yourComboName.SetFocus
              Cancel = true
          Case "Active"     'does not  require an entry in the text box
              'do nothing
          Case Else           'Any value other than "Active"
               If Len(me.txt_yourDateField & "") = "" Then
                   msgbox "Enter a date in the date field!"
                   me.txt_yourDateField.setfocus
                   Cancel = true
               End if
      End Select

End Sub

Open in new window

0
 
PipMicAuthor Commented:
hi,

sending A03 sample
EE-V203.mdb
0
 
Dale FyeCommented:
Access version is immaterial, I cannot download anything for network security reasons.
0
 
PipMicAuthor Commented:
Hi,

Not quite sure why it wont work....this what should happen:-

The combo has several choices, eg
active
inactive
deceased

one of these three has to be selected.

Once all the other entries have been inputted, the Beforeupdate must check this combo entry and if the choice is active then no date is required in the date field and the record can be saved without a date in the date field.

If however inactive or deceased is selected then there has to be a date in the date field.

If there isn't, then a msg box should appear saying that a date entry is required. If after, say, two attempts no date in inputted then a msg box should appear saying that the form will close and the record will not be saved.
0
 
Dale FyeCommented:
Can you copy the code you are using and post it here, so I can see exactly what you are doing?

If you just copied my code, and pasted it into a forms code module, you may not actually have a link between the form and the code.  Open the form in design view, select the form, then display the Properties dialog.  On the Event tab, you should see [Event Procedure] listed beside the Form_BeforeUpdate event.  If not, select that from the dropdown and then click on the "..." button next to that to go to the code module.  The cursor should be in the Form_BeforeUpdate event procedure.
0
 
PipMicAuthor Commented:
ok..... may have to do that tomorrow...please stay in contact..

thanks
0
 
PipMicAuthor Commented:
hi,

this is the code...I'm attaching a REVISED sample in A03 in case anyone else wants to have a look at it...

It is set in the Forms BeforeUpdate as an event procedure.

Select Case Nz(Me.Combo1, "Empty")
         Case "Empty"      'no entry
              MsgBox "Make a selection from the dropdown list"
              Me.Combo1.SetFocus
              Cancel = True
          Case "Active"     'does not  require an entry in the text box
              'do nothing
          Case Else           'Any value other than "Active"
               If Len(Me.DoD & "") = "" Then
                   MsgBox "Enter a date in the date field!"
                   Me.DoD.SetFocus
                   Cancel = True
               End If
      End Select
EE-V203.mdb
0
 
Dale FyeCommented:
My bad.

The line that looks like:

               If Len(Me.DoD & "") = "" Then

should read:

               If Len(Me.DoD & "") = 0 Then
0
 
PipMicAuthor Commented:
Hi,

It works a treat....can I just ask whether you can explain briefly how the code works.

line by line...it would be beneficial if I could understand the logic of the code.

Many thanks
0
 
PipMicAuthor Commented:
nice routine
0
 
Dale FyeCommented:
sure:

The Select Case statement is similar to an If Then statement, but you can have multiple values listed on a single line and you only get one condition to evaluate.  In this case, I evaluated me.Combo1.   It starts with a SELECT Case and ends with END Select; between those bookends, you have a series of CASE statements which list one or more values that will be tested against the value at the End of the SELECT Case line.

I used the NZ() function so that if me.Combo1 was NULL, it would return the word "Empty".  The rest of the comments will be embedded in the code.
Select Case Nz(Me.Combo1, "Empty")

         Case "Empty"      'no entry
              'If me.Combo1 is NULL then display this message
              MsgBox "Make a selection from the dropdown list"

              'Move the cursor to the combo1 dropdown list
              Me.Combo1.SetFocus

              'In the Before Update event, you have an Argument called Cancel.  This
              'Argument tells Access whether to Cancel the update or not.  The default value
              'for this argument is False (0), so you don't need to set that value unless
              'you want to cancel the Update.  In your case, you want to set the Cancel
              'argument to True if the combo is blank, or if the date is blank and the 
              'user selected anything other than "Active"
              Cancel = True
          Case "Active"     'does not  require an entry in the text box
              'I generally include options for all of the possible values of my test argument
              'when I use Case statements.  This makes it easier for me to understand what
              'I was doing and why when I come back to the code in a year or more.
              'do nothing
          Case Else           'Any value other than "Active"
               'This last Case statement handles any situation other than an empty selection
               'or a selection of "Active" in the combo box.

               'The if statement below checks to see whether there is anything in the DoD
               'field of your form.  The reason I used this technique is that the value in that 
               'field could be NULL, an empty string, or contain a date value.  The only way to
               'test for both NULL and "" in a single line of code is to concatenate an empty
               'string "" to the value of the control.  This will cause the value to now appear 
               'as an empty string.  I'm not sure why I used the LEN function:

               If Len(Me.DoD & "") = 0 Then

               'I could also have used, the following, they both test for the same thing.

               IF Me.DoD & "" = "" Then

                   MsgBox "Enter a date in the date field!"
                   Me.DoD.SetFocus
                   Cancel = True
               End If
End Select 

Open in new window

Hope this helps
0
 
PipMicAuthor Commented:
thanks a lot...much appreciated  :)  helps with my learning process
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now