how do I show a record using a search function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which contains data and I also have a report which shows data
from the table.

I would like to create a form which will allow people to enter a date, name
or number and then press a button which will then show the record with the
details entered on the form but using a report

Thanks
 
Hi,

build a dynamic SQL statement with values from the textboxes

Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select Top 100 Percent Candidate_ID, Title, First_Name as 'First
Name' , Surname, Tel_Work as 'Work Tel', Date_Of_Birth as 'DOB', Email,
Postcode, County, Locations from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & Me.Title.Value
If Me.chkRight.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & "'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '%' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & Me.First_Name.Value
If Me.chkRight.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & Me.Surname.Value
If Me.chkRight.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & Me.Email_Address.Value
If Me.chkRight.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & Me.Ad_Postcode.Value
If Me.chkRight.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & Me.Ad_County.Value
If Me.chkRight.Value = True Then
SQL = SQL & "%"
End If
SQL = SQL & "'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"

then open your report


DoCmd.OpenReport "Report1"
Report_Report1.RecordSource = SQL


Hope it helps
 
Hi Alex
Thank you for the code, it works but I was wondering if you would know how I
could make sure that only reports with the specific requirements is shown and
therefore cannot move to a different report.

What I mean by the above is when a number is entered into a textbox and then
the search button clicked on then the report opens however on the bottom of
the report it has arrows to move to the next report however it also shows
other reports that do not contain the number selected to be searched.

I hope this makes sense.

Thanks
 
Hi,

if you are using my code as below, there may be another line of code
required after setting the recordset

Report_Report1.RecordSource = SQL

you may need this to refresh the data in the report


Report_Report1.Requery


post back here if you are still having problems.
 
Back
Top