我使用Access数据库，用于连接到我们服务器上运行的SQL Server数据库 .

我遇到的问题涉及两种形式 .

Form1（Canvass_Applicants_Search）用于将选择条件传递给Form2（Canvass_Applicant_List），Form2在表单加载时运行SQL查询 . Form2（代码如下所示）采用Form1中的条件并将其传递给下面的SQL查询 .

下面的SQL查询适用于传递给它的大多数条件，并将返回要显示的表单的数据 . 但是，根据某些条件，查询将导致Access崩溃并显示消息“Microsoft Access已停止工作” . 我可以使用下面相同的SQL代码直接从Access运行select查询，并且查询将与导致下面的SQL查询崩溃的相同条件一致地工作 .

作为VB和SQL的相对新手，我怀疑我的代码质量有些不正确或不够健壮 .

Private Sub Form_Load()
On Error GoTo Err_Load

   Dim rs As DAO.Recordset
   Dim rs1 As DAO.Recordset
   Dim db As Database
   Set db = CurrentDb()
   Dim strSQL As String
   Dim FullArgs As Variant
   Dim ProfessionArgs As Variant
   Dim SpecialtyArgs As Variant


' Get the full concatanated string of criteria from the Canvass_Applicants_Search form i.e. Me.OpenArgs and assign it to FullArgs
' Then split the concatanated string into seperate Profession and Specialty criteria using the split function

   FullArgs = Me.OpenArgs
   ProfessionArgs = Split(FullArgs, "|")(0)
   SpecialtyArgs = Split(FullArgs, "|")(1)

' This form will display data from a recordset that is created using the selections made on the Form Canvass_Applicant_Search
'Create and SQL statement where ObjectType=1 relates to Applicants and Me.OpenArgs is the consolidated Position Attributes string. The form will display a recordset where all applicants are selected depending on their position attribute selection

strSQL = "SELECT p.ObjectAttributeId, p.ObjectID, p.AttributeId, s.ObjectAttributeId, s.ObjectID, s.AttributeId,dbo_Person.PersonName, dbo_Person.Surname, "
strSQL = strSQL + "dbo_Applicants.AvailableDate, dbo_Applicants.AssessmentDate, dbo_Applicants.PriorityValueId, dbo_ApplicantSectorDefinedColumns.Grade, dbo_ApplicantSectorDefinedColumns.AssignmentType, dbo_ApplicantSectorDefinedColumns.EmploymentType, "
strSQL = strSQL + "dbo_Locations.Code, dbo_Locations.Description, dbo_ApplicantStatus.Description, dbo_ListValues.Description "


strSQL = strSQL + "FROM (((dbo_ObjectAttributes AS p INNER JOIN dbo_ObjectAttributes AS s ON p.ObjectID = s.ObjectID) LEFT JOIN dbo_ApplicantSectorDefinedColumns ON s.ObjectID = dbo_ApplicantSectorDefinedColumns.ApplicantID) "
strSQL = strSQL + "INNER JOIN dbo_Person ON s.ObjectID = dbo_Person.PersonID) INNER JOIN (((dbo_Applicants LEFT JOIN dbo_Locations ON dbo_Applicants.LocationId = dbo_Locations.LocationId) LEFT JOIN dbo_ApplicantStatus ON dbo_Applicants.StatusId = dbo_ApplicantStatus.ApplicantStatusId) "
strSQL = strSQL + "LEFT JOIN dbo_ListValues ON dbo_Applicants.AvailabilityId = dbo_ListValues.ListValueId) ON s.ObjectID = dbo_Applicants.ApplicantId "

If SpecialtyArgs = 0 Then
strSQL = strSQL + "WHERE (" & ProfessionArgs & ");"
Else
strSQL = strSQL + "WHERE (" & ProfessionArgs & ") AND (" & SpecialtyArgs & ");"
End If

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Set Forms("Canvass_Applicant_List").Recordset = rs

Exit_Load:
    Exit Sub

Err_Load:
    MsgBox Err.Description
    Resume Exit_Load
End Sub
sql sql-server vba ms-access