Tabbed form help

[suasponte835]

Hello,

I created a database for my company to track injuries and root causes of injuries. I was using a standard access form to input the data but wanted to make something more user friendly and better to look at instead of the linked tables. I created a 3 tab form with Employee info, Injury Info and root cause info tabs. The forms across all 3 tabs update and seem to talk to each other fine. The problem is none of he tabbed forms display the correct number of records. The search bar at the bottom of the screen only shows 37 records and I have 50 in my tables. I verified the records are there. Does anyone know why this is happening?

 

[plog]

What is the control source of the form? My guess is you have based it on a query with an INNER JOIN and not a table.

 

[suasponte835]

When I created it, I just added existing fields from each table I was using. It allows me to enter data and it saves it to the table, but then I can't pull it back up with that form.

 

[plog]

Open the form to design view, click on properties and find out what the Control Source is.

 

[chellebell1689]

On the form go to Property Sheet -> Data Tab -> Record Source (click the "...") -> View (top left corner) -> "SQL View"

Copy and paste that text here, please.

 

[suasponte835]

It says the control source is my employee table

 

[chellebell1689]

Quick question, is there any records that have info in one table, but not all three? I just recently had the same problem and that was the case for me. I wanted to show all the Family ID's (from Family Table) and who is assigned to them (from Members Table), but one family was empty so it wasn't shown. Maybe that's the case here?

 

[suasponte835]

Everything seems to be good as far as that goes. When I switch back to the form I used the wizard to make, everything works find. It has to be something I did with the way I made the tabbed forms. I just created a blank form, made the tabs and dropped the existing fields in the respective tab.

 

[suasponte835]

On the form go to Property Sheet -> Data Tab -> Record Source (click the "...") -> View (top left corner) -> "SQL View"

Copy and paste that text here, please.

SELECT Employee.ID, Employee.LastName, Employee.FirstName, Employee.SSN, Employee.CompanyName, Employee.ClientName, Employee.Division, Employee.ClientState, Employee.JobTitle, Employee.Branch, Employee.Notes, Employee.HotList, Injury.ID AS ID_Injury, Injury.ID AS ID_Injury1, Injury.EmployeeID, Injury.InjuryDate, Injury.InjuryType, Injury.InjuryArea, Injury.InjuryPart, Injury.AdditionalInfoInjury, Injury.TimeLoss, Injury.TimeLossDays, Injury.OSHALog, Injury.FirstAidSel, Injury.GBclaim, Injury.InitialVisitCost, Injury.Visit2, Injury.Visit3, Injury.Visit4, Injury.Visit5, RootCause.ID AS ID_RootCause, RootCause.ID AS ID_RootCause1, RootCause.InjuryID, RootCause.ContributingActs, RootCause.ContributingConditions, RootCause.DisicplineCorrective, RootCause.AdditionalNotesRC
FROM (Employee INNER JOIN Injury ON Employee.ID = Injury.EmployeeID) INNER JOIN RootCause ON Injury.ID = RootCause.InjuryID;

 

[suasponte835]

Open the form to design view, click on properties and find out what the Control Source is.

I see where is says "inner join." How do I correct this?

 

[plog]

You reconfigure your forms. Forms that act on data (add/edit/delete) should be based on tables, not queries. 1 form, 1 table.

This doesn't mean you can't have it appear like you have one form, what you need to do is use sub-forms for the other tables. The way to do this is predicated on your relationships. The main table (the 1 in a 1 to many relationship) will be the main form. Then the many table will become a subform.

So if you have employees and one employee can have multiple injuries, then your main form would be based on employees and you would have a subform for their injuries.

 

[suasponte835]

Thank you very much for your help