Checking multiple fields and doing different things based on those results

X

xPaul

Registered User.
Local time
Today, 06:22
Joined
Jan 27, 2013
Messages
65
Hi all,

Basically I am trying to create some code that will check for nulls and then do different things depending on which result it gets.

So pseudo code:

PHP: 
If cmb_name or cmb_outcome IsNull then
     Show Message "At least one dropdown must be selected"
 
          If cmb_name IsNotNull then
               Check to see if that persons name is held in the DB.
               Show Message if name does not exist
          Else
               Requery
          End If
 
          If cmb_outcome IsNotNull then
               Check to see if only the outcome exists in the DB.
               Show Message if outcome does not exist.
          Else
               Requery
          End If
 
          If cmb_name and cmb_outcome IsNotNull then 
               Check to see if that persons name is held in the DB.
               and
               Check to see if any outcome held against the person exists in the DB.
               Show Message if no record exists.
          Else
               Requery
          End If
End If

What I have so far:

PHP: 
If IsNull(Me.cmb_name) And IsNull(Me.cmb_outcome) Then
    Call MsgBox("At least one combobox must be selected before you can filter", vbExclamation, "Warning!")
ElseIf DCount("[cmb_name]", "tbl_log", "[Name]= '" & Replace(Me.cmb_name, "'", "''") & "'") = 0 Then
    Cancel = True
    Call MsgBox("Your name isn't held on this table!", vbExclamation, "Warning!")

Any help would be greatly appreciated.
 
If it is a ComboBox, try using the ListIndex property of the combo box, if it is -1 then nothing is selected.. Else something is chosen..

Code: 
If Me.cmb_name.ListIndex = -1 And Me.cmb_outcome.ListIndex = -1 Then
    Call MsgBox("At least one combobox must be selected before you can filter", vbExclamation, "Warning!")
    Exit Sub
End If
The other details you might have to figure out how to work out, because the ElseIf structure will not support all cases.. Consider, if the user has selected some outcome but not the name, then the ElseIf you have will most definitely fail, as name is Null; and Replace() is a String function whihc is not capable of handling Null..

Best would be Multiple If's..
 
So I have got this far, and most of it works horah!

PHP: 
If IsNull(Me.cmb_name) And IsNull(Me.cmb_outcome) Then
    Call MsgBox("You need to select at least one combobox", vbOKOnly, "Information!")
Exit Sub
End If
       
        
If Me.cmb_name.ListIndex > -1 Then
    If DCount("[cmb_name]", "tbl_log", "[Name]= '" & Replace(Me.cmb_name, "'", "''") & "'") = 0 Then
        Cancel = True
        Call MsgBox("Your name isn't held on this table!", vbExclamation, "Warning!")
    Else
    Me.Requery
    End If
Exit Sub
End If


If Me.cmb_outcome.ListIndex > -1 Then
    If DCount("[cmb_outcome]", "tbl_log", "[Outcome]= '" & Me.cmb_outcome & "'") = 0 Then
        Cancel = True
        Call MsgBox("There are no " & Me.cmb_outcome & " held on this table!", vbExclamation, "Warning!")
    Else
        Me.Requery
    End If
Exit Sub
End If


If Me.cmb_name.ListIndex > -1 And Me.cmb_outcome > -1 Then
    If DCount("[cmb_outcome]", "tbl_log", "[Outcome]= '" & Replace(Me.cmb_name, "'", "''") & "'") = 0 Then
        Cancel = True
        Call MsgBox("You do not have any " & Me.cmb_outcome & " to your name!", vbExclamation, "Warning!")
    Else
        Me.Requery
    End If
Exit Sub
End If
However my last:
PHP: 
DCount("[cmb_outcome]", "tbl_log", "[Outcome]= '" & Replace(Me.cmb_name, "'", "''") & "'")
does not work like I wish it to.

I am trying to check if there are 0 outcome against that persons name.

I thought the way Dcount was: <value>, <in this table>, <criteria>.

Thanks
 
Paul, is this LOC..
Code: 
DCount("[cmb_outcome]", "tbl_log", "[Outcome]= '" & Replace(Me.cmb_name, "'", "''") & "'")
Trying to deal with names like O'Brian, O'Connor?
If so the other way to do would be..
Code: 
DCount("[cmb_outcome]", "tbl_log", "[Outcome]= [COLOR=Red][B]""[/B][/COLOR]" & Me.cmb_name & "[COLOR=Red][B]""[/B][/COLOR]")
 
Yes, that's exactly what I am having to deal with.

I have tried your alternative to no success.

I believe the issue lies with the criteria part of the dcount.

With the last dcount I am trying to count all outcomes that relate to a specific person and only that person.

If the number = 0 then
Msgbox "You have no " & Outcome & " to your name"
Else
Me.Requery

Can you specify more specific criteria in a Dcount?

_____

To elaborate further:

Regardless if I select a person with 0 Successful outcomes to their name, or 10 Successful Outcomes to their name I still get the - Msgbox "You have no " & Outcome & " to your name".
 
So in that case should the Criteria not be looking in the Name field in the table instead of looking at the Outcome field???

I would advice you to follow some Naming convention.. 'Name' is not a good field name; 'Name' is a Keyword in Access, and should not be used.. Try replacements like firstName, lastName, clientName, customerName etc....
 
Last edited:
I understand that pr2, they are named slightly differently when in my database. I just rename the sensitive parts when I post on here.

When I change the dcount to:
Code: 
DCount("[cmb_outcome]", "tbl_log", "[name]= """ & Me.cmb_name & """") = 0
It does the opposite. It knows the user is there, but it is not checking to see if there are any outcome against that person.

_____

This time it is returning a true value when it should really be the false value.

I have no Failed against my name.
 
Okay could you explain in simple English.. What you want the DCount to do? What are the possible outcomes? Try combining them in the criteria..
Code: 
DCount("[cmb_outcome]", "tbl_log", "[Name]= """ & Me.cmb_name & """ AND [Outcome]= '" & Me.cmb_outcome & "'")
 
pr2-eugin said:
Okay could you explain in simple English.. What you want the DCount to do? What are the possible outcomes? Try combining them in the criteria..
Code: 
DCount("[cmb_outcome]", "tbl_log", "[Name]= """ & Me.cmb_name & """ AND [Outcome]= '" & Me.cmb_outcome & "'")
Click to expand...

Slightly difficult as I over explain things.

Basically I want the dcount to check for the amount of selected outcomes against a selected person.

If the value is 0 I want a messagebox to show saying that there is no outcome against that person.

If the value > 0 then it will requery.

_____
Name Outcome
Jimmy Successful
Jimmy Failed
Jimmy Successful
Jimmy Successful
Paul Successful

The person will select the following comboboxes.

cmb_name -> Paul
cmb_outcome -> Failed

The button will be pressed and it will run the dcount to check to see how many failed is against Paul's name. In this case there are none and therefore the message box will appear.

In the case of:

cmb_name -> Jimmy
cmb_outcome -> Successful

The button will be pressed, it will run the dcount to see how many successful are against Jimmy's name. In this case it will requery as there are more than 0 Successful.

Hope that makes sense. :rolleyes:
 
That worked. :) Thanks pr2 - Thanks coming your way!

In the next post, I'll show my working solution for all.
 
Here is my working and final solution:

PHP: 
If Me.cmb_name.ListIndex > -1 And Me.cmb_outcome.ListIndex > -1 Then
    If DCount("[cmb_outcome]", "tbl_log", "[name]= """ & Me.cmb_name & """ AND [Outcome]= '" & Me.cmb_outcome & "'") = 0 Then
        Call MsgBox("You do not have any " & Me.cmb_outcome & " to your name!", vbExclamation, "Warning!")
    Else
        Me.Requery
        Call MsgBox("Filtered Successfully", vbInformation, "Information!")
    End If

ElseIf Me.cmb_name.ListIndex = -1 And Me.cmb_outcome.ListIndex = -1 Then
    Call MsgBox("You need to select at least one combobox", vbExclamation, "Warning!")

ElseIf Me.cmb_name.ListIndex > -1 Then
    If DCount("[cmb_name]", "tbl_log", "[name]= '" & Replace(Me.cmb_name, "'", "''") & "'") = 0 Then
        Cancel = True
        Call MsgBox("Your name isn't held on this table!", vbExclamation, "Warning!")
    Else
        Me.Requery
        Call MsgBox("Filtered Successfully", vbInformation, "Information!")
    End If

Else
    If DCount("[cmb_outcome]", "tbl_log", "[Outcome]= '" & Me.cmb_outcome & "'") = 0 Then
        Cancel = True
        Call MsgBox("There are no " & Me.cmb_outcome & " held on this table!", vbExclamation, "Warning!")
    Else
        Me.Requery
        Call MsgBox("Filtered Successfully", vbInformation, "Information!")
    End If
End If
 
xPaul said:
That worked. :) Thanks pr2
Click to expand...
Glad to have helped..

Just a future note, since the beginning of this thread.. In all posts all you have shown is code, nothing descriptive.. At times code proves to be of no use, unless we see some sample data.. We have no idea of what the data is/how they would be related until you show them to us.. Until that point it will be only trial and error/guess work..

xPaul said:
does not work like I wish it to.
Click to expand...
Did not help at all.. I had to guess maybe it is because of the names like O'Connor.. It would be really helpful if you had said, "I needed to count the number of Instances where the Client has made Successful/Failed payments".. As this will not only highlight the outcomes involved, but also give us a clear idea what you actually want; instead of going around in circles..

Sorry if this offends you.. Just making things a bit clear for you.. Good Luck.. :)
 
pr2-eugin said:
Glad to have helped..

Just a future note, since the beginning of this thread.. In all posts all you have shown is code, nothing descriptive.. At times code proves to be of no use, unless we see some sample data.. We have no idea of what the data is/how they would be related until you show them to us.. Until that point it will be only trial and error/guess work..

Did not help at all.. I had to guess maybe it is because of the names like O'Connor.. It would be really helpful if you had said, "I needed to count the number of Instances where the Client has made Successful/Failed payments".. As this will not only highlight the outcomes involved, but also give us a clear idea what you actually want; instead of going around in circles..

Sorry if this offends you.. Just making things a bit clear for you.. Good Luck.. :)
Click to expand...

It doesn't offend me, I already know it from a personal level.

I find it hard to explain, then get lost in explaining and not everything comes out . . . Kind of frustrating at times.

:o
 
You must log in or register to reply here.

Similar threads

A
Automation Error
Replies
10
Views
545
The_Doc_Man
The_Doc_Man
jwcolby54
VBA and ChatGPT
2 3
Replies
55
Views
4,861
Uncle Gizmo
Uncle Gizmo
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
1,386
ricardocpa
R
M
Solved Duplicate value check - complicated.
Replies
11
Views
2,472
Marshall Brooks
M
F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
818
Faoineag
F

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom