Scott,
I have got it working in a sample database. (feeling exhilarated!)
I have put a button called cmdAddRecs on my main form.
I have put a subform control called Child13 on my main form.
Here is the code for the click event of cmdAddRecs
---------------------------------------------------------------------------------------Private Sub cmdAddRecs_Click() Dim strSQL As String Dim dbs As DAO.Database Set dbs = DBEngine(0)(0)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_CCNo = """ & Me.[Child13].Form.CFS_CCNo & """ "_ & "AND tbl_UnitLog.UL_Unit Not In " _ & "(SELECT a.UL_Unit " _ & "FROM tbl_UnitLog As a INNER JOIN tbl_UnitLog AS b ON a.UL_Sts=b.UL_Sts " _ & "WHERE b.UL_Sts=""10-8"")"' Debug.Print Me.[Child13].Form.CFS_CCNo' Debug.Print strSQL dbs.Execute strSQL, dbFailOnError set dbs = nothingEnd Sub-------------------------------------------------------------------------------------------You will need to change Child13 to the name of your subform controlJeanette Cunningham"Scott Whetsell, A.S. - WVSP"<
[email protected]> wrote in messagenews:
[email protected]...> Jeanette,>> The status codes are derived from another table, but the code itself is> stored in the Unit Log table as text. Here are my fields in the Unit Log> table and their types.>> Field Type> UL_RCN Autonumber (PK)> UL_Unit Text (len 100)> UL_CCNo Text (len11)> UL_Sts Text (len 6)> UL_Date Date/Time (format: yyyy-mm-dd)> UL_Time Date/Time (format: hh:mm)> UL_Details Memo> UL_User Text (len 3)>> Pertinent status codes are:> DSP = DSP> ENR = 10-17> ARR = 10-23> CLR = 10-8>> The event launching the code is the double click event of a text box and> will set the status code to 10-8 (since that is what we are trying to do).>> Sample data (RCN omitted as it is an autonumber):> ==================================================> UNIT STS TIME DATE CCNO DETAILS> USER> 644 DSP 19:31 12-09-07 2007-000007> ADM> 644 10-17 19:31 '' '' '' '' ''> ''> 715 10-17 19:31 '' '' '' '' ''> ''> 610 10-17 19:41 '' '' '' '' ''> ''> 644 10-23 19:46 '' '' '' '' ''> ''> 705 10-17 19:51 '' '' '' '' ''> ''> 715 10-23 19:54 '' '' '' '' ''> ''> 610 10-23 19:56 '' '' '' '' ''> ''> 644 LOG 19:57 '' '' '' '' ''> CNCL 715 ''> 715 10-8 19:57 '' '' '' '' ''> ''> ==================================================>> With the above data, on double click of the text box on the main formshould> result in identifying that units 644, 715, and 610 are still not cleared,> target those units, and add a new record to the unit log table showingeach> of those units 10-8 at the current time.>> Hope that helps, thanks.>>> "Jeanette Cunningham" wrote:>>> 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 moreinfo.>> 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">> <
[email protected]> wrote in message>> news:
[email protected]...>> > Jeanette,>> >>> > I do feel that we are moving in the right direction. My issues justseem>> > to>> > be more specific because they are more industry specific. I tried thenew>> > code that you had posted and it does detect and return a single recordfor>> > 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 toadd>> > data to tbl_UnitLog.>> >>> > Or is that the wrong direction for what we are trying to do here?>> >>> > Thanks>> >>> >>> >>> > "Jeanette Cunningham" wrote:>> >>> >> 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 anew>> >> 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"" Andtbl_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"" Andtbl_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">> >> <
[email protected]> wrote in message>> >> news:
[email protected]...>> >> > Sorry for the confusion. We are in the process of normalizing the>> >> > status>> >> > codes used, and I can programmatically change the input to theproper>> >> > codes.>> >> >>> >> > Appropriately used for clear will be 10-8.>> >> >>> >> >>> >> > The following query code worked for locating the appropriaterecords,>> >> > but>> >> > I>> >> > am getting and error on running the Insert command that the numberof>> >> > query>> >> > values and destination fields are not the same. I arrived at thecode>> >> > 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>> >> > =========================>> >>>> >>>> >>>>>>>>
