You forgot the 'ON' keyword. Here's the correct syntax:

SELECT
    Customer_Table.NRIC,
    Customer_Table.Name,
    Customer_Table.Address,
    Customer_Table.[Contact No],
    Customer_Table.DateOfNotice,
    Customer_Table.Status,
    Customer_Account_Table.[Account No],
    Customer_Account_Table.Salary
FROM
    Customer_Account_Table
INNER JOIN
    Customer_Table
ON
    Customer_Account_Table.NRIC = Customer_Table.NRIC
WHERE Customer_Table.NRIC=1;


And here's a little short-cut. The following is equivalent to the above SQL statement:

SELECT
    C.NRIC,
    C.Name,
    C.Address,
    C.[Contact No],
    C.DateOfNotice,
    C.Status,
    CA.[Account No],
    CA.Salary
FROM
    Customer_Account_Table As CA
INNER JOIN
    Customer_Table As C
ON
    CA.NRIC = C.NRIC
WHERE C.NRIC=1;

This gives the table names aliases ('C' and 'CA') using the 'AS' keyword. You can also omit the 'AS' keyword and the aliases will still be applied.

A good practice to get in the habit of when using embedded SQL in code is to first create your SELECT query in Access's QBE grid, make sure your query runs, then swith to SQL view, copy the working SQL statement, and paste it into your code module. Cuts down on debugging issues.

I also like to declare and initialize a strSQL variable first, then use that variable as the source string argument of the OpenRecordset method. This does two things. First, it makes your code more readable (see below), and second, it allows you to examine the string value assigned to strSQL in the locals window to see if its what you expected.

So here is your finished module:

Private Sub cmdSearch_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strWhereClause As String

    strWhereClause = ""
    strWhereClause = "[C.NRIC] = " & Me!txtNRIC

    strSQL = "SELECT DISTINCTROW C.NRIC, C.Name, " _
                     & "C.Address, C.[Contact No], C.DateOfNotice, " _
                     & "C.Status, CA.[Account No], CA.Salary " & _
                "FROM Customer_Account_Table As CA " & _
                "INNER JOIN Customer_Table AS C " & _
                "ON CA.NRIC = C.NRIC " & _
                "WHERE " & strWhereClause & ";"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL)

    rst.MoveLast
    If rst.RecordCount <> 0 Then
        txtNRIC = rst!NRIC
        txtName = rst!Name
        txtAddress = rst!Address
        txtContactNo = rst![Contact No]
        txtDateOfNotice = rst!DateOfNotice
        txtStatus = rst!Status
        txtAccountNo = rst![Account No]
        txtSalary = rst![Salary]
    Else
        MsgBox "No records satisfied you criteria", vbExclamation, "SearchResults"
    End If

    rst.Close

End Sub

Note the change to your where clause. Since NRIC is the common field used to join two tables, your where clause needs to indicate which NRIC value you are referencing:

strWhereClause = "[C.NRIC] = " & Me!txtNRIC

This is the primary key value.

To get a bit more comfortable with SQL, search your file system for the Jet SQL help file and us it as a reference (the file name is JETSQL40.CHM). Also, Microsoft has developed 3 Jet SQL sample databases (fundamental, intermediate, and advanced) that you can download as additional references. The URL for the 1st is:

http://msdn.microsoft.com/library/de.../acfundsql.asp

You can find links to the other two at the bottom of this page.

Regards,

Bob

Also, you don't need the DISTINCTROW predicate and can just delete it. Since you are using your Customer table's primary key value as your search criteria, your query result set can consist of only one, unique record anyway. DISTINCTROW comes in handy if you were, say, loading a combo box with customer last names or something and only wanted the name to appear once in the drop down list.

Final thought,

SQL ignores white space, so you can break your SQL statments up basically anyway you want to enhance readability. If you post either of the SQL 'blocks' at the top of 'two-posts-ago' directly into Access's SQL view, they will run as is.

hi, i have try the methods u have taught me and i have change my wrong code.

however i still get the error : too few parameters, expected 1.

y is it so??

nono

Post your current code and we'll see whats up. Its running OK on my end.

Bob

hi this is my coding!!!!
thanks!

form record source : SELECT Customer_Table.Name, Customer_Table.Address, Customer_Table.[Contact No], Customer_Table.DateofNotice, Customer_Table.Status, Customer_Table.[Reply Date], Customer_Account_Table.[Account No], Customer_Account_Table.[Account Type], Customer_Account_Table.Salary, Customer_Account_Table.Remarks, Customer_Account_Table.Memo, Customer_Account_Table.[Action Taken] FROM Customer_Table INNER JOIN Customer_Account_Table ON Customer_Table.NRIC=Customer_Account_Table.NRIC;
---------------------------------------------------------------
search textfield : txtNRIC (unbound field)

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strWhereClause As String

    strWhereClause = ""
    strWhereClause = "[c.NRIC] = " & Me!txtNRIC

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
        "c.NRIC, c.Name, c.Address, c.[Contact No], " & _
        "c.DateofNotice, c.Status, c.[Reply Date], " & _
        "ca.[Account No], ca.[Account Type], ca.Salary, " & _
        "ca.Remarks, ca.Memo, ca.[Action Taken] " & _
        "FROM Customer_Table As c INNER JOIN Customer_Account_Table As ca ON ca.NRIC = c.NRIC " & _
        " WHERE " & strWhereClause & ";")

    rst.MoveLast
    If rst.RecordCount <> 0 Then
        txtNRIC = rst!NRIC
        txtName = rst!Name
        txtAddress = rst!Address
        txtContactNo = rst![Contact No]
        txtDateofNotice = rst!DateofNotice
        txtStatus = rst!Status
        txtReplyDate = rst![Reply Date]

        txtAccountNo = rst![Account No]
        txtAccountType = rst![Account Type]
        txtSalary = rst!Salary
        txtRemarks = rst!Remarks
        txtMemo = rst!Memo
        txtActionTaken = rst![Action Taken]
    Else
        MsgBox "No records are found. Please try again", vbExclamation, "SearchResults"
    End If

    rst.Close

Exit_cmdSearch_Click:
    Exit Sub

Err_cmdSearch_Click:
    'MsgBox "search error occurred!!"
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click

End Sub

Private Sub cmdClear_Click()
On Error GoTo Err_cmdClear_Click
    txtNRIC = ""
    txtName = ""
    txtAddress = ""
    txtContactNo = ""
    txtDateofNotice = ""
    txtStatus = ""
    txtReplyDate = ""

    txtAccountNo = ""
    txtAccountType = ""
    txtSalary = ""
    txtRemarks = ""
    txtMemo = ""
    txtActionTaken = ""

Exit_cmdClear_Click:
    Exit Sub

Err_cmdClear_Click:
    MsgBox "clear error"
    Resume Exit_cmdClear_Click
End Sub

when i enter the nric number,and click on the search (cmdSearch)
error msg : too few parameters, expected 1

when the nric number is blank, and click on the search (cmdSearch)
error msg : Syntax error (missing operator) in query expression '[c.NRIC]='

pls advice and thanks again!
nono

Hi nono,

Your code is fine. I ran it exactly as you posted it after I added the new fields.

The "missing operator" error arrises simply because the code doesn't
check your search criteria for null yet. Just add the check at the top of the procedure:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strWhereClause As String

    ' Be sure the user entered a search criteria value.
    If IsNull(Me!txtNRIC) Or Me!txtNRIC = "" Then
        MsgBox "No criteria specified.", vbExclamation, "Search Results"
        Me!txtNRIC.SetFocus
        Exit Sub
    End If

    strWhereClause = ""
    strWhereClause = "[c.NRIC] = " & Me!txtNRIC

etc...

The only way I can reproduce your "too few parameters" error is if I delete a field from one of the base tables. Are you sure you didn't add some fields to you SQL or code and forgot to add them to your table or form? Here are the tables:

Customer_Table
- NRIC (Number)(Primary Key)
- Name (Text)
- Address (Text)
- Contact No (Text)
- DateOfNotice(Date/Time)
- Status (Text)
- Reply Date (Date/Time)

Customer_Account_Table
- AccountNo (Number)
- Account Type (Text)
- Salary (Currency)
- Remarks (Text)
- Memo (Text)
- Action Taken (Text)
- NRIC (Number) (Foreign Key)

Here's what I'm running:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strWhereClause As String

    If IsNull(Me!txtNRIC) Or Me!txtNRIC = "" Then
        MsgBox "No criteria specified.", vbExclamation, "Search Results"
        Me!txtNRIC.SetFocus
        Exit Sub
    End If

    strWhereClause = ""
    strWhereClause = "[c.NRIC] = " & Me!txtNRIC

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
        "c.NRIC, c.Name, c.Address, c.[Contact No], " & _
        "c.DateofNotice, c.Status, c.[Reply Date], " & _
        "ca.[Account No], ca.[Account Type], ca.Salary, " & _
        "ca.Remarks, ca.Memo, ca.[Action Taken] " & _
        "FROM Customer_Table As c " & _
        "INNER JOIN Customer_Account_Table As ca ON ca.NRIC = c.NRIC " & _
        "WHERE " & strWhereClause & ";")

    rst.MoveLast
    If rst.RecordCount <> 0 Then
        txtNRIC = rst!NRIC
        txtName = rst!Name
        txtAddress = rst!Address
        txtContactNo = rst![Contact No]
        txtDateOfNotice = rst!DateofNotice
        txtStatus = rst!Status
        txtReplyDate = rst![Reply Date]
        txtAccountNo = rst![Account No]
        txtAccountType = rst![Account Type]
        txtSalary = rst!Salary
        txtRemarks = rst!Remarks
        txtMemo = rst!Memo
        txtActionTaken = rst![Action Taken]
    Else
        MsgBox "No records are found. Please try again", vbExclamation, "SearchResults"
    End If

    rst.Close

Exit_cmdSearch_Click:
    Exit Sub

Err_cmdSearch_Click:
    'MsgBox "search error occurred!!"
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click

End Sub

Private Sub cmdClear_Click()
On Error GoTo Err_cmdClear_Click
    txtNRIC = ""
    txtName = ""
    txtAddress = ""
    txtContactNo = ""
    txtDateOfNotice = ""
    txtStatus = ""
    txtReplyDate = ""

    txtAccountNo = ""
    txtAccountType = ""
    txtSalary = ""
    txtRemarks = ""
    txtMemo = ""
    txtActionTaken = ""

Exit_cmdClear_Click:
    Exit Sub

Err_cmdClear_Click:
    MsgBox "clear error"
    Resume Exit_cmdClear_Click
End Sub

Regards,

Bob

nono,

What's all this stuff? It isn't part of your code module is it? Why all the extra fields that aren't listed in the SQL statement you are using to generate your recordset?

------------------------------------------------------
hi this is my coding!!!!
thanks!

form record source : SELECT Customer_Table.Name, Customer_Table.Address,
Customer_Table.[Contact No], Customer_Table.[Bankruptcy No],
Customer_Table.DateofNotice, Customer_Table.[Date of Bankruptcy],
Customer_Table.Status, Customer_Table.[Reply Date],
Customer_Account_Table.[Account No], Customer_Account_Table.[Account Type],
Customer_Account_Table.Balance, Customer_Account_Table.Quantity,
Customer_Account_Table.[Branch Code], Customer_Account_Table.[Branch Name],
Customer_Account_Table.[Officer In Charge], Customer_Account_Table.[Division
Code], Customer_Account_Table.[Division Name], Customer_Account_Table.Remarks,
Customer_Account_Table.Memo, Customer_Account_Table.[Action Taken] FROM
Customer_Table INNER JOIN Customer_Account_Table ON
Customer_Table.NRIC=Customer_Account_Table.NRIC;
---------------------------------------------------------------
search textfield : txtNRIC (unbound field)

hi do i need to put anything in my form - recordsource???

pls advice,
nono

Nope. Form's records source is blank (unbound). Your form should have 13 text boxes on it, one for each of the fields in your recordset. Their control source property is also blank (unbound). And two command buttons: cmdSearch, cmdClear.