SQL Statement wording

[magmike]

I can't quite seem to write the SQL statement right for this issue -
help would be awesome!

First, some background:
My main table is called ProspectTable. This is where the main company
information is kept. The company is identified by ID, and in related
tables, ID is inserted into a field called CompanyID. I've recently
created a Table called SelectionCampaign and another called
SelectionList. It allows me to create "Lists" for different reasons.

When utilizing this feature, the user first creates a campaign and
gives it a name, such as "THCA Trade Show Attendee". Then, a user can,
using the main form, ProspectForm, search and mark all the desired
companies by checking the SelectMe checkbox, which is a field in the
main table ProspectTable. Then, when finished, the user pushes a
command button (Save List), which then inserts into the table
SelectionList, the current campaign id (CampID - which is populated
into an invisible unbound field on ProspectForm when the user pushes
the Start List button), and CompanyID. Once that is done, the user can
click a "Stop List" button which clears the invisible unbound field
for CampID, and then unselects the field SelectMe that are currently
selected.

This part works fine, however, in creating a "Load List" button, which
first populates the invisible unbound field with CampID (based on the
selected "campaign"), and then runs SQL to mark all the companies that
have a record in the SelectionList table with the matching CampID.

When I run this code, however, I get a box asking for
SelectionList.CompanyID. This button is pushed from within a subform
(SelCampStart - which is populated by the form SelectionCampaignStart)
on the main form (ProspectForm). Here is the code:

Private Sub Command7_Click()
Dim stSQL As String

stSQL = "UPDATE ProspectTable " & _
"SET ProspectTable.SelectMe = -1 " & _
"WHERE ProspectTable.ID = SelectionList.CompanyID AND Forms!
ProspectForm!CampID = SelectionList.CampID;"

Forms!ProspectForm!SelCampID = CampID
DoCmd.RunCommand acCmdRefresh

DoCmd.RunSQL stSQL

DoCmd.GoToControl COMPANY
DoCmd.ApplyFilter , "SelectMe = -1"
End Sub

Thanks in advance for you help!

magmike

 

[Ken Sheridan]

You are referencing the SelectionList table, but this is not included in the
SQL statement. If I've understood you correctly I think you'll need to use a
subquery:

stSQL = "UPDATE ProspectTable" & _
" SET SelectMe = TRUE" & _
" WHERE EXISTS " & _
"(SELECT *" & _
" FROM SelectionList" & _
" WHERE SelectionList.CompanyID = ProspectTable.ID
" AND SelectionList.CampID = " & Me.CampID & ")"

A few supplementary points:

1. Use the TRUE constant rather than the implementation of a Boolean TRUE
as -1.

2. When building the SQL statement concatenate the value of the CampID
control into the string rather than a reference to it. The above assumes
that CampID is a number data type. If its text you'd use:

" AND CampID = """ & Me.CampID & """)"

to wrap the value in quotes characters.

3. You can use Me to refer to the current form rather than fully
referencing it.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Missed a few characters off the end of one line; should have been:

stSQL = "UPDATE ProspectTable" & _
" SET SelectMe = TRUE" & _
" WHERE EXISTS " & _
"(SELECT *" & _
" FROM SelectionList" & _
" WHERE SelectionList.CompanyID = ProspectTable.ID" & _
" AND SelectionList.CampID = " & Me.CampID & ")"

Ken Sheridan
Stafford, England