I have a simple form with a list box. The list box is populated from a table and contains names of people.
When a name is clicked in the form the following code is run:
Code:
Private Sub cmbResource_AfterUpdate()
MsgBox "On the next screen type Y for active projects or N for inactive projects"
DoCmd.OpenQuery "qryResourceProject", acViewNormal, acReadOnly
End Sub
The selected value is passed to a query which perfoms the following:
Code:
PARAMETERS [Y or N] Text ( 255 );
SELECT dbo_PROJECT.pjct_id, dbo_PROJECT.pjct_describe AS Description, dbo_PROJECT.pjct_cust_name AS Customer, dbo_PROJECT.pjct_tp_code AS TPCode, qryDevResource.res_name AS B2BLead, qryDevResource_1.res_name AS B2BDev, qryCSSResource.res_name AS CSS, dbo_PROJECT.pjct_receive_date AS ReceiveDate, dbo_PROJECT.pjct_compl_rqst AS CompletionRequested, dbo_PROJECT.pjct_compl_perc AS [Completion%], dbo_PROJECT.pjct_compl_actl AS CompletionActual, dbo_PROJECT.pjct_type AS Type, dbo_PROJECT.pjct_cat AS Category, dbo_PROJECT.pjct_active
FROM ((dbo_PROJECT LEFT JOIN qryDevResource ON dbo_PROJECT.pjct_is_res_lead_id = qryDevResource.res_id) LEFT JOIN qryDevResource AS qryDevResource_1 ON dbo_PROJECT.pjct_is_res_dev_id = qryDevResource_1.res_id) LEFT JOIN qryCSSResource ON dbo_PROJECT.pjct_css_res_id = qryCSSResource.res_id
WHERE (((qryDevResource.res_name)=[forms]![frmResourceProject]![cmbResource]) AND ((dbo_PROJECT.pjct_active)=[Y or N])) OR (((qryDevResource_1.res_name)=[forms]![frmResourceProject]![cmbResource]) AND ((dbo_PROJECT.pjct_active)=[Y or N]));
The reults are returned successfully but then the issue happens, the field qryDevResource.res_name has an adverse reaction. The values in this field are identified by a unique inetger. The first value in the resource table is removed therby orphaning all of the projects from the projects table.


The data is not stored in Access, Access is just used for a front end. I have a SQL server connected to via ODBC.

Anybody have an idea what is happening?