Form checking query field for boolean value. 2

[Thenolos]

Hello, I have a Command Button on a form that needs to check a query field for a boolean value of true or false. The Query field it checks is based on a combo box. For example.

If [Combo90].Value is "Northern Florida" , I want to check and see if field [Northern Florida] on a query that returns only one record is true or false. I have absolutely no idea how to go about this in a manner that will not require eight million lines of code, so if anyone might be able to give me a hint I would greatly appreciate it.

--Tony

 

[TheAceMan1]

How are ya Thenolos . . . . .

[blue] I want to check and see if field [Northern Florida] on a query that returns only one record is true or false.[/blue]

Of all the records in the field [blue][Northern Florida][/blue], are you saying the query normally only returns one?

Be more specific?

See Ya! . . . . . .

 

[willir]

Tony

You mean you when you click on the command button, you want to see True or False if you found a / the valid record, or if the value of the field in the combo box is set to true or false???

You have not indicated what you want to do if the result is True or False.

Here is a stab in the dark...

Assumptions:
- Me.Combo90 is a text string
- I am going to assume your command button is called cmdFindIt
- I will use YourField to describe the field you will use to search on
- I will use YourTable for the name of your table

The function DCount returns the number of records meeting a condition.

False = 0
Anything else, + or - is True

For the OnClick event for the cmdFindIt button

Dim strQ as String
Dim strTest as String

strQ = Chr$(34)
strTest = Me.Combo90

If (DCount("[YourField]", "YourTable", "[YourField] = " & strQ & strTest & strQ)) Then

   'Answer is true, now what are you going to do?

Else

   'Answer is false, what are you going to do

End If

A lot of holes here, but hopefully, you moved a few steps forward towards your goal.

Richard

 

[Thenolos]

Sorry that I did not explain enough...

I have a query "qryUserCheck" that returns only one record, based on a field [User] on Form [Main]. This query has about 93 fields with different districts, all containing boolean values.

The form I am working on contains two combo boxes, one for a state and one for a particular district. After update of the second box, an unbound textfield unites the two into one text string. (Combo90=Florida, Combo91=Northern, Text70=Northern Florida)

Then names of the boolean value fieldes in "qryUserCheck" are for example "Northern Florida" , "Southern Florida" , "Central California" , etc.

When the command button [cmdUserDistChk] is clicked, I need to check the field that coresponds with the text value in [Text70] for a value of True or False. For example, If Text70 = Northern Alabama then the field "Northern Alabama" will be checked for a value of true or false.

If the boolean value is true, then a new form [frmDistWrk] is opened. If the value is false, then a message is displayed: "You do not have authorization to access this district."

Hope that clears it up a bit.

--Tony

 

[Thenolos]

A star for willr for getting me rolling in the right direction.

I trashed the query and used the User table directly to accomplish what I needed, here is what I used:

Dim strUser As String
strUser = Forms![Welcome Main]![User].Value

Dim strDist As String
strDist = "[" & [Combo60] & "" & [Combo64] & "]" & "= True AND [User] ="

Dim varX As String
varX = DCount("[User]", "Users", strDist & "'" & strUser & "'")

If IsNull([Combo60]) = False Then
    If varX > 0 Then
        Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "District Work"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.close A_FORM, "District Select"
    Else
        If Forms![Welcome Main]![Admin] = True Then
            Dim stDocName As String
            Dim stLinkCriteria As String
            stDocName = "District Work"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
            DoCmd.close A_FORM, "District Select"
       Else
          If Forms![Welcome Main]![Supervisor] = True Then
              Dim stDocName As String
              Dim stLinkCriteria As String
              stDocName = "District Work"
              DoCmd.OpenForm stDocName, , , stLinkCriteria
              DoCmd.close A_FORM, "District Select"
          Else
            Msgbox "You are not authorized to work this district", 16, "Stop."
        End If
    End If
End If

Thanks for the help,

--Tony

 

[Thenolos]

Bugs in above... used this rather

Dim strUser As String
strUser = Forms![Welcome Main]![User].Value

Dim strDist As String
strDist = "[" & [Combo60] & "" & [Combo64] & "]" & "= True AND [User] ="

Dim varX As String
varX = DCount("[User]", "Users", strDist & "'" & strUser & "'")

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "District Work"

If IsNull([Combo60]) = False Then
    If varX > 0 Then
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.close A_FORM, "District Select"
    Else
        If Forms![Welcome Main]![Admin] = True Then
            DoCmd.OpenForm stDocName, , , stLinkCriteria
            DoCmd.close A_FORM, "District Select"
        Else
            If Forms![Welcome Main]![Supervisor] = True Then
                DoCmd.OpenForm stDocName, , , stLinkCriteria
                DoCmd.close A_FORM, "District Select"
            Else
                Msgbox "You are not authorized to work this district", 16, "Stop."
            End If
        End If
    End If
End If

 

[TheAceMan1]

Thenolos . . . . .

Wow! . . . . I was gonna give ya this for use with the query:

[blue]   If DLookup("[" & Me!Text70 & "]", "qryUserCheck") Then
      'Open Form
   Else
      MsgBox "Not Authorized!"
   End If[/blue]

That is, if the query is set to return one record as you've stated . . . . .

See Ya! . . . . . .