Single Form, Multiple Tables...

Sabotage1945

Registered User.
Local time
Yesterday, 19:44
Joined
Sep 30, 2004
Messages
17
Sorry for the newbie question but...

I've got a form called 'frmStudent' which is linked to 'tblStudent'. I have designed it as a tabbed form, the first tab has "Student name", "Student Number", etc. The second tab is linked to visits by the student to a mentor (such as a tutor etc). Here I'd like to have "Date of visit", "Mentor Name", etc.

So far I've been able to get the first tab to work (I'm guessing because it's linked to 'tblStudent'), but can't get my 2nd tab to work as I have no clue on how to link it with 'tblVisits'. Is there a VBA methor or perhaps a nother method to do this?

I'm attaching a link to the sample of what I'm doing (please shift-open into frmStudent to view what I'm talking about). Any ideas and suggestions would be greatly appreciated!

DOWNLOAD DATABASE:
http://www.myfilestash.com/userfiles/sabotage1945/Student_Help.zip

Kindest regards,
Sab.
 
if your students table and visits table are linked (i suppose by studentid), then you can create a query with all the required fields from both tables, and then you can bind your form to the newly created query.

As a suggestion, do not bind your forms directly to tables, but always bind them to queries.
 
Thanks maxmangion!!

As a newbie, I didn't even think of binding the forms to the queries... but now it works great. I still have one problem as I'm not certain if each of the combo-box should also refer to the query I created. All of them seem to work except one which points to "Mentor Name" which is a combination of txtFirstName, txtLastName, on the criteria that they both have check marks on the "chkUser" AND "chkMentor" in the 'tblUser'.

The query you suggested already has "Mentor Name" field based on the criteria I set above, but my form refuses to allow any changes. I'm assuming that in the form I should point to the query and not the original table - correct?

Perhaps this is a question better placed in the Queries section of this form... but it directly deals with this issue in my form window.

Thank you again,
Sab.
 
I'm assuming that in the form I should point to the query and not the original table - correct?

Correct. You should call the mentor name from the field in your query, not from the table. Moreover if your mentor name field is a combination of two other fields (from what you said, txtFirstName and txtLastName), there is no point in saving the mentor name field in your table. You can either call it from the query, or else create an unbound text box and set it to:

=[txtFirstName] & " " & [txtLastName]

If you take some time and search this forum, you will find several threads, stating why it is not ideal to store calculated fields in tables.
 
Thanks again for the confirmation on that! I believe I'm doing this correctly now in my database...

However, I seem to be able to 'see' the fields of the query in my forms - but it appears that I cannot "Add" new data to it. Is that the limitation of extracting information from the query, that you cannot add information?

This final confirmation would be helpful as I'm going crazy trying to figure out why my new data isn't being accepted.

Thank you again,
Sab.
 

Users who are viewing this thread

Back
Top Bottom