QueryDef (1 Viewer)

[DanielAngin]

I have append query that I expect to [add currCarID] to my "tblMultiSelectReportCat" when I made a selection on the form. The codes seems to be ok (no error) but nothing is appeneded to the "tblMultiSelectReportCat" when I made my selections

Append Query (ACCESS SQL):

INSERT INTO tblMultiSelectReportCat ( CatID )
SELECT [currCatID] AS Expr1
FROM tblMultiSelectReportCat;


Option Compare Database
Option Explicit
Dim qdfCatRptAdd As QueryDef
Dim qdfCatRptRem As QueryDef
--------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute "Delete * FROM tblMultiSelectReportCat"
Set qdfCatRptAdd = CurrentDb.QueryDefs("qryAppCatID")
Set qdfCatRptRem = CurrentDb.QueryDefs("qryRemCatID")
End Sub
--------------------------------------------------
Private Sub lboHosCat_AfterUpdate()
Dim intCurrCat As Integer
Dim strChosenHospital As String

If Me!lboHosCat.Selected(Me!lboHosCat.ListIndex) Then
qdfCatRptAdd.Parameters("currCatID") = Me!lboHosCat.ItemData(Me!lboHosCat.ListIndex)
qdfCatRptAdd.Execute dbFailOnError
Else
qdfCatRptRem.Parameters("currCatID") = Me!lboHosCat.ItemData(Me!lboHosCat.ListIndex)
qdfCatRptRem.Execute dbFailOnError
End If

-------------------------------------------------------

I appreciate your inputs and assistance.

Thank you -- Daniel

 

[VilaRestal]

Change the query to

INSERT INTO tblMultiSelectReportCat (CatID)
VALUES ([currCatID])

should do it for qryAppCatID

DELETE FROM tblMultiSelectReportCat WHERE CatID = [currCatID]

should do it for qryRemCatID (I guess)

I think the code's OK except...

 

[VilaRestal]

I would say the declaring of the query defs at the module level and assigning them on Form Open is unnecessary and not great practice. Could I suggest this:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "Delete * FROM tblMultiSelectReportCat"
End Sub

Private Sub lboHosCat_AfterUpdate()
    Dim intCurrCat As Integer
    Dim strChosenHospital As String
    Dim qdf As QueryDef
    If Me!lboHosCat.Selected(Me!lboHosCat.ListIndex) Then
        Set qdf = CurrentDb.QueryDefs("qryAppCatID")
    Else
        Set qdf = CurrentDb.QueryDefs("qryRemCatID")
    End If
    qdf.Parameters("currCatID") = Me!lboHosCat.ItemData(Me!lboHosCat.ListIndex)
    qdf.Execute dbFailOnError
End Sub

is simpler?

 

[DanielAngin]

Kudos to you Vila! I tested and both and they work great. Thank you...

 

[VilaRestal]

You're welcome. If I may add the lboHosCat_AfterUpdate sub could even be rewritten as this:

Private Sub lboHosCat_AfterUpdate()
    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs(IIf(Me!lboHosCat.Selected(Me!lboHosCat.ListIndex),"qryAppCatID","qryRemCatID"))
    qdf.Parameters("currCatID") = Me!lboHosCat.ItemData(Me!lboHosCat.ListIndex)
    qdf.Execute dbFailOnError
End Sub

For extra succinctness.