Hi,

i wish to have a search function on the primary key. on the top of my page i will have a field where user will enter the primary key to do the searching, and the bottom part where there are many fields (empty until the search button is pressed)

which means i will have my primary key field together with the other fields in one form but user will need to type in the primary key, press the command button "search" and data that is pertaining to this primary key will appear on this same form.

can someone tell how can i start to work this out???

Pls advice!!!

Pretty simple - you can do most of it through wizards, then just tweak it a little.

First create a form with the primary key as the record source
(eg SELECT id FROM Table1)

Then (making sure that the wizards are enabled), add a combo box, choose the third option, of go to a record based on selection.

Then put a subform based on the entire table on your main form as a datasheet view.

If you don't want the users to do it after the combo box has been updated, add a button and cut and paste the code from the AfterUpdate event to the Click event.

HTH

Steven

I am a loud man with a very large hat. This means I am in charge

yeh, i managed to work it out. can i ask actually how can i prevent the user to make any changes to my search form??? i mean the bottom part. i try to set the enables format to no in the textfield properties. it solves the problem of preventing editing the data but it is not clear enough as displayed on the form.

pls advice
nono

Hi,

If you want to go with all unbound fields (as you describe), you'll need to use some data access objects. The first example below uses DAO:

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

    strWhereClause = ""
    strWhereClause = "[EmployeeID] = " & Me!txtEmployeeID

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
            "EmployeeID, LastName, FirstName " & _
            "FROM Employees" & _
            " WHERE " & strWhereClause & ";")
    rst.MoveLast
    If rst.RecordCount <> 0 Then
        txtEmployeeID = rst!EmployeeID
        txtLastName = rst!LastName
        txtFirstName = rst!FirstName
    Else
        MsgBox "No records satisfied you criteria", vbExclamation, "SearchResults"
    End If

    rst.Close

End Sub

Private Sub cmdClear_Click()
    txtEmployeeID = ""
    txtLastName = ""
    txtFirstName = ""
End Sub

Another way to go would be to use bound controls and simply filter the form. Here you would bind all your fields to the forms record source, place a single unbound control on the form to recieve your input criteria, and use some code like the following:

Private Sub cmdSearch_Click()

    Dim intSearchValue As Integer
    Dim strSearchString As String


    If Me!cmdSearch.Caption = "&Search" Then

        intSearchValue = IIf(Me!txtSearchValue = "", 0, Me!txtSearchValue)
        If intSearchValue = 0 Then
            MsgBox "No criteria specified.", vbExclamation, "Search Results"
            Me!txtSearchValue = ""
            Exit Sub
        Else
            strSearchString = "[EmployeeID] = " & intSearchValue
        End If

        ' Filter frmEmployees with new search string.
        Me.Filter = strSearchString
        Me.FilterOn = True
        Me!cmdSearch.Caption = "&Show All"

        ' Check to see if the record exists.
        If Me.RecordsetClone.RecordCount = 0 Then
            MsgBox "No records meet your criteria", vbExclamation, "Search Results"
            Me.FilterOn = False
            Me!txtSearchValue = ""
            Me!cmdSearch.Caption = "&Search"
            Me!txtSearchValue.SetFocus
            Exit Sub
        End If

    ' If caption set to "Show All", remove filter.
    Else
        Me.FilterOn = False
        Me!txtSearchValue = ""
        Me!cmdSearch.Caption = "&Search"
        Me!txtSearchValue.SetFocus
    End If

End Sub

HTH,

Bob

hi i am not very good in sql statement .

can u tell me what is the difference between the unbound and bound textfield??

i knew that your first sql statement actually retrieves only from one table. how can i retrieve data from 2 tables after i click the search button??
i try to add in more fields but error occurred -
error msg : "too few parameters. expected 1"

i tried the 2nd example, error msg : "type mismatch" y is this so???

by the way can i place this inside my code for the 1st sql statement??? -

Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
"NRIC, Name, Address, [Contact No], " & _
"FROM Customer_Table" & _
" WHERE " & strWhereClause & ";")

rst.MoveLast
If rst.RecordCount <> 0 Then
   txtNRIC = rst!NRIC
   txtName = rst!Name
   txtAddress = rst!Address
   txtContactNo = rst![Contact No]
    .....
     .....

i mean 'contact no' - as access cannot recognize a field name seperated.

pls advice
thanks!!!1

Which field is your search criteria ([Contact No]?) and what's its data type?

My EmployeeID is an Integer field. I made the following change to check for null at the top of the code:

If Me!cmdSearch.Caption = "&Search" Then
  If IsNull(Me!txtSearchValue) Or Me!txtSearchValue = "" Then
    MsgBox "No criteria specified.", vbExclamation, "Search Results"
    Me!txtSearchValue = ""
    Exit Sub
Else
    intSearchValue = Me!txtSearchValue
    strSearchString = "[EmployeeID] = " & intSearchValue
End If

Works better. .Net is ruining my ability to think in Access. Its new Option Strict feature restricts implicit conversions to only widening conversions. Widening conversions to do not permit any conversion between numeric types and strings, among other things. So in .NET,the assignment statement intSearchValue = Me!txtSearchValue would fail.

Regrds,

Bob

A bound form is a form that has it's Record Source property set to an underlying data source which can be a table, query, or SQL statment. A bound control is a control on a bound form that has its Control Source property set to one of the fields included in the form's underlying data souce. Both properties can be set at design-time (via the form designer) or run-time (via code). A form that does not have a value assigned to its Record Source property (= empty string) is said to be 'unbound'. A control that does not have a value assigned to its Control Source property (= empty string) is said to be 'unbound'. You can have unbound controls on bound forms, but you can't have bound controls on unbound forms (there is no underlying data source to bind to).

~~~~~~~~~

To join two tables, use something like:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY CompanyName;

This lists all the customer names from the Northwind.mdb that have placed orders.

~~~~~~~~~

You can change the fields in your select statement and recordset object to anything you like as long as those fields exist in your base table(s).

~~~~~~~~~

HTH,

Bob

Well...OK...you can have bound controls on an unbound form, but they'll just display that annoying little #Name? thing 'cause there is no data source associated with them. ;)

hi, thanks for the information on the bound and unbound fields.

i have try the examples u have given me however there is still some errors that i could not solve.

this is my code, the error occur on the syntax of the inner join.
----------------------------------------------------------------
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 = "[NRIC] = " & Me!txtNRIC

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
            "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_Table INNER JOIN Customer_Account_Table.NRIC = Customer_Table.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
        txtAccountNo = rst![Account No]
        txtSalary = rst!Salary
    Else
        MsgBox "No records are found. Please try again", vbExclamation, "SearchResults"
    End If

    rst.Close

Exit_cmdSearch_Click:
    Exit Sub

Err_cmdSearch_Click:
    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 = ""
    txtAccountNo = ""
    txtSalary = ""
Exit_cmdClear_Click:
    Exit Sub
Err_cmdClear_Click:
    MsgBox Err.Description
    Resume Exit_cmdClear_Click
End Sub
------------------------------------------------------

pls advice.
nono