Tables question

[Moses76]

Employee database :I have a table containing the following

ID ( Primary Key)
First Name
Last Name
Department

Second table where I enter details of the leaves that they take

Absence Records(Primary Key)
ID(Foreign key)
First and Last Name key (I combine these two in a separate query)
Type of Leave Taken
Number of hours of leave taken
Date on which leave was taken

The two have a one to many relationship
My issue is when I try to insert data from the subform into the Absence table it gives me a message saying No such record present in Agent master table . Is it due to the fact that in the master table the names are separately stored as First Name and Last Name? where as In the subform for entering the leaves I have it autopopulating a combo box.
Eventually I ended up changing the relationship and unchecking enforce referential integrity and it let me insert it.

Please guide me . Am i following the best approach by using a query to concatenate the first and last name and autopopulate the combox ?
Should I use kind of procedure to update the master table with the concatenated string right at the outset ?

Moses

 

[CJ_London]

I think your problem is your employee firstname/lastname in your absence table. Just to clarify your tables should be as follows:

EmployeeTbl
ID ( Primary Key)
First Name
Last Name
Department

AbsenceTbl
ID (PrimaryKey)
EmployeeID(Foreign key)
Type of Leave Taken
Number of hours of leave taken
Date on which leave was taken

If they are then the recordsource for your form should just be the AbsenceTbl

The rowsource for your combobox should be

SELECT ID, FirstName & " " & LastName FROM EmployeeTbl

The Combobox Controlsource should be EmployeeID and the bound column=1, columncount=2, column widths = 0;2;

Try this and see

 

[Moses76]

Something funny is happening . when I click on the dropdown for the name and choose one of the names - it changes it for the whole column. and not just that one row. any ideas about whats going on.

 

[Moses76]

Was doing something wrong . Seems to be working now.