Full Version: Creating Subform Linking To Main Form
UtterAccess Forums > Microsoft® Access > Access Forms
xfaith
I am trying to have a main form showing the Employee with Tabs(Pages) for addditional info like Certs, Volunteer work, Installs Completed. I think I have it mostly figured out on the back end (I maybe wrong on that also which might be causing my issue). To explain let me show my tables
ontacts
EmpID
FirstName
LastName
Rank
TblShips
ShipID
Ship
TblShipRide
ShipRideID
ShipID
EmpID
DateComp
TblShipInstall
ShipInstallID
ShipID
EmpID
DateComp
----Certificates-------
TblCertificates
CertID
CertName
TbleEmpCert
EmpCertID
CertID
EmpID
Ok so I used a query to pull the information together to show the actual names of the Employee and Ships instead of just showing the ID numbers, same for the certiifcates.
So instead of showing
EmpID ShipRideID DateComp
1 2 5/5/11
It does show
David Rambleer 5/5/11
Which is what i wan it to show.
But Now I wanted to take it a step further and have it part of Main form as a Tab/Page that I can click through to see what ships they have installed on and what certs are done.
But I think the way I have the query setup:
SELECT [Rank] & " " & [Last Name] AS Name, TblShips.Ship, TblShipInstall.[Date Completed]
FROM Contacts INNER JOIN (TblShips INNER JOIN TblShipInstall ON (TblShips.ShipID = TblShipInstall.ShipID) AND (TblShips.ShipID = TblShipInstall.ShipID)) ON Contacts.EmpID = TblShipInstall.EmpID
GROUP BY [Rank] & " " & [Last Name], TblShips.Ship, TblShipInstall.[Date Completed];
Its not allowing me to link the EmpID from the main form to the EmpID that show be associated in the query.
Somehow I did it with one of my subforms but it was a while ago and I just grabbed it from an old database and reusing it, but unsure how its pulling the data. Additionally I dont know once it links the EmpID how I can have it not show the EmpID/Name in the subform since we already know who that is.
I hope that this make sense to anyone. I can probably upload the database if needed once I scrubb any PII.
LoveMonkey
Hi,
assume the query you posted was for the record source of your sub form. Try adding:
WHERE Contacts.EmpID = Forms!MAINFORMNAME!EMPIDCONTROLNAME
to the query.
If that doesn't work and you want to upload the database I'll be happy to have a look at it.
Cheers
xfaith
I have uploaded it so you can take a look at it. Ill be looking @ it at home to try your suggestion but this might be better if you can actually see it.
I have attached a 2007 and 2003 not sure which is the best to
LoveMonkey
Hi xfaith,
had a quick look and I think the problem is that the query your sub form is based on does not include the field EmpID in it's record source.
I just added that to the your query and it allowed me to add the form through the link master/child fields. Some of your macros don't seem to be working on my computer though so I don't know if it's entirely fixed, but attached is your database with the updated query.
Hope this helps.
Cheers
xfaith
Thanks for the quick look over.
One question, how did you get the EmpID not to show on the subform. I figured you just reduced the size showing but could not bring it back. (Just want to know for future)
LoveMonkey
Oh, I didn't actually change anything on the design of your subform, I just used it exactly as it was. All I did, literally, was add EmpID to Ship Install Query and then add Ship Install Query Subform to the tab and follow the wizard to make the links.
think if you don't have a control for it in form view then it won't show a column in datasheet view, even though it is part of the form record source. If you add a text box to your subform, link it to Emp ID and label it then it will show in a column in datasheet view with a column heading of whatever the text box label says.
Cheers
Jeff B.
Any chance any of the fields are defined as "lookup" data types in their underlying tables? That can confuse the issue ...
LoveMonkey
Hi Jeff,
one of the table fields are look up data types.
xfaith,
It was late last night/early this morning when I posted before and I forgot to mention that you do have spaces in some of your names. It might save you some trouble down the line if you remove those spaces before you go much further.
How are the subforms working out for you now?
Cheers
xfaith
Looking good so far. After I started I remembered about not having spaces in names for things so I have been slowly going back and changing it.
My next hurdle which I dont think should actually be that hard to think was going to have a splash page where I can add the info
IE
Add Ship Ride
Add Ship Install
(think that is easy part have not tried yet)
And then I was going to have something like
Total Ship Rides:
Total Ship Install:
Which I believe I can just pull the ShipRideID/ShipInstallID and use that Number in the space since thats what I will be counting anyway.
Well guess I will try that tomm.
till I think of somethin else that might be good to try and collect data for (this used to be like 10 differant excel forms) and was getting to be a hassle.
LoveMonkey
Hi xfaith,
Glad we were able to help. I really like access for sorting information too, makes administrative jobs so much easier.
The pages to add additional records should be fairly easy to use. If I understand your needs correctly you will just create them like normal forms that are opened by a command button on the main form. You will need to add an event somewhere to requery the main form after adding a record which will depend on how you lay out your forms.
For your totals you will want to use one of the count functions, depending on what exactly it is you want to accomplish. You can add criteria to your counts too, so you can show total per staff, total per ship, over all total etc etc.
If you need help on another topic post back to UA. I'll help out where I can.
Cheers
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.