VBA to Unbound to Query value failure

[carnealse]

For context, I am referring to Navy students on a Naval Intel base just out of boot camp who need to earn their expanded liberty. The higher the phase they are in, the more liberty they get.
Now...
I coded a way to determine what liberty phase my students are in VBA.
I created a query to determine who is phase one so we filter down the records to who needs to muster on Saturday and Sunday. If they are phase two, they do not.
In my form I made an unbound box that returns the result of the VBA code to determine their phase and it works like a charm.
[EDITED ADDED INFO]*At times students come in and are from other schools (language, BUD/s, etc) or we are the second stop after boot camp and we do not require them to be there a minimum of 14 days or pass a physical test since they have been in longer. We only require them to get their watch qualifications. The program sees that they are there less than 14 days and without code puts them on phase one, but with code puts them on phase two if they are those special cases. I am trying to get that query to recognize the special cases based off the code in VBA and the value returned.*
The problem is when I need to run a query, I cannot get the $#%ing query to look at the value of the unbound box per record to filter down to only students on phase one.
Here is the code used to determine their phase and return it in the form's unbound box called txtphase.

Dim a As Integer, b As Integer, c As Integer, d As Integer, phasenumber As Integer
    If Me.rover = True Then
    a = 2
    End If
    If Me.messenger = True Then
    b = 2
    End If
    If Me.mockprt = "PASS" Then
    c = 2
    Else
        If Me.daysonboard < 14 And Me.mockprt = "NA" Then
        c = 4
    End If
    End If
    If Me.daysonboard > 13 Then
    d = 2
    End If
    If a + b + c + d = 8 Then
    phasenumber = 2
    Else
    phasenumber = 1
    End If
    txtphase = phasenumber

The name of the table used is currentstudents.
The name of the form used is N91 RECORDS
The name of the query used is phaseonequery

What am I to do? Where is my failure here? Thanks.

 

[BlueIshDan]

Remove red line. I didn't read your whole post. Too much information that has nothing to do with what the error or what your question is. Please narrow down what you're looking to get from us.

    Dim a As Integer, b As Integer, c As Integer, d As Integer, phasenumber As Integer
    
    If Me.rover = True Then
        a = 2
    End If
    
    If Me.messenger = True Then
        b = 2
    End If
    
    If Me.mockprt = "PASS" Then
        c = 2
    Else
    
    If Me.daysonboard < 14 And Me.mockprt = "NA" Then
        c = 4
    End If
    
[COLOR="Red"]    End If[/COLOR]
    
    If Me.daysonboard > 13 Then
        d = 2
    End If
    
    If a + b + c + d = 8 Then
        phasenumber = 2
    Else
        phasenumber = 1
    End If
    
    txtphase = phasenumber

 

[vbaInet]

The problem is when I need to run a query, I cannot get the $#%ing query to look at the value of the unbound box per record to filter down to only students on phase one.

That's because you've not told the $#%ing query where to look for the value

On a serious note, your query cannot cycle through your form to pick a value. It will only pick the value from the current record. Your query needs to be told from which table (or query) it needs to cycle through. And you can call your function to return the value from within your query.

 

[carnealse]

Mr Dan...
No such thing as too much info. Not in my line of work anyway haha. Besides, why give you a piece of the puzzle and say figure it out. You have a bigger picture here to work with.
If I remove the End If, then the code is worthless. That End If has to be there. Run it yourself and tell me it doesn't honk at you and tell you that you are missing an End If. There two If statements so it wants two End If's.
Yes it has everything to do with it. This is the code I use to get the phase. That is the code I want to reference from the unbound box per record to get the phase number to filter out all phase two people.
It is not an error, it is a failure to draw information for proper filtering based off criteria in the code.

Mr. vbaInet...
Yeah that #$%^ing makes sense lol. I didn't think about that on the current record only, clearly. So can I tell a field to run the code so that I still get the same info, just in a field and change my unbound to point at that field instead?
Sorry, I blow crap up and do bad things to bad guys for a living. Access and coding are not my primary languages so that is why I am not that clear on this.

 

[BlueIshDan]

Sorry your lack of indenting made me miss your else stuffed in there.

 

[carnealse]

Hey no worries man. I get it. Its an eye screw.
I'd use that Select Case yadda yadda if I had more skills and understood this stuff better. I am new at all this compared to you saltier VBA and Access types.
I don't indent because, well, I don't need to to see it because I am writing it. However, I get that it can be easier for others to sift through the pile of code rubble.
Maybe next time I will remember huh?

 

[vbaInet]

Indenting code is a common practice. In fact it's how code should be written in most environments. You'll be able to spot problems quicker as well.

The code should sit in a function and your function should return the resulting value. This function will take in all the relevant parameters needed for the calculation. You can then call the function in your textbox like this:

=FunctionName([Field1],"SomeText",9)

As you can see you can pass in any type of variable or give it a field name directly.

In your query you need to join it to record source of your form (i.e. the query or table bound to your form) and call the function inside the query like above but without the equal too sign(=).

 

[carnealse]

So indent for easier viewing, check.
Create a function check.
Understand the function you just placed in front of me, IsNull, see what I did there? lol
Anyway, now Im feeling like the guy banging his head. Lets break how I read that function code down; =AnyFuntionNameIWant([WhatIsField1?],"WhatTextForWhat?", WhatIsThis9For?)
I get where to call it in the form. Now the query, I put it in...I am guessing the field section of the query with its own new field name?

 

[vbaInet]

It was only example of the sort of parameters you can pass to the function. It can be a field name, or a string or some number... etc. Before we proceed, let's see the function. Make sure it's the whole thing (i.e. from Public Function to End Function)

And make sure the function is placed in a Module. Not a Class Module but a Module.

 

[carnealse]

Im about ready to start drinking to drown the frustration on my lack of knowledge. Clearly, I just don't get it.
I have no idea how to do a function other than make a module, and say;

public function phase as integer()

Drawing a blank because other than the code I put before to get the phase I don't know how else to get the phase into the unbound textbox, so that is what I thought would go her. I was wrong. It doesn't work in here. Sit Norm All Fd Up.

End function

 

[vbaInet]

Here's your function, place it in a Module (Insert menu > Module):

Public Function GetPhase(blRover As Boolean, _
                         blMessenger As Boolean, _
                         varMockPrt As Variant, _
                         varDaySonBoard As Variant) As Variant

    Dim a As Integer, b As Integer, c As Integer, d As Integer, phasenumber As Integer
    
    If blRover = True Then a = 2
    
    If blMessenger = True Then b = 2
    
    If Nz(varMockPrt, vbNullString) = "PASS" Then
        c = 2
    ElseIf Nz(varDaySonBoard, vbNullString) < 14 And Nz(varMockPrt, vbNullString) = "NA" Then
        c = 4
    End If
    
    If Nz(varDaySonBoard, vbNullString) > 13 Then
        d = 2
    End If
    
    If a + b + c + d = 8 Then
        GetPhase = 2
    Else
        GetPhase = 1
    End If
End Function

Now I need you to test the function with a Msgbox. Create a command button and on the click event call (or run) the function from there:

MsgBox GetPhase(True, True, "Pass",14)

I hope from that you'll understand how the function is run and called.