Filter specific records in subform

[Scott Whetsell, A.S. - WVSP]

My subform tracks when personnel are dispatched, arrive, etc., to different
events. My main form tracks the first times for dispatch, enroute, arrive,
and the last clearing time. The subform shows the complete history for a
specific incident, tracked by a unique sequence number (not an autonumber).
The linking field is this sequence number between the main and subform.
Currently if a user double-clicks the clearing time box on the main form, it
will add the clear event to the subform for the primary unit. I would like
to expand this that it detects all other units who also were arrived, but not
yet cleared, and clear them.

I have an independent query that will show the most recent activity event
for each unit.

How can I detect which units have not cleared at the time the clear box is
double-clicked and clear them?

Important fields in the subform are:
UnitID
IncidentNumber (Linking field)
Status



Any ideas are appreciated,
Thanks

 

[roger]

Sounds to me like you need to write a UPDATE QUERY that updates this stuff.

And then when the user clicks on whatever he clicks on, you run that update
query, (and then requery your subform to show the result)

Do I understand the question?

 

[Scott Whetsell, A.S. - WVSP]

Essentially that is what needs done, however I do not want to rewrite the
existing data, but add new records showing what time the units cleared.

 

[roger]

Then its an APPEND QUERY
same difference

Essentially that is what needs done, however I do not want to rewrite the
existing data, but add new records showing what time the units cleared.

 

[Scott Whetsell, A.S. - WVSP]

I understand that I am appending to the record set. My question is how to
identify which units were assigned from the list and have not cleared, then
clear those units.

What I want to tell it to do is:

For Each Unit in subform Where last status <> clear then add record with
unit status = clear.

 

[Jeanette Cunningham]

Hi Scott,
It sounds as if you wish to change the cleared status for selected records
in your subform.
const cstrStub = "UPDATE tblYourTable SET tblTableYourTable.UnitStatus =
""clear"" WHERE "
strWhere = *this is the bit you need to work out*
strSQL = cstrStub & strWhere

Getting the strWhere is the bit you need to work on.
We can't just update all records to UnitStatus = clear, because you just
want the uncleared ones that show on your subform.
How do you select the records which show on your subform? This will be the
clue to setting up strWhere for the update query.
Does this process make sense?
Jeanette Cunningham




"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

The subform shows all unit activity assigned to that call, not just the most
recent. The binding between the subform and main form is a unique assigned
number.

I need to isolate the most recent record for each unit in the subform. If
their status is not equal to the clear code, then I need to add a new record
with that unit and status clear.

This is a little hard to explain, sorry I am having trouble clarifying.

 

[Jeanette Cunningham]

Scott,
sounds like if you know the CallID for the subform, you can set the status
to clear for all calls with that CallID on your subform. Is this more like
your setup?

So maybe we have an update query more like this:
strSQL = "UPDATE tblCalls SET tblCalls.UnitStatus = ""clear"" WHERE
TblCalls.CallID = " & Me.txtCallID & ""
The value for Me.txtCallID will come from the textbox for CallID on your
subform.
I am assuming that CallID is a number field. It it is a text field:
use = """ & Me.txtCallID & """"
The code that runs the update query will be on the subform.

Without seeing your form and subform in front of me, I am still guessing,
but hope this puts you on the right track.

Jeanette Cunningham

"Scott Whetsell, A.S. - WVSP"

 

[Jeanette Cunningham]

Scott,
I have just re-read your initial post.

The query would look more like this:

strSQL = "UPDATE tblName SET tblName.Status = ""clear"" WHERE
tblName.IncidentNumber = " & Me.SubformName.Form.txtIncidentNumber & ""

I don't know the name of the table, substitute your table name.
I assume you have a textbox on the subform that has the IncidentNumber on
it. I have called it txtIncidentNumber.
It looks like the UnitID has a different value for each row on your subform.

Hope this helps
Jeanette Cunningham



"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Below is a text representation of the subform, again the only important parts
of the main form is the callID and Clear time

Subform:
UNIT STATUS DESCR TIME CALLID
101 DSP 12:00 2007-00001
105 ENR Will asst 12:00 2007-00001
101 ENR 12:01 2007-00001
103 ARR 12:05 2007-00001
105 ARR 12:06 2007-00001
101 ARR 12:07 2007-00001
103 CLR 12:10 2007-00001

When the clear time field is filled in on the main form, I would like it to
add records indicating that units 101,105 are clear and whatever time. It
should realize that Unit 103 is already cleared, and disregard that unit.

Does that help clarify?

 

[Jeanette Cunningham]

Scott,
Thanks for the info, it changes the ideas a lot.
You want to add 2 more records to tblName
the new records would be

Unit Status Time CallID
101 Clr 2007-00001
105 Clr 2007-00001

How will your query know what value to add for Time, is it recorded on your
subform? or is it the current date and time?

strSQL = "INSERT INTO tblName ( Unit, CallID ) " _
& "SELECT DISTINCT tblName.Unit, tblName.CallID " _
& "FROM tblName " _
& "WHERE tblName.Status <> ""Clr"" And tblName.CallID = """
& me.txtCallID & """"
db.execute strSQL, dbFailOnError

the above will add the 2 records for Units 101 and 105and CallID to tblName.
Note: I haven't set up this table and tried the query, I have checked it but
there could be an error there, use your own table name.

After the 2 records with Unit and CallID have been added to the table, we
will set the Status and Time for them with an Update query.

You can have a go at doing the update query yourself. You need to select the
2 unit numbers we just added in the query above, set theit status to Clr and
their Time to Now.

Jeanette Cunningham




"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Jeanette,

I'll admit up front that I am not good with SQL string queries. I changed
the fields as below and I am receiving an error of: "Too few parameters.
Expected 1."

======== Here is how I updated the code========
strSQL = "INSERT INTO tbl_UnitLog ( UL_Unit, UL_CCNo ) " _
& "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo =
""" & Me.CFS_CCNo & """"
dbs.Execute strSQL, dbFailOnError
====================================

My target table is tbl_UnitLog
Fields are: UL_Unit, UL_CCNo, UL_Sts, UL_Time, UL_Date, UL_Details, UL_User


Thanks

 

[Jeanette Cunningham]

Scott,
the error message is Access trying to tell you that it can't find the data
to do the update query.
I think this makes it pretty clear that the control on your form called
CFS_CCNo is a for a number data type.
I had written the code for a text data type for that field.

replace """ & Me.CFS_CCNo & """" ( last line of query)
with " & Me.CFS_CCNo & ""

another point is that the status in your table is not a number, it was
something like CLR or ARR etc in your last post
so
replace ""10-18"" (on the 4th line of your code in this post)
with ""CLR""


as a test, please add the following to your code
______________________________________________________________________
strSQL = "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""CLR"" And tbl_UnitLog.UL_CCNo = "
& Me.CFS_CCNo & ""
Debug.Print "strSQL: " & strSQL
_______________________________________________________________________
above the previous code, so that it looks just like the code below
___________________________________________________________________
strSQL = "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""CLR"" And tbl_UnitLog.UL_CCNo = "
& Me.CFS_CCNo & ""
Debug.Print strSQL

strSQL = "INSERT INTO tbl_UnitLog ( UL_Unit, UL_CCNo ) " _
& "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""CLR"" And tbl_UnitLog.UL_CCNo =
" & Me.CFS_CCNo & ""
dbs.Execute strSQL, dbFailOnError
______________________________________________________________________

Now either try to run this code on your form in form view or step through
the code in the VBA editor.
after the end of the line Debug.Print strSQL, access will print the query
string in the immediate window
Press Ctl+G to open the immediate window
You should see strSQL followed by a query string
Copy the query string and paste it into the SQL view of a new query. Open
the query in dataview.
If the query returns records without any errors - that's good. Remove the
first extra lines of code that we put in for the test.
If any errors, post back.


Try the above
Jeanette Cunningham


"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Jeanette,

I will try the new code as you indicated below. Just to clarify, the CCNo
field and Sts field are both text fields. Sts could be text or numeric code.
Status code 10-8 is the same as CLR.

 

[Jeanette Cunningham]

Scott,
Thanks for the clarification.
2 things:
use tbl_UnitLog.UL_CCNo = """ & Me.CFS_CCNo & """"
because CCNo is a text field.

There will be a problem doing this update query with the Sts field if 10-8
is the same as CLR.

If we use WHERE tbl_UnitLog.UL_Sts <> ""10-8""
Access will be searching the table and looking for all entries in the Sts
field that have 10-8

If we use WHERE tbl_UnitLog.UL_Sts <> ""CLR""
Access will be searching the table and looking for all entries in the Sts
field that have CLR
Your table will have one or the other, not both.
Only one of them will allow the update query to work.
In a previous post, you showed that the entries in the field called Sts were
CLR, ENR, ARR
If all the entries in the Sts field are like CLR, ENR, ARR this is what you
need to use
If all the entries in the Sts field are like 10-8 then use that instead.

Hope this makes sense to you.

Jeanette Cunningham



"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Sorry for the confusion. We are in the process of normalizing the status
codes used, and I can programmatically change the input to the proper codes.

Appropriately used for clear will be 10-8.


The following query code worked for locating the appropriate records, but I
am getting and error on running the Insert command that the number of query
values and destination fields are not the same. I arrived at the code below
by troubleshooting your code in a new query.


===========code===========
Set dbs = CurrentDb
Dim strSQL As String
Dim sts As String
sts = "10-8"
strSQL = "INSERT INTO tbl_UnitLog (UL_Unit,UL_CCNo)" & _
"SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit,
Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"
dbs.Execute strSQL, dbFailOnError

Me.UnitLogSub.Requery
=========================

 

[Jeanette Cunningham]

Scott,
hope you get the feeling that we are making progress, because I do.

My target table is tbl_UnitLog
Fields are: UL_Unit, UL_CCNo, UL_Sts, UL_Time, UL_Date, UL_Details,
UL_User

Just to clarify that I am understanding your last post in the way you
intended -
Using the code below, you can get the query string, paste it into a new
query and it returns records - is this bit correct?
-----------------------------------------------------------------------------------------
strSQL = "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo =
"""
& Me.CFS_CCNo & """"
Debug.Print "strSQL: " & strSQL
------------------------------------------------------------------------------------------
If the above bit is correct than we can continue.
If tbl_UnitLog has the field Sts with 10-8 instead of CLR then
If the code above correctly returns records from the Debug.Print string,
then the correct update query is

strSQL = "INSERT INTO tbl_UnitLog ( UL_Unit, UL_CCNo ) " _
& "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo =""
" & Me.CFS_CCNo & """"
dbs.Execute strSQL, dbFailOnError

Try this exact code above and post back.

Jeanette Cunningham




"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Jeanette,

I do feel that we are moving in the right direction. My issues just seem to
be more specific because they are more industry specific. I tried the new
code that you had posted and it does detect and return a single record for
each unit, however it is not excluding the units whose status is 10-8. I had
changed the select string some and I am getting the correct results, I just
don't know what to do with it to add the records.

===== MY CODE =====

strSQL = "SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit, " & _
"Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"

=================

Is there a way to target the results of the query, ie.
For each record in strSQL add .rst info.

I am using the .rst command throughout other parts of the program to add
data to tbl_UnitLog.

Or is that the wrong direction for what we are trying to do here?

Thanks

Scott,
hope you get the feeling that we are making progress, because I do.

My target table is tbl_UnitLog
Fields are: UL_Unit, UL_CCNo, UL_Sts, UL_Time, UL_Date, UL_Details,
UL_User

Just to clarify that I am understanding your last post in the way you
intended -
Using the code below, you can get the query string, paste it into a new
query and it returns records - is this bit correct?
-----------------------------------------------------------------------------------------
strSQL = "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo =
"""
& Me.CFS_CCNo & """"
Debug.Print "strSQL: " & strSQL
------------------------------------------------------------------------------------------
If the above bit is correct than we can continue.
If tbl_UnitLog has the field Sts with 10-8 instead of CLR then
If the code above correctly returns records from the Debug.Print string,
then the correct update query is

strSQL = "INSERT INTO tbl_UnitLog ( UL_Unit, UL_CCNo ) " _
& "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo =""
" & Me.CFS_CCNo & """"
dbs.Execute strSQL, dbFailOnError

Try this exact code above and post back.

Jeanette Cunningham




"Scott Whetsell, A.S. - WVSP"

Sorry for the confusion. We are in the process of normalizing the status
codes used, and I can programmatically change the input to the proper
codes.

Appropriately used for clear will be 10-8.


The following query code worked for locating the appropriate records, but
I
am getting and error on running the Insert command that the number of
query
values and destination fields are not the same. I arrived at the code
below
by troubleshooting your code in a new query.


===========code===========
Set dbs = CurrentDb
Dim strSQL As String
Dim sts As String
sts = "10-8"
strSQL = "INSERT INTO tbl_UnitLog (UL_Unit,UL_CCNo)" & _
"SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit,
Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"
dbs.Execute strSQL, dbFailOnError

Me.UnitLogSub.Requery
=========================

 

[Jeanette Cunningham]

Scott,
we need to avoid using Last in the query because Last is unreliable.
I have done some work on this, getting closer, but need a little more info.
What values do you have in tbl_UnitLog for UL_CCNo - post the values for
Units 101, 103 and 105 please.

I assume you have a table with status that includes 10-8 and the other
values that replace DSP, ARR etc. Please post the table name and field
name(s).






"Scott Whetsell, A.S. - WVSP"

Jeanette,

I do feel that we are moving in the right direction. My issues just seem
to
be more specific because they are more industry specific. I tried the new
code that you had posted and it does detect and return a single record for
each unit, however it is not excluding the units whose status is 10-8. I
had
changed the select string some and I am getting the correct results, I
just
don't know what to do with it to add the records.

===== MY CODE =====

strSQL = "SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit, " & _
"Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"

=================

Is there a way to target the results of the query, ie.
For each record in strSQL add .rst info.

I am using the .rst command throughout other parts of the program to add
data to tbl_UnitLog.

Or is that the wrong direction for what we are trying to do here?

Thanks

Scott,
hope you get the feeling that we are making progress, because I do.

My target table is tbl_UnitLog
Fields are: UL_Unit, UL_CCNo, UL_Sts, UL_Time, UL_Date, UL_Details,
UL_User

Just to clarify that I am understanding your last post in the way you
intended -
Using the code below, you can get the query string, paste it into a new
query and it returns records - is this bit correct?
-----------------------------------------------------------------------------------------
strSQL = "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo
=
"""
& Me.CFS_CCNo & """"
Debug.Print "strSQL: " & strSQL
------------------------------------------------------------------------------------------
If the above bit is correct than we can continue.
If tbl_UnitLog has the field Sts with 10-8 instead of CLR then
If the code above correctly returns records from the Debug.Print
string,
then the correct update query is

strSQL = "INSERT INTO tbl_UnitLog ( UL_Unit, UL_CCNo ) " _
& "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo
=""
" & Me.CFS_CCNo & """"
dbs.Execute strSQL, dbFailOnError

Try this exact code above and post back.

Jeanette Cunningham




"Scott Whetsell, A.S. - WVSP"

Sorry for the confusion. We are in the process of normalizing the
status
codes used, and I can programmatically change the input to the proper
codes.

Appropriately used for clear will be 10-8.


The following query code worked for locating the appropriate records,
but
I
am getting and error on running the Insert command that the number of
query
values and destination fields are not the same. I arrived at the code
below
by troubleshooting your code in a new query.


===========code===========
Set dbs = CurrentDb
Dim strSQL As String
Dim sts As String
sts = "10-8"
strSQL = "INSERT INTO tbl_UnitLog (UL_Unit,UL_CCNo)" & _
"SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit,
Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"
dbs.Execute strSQL, dbFailOnError

Me.UnitLogSub.Requery
=========================

 

[Jeanette Cunningham]

Scott,
we need to using Last in a query because it is unreliable.
I have built myself the tbl_UnitLog and tblUnitSts
To solve this issue we need to your table with the values for Status.
Can you please post the details of your table that has 10-8 and what ever
the other values are for ARR


"Scott Whetsell, A.S. - WVSP"

Jeanette,

I do feel that we are moving in the right direction. My issues just seem
to
be more specific because they are more industry specific. I tried the new
code that you had posted and it does detect and return a single record for
each unit, however it is not excluding the units whose status is 10-8. I
had
changed the select string some and I am getting the correct results, I
just
don't know what to do with it to add the records.

===== MY CODE =====

strSQL = "SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit, " & _
"Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"

=================

Is there a way to target the results of the query, ie.
For each record in strSQL add .rst info.

I am using the .rst command throughout other parts of the program to add
data to tbl_UnitLog.

Or is that the wrong direction for what we are trying to do here?

Thanks

Scott,
hope you get the feeling that we are making progress, because I do.

My target table is tbl_UnitLog
Fields are: UL_Unit, UL_CCNo, UL_Sts, UL_Time, UL_Date, UL_Details,
UL_User

Just to clarify that I am understanding your last post in the way you
intended -
Using the code below, you can get the query string, paste it into a new
query and it returns records - is this bit correct?
-----------------------------------------------------------------------------------------
strSQL = "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo
=
"""
& Me.CFS_CCNo & """"
Debug.Print "strSQL: " & strSQL
------------------------------------------------------------------------------------------
If the above bit is correct than we can continue.
If tbl_UnitLog has the field Sts with 10-8 instead of CLR then
If the code above correctly returns records from the Debug.Print
string,
then the correct update query is

strSQL = "INSERT INTO tbl_UnitLog ( UL_Unit, UL_CCNo ) " _
& "SELECT DISTINCT tbl_UnitLog.UL_Unit, tbl_UnitLog.UL_CCNo " _
& "FROM tbl_UnitLog " _
& "WHERE tbl_UnitLog.UL_Sts <> ""10-8"" And tbl_UnitLog.UL_CCNo
=""
" & Me.CFS_CCNo & """"
dbs.Execute strSQL, dbFailOnError

Try this exact code above and post back.

Jeanette Cunningham




"Scott Whetsell, A.S. - WVSP"

Sorry for the confusion. We are in the process of normalizing the
status
codes used, and I can programmatically change the input to the proper
codes.

Appropriately used for clear will be 10-8.


The following query code worked for locating the appropriate records,
but
I
am getting and error on running the Insert command that the number of
query
values and destination fields are not the same. I arrived at the code
below
by troubleshooting your code in a new query.


===========code===========
Set dbs = CurrentDb
Dim strSQL As String
Dim sts As String
sts = "10-8"
strSQL = "INSERT INTO tbl_UnitLog (UL_Unit,UL_CCNo)" & _
"SELECT tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit,
Last(tbl_UnitLog.UL_STS) AS LastOfUL_STS" & _
"FROM tbl_UnitLog" & _
"GROUP BY tbl_UnitLog.UL_CCNo, tbl_UnitLog.UL_Unit" & _
"HAVING (((tbl_UnitLog.UL_CCNo)=[Me].[CFS_CCNo]) AND
((Last(tbl_UnitLog.UL_STS))<>sts))"
dbs.Execute strSQL, dbFailOnError

Me.UnitLogSub.Requery
=========================