Tara:
Don't give up hope. Its not nearly as difficult as you might think. First
thing to do is create the form and put two text boxes on it. Name them
txtFirstName and txtLastName say. To do this select each and change their
Name properties in the properties sheet from the name Access gives them.
Save the form with a suitable name, frmNameDialogue for instance. Open the
form and enter a first and last name, remembering to press the return key
after the last one so the control updates.
With the form still open, open your query in design view and in place of the
[Enter first name] and [Enter last Name] parameters in the criteria row put
refernces to the form's controls:
Forms!frmNameDialogue!txtFirstName
and
Forms!frmNameDialogue!txtLastName
Save the query. If you now run the query (with the form still open) you
should see just the row(s) for the name you entered.
If everything is working OK close the query and go back to the form and
open it in design view. Add a button to the form. Select the button and in
its properties sheet select the On Click event property. Click on the
'build' button; that's the one to the right with 3 dots. Then select 'Code
Builder' in the next dialogue. When you exit the dialogue the VBA window
will open at the button's event procedure. The first and last lines will be
in place and you add the new code between them. Lets assume you want to open
the query directly, so the code would be as follows. This time I've included
some validation and customized the message so it includes the not found
name. I've commented the code so you can see what it's doing. Comments are
an important part of programming. Hopefully the indenting of the code will
have been preserved when you raed it, but that depends on what you are using
to read it. Indenting shows the flow of the code, e.g everyting between an
If and an End If will be indented.
Here's the code:
' declare variables to hold messages and criteria string
Dim strMessage As String
Dim strCriteria As String
' first make sure both names have been entered
If Not IsNull(Me.txtFirstName) And Not IsNull(Me.txtLastname) Then
' find out if name exists in table.
' build criteria string; the followig two lines
' are actually a single line of code; the underscrore
' character at the end of the first line is a continuation character
strCriteria = "[FirstName] = """ & Me.txtFirstName & _
""" AND [LastName] = """ & Me.txtLastName & """"
' look up any row(s) which match the criteria
If Not IsNull(DLookup("[LastName]", "[YourTableName]", strCriteria) Then
' name exists so open query
DoCmd.OpenQuery "YourQueryName"
Else
' build message to inform user that name not found
strMessage = Me.txtFirstName & " " & Me.txtLastName & " not found."
' pop up message box
MsgBox strMessage, vbInformation, "Warning"
End If
Else
' one or both names missing so inform user
' build message to inform user that name not found
strMessage = "Both names must be entered."
' pop up message box
MsgBox strMessage, vbExclamation, "Invalid Operation"
End If
If you want to print a report based on the query just open the report
instead by calling the OpenReport method rather than the OpenQuery method:
DoCmd.OpenReport "YourReportName"
If you want to preview the report:
DoCmd.OpenReport "YourReportName", View:=acViewPreview
If you want to open a form based on the query call the OpenForm method:
DoCmd.OpenForm "YourFormName"
You are doubtless feeling by now that all that looks horrendously
complicated, but if you copy and paste the above code into the event
procedure and make sure that your table, field and query/form/report names
are correctly substituted for the names I've used it should work. Unless
I've made some silly minor error(s) that is! If so than get back to me;
debugging is another important part of programming. Hopefully I've not made
any errors in the code but I wouldn't rule it out. When writing code its not
unusual to make small errors, just a missing comma or quote can be enough,
but weeding out the bugs is part of the process.
Do try and understand what the code is doing rather than just copying it in.
If you understand how it works its easier to spot any things which might
either raise a runtime error, or just not give the results you expect.
Good luck,
Ken Sheridan
Stafford, England
