How to Add new records in related table

[fayez_MrExcel]

I have an EmpMaster table and SalaryMaster details with a "enforce referential integrity" and "cascade Update related fields". And i also create a query of Q_Empmaster. Note: Primary key employee number.

My question is: Why is that whe i entere new records in EmpMaster it does not automatically update or add the new number i entered. Example I add new employee number 10009 in EmpMaster, but when i open the SalaryMaster and Q_EmpMaster the new employee 10009 is not there..

Help please

 

[SydneyGeek]

You won't get a Salary record just because you added an Employee record. You will need to create a Salary record using the new EmployeeID.

The referential integrity ensures that you cannot create a salary record for an employee whose ID is not already in the system, but it does not create the downstream records for you. You will also find that, once you have created a Salary record for an Employee, you will be unable to delete the Employee without first deleting the Salary record(s) for that Employee.

Denis

 

[fayez_MrExcel]

Sir but what about the Query that i make...i used the same EmpMaster table in my query. I believe that what ever table is inlcuded in the query should be automatically updated right? but why my EmpMaster table inside the query does not include the last employee which was entered in the EmpMaster table..

 

[SydneyGeek]

It depends on how you built the query. If it is based entirely on the EmpMaster table, you should get the new employee record. If it is based on a join between EmpMaster and SalaryMaster, you will not see a record until there is a matching Salary record.

If you *do* want to see all Employee records regardless of whether you have a Salary record for them, open the query in Design view and right-click the join between the tables. Pick the join properties, and select the join type that shows all records from Employee with matching records from Salary. It will be type 2 or 3. Click OK and run the query to see the data.

Denis

 

[fayez_MrExcel]

the query is entirely based on the EmpMaster alone that's why im surprised why i did not get the new record...

In regards to adding the new record to other table. Sir how can i add the value of a certain text box to a particular table. Example i want to save the "frmNewEmp.Nametextbox" to SalaryTable.Name

Thanks sir..newbies here

 

[SydneyGeek]

the query is entirely based on the EmpMaster alone that's why im surprised why i did not get the new record...

If you switch to the query immediately, before nvigating off the new record (and therefore saving it), you will not see the new record in any forms, queries or reports that use it.

In regards to adding the new record to other table. Sir how can i add the value of a certain text box to a particular table. Example i want to save the "frmNewEmp.Nametextbox" to SalaryTable.Name

Use the interface. First, make sure that the EmployeeID field in the Employees table is also in the Salaries table, with exactly the same name. This should be the join between the tables.
1. Create the Employees form
2. Create the Salaries form and make it a Datasheet (in the form's Properties).
3. In Design view on the Employees form, press F11 to bring the database container to the front. Drag the icon for the Salaries form onto the Employees form, and let go.
4. Go and take a look; for any employees that have a Salary record, you will see that displayed. Otherwise you will have a blank line. To create a salary entry for an employee, just enter the salary and other details. The SalaryID field will be filled for you if it is an AutoNumber; the EmployeeID field will be populated automotically because of the form setup.
There is no need to pull the name into the Salaries table because the ID creates a direct link back to the Employees table, where that information should be stored.

Denis