SQL INNER JOIN 1

[hafa2000]

Hi,

I have a form with 3 unbound text boxes:

txtJob
txtSpec
txtConsignorName

The form allows a user to search on one or more Job number or Spec number. The user can enter the numbers in the txtJob field or txtSpec field to search for Job numbers or Spec numbers which begin with those numbers and click the Search button.
The form performs the search on the criteria the user enters, loads the result into a local table (tvwDelivery) from the linked server table (tblDelivery)

In other words, the code below examines the criteria entered and creates a search predicate to find the result. The code looks at the unbound text boxes one at a time to create a search string.

Private Sub cmdSearch_Click()
Dim db As DAO.Database
Dim strSQL As String, varSQLWhere As Variant, intRCount As Integer

varSQLWhere = Null

If Not IsNothing(Me.txtJob) Then
' Create a wildcard search for leading characters
varSQLWhere = "(C.JobID Like '" & Me.txtJob & "*')"
End If
If Not IsNothing(Me.txtSpec) Then

varSQLWhere = (varSQLWhere + " AND ") & _
"(C.SpecNo Like '" & Me.txtSpec & "*')"
End If
If Not IsNothing(Me.txtConsignor) Then

varSQLWhere = (varSQLWhere + " AND ") & _
"(C.ConsignorID Like '" & Me.txtConsignor & "*')"
End If
If IsNothing(varSQLWhere) Then
Call CustomError("You must enter at least one search criteria.", _
OK, "Find Jobs")
Exit Sub
End If

strSQL = "INSERT INTO tvwDelivery ( DeliveryDocketID, JobID, DeliveryDocketNo, ConsignorID, SpecNo) " & _
"SELECT C.DeliveryDocketID, C.JobID, C.DeliveryDocketNo, C.ConsignorID, C.SpecNo " & _
"FROM tblDelivery AS C " & _
"WHERE " & varSQLWhere
If fctFind(strSQL) = False Then
Call CustomError("There was a problem with the search. " & _
"Please close this window and try again.", , "Search Failure")
Exit Sub
End If

Set db = DBEngine(0)(0)
db.TableDefs.Refresh
intRCount = db.TableDefs("tvwDelivery").RecordCount

If intRCount <> 1 Then
Me.RecordCount = intRCount & " Records found."
Else
Me.RecordCount = intRCount & " Record found."
End If

Me.RecordCount.Visible = True
Set db = Nothing
Me.Requery


End Sub

At the moment the code only searches the criteria for txtJob and txtSpec from 1 table called tblDelivery and inserts into tvwDelivery

tblDelivery
Delivery DocketID Auto
JobID Number
Delivery Docket No Number
ConsignorID Number
SpecNo Text

tvwDelivery
Delivery DocketID Number
JobID Number
Delivery Docket No Number
ConsignorID Number
SpecNo Text
ConsignorName Text

The user now wants to search for a Consignor on the txtConsignorName field. But this information would have to come from the tblConsignors

tblConsignors
ConsignorID Auto
ConsignorName Text

Could one of you Access SQL Masters point me in the direction of showing me how this could be written as an INNER JOIN to the tblConsignors.ConsignorID? This does not seem correct:

strSQL = "INSERT INTO tvwDelivery ( DeliveryDocketID, JobID, DeliveryDocketNo, ConsignorID, SpecNo) " & _
"SELECT C.DeliveryDocketID, C.JobID, C.DeliveryDocketNo, C.ConsignorID, C.SpecNo, Cn.ConsignorName " & _
"FROM tblDelivery AS C INNER JOIN tblConsignors AS Cn " & _
"ON C.ConsignorID = Cn.ConsignorID " & _
"WHERE " & varSQLWhere


Any assistance would be greatly appreciated

 

[PHV]

Replace this:
strSQL = "INSERT INTO tvwDelivery ( DeliveryDocketID, JobID, DeliveryDocketNo, ConsignorID, SpecNo) " & _
with this:
strSQL = "INSERT INTO tvwDelivery ( DeliveryDocketID, JobID, DeliveryDocketNo, ConsignorID, SpecNo, ConsignorName) " & _


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hafa2000]

Hi PHV,

Thanks so much for the reply. I don't know why I didn't see it missing in the INSERT Statement - worked like a charm!!