Insert Into + DLookUp + IF criteria

[deejayquai]

Hello

I'm trying to append a series of values into fields from one table to
another based on highlighted rows in a multi-select listbox. I'm
stuck however when I need to append a couple of fields which is based
on a Dlookup kind of function and an IF function.


My basic code is based on:


For Each lbl1ID In lstStudents.ItemsSelected


DoCmd.RunSQL "INSERT into tblGroupMembers ([Student ID], [Group
Code ID], [Surname], [First Name], [Class Year],[Initials],[Form],
[SEN Status],[Gifted/Talented Status],[Gender],[Current WL Level])" _
& "VALUES (""" & lstStudents.Column(0, lbl1ID) _
& """,""" & [Group Code ID] _
& """,""" & lstStudents.Column(1, lbl1ID) _
& """,""" & lstStudents.Column(2, lbl1ID) _
& """,""" & lstStudents.Column(6, lbl1ID) _
& """,""" & Nz(lstStudents.Column(3, lbl1ID), Null) _
& """,""" & lstStudents.Column(7, lbl1ID) _
& """,""" & lstStudents.Column(4, lbl1ID) _
& """,""" & lstStudents.Column(5, lbl1ID) _
& """,""" & lstStudents.Column(8, lbl1ID) _
& """,""" & [cboAchieve] & """)"
Next:


Works fine. But I now need to insert into two new fields- [LY Effort]
and [LY Attainment] using a dlookup function based on the [Student ID]
value in the list box (ie Column 0) and the [Class Year] value in the
list box (ie Column 6) which has to be -1 (ie the previous year!).


Something like this-


[LY Effort] = DLookup("[Overall Effort]", "tblGroupMembers", "[Student
ID] = lstStudents.Column(0,lbl1ID}" And "[Class Year] =
lstStudents.Column(6,lbl1ID)-1")


[LY Attainment]= = DLookup("[Overall Attaiment]", "tblGroupMembers",
"[Student ID] = lstStudents.Column(0,lbl1ID}" And "[Class Year] =
lstStudents.Column(6,lbl1ID)-1")


The final bit is that I don't want to append the [LY Effort] or [LY
Attainment] into the new record if the [Class Year] -1 value is 7 or
if it is null (as the records won't exist!). I need some kind of
error transaction handler that lets those rows highlighted in the list
box pass through up to the point where the rule is broken.


Something like this-


If lstStudents(6,lbl1ID)-1 = 7 or IsNull Then Stop Transaction etc etc


Simple eh!


Grateful for anyones time and patience on this. I have tried
searching very hard and attempted many times to work this out before
posting here. I've used a series of variables and split the For Each
Next routine through a If Then Else but I think I'm out of my depth on
this one!


regards


David

 

[WayneRyan]

David,

Not sure what you mean.

You will always write a new record.
It will have the fields [LY Effort] & [LY Attainment] in it.

The contents of these fields can be provided by the Nz and DLookUp functions.

DoCmd.RunSQL "INSERT into tblGroupMembers ([Student ID], " & _
             "                             [Group Code ID], " & _
             "                             [Surname], " & _
             "                             [First Name], " & _
             "                             [Class Year], 
             "                             [Initials], 
             "                             [Form], " & _
             "                             [SEN Status], " & _
             "                             [Gifted/Talented Status], " & _
             "                             [Gender], " & _
             "                             [LY Effort], " & _        <------- Add 2 new fields
             "                             [LY Attainment]) " & _    <-------
             "VALUES (""" & Me.lstStudents.Column(0, lbl1ID) & """,""" & _
                            Me.[Group Code ID] & """,""" & _
                            Me.lstStudents.Column(1, lbl1ID) & """,""" & _
                            Me.lstStudents.Column(2, lbl1ID) & """,""" & _
                            Me.lstStudents.Column(6, lbl1ID) & """,""" & _
                            Nz(Me.lstStudents.Column(3, lbl1ID), Null) & """,""" & _
                            Me.lstStudents.Column(7, lbl1ID) & """,""" & _
                            Me.lstStudents.Column(4, lbl1ID) & """,""" & _
                            Me.lstStudents.Column(5, lbl1ID) & """,""" & _
                            Me.lstStudents.Column(8, lbl1ID) & """,""" & _
                            Me.[cboAchieve] & """,""" & _
    use Nz & Dlookup ---->  Nz(DLookup("[Overall Effort]" ...), "Default Value Or Empty String") & """,""" & _
    to provide data  ---->  Nz(DLookup("[Overall Attaiment]" ...), "Default Value Or Empty String") & """)"

If you didn't want to enter a record if the overall Effort and Attainment weren't available
you'd need something like:

If  Len(Me.lstStudents.Column(6, lbl1ID)) = 0 Or _
   CLng(Me.lstStudents.Column(6, lbl1ID)) = 7 Then
     MsgBox("No Transaction")
Else
     DoCmd.RunSQL ...
End If

hth,
Wayne