Solved

Form to search multiple tables (Urgent!)

Posted on 2004-04-17
60
4,363 Views
Last Modified: 2008-02-07
Hi.  

For my A-Level ICT coursework, I am making an appointment booking system, and I am stuck on creating a form to search the tables.  This may sound like it could be quite simple, but it gets harder.  

I have already made a form with tabs that switch between three different sub-forms (student, tutor, appointment <= The tables in use), which is OK, but I wanted to do something different.  
The idea for the new search form is that there are two radio buttons, one for "Student/Tutor" search and one for "Appointment" search.  When "Student/Tutor" is selected, two checkboxes underneath are made visible, "Student" and "Tutor".  This would allow a customised search method for Student, Tutor, or both.  Appointment searching is based on the same principle, but for AM and PM.  [Also, when one of the radio buttons is selected, the checkboxes for the other radio button are made to be FALSE]  It is after this that I am having problems.  My first thought was to create one massive query that references all of the data, but I do not know how to change the "Show" option based upon a checkbox.  Also, the query would be quite cumbersome and hard to manipulate.  

I also had thoughts about having two/three different sub-forms and changing the visibility property based upon the checkboxes, but I did not know (a) whether it would work, and (b) how to do it.  

Any ideas?  This is due in a couple of weeks.  

Thanks,

Paul.  
0
Comment
Question by:W2k-User
  • 34
  • 24
60 Comments
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10849905
Urgent for 60 points? Obviously, not _that_ urgent.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 160 total points
ID: 10850040
We're not allowed to "do" homework at this site, but we're allowed to give directions.

In this case I would probably create a query to extract teacher, student and appointment time.
This can be placed in a subform and e.g. by using cascading comboboxes you can select the student/teacher combination.
Just check for that: http://www.candace-tripp.com/_pages/access_downloads.asp#5

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10852664
Re: ala_frosty: I only have 30 points remaining....
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10852701
This search feature is only a small part of the project.  I will try the downloads from this site.  

Thankyou for your suggestion, and I will post back with the results.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10852924
Dear ala_frosty,

Please don't take this as an offence, but when you're here to collect expert points, then please skip these questions in the future without adding a comment.
I'm here to help others disregarding the points offered as I know how hard Access was on me when starting to work with it and some (now obvious) solutions took me hours (sometimes even days) to figure out how to get it done.

Just to be clear, my comment isn't intended to offend you, I value the cooperation with fellow Access experts (including you) too much to want to offend anyone of them.

Nic;o)
BTW I see in your profile a question, just click on my membername to see what can be filled in there :-)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853103
What do you mean: "Disregarding the points offered"?  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10853186
"Not looking to the points"
I just post comments on questions that interest me.

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853288
Oh..  OK then.  

The options on the candace-tripp site don't really seem to help.  Is it possible to create a query in real-time and display it on a form, maybe in SQL?  Would this be an effective way of doing it?  

Paul.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853304
Oh, I understand what you mean now.  I thought that it was me disregarding the points... Sorry!  I am quite new here, so do not have a lot of points to offer, and I don't think that it would be appreciated if I had multiple accounts here.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10853345
I was indicating that for the selection you would create a query with all three "involved" objects (student, tutor, appointment).
Now having a set of cascading combo's will narrow down the possibilities to get the appointments of a student, next those for the student/teacher.
By using another set you could first select the teacher and then the teacher/student to see the appointments.

Getting the idea ?

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853409
Ah.. A query to reference other queries?  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10853437
Normally I use such a query in a datasheet subform showing all occurrences and the effect the selected value in the combo has.
Like the cascading combo sample you downloaded.
Next you can use the OnCurrent event of the sbuform with the query to "echo" the value of the appointmentID to the mainform.
On this (normally hidden) appointmentID you can link an appointment subform to show the details and/or allow updates.

Bit clearer ?

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853508
This query with all involved objects, would I reference the other tables using the "*" option to select all parts, or add them all individually?
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853634
Ah.  I think that I am almost getting there.  
Re: the last post by me: It would be the individual parts.  

However:
1) How do I get it to only show the parts selected, eg Students?  Can the "Show" part be changed using the "OnClick" VBA event?  
2) For the search box: I was hoping that I could use combo box for entering the data so that the AutoCorrect option could be used.  Would I use the "Visible" property, or is there a way to change what the record source is?  

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10853887
As described, you need to have two sets of combo's when you want a "path" Student/Teacher and a "path" Teacher/Student.

When you select another Student (in the "path" Student/Teacher), then the Teacher combo will be reset automaitically.
Every combo has by default the autoappend property set to true, thus appending the first hit inthe list matching the typed character(s)

Getting the idea Paul ?

BTW when you have an appointment selected, what action should be available or is it just for searching ?

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853901
I'm sorry.  I can tell that you are getting frustrated...!  

OK.  When the search has been performed, it would be useful to be able to edit the record, so that, for example, the time could be changed, the student details changed, etc.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10853943
I know that this is going to sound really stupid, but I am really lost now.  

From the top (how I am imagining it):

I have two radio buttons which have two check boxes under each one that can select different search options, teacher/student and appointment(AM)/appointment(PM).  When selected, these will control eachother (to deselect the checkboxes for the other radio button), and will change the Shown property for the columns in the query (I hope this is possible).  The checkboxes will also show different search combo boxes as applicable, and also change the query function, so that if the AM _or_ PM box is selected, the time (in 12hr format) will only used once under the time column, but if both are selected, the value will be used once, and then in the "or" row beneath, but +12 for PM.  If Student/Tutor is selected, the Student and Tutor check boxes will be shown.  If Student _or_ Tutor is selected, a combo box will be shown that gets its values from the Student or Tutor table respectively.  If both of them are selected, it will get the values from a query that has both Student and Tutor values in (if possible).  Selecting the Student check box will change the query to search for student records from the combo box, and the same for tutors with tutor records.  If both are selected, the query will be modified so that both the Student name and Tutor name columns are searched and all applicable records shown, i.e. if both student and tutor checkboxes selected, and "Smith" entered in the search box, all students and tutors with the name of Smith will be shown.  [However, the name is split into two separate fields, so maybe the query would use some sort of expression to join First Name and Last Name together].  From this, there would be a find button to initiate the query, and an edit button would appear (perhaps from the OnClick event making it visible).  This would allow the record to be edited.  If there is no instance of the name or date/time, then the user would be prompted if he/she wanted to create a new record.  

Is all of this possible?  

I don't know how to thank you enough for your help.  

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10853946
Will take a lot of comments more to get me fustrated <LOL>

OK, lets settle for the two sets of combo's managing the selection of a subform with the teacher/student/appointment query.

In the subform for the "all three query" add in the OnCurren event the code:

parent.txtAppointmentID = me.appointmentID

Also add a new field "txtAppointment" to the main form (make invisible when finished testing).

Now add a subform for just the Appointments and after making it a single form with a nice layout single click the form so we see the linkage fields in the datatab of the properties window.
There enter for the Child linkage field: AppointmentID and for the Master linkfield: txtAppointment

Now access will synchronize that subform with the selected one of the "all three query" subform.

This will allow the editing of the appointment.

Clear ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10853985
Hmm, not making it easy on yourself :-)

Drop me a line in my mailbox so I can mail a sample with filter possibilities and the way I do this so you have a reference what "forms" I'm thinking in.

Nic;o)
(Click membername for email)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10854530
Nic;o)  :
<You have email>

There is another thing.  If I try to create a Data Access Page in Access (I have Access 2002/XP...whatever it is), I am told that:

The data definition of this data access page has been corrupted and can't be repaired.  You must recreate the page.  Save has been disabled.  

Any ideas.  I have tried searching for that on the internet, and the answers are all related to opening DAPages in IE and enabling ActiveX components.  [BTW: I have tried that].  To make things worse, I used the Detect and Repair facility, and since it took about 30min to get 2 bars on the progress meter (of about 30), I pressed cancel.  Now Excel does not work.  I think that I am going to have to reinstall Office.  

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10854651
Got al three of them :-)

Would indeed reinstall Office :-)

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10854672
Got it.  You have Office 2002/3 then?  Forgot to say, as it is in Access 2002 format.  

Paul.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10854686
Nic;o)

So that right click idea would work on subforms then? Would the end user have to right click in the subform?

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10854742
The right-click works on main  and subforms and even the table and/or queries. It's always hard for me when I have to handle an excel file as I always use the right click for quick and dirty checks on my data in query results :-)

Just give it a try.

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10854752
Nic;o)

I tried it just after posting the message (the wrong way to do it...!).  Which is the best way to communicate: via email or through this thread?  

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10854766
This thread, is an EE rule thus keeping the comments meaningfull for others to read.

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10862133
Nic;o)

I have added a column to the tables called "Type" ["Student", "Tutor", "Appointment"].  From this, I would be able to follow the example better, as before, I was struggling to find a way to distinguish between each table in the query.  Is this the right way to go about it?  

Paul.  

PS: You have email (or, at least, you did yesterday...!)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10866857
What do you want to achieve with the type column?

Normally I try to show the user the available fields and instruct them to use the right-click popup as coding this many selections is not only a hell of a job, but it's often not clear to the user how a selection is made.

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10867407
From the type column, hopefully possible to select the type of record selected, like in the "Sample-Combo..." database with the reporter selection combo box.  Is there a better way?

Paul.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 54

Expert Comment

by:nico5038
ID: 10867447
By using the OnCurrent event in the selection subform with student/teacher/appointment you can place the keyfields (hidden) on the mainform.
Now you can link a subform for showing the details (and/or update) of the separate tables.

Clear ?

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10867514
Err... Pass?
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10867518
Access is not my strong point...
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10885768
Hello?

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10885893
How far did you get with my remark "By using the OnCurrent event in the selection subform with student/teacher/appointment you can place the keyfields (hidden) on the mainform." ?

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10886158
I did not quite understand it.  I am still stuck with the query.  With this query, will the columns not have the results from each table next to each other?  
I.e.  AppointmentID,Time,Date,etc,StudentID,Surname,Forename,Postcode,etc,TutorID,Surname,Forename,Postcode,etc   ?  This is a problem that I have realised.  There is not a common fieldset that is shared amongst the tables for this to work effectively.  

Any ideas?

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10886576
Try:
SELECT tbl_Appointments.[Appointment-ID], tbl_Appointments.Date, tbl_Appointments.Time, tbl_Appointments.Duration, tbl_Appointments.[Tutor-ID], tbl_Appointments.[Student-ID], [tbl_Student].[Surname] & ", " & [tbl_Student].[Forename] AS StudentName, [tbl_Tutor].[Surname] & ", " & [tbl_Tutor].[Forename] AS TutorName
FROM tbl_Tutor INNER JOIN (tbl_Student INNER JOIN tbl_Appointments ON tbl_Student.[Student-ID] = tbl_Appointments.[Student-ID]) ON tbl_Tutor.[Tutor-ID] = tbl_Appointments.[Tutor-ID];

The ID's can be hidden, but are needed for the OnCurrent event to place on the mainform to link the Tutor and Student subform.

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10889020
Nic;o)

I will re-read the instructions that you have given me tonight and try to make a form that will do all of this to use as a subform.  The thing is, though, with the "Super Query" that has a reference to all of the rows in it, has one record from each table on each row _together_, i.e., one one row, there is a record for tutor, student, _and_ appointment.  Can I still search this?  

Also, I have had to rename the tables etc. so that they don't have underscores or hyphens in to conform to naming conventions by some people called Leszynski and Reddick.  The SQL is now:

SELECT tblAppointments.[AppointmentID], tblAppointments.Date, tblAppointments.Time, tblAppointments.Duration, tblAppointments.[TutorID], tblAppointments.[StudentID], [tblStudent].[Surname] & ", " & [tblStudent].[Forename] AS StudentName, [tblTutor].[Surname] & ", " & [tblTutor].[Forename] AS TutorName
FROM tblTutor INNER JOIN (tblStudent INNER JOIN tblAppointments ON tblStudent.[StudentID] = tblAppointments.[StudentID]) ON tblTutor.[TutorID] = tblAppointments.[TutorID];


One more thing, I ran the query, and typed in some numbers in the prompts, and was wondering: what exactly does it do?  


Paul.

PS.  I have emailed you the new database.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10913700
The trouble I am having is that the form is not filtering properly.  It shows the records from all three sources at once, which is not preferable.  

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10913837
Hmm, there should be no popups when running the query:
SELECT tblTutor.repTutorID, tblTutor.strSurname, tblStudent.repStudentID, tblStudent.strSurname, tblAppointments.repAppointmentID, tblAppointments.strType, tblAppointments.datDate, tblAppointments.timTime
FROM tblTutor INNER JOIN (tblStudent INNER JOIN tblAppointments ON tblStudent.repStudentID = tblAppointments.repStudentID) ON tblTutor.repTutorID = tblAppointments.repTutorID;

Only when you changed fieldnames. Name above query: "qrySelectTutorStudentAppointment"

It's indeed by design to have all rows displayed. By using a combo selecting the distinct Tutor, Student and/or Date you should be able to filter the rows.

Just start with a tutor combo based on:
SELECT DISTINCT qrySelectTutorStudentAppointment.repTutorID, qrySelectTutorStudentAppointment.tblTutor.strSurname FROM qrySelectTutorStudentAppointment;

In the afterupdate event the filter for the subform can be build, just give that a try.

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10922044
Nic;o)

Got the Tutor box named, and changed the row source to "SELECT DISTINCT qrySelectTutor..." as you said, and added a subform based upon the query "qrySelectTutorStudentAppointment".  After I saved the form, and changed to form view from design view, I am presented with the error message: "The specified field 'qrySelectTutorStudentAppointment.strSurname' could refer to more than one table listed in the FROM clause of your SQL statement"

Any ideas?  Am I not doing it right?  

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10922392
Just change the combo's rowsource by opening the query and selecting the Totor's strSurname as the student has the same fieldname Access will show an extra "qualifier" for both of these fields.

Clear ?

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10947402
Nic;o)
The project itself is due in today, but if it is OK with you, I would like to finish the database anyway.  Is this OK?  

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10947583
No problem :-)

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10963018
Thankyou.

Paul.  

BTW., I tried Excel again, and let the reinstall operation that automatically started run to its completion (about 30 seconds), and it worked fine, but still no Access data access pages.  I also started to have problems with my whole PC, especially cut and pasting (it always crashed when I did this).  I therefore reinstalled Windows and Office etc. again, and I still have no data access pages in Access.  I keep getting the message that:

The data definition of this data access page has been corrupted and can't be repaired.  You must recreate the page.  Save has been disabled.  

Any ideas???

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10963095
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10963472
I have got SP3.  I download things from school as they have a fast internet connection, and CD writers in the laptops.  I have a 56k modem, and can download at (if I am lucky) 2.2k (otherwise, ~1.5k)

Could the problem be that I am using SP3?  

Paul.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10963523
Perhaps the .mdb got corrupt try these MS Access database recovery steps:

1) Create a backup of the corrupt database. (Just in case of)

2) Create a new database and use File/Get external data/Import to get all objects of the damaged database.

3) Try these Microsoft solutions:
Repair A97/A2000:
http://support.microsoft.com/support/kb/articles/Q109/9/53.asp
Jetcomp:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273956
and/or read the article:
ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;306204

4) Bit "heavier":
Access decompile:
http://www.granite.ab.ca/access/decompile.htm

5) Try a recovery tool:
Access recovery:
http://www.officerecovery.com/access/index.htm

6) Ask a company (will cost $$'s ! )
http://www.pksolutions.com/services.htm

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10963592
This is not with an existing database.  If I create a new database and try make a DAP, I get given the message.  That is why I am so confused.  

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10963613
Sure all Access parts have been installed ?
And you could try first to reinstall Access without SR's and see or that does help...

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10963655
I reinstalled EVERYTHING over the weekend, Windows, Office, everything.  Took up all of Saturday afternoon (I work Saturday morning) and Sunday (it is a P133).  For Office, I have Microsoft Office XP Professional with FrontPage, and I did a full install using the option from the menu.  

Paul.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10966727
Pentium 133 ?
That's not really suitable for running office.....

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10967632
The funny thing is, however, it seems to run Office XP as fast as it did Office 97.  Also, it was really a necessity, as my school has Office XP as well, and it is a real pain to keep converting from XP to 97 and vice versa.  Also, I had problems with Word documents, more specifically, pictures in Word documents turning into red crosses.  I followed Microsoft's tech support page and updated my copy to SR-3 (whatever the newest one was [it was one up from the one that they suggested]) and it still did it.  XP looks nicer as well, and now I have it, I don't think that I could go back and be perfectly happy.  

Any ideas about the Access DAP's?  

Paul.  

BTW: What time zone are you in?  Better question: How many hours ahead/behind British Summer Time?  I am in the BST zone, so was wondering what time of day it is where you are.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10967705
I'm in the Dutch timezone GMT+1.

No idea about the DAP's, running out of thoughts on that....

Nic;o)
0
 
LVL 1

Author Comment

by:W2k-User
ID: 10967717
So its ~3:30 then?  Same as me.  

Microsoft only seem to have had any problems with DAP's when using it with the ActiveX system through Internet Explorer, i.e., in a website.  

Paul.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 11038330
OK.  Due to problems outside my control, I had to reinstall Windows _yet again_!!! [Bloomin computers...], so I now have Windows XP on my P133.......!  However, Data Access Pages now work, and it does not run any slower than 2k did.  

Paul.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 11294833
Sorry!
My computer has been playing up again, so have been left w/o a working OS, and have had to reinstall Windows again!

Agree with recommendation, and was going to proceed with it myself, but there was an error with the site, apparantly.  

Paul.
0
 
LVL 1

Author Comment

by:W2k-User
ID: 11294842
Nico:

Thankyou very much for your assistance on this issue.  I only hope that I can repay the favour in some way.  
0
 
LVL 1

Author Comment

by:W2k-User
ID: 11294850
For future reference, all answers provided by Nic;o) helped with the solution, not just one of them.

Paul [W2k-User].
0
 
LVL 54

Expert Comment

by:nico5038
ID: 11299277
Thanks Paul :-)

Nic;o)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now