How to get value in a combo box in a form to a table field

[raghavcacs]

Hi,

I have a form for input which have following fields:

Emp No, Emp Name, Leave Type and some other fields.

Form is getting this Emp No in Combo box method from a query which will fetch only the active employee's employee number.

I need the following:

1) Upon selecting the Employee number from combo box, It has to automatically select the employee name from that query.
2) Leave type and other fields needs to be filled.
3) After keying in the details, I will press a button, upon which all these details needs to be uploaded in a table.

My problems are:

1) I don't know what is the syntax i used to map the form variable to Table field.

2) how can i lookup employee name upon selection of employee number from the combo box

Can anyone help me on this?

 

[revver]

I am not sure that I understand your question but assuming that I do

Problem 1. The RecordSource of the form should be set to the name of the table you want to save the data in. The ControlSource of each text box on the form should be set to the corresponding field in the table.

Problem 2. You shouldn't be storing both the Employee Number and the Employee Name in this table, only store the Employee Number. The name can readily be looked up from tblEmployees any time it is needed. If you must store it in this table (or just display it on this form) you could use something like

=DLookUp("EmpName","tblEmployees","EmpNo=" & [FormName].[cboEmpNo])

 

[raghavcacs]

Below is my problem:
I am going to have employee number from a query (which i will link with the form as you said). Upon selecting the employee number in a combo box, the corresponding employee name has to be grapped from the query and shown in the form, then i have other fields like leave etc., which i will fill in the form. Upon filling, i will press a button, and the fields i filled in the form needs to be updated into table2.

Am I clear? Please let me know if you have any solution or any other idea of doing this?

 

[revver]

Put simply, if you want the form to save data to Table2 then the RecordSource of the form must be either
Table2
or
A query including Table2.

Therefore it sounds like you need to change your query to include Table2.

As for displaying the Name, if the Name field is already part of the query (name has to be grapped from the query), what is the problem. Otherwise, my suggestion of using DLookup to display Name on the form still applies. It uses tblEmployees to look up the Name from the Number. Just set the ControlSource of the text box to
=DLookup("EmpName","tblEmployees","EmpNo=" & [FormName].[cboEmpNo])

If this still doesn't make sense, please list the pertinent fields in both tables and the SQL of your query.

 

[bhob]

Your form should update the table in real time as you enter it (remember that no data is stored in the form. All data is in a table somewhere). If the table you want to update is not the record source of the form you're using, then you have dual entries which violates the rules of data integrity and table normalization. You should only store each piece of data in one place. If you want to display it in multiple places/ways, you can easily do so with queries, forms, reports, and other features of MS Access.

 

[bhob]

It sounds like REVVER knows a lot more about this than I do, though!

 

[raghavcacs]

Revver,

Your suggestion worked for me.

I used Dlookup in VBA and assigned the same to Controlsource of the text box in the form.

That worked.
Thanks for the suggestion.


Bhob, I understand what you are saying. I know them also. Anyhow, thanks for your headsup.