How to Display multiple tables in one form

[saruafra]

Dear All

I Have 2 forms based on 2 Tables Emp_Mast & Sal_Mast. Emp_mast have records of Emp_Id, Emp_Name, Basic_Salary etc..... and Sal_Mast have record of Emp_Id, Emp_Name, Basic_Salary, Ot_Hrs, Net_Salary etc....



To make Salary Calculations what I need is If I enter Emp_Id in the Sal_Mast form it will display the Emp_Name, Basic_Salary from the emp_Mast table related to the given Emp_Id in the last focus of Emp_Id. and save to Sal_Mast Table.

Please anyone provide me the solution

Thanks

[pkstormy]

Can you provide a little more information regarding how you have things set up? It's difficult to answer what your question might be with the information you've supplied.

[saruafra]

yes the Emp_ID, Emp_Name, Basic_Salary are the common fields of both tables Emp_Mast & Sal_Mast.

What I need is If I enter the Emp_Id in the Text box of frm_Salary the appropriate Emp_Name & basic_Salary to be display, that is already in Emp_Mast Table.

[pkstormy]

Dear All

I Have 2 forms based on 2 Tables Emp_Mast & Sal_Mast. Emp_mast have records of Emp_Id, Emp_Name, Basic_Salary etc..... and Sal_Mast have record of Emp_Id, Emp_Name, Basic_Salary, Ot_Hrs, Net_Salary etc....

To make Salary Calculations what I need is If I enter Emp_Id in the Sal_Mast form it will display the Emp_Name, Basic_Salary from the emp_Mast table related to the given Emp_Id in the last focus of Emp_Id. and save to Sal_Mast Table.

Please anyone provide me the solution

Thanks

Actually what I don't understand is "If I enter Emp_Id in the Sal_Mast form it will display the Emp_Name, Basic_Salary from the emp_Mast table related to the given Emp_Id in the ??last focus of Emp_Id?? and save to Sal_Mast table."

If you're trying to update a field in another table (which is different than the recordsource table of the form) and it needs to be based upon values entered on that form, the easiest way I found to do this is via a function or via an update query.)

To update another table via an update query...

1. Design an update query which has your calculation in an expression (example: MyExpression: (Forms!MyFormName!SomeFieldName * Forms!MyFormName!SomeOtherField)/100) and then set this to update the appropriate field. Set criteria in this query for the main ID field (ie. autonumber or primary key field - probably Emp_Id) to something like =Forms!MyFormName!IDField (or for your case, probably =Forms!MyFormName!Emp_ID. This is so it knows which record to update.
2. On your form design, create vba code in whatever event (usually an AfterUpdate event of one or both of your fields for the calculation) to then run the update query (you'll probably want to also check for invalid values on the form before running the update query).

example:
Private sub MyCalc1Field_AfterUpdate()
if isnull(me.MyCalc1Field.value) or isnull(me.MyCalc2Field.value) then
msgbox "values cannot be blank."
exit sub
end if
docmd.setwarnings false '(turn warnings off so no prompt to update)
docmd.openquery "MyQueryToUpdateSalaryField"
docmd.setwarnings true '(turn warnings back on)
end sub

To display information on the form from a field (or fields) in another table, you can utilize the dlookup command for the sourceobject of a field (or create a combobox and set the controlsource of another text box to something like =Forms!MyFormName!MyComboboxName.column(X) where X is the appropriate column in the combobox).

Example using dlookup in the sourceobject to get values from another table:
=dlookup("[MyFieldNametoLookup]","myTableNametoLookup","[Emp_Id] = " & Forms!MyFormName!Emp_Id & "")

[pkstormy]

yes the Emp_ID, Emp_Name, Basic_Salary are the common fields of both tables Emp_Mast & Sal_Mast.

What I need is If I enter the Emp_Id in the Text box of frm_Salary the appropriate Emp_Name & basic_Salary to be display, that is already in Emp_Mast Table.

To do this, you may want to make an unbound combobox which has as its rowsource, the Emp_Id, Emp_Name, basic_Salary fields in the rowsource query. Then create another text box and for the controlsource put =Forms!MyFormName!MyUnboundComboboxName.column(1) and another texbox with a controlsource =Forms!MyFormName!MyUnboundComboboxName.column(2). (or you could combine them into 1 textbox) =Forms!MyFormName!MyUnboundComboboxName.column(1) & " --- " & =Forms!MyFormName!MyUnboundComboboxName.column(2).

(note: you can link whatever tables you need to link together in the combobox's rowsource to get the appropriate information.)