Another Quick Q

[YoungManRiver]

All,

Been looking for an example, as my attempt is causing error, but all the docs I find only show how to read from "form" not write to "form" for selected items in a listbox object.

My code to read is this:

Sub LOC_sav()
    Dim Wspace As dao.Workspace, dbs As dao.Database, rsDEST As dao.Recordset
    Dim ctlSource As Control, strItems As String, iCRow As Integer
    Dim SQLstmt As String, SrcIdx As Variant, Action, LOGusr
    Set Wspace = DBEngine.Workspaces(0)
    Set dbs = CurrentDb
    DoCmd.Hourglass True
    DoCmd.Hourglass False
    SrcIdx = DLookup("loc_id", "tblDATloc", "[loc_id]=" & Targetform![cboxABV])
    If SrcIdx > 0 Then
        SQLstmt = "SELECT * FROM tblDATloc WHERE ([loc_id]=" & Targetform![cboxABV] & ");"
        Action = "EDIT"
    Else
        SQLstmt = "SELECT * FROM tblDATloc;"
        Action = "ADD"
    End If
    Set rsDEST = dbs.OpenRecordset(SQLstmt, dbOpenDynaset)
    With rsDEST
        If Action = "ADD" Then
            .AddNew
            ![loc_aid] = Form_frmASMmain![tboxLID]
            ![loc_adt] = Now
        Else
            .MoveFirst
            .Edit
        End If
        ![loc_ad1] = Targetform![tboxAD1]
        ![loc_ad2] = Targetform![tboxAD2]
        ![loc_cty] = Targetform![tboxCTY]
        ![loc_tmx] = Targetform![cboxTMT]
        ![loc_tcx] = Targetform![cboxTPR]
        ![loc_smx] = Targetform![cboxPSP]
        ![loc_scx] = Targetform![cboxSHP]
        ![loc_nam] = Targetform![tboxLDS]
        ![loc_zip] = Targetform![tboxZIP]
        If Targetform![cboxABV].Visible = True Then
            ![loc_abv] = DLookup("loc_abv", "tblDATloc", "[loc_id]=" & Targetform![cboxABV])
        Else
            ![loc_abv] = Targetform![tboxABV]
        End If
        ![loc_stt] = Targetform![cboxSTT]
        ![loc_ctr] = Targetform![cboxCTR]
        .Update
        .Close
    End With
    SrcIdx = DLookup("ldx_id", "qrySETloc", "[loc_id]=" & Targetform![cboxABV])
    If SrcIdx > 0 Then
        SQLstmt = "SELECT * FROM tblDATl_idx WHERE ([ldx_id]=" & SrcIdx & ");"
        Action = "EDIT"
    Else
        SQLstmt = "SELECT * FROM tblDATl_idx;"
        Action = "ADD"
    End If
    Set rsDEST = dbs.OpenRecordset(SQLstmt, dbOpenDynaset)
    With rsDEST
        Set ctlSource = Targetform![lboxLTY]
        For iCRow = 0 To ctlSource.ListCount - 1
            If ctlSource.Selected(iCRow) Then
                If Action = "ADD" Then
                    .AddNew
                    ![ldx_aid] = Form_frmASMmain![tboxLID]
                    ![ldx_adt] = Now
                Else
                    .MoveFirst
                    .Edit
                End If
                ![ldx_ldx] = Targetform![cboxABV]
                ![ldx_tdx] = ctlSource.Column(0, iCRow)
                ![ldx_eid] = Form_frmASMmain![tboxLID]
                ![ldx_edt] = Now
                .Update
            End If
        Next iCRow
        Set ctlSource = Nothing
        .Close
    End With
    DoEvents
    Set rsDEST = Nothing
    Set dbs = Nothing
    Set Wspace = Nothing
    DoCmd.Hourglass False
End Sub

and this writes to the two database tables fine (both table also contain loc_id => autonumber and ldx_id = > autonumber respectively). The code that is erroring is:

Sub LOC_get()
    Dim Wspace As dao.Workspace, dbs As dao.Database, rsDEST As dao.Recordset
    Dim SQLstmt As String, SrcIdx As Variant, N
    Set Wspace = DBEngine.Workspaces(0)
    Set dbs = CurrentDb
    DoCmd.Hourglass True
    DoCmd.Hourglass False
    SQLstmt = "SELECT * FROM qrySETloc WHERE ([loc_id]=" & Targetform![cboxABV] & ");"
    Set rsDEST = dbs.OpenRecordset(SQLstmt, dbOpenDynaset)
    With rsDEST
        .MoveLast
        .MoveFirst
        Targetform![tboxABV] = ![loc_abv]                   ' Location Abbrev.
        If ![loc_tmx] > 0 Then
            Targetform![cboxTMT] = ![loc_tmx]               ' Location Trans Method
        End If
        If ![loc_tcx] > 0 Then
            Targetform![cboxTPR] = ![loc_tcx]               ' Location Transporter
        End If
        If ![loc_smx] > 0 Then
            Targetform![cboxPSP] = ![loc_smx]               ' Location Ship Method
        End If
        If ![loc_scx] > 0 Then
            Targetform![cboxSHP] = ![loc_scx]               ' Location Shipper
        End If
        Targetform![cboxSTT] = ![loc_stt]                   ' Location State
        Targetform![cboxCTR] = ![loc_ctr]                   ' Location Country
        Targetform![tboxAD1] = ![loc_ad1]                   ' Location Address Line 1
        Targetform![tboxAD2] = ![loc_ad2]                   ' Location Address Line 2
        Targetform![tboxCTY] = ![loc_cty]                   ' Location City
        Targetform![tboxLDS] = ![loc_nam]                   ' Location Description
        Targetform![tboxZIP] = ![loc_zip]                   ' Location Zip Code
'        For N = 1 To .RecordCount
'            Targetform![lboxLTY].ItemData (![ldx_tdx])      ' Location Type (Multiple)
'            .MoveNext
'        Next N
        .Close
    End With
    DoEvents
    Set rsDEST = Nothing
    Set dbs = Nothing
    Set Wspace = Nothing
    DoCmd.Hourglass False
End Sub

where the commented out code is where the error occurs. Know it is a simple "get the syntax or methods right" situation, but not finding an example has me bent.

Would appreciate all help, especially now!!

Oh! Code for the query is:

SELECT DISTINCT tblDATloc.loc_id, tblDATloc.loc_abv, tblDATloc.loc_nam, tblDATltype.lty_typ, tblDATltype.lty_des, tblDATloc.loc_ad1, tblDATloc.loc_ad2, tblDATloc.loc_cty, tblDATloc.loc_stt, tblDATloc.loc_zip, tblDATloc.loc_ctr, tblDATl_idx.ldx_id, tblDATl_idx.ldx_tdx, tblDATloc.loc_tmx, tblDATloc.loc_smx, tblDATloc.loc_tcx, tblDATloc.loc_scx
FROM ((((((tblDATloc LEFT JOIN tblDATltype ON tblDATloc.loc_typ = tblDATltype.lty_id) LEFT JOIN tblDATl_idx ON tblDATloc.loc_id = tblDATl_idx.ldx_ldx) LEFT JOIN tblDATltype AS tblDATltype_1 ON tblDATl_idx.ldx_tdx = tblDATltype_1.lty_id) LEFT JOIN tblDATtmeth ON tblDATloc.loc_tmx = tblDATtmeth.tmt_id) LEFT JOIN tblDATtmeth AS tblDATtmeth_1 ON tblDATloc.loc_smx = tblDATtmeth_1.tmt_id) LEFT JOIN tblDATtrans ON tblDATloc.loc_tcx = tblDATtrans.trs_id) LEFT JOIN tblDATtrans AS tblDATtrans_1 ON tblDATloc.loc_scx = tblDATtrans_1.trs_id;

Thanks!

YMR

 

[gwinn7]

Please forgive, but I am not seeing which line of code is causing the error and what the error is! What is the error? What happens when you step-through the code?

Apart from your main concern, I am seeing a number of ways to strengthen your code for performance, testing, and handling errors.

Gary
gwinn7

 

[YoungManRiver]

Gary,

Did you understand it was the lines I commented out that error, when you remove the comment markers?

YMR

 

[gwinn7]

OK, I see the lines now, but what is the error?

Gary
gwinn7