list box in a continuous form

[randompherret]

I've been running my head into a wall all morning on this, I'm sure it is something simple I am missing. A point in the right direction would be helpful.

I work at a landscape company. In my database I have customers, that can have multiple contracts. Generally contracts are of one type, just seasonal mowing, or snow plowing, etc. However some contracts can have 2 or more types. In my attempt to normalize the database I have:
tblcontracts
ContractId (key)
CustomerId
OtherData
jctContracts
ContractId (key)
ContractType (key)
tblContractType
TypeId (key)
TypeName

What I want to do is have a continuous form that shows all the contracts for a customer with a multiple selection listbox (only 3 or 4 types right now, but a combo box may get used later if we end up with more types) that a user could select the types it is.

My first thought was a subform with the listbox in it but you cant have a subform in a continuous form.

In my many attempts this morning i made a query with subqueries that check and uncheck boxes based on existing data, but that data isn't updateable.

I am almost about to just unnormalize as we have so few types.

thanks ahead of time for any help.

 

[randompherret]

my solution:

I gave up on the listbox idea and ran with the check boxes.
instead of subqueries i have a main query for most of the details
for the interested:
xtContractByType

TRANSFORM Nz(-Count([sitenumber]),0) AS HasType
SELECT jctContracts.ContractId
FROM tblContracts INNER JOIN jctContracts ON tblContracts.ID = jctContracts.ContractId
GROUP BY jctContracts.ContractId
PIVOT jctContracts.ContractType In (1,2,3);

then to:
qryContractList

PARAMETERS [Forms]![frmCustomers]![cboSiteName] Short;
SELECT tblContracts.ID, tblContracts.ContractNumber, tblContracts.SiteNumber, tblContracts.DateSigned, tblContracts.SoldBy, tblContracts.TotalSeasons, tblContracts.canceled
FROM tblContracts
WHERE (((tblContracts.SiteNumber)=[Forms]![frmCustomers]![cboSiteName]))
ORDER BY tblContracts.canceled;

On the form i have check marks for fsa, snow and 1x.their source is

=DLookUp("[1]","[xtContractByType]","[contractid]=[id]")

they are not changeable because of the cross tab fun. so i made this sub:

Sub ToggleContractTypes(Checked As Boolean, ContractId As Integer, ContractType As Integer)
  Dim dbs As DAO.Database
  Dim sql As String
  Set dbs = CurrentDb
If Checked = True Then
sql = "DELETE jctContracts.ContractId, jctContracts.ContractType FROM jctContracts WHERE (((jctContracts.ContractID) = " & ContractId & ") And ((jctContracts.ContractType) = " & ContractType & "))"
Else
sql = "INSERT INTO jctContracts ( ContractId, ContractType ) SELECT " & ContractId & " AS ContractId, " & ContractType & " AS ContractType"
End If

dbs.Execute sql, dbFailOnError
End Sub

and for each button i have this code:

Private Sub chkfsa_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
ToggleContractTypes chkfsa, [id], 1
Me.Requery
End Sub

I also have that code on the keydown.
The result is usable. it has an extra 1/2 second of lag then a normal checking does, but to the user it looks no different.

had an issue with posted code, edited

Any suggestions on how to improve this solution?