[charliekim]

i have one more question..

i have 3 forms in VB

1. frmDirectory
2. frmFiles
3. frmSearch

frmDirectory is where the user input all their user information ie. first name, last name, address, phone etc.

frmFiles is used when the user presses the view all button, another form will pop up with a datagrid and populate the datagrid with all the recordsets in the database.

frmSearch is where the third form contains 2 textboxes, and a search, clear, and return button.

what i need help with is the search button.
basically the user will type in the first name and last name .. when the user presses search, the sql statement will search the database for the match and if there is a match, it will fill in the proper information in the frmDirectory text fields..

it might sound confusing so let me give you an example...

in frmDirectory there are many textfields where the user inputs their info.

so lets say that the user fills in the last name text field (Smith), first name(Charlie), address and phone number.

the user inserts the info into the database... all is said and done..

now when the user presses the search button, the frmSearch will pop up.

the user enters in Smith of the last name, and Charlie for the first name .... and presses the search button...

now when there is a match, it will go back to frmDirectory and fill in the appropriate text fields .. example .. last name will be automatically filled with Smith, first name with Charlie and the address and phone the user supplied with in the beginning..

i know this sounds confusing.. but i need help!!!

thanks all...

[coderitr]

You can set the property of a control on another form by qualifying it with the form name. In your example, the user enters information and clicks the search button. All the database lookup work is done in frmSearch. Now the search is done and a match was found so you need to populate the text boxes on the parent form using the data from the database -- do it like this:

frmDirectory.txtFirstName.Text = dbrec!First_Name
frmDirectory.txtLastName.Text = dbrec!Last_Name
frmDirectory.txtAddress.Text = dbrec!Address

... and so on.

Once all the fields are filled in, "Unload Me" to dismiss the search form and return focus to the directory form. Try not to succumb to the temptation to leave forms loaded (and hidden.) This creates memory problems for your application and can cause the application to not completely close when you exit.

[charliekim]

thanks, i'll give it a try .... good tip on the unload.me

i always tend to use hide.me

[coachdan32]

hide.me only moves the focus back to the other form, it does not release it - memory is still being held

[charliekim]

how would the database search be done, is it an sql statement?

i'm not to sure..
thanks.

[coderitr]

Yes, in a SQL statement. Think of any catalog search: you are looking for one or more items (select) in a given category (from) that have the criteria that you specify (where) as in:

SELECT First_Name, LastName FROM Employees WHERE HireDate > #1/1/2002#

The '#' is used Jet (MS Access) to specify a literal date in a SQL statement.

[charliekim]

frmDirectory.txtFirstName.Text = dbrec!First_Name
frmDirectory.txtLastName.Text = dbrec!Last_Name
frmDirectory.txtAddress.Text = dbrec!Address

this part would go right after the sql statement?

[coderitr]

Public Function Search(fname As String, lname As String)
On Error Goto Search_Error

''' initialize the holder fields on the directory form
frmDirectory.txtFirstName.Text = ""
frmDirectory.txtLastName.Text = ""
frmDirectory.txtAddress.Text = ""
frmDirectory.txtCity.Text = ""
frmDirectory.txtState.Text = ""
frmDirectory.txtPostCode.Text = ""

''' open the database
Dim cnn As New ADODB.Connection
cnn.Open <database connection string>

''' build the sql statement
Dim strSql As String
strSql = ""
strSql = strSql & "SELECT * FROM TableName"
strSql = strSql & " WHERE first_name = '" & fname & "'"
strSql = strSql & " AND last_name = '" & lname & "'"

''' open the recordset
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.LockType = adLockReadOnly
rst.Open strSql, cnn

''' if a match was found then display the data on the form
If rst.RecordCount > 0
rst.MoveFirst
frmDirectory.txtFirstName.Text = rst!first_name
frmDirectory.txtLastName.Text = rst!last_name
frmDirectory.txtAddress.Text = rst!address
frmDirectory.txtCity.Text = rst!City
frmDirectory.txtState.Text = rst!State
frmDirectory.txtPostCode.Text = rst!postal_code
Else
MsgBox "Not Found"
End If

''' close the recordset
rst.Close
Set rst = Nothing

''' close the database connection
cnn.Close
Set cnn = Nothing

Exit Function

Search_Error:

''' report errors -- not found condition will not get here
MsgBox "Error in search function" & vbCrLf & vbCrLf & _
Err.Number & vbCrLf & Err.Description, vbOKOnly + _
vbExclamation, Me.Caption

''' safely close the recordset object
If Not rst is Nothing Then
If rst.State = adStateOpen then rst.Close
set rst = Nothing
End If

''' safely close the database connection object
If Not cnn is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
set cnn = Nothing
End If

End Function