Field Value control through a combo box from another table

[visitnag]

Hi,

I have created a Form for data entry into Deductions table.
Here I am restricting the primary value of empId through a combo box.
The values of combo are taken from a master table.

There is a subform to show the entries of each session (unless and until form is closed) to facilitate the users
what they have entered recently



While making data entry..

I want to check, when a record is already available in Deductions table, the combo box selection of that entry should
prompt that the record is already available in DeductionsT..and the record details can be shown in the Deductions parent
form, when not availble it should proceed with the data entry.

I could succeed to some extent in this regard.

The Problem:

1. When I press tab at combo its proceeding with null value.

2. If I select a value its giving warning at the end of the data entry that the record is available in the
DeductionsT without showing its complete details in the respective text boxes.

3. When a new entry is selected, the values in the combo and EmpID text field are disappearing, but data entry is happening successfully.

4. The new entries of a session are not populating in the subform.


Please also suggest me how to delete a wrong entry, rather than editing the record.


I have tried my best and tried googled to get help, but could not succeed.

I have attached a sample db.

Please help

Thank you

[davegri]

I suspect that a good bit of the problems are caused by having Data Entry = Yes for the subform. Change it to NO and post back with any remaining problems.



Another big problem is assigning primary keys as equal relationships in your tables. The deductions table EMPID must NOT be a primary key.
If it's a primary key, you could never have more than one record per employee in the table.

[visitnag]

I suspect that a good bit of the problems are caused by having Data Entry = Yes for the subform. Change it to NO and post back with any remaining problems.



Another big problem is assigning primary keys as equal relationships in your tables. The deductions table EMPID must NOT be a primary key.
If it's a primary key, you could never have more than one record per employee in the table.

Thank you for your reply. Yes I need only one entry per employee in Deductions list

[davegri]

Thank you for your reply. Yes I need only one entry per employee in Deductions list

Then there's no reason to have a deduction table. Just put the info in the MasterT.

[visitnag]

Yes...but master table data is extracted separately which has 15 fields.

The deductions are received manually. Total records runs more than 2000. Till now the deductions are taken in an excel.. then it's into table and updation with master. In this process some duplication is going on.

[davegri]

Need more details. What is a session?
Master table is extracted from where? How often? Does Master table start as empty for each session?
What does "Till now the deductions are taken in an excel" mean? What do you do now instead? Are you importing Excel data?

An explanation of the complete process would help in identifying more specific suggestions.

[visitnag]

Thank you.
The Master table is taken from a data source once in a year on a particular date.
Then the deductions/expenses are taken manually from the employees.
The records of deductions/expenses are only one record per employee.
As of now we are keying in the deductions/expenses into an excel, later we are importing into a deductions table.
In excel duplication of data entry is happening rather than correcting the record
As and when we receive the deductions list from employees we give a batch number for that particular day. (this batch number is used to generate report for that batch)
Then we are updating the master with deductions table.
Depending on the expenses some calculated fields are arrived

I am leaving the per session option. Now I have coded the subform to show the record info if it is available in deductions table when the empid is selected
from combo, but the same info is not appearing in the relevant text boxes in the parent form.

Now I need :
The combo box entry should show the record, if available, from deductions table in relevant text boxes in parent form. If not available only blank values in
relevant textboxes of parent form(except empId text box, then user can proceed with new record entry)

[davegri]

Emp-davegri-v01.zip

OK, here's a new design. The DeductionsF form has no subform. The deduction fields are in the DeductionsF form and in MasterT. The DeductiondF form is bound to MasterT and is the only form required.
The button on the DeductionsF form will run code to update MasterT with all records in DeductionsT. Criteria in the code will restrict MasterT records that get updated based on date, batch, EmpID and/or whatever.
The combo box will search for EmpID if user needs manual updates after batch update.

[visitnag]

Thank you very much. In other way its helping me.