Help w/ Access Database

[MGMorden]

I've got a database query (modeling a Doctor's office) that I need to run. I've got a table with patients, and another with appointment times. Now, I can create a query to show all appointments easily enough. What I need to know is how to I reference the values of text boxes on a form in SQL?

For example, I've got a form with the following:

TextBox named fnamebox
TextBox named lnamebox
Button named btnSearch

The btnSearch initiates a SQL query with roughtly the following:

SELECT Patients.FirstName, Patients.LastName, Appointments.AppointmentTime, Appointments.AppointmentDate
FROM Patients INNER JOIN Appointments ON Patients.PatientID=Appointments.PatientID;

Now I need to add a WHERE in that says Patients.FirstName = (whatever is in fnamebox) and Patients.LastName = (whatever is in lnamebox)

Any idea on how to reference that data?

 

[EagleKeeper]

An option would be to place some code to extract data from the text boxes and then feed the query.
Create a couple of dummy tables that would contain the current value of the text box, then perform your query with a join using the dummy tables.

There also may be other ways.

 

[bunker]

This is only to create the sql string, the connection and everything else I don't know off the top of my head

In code for the onClick action of the button:

Dim strSQL as String

strSQL = "SELECT Patients.FirstName, Patients.LastName, Appointments.AppointmentTime, Appointments.AppointmentDate
FROM Patients INNER JOIN Appointments ON Patients.PatientID=Appointments.PatientID WHERE Patients.FirstName = ' " & your_form_name.fnamebox.value & " ' AND Patients.LastName = ' " & your_form_name.lnamebox.value & " ' "

I had to use spaces so you can actually see the single/double quotes, but you don't want spaces between them.

Then run that string and work with the result set.

 

[joinT]

Is this "within" MS Access? Like the form elements will be in an MS Access form?

 

[bunker]

Originally posted by: joinT
Is this "within" MS Access? Like the form elements will be in an MS Access form?

From his post:

What I need to know is how to I reference the values of text boxes on a form in SQL?

I'm assuming he means a form in the database and not a separate VB form.

 

[BFG10K]

What I need to know is how to I reference the values of text boxes on a form in SQL?

That's easy - build the query in design view and then look at the SQL view to see what it looks like.

To specific the criteria for the boxes, use forms![form name]![textbox name].