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

raghavcacs

Board Regular
Joined
Mar 13, 2009
Messages
84
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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

revver

Active Member
Joined
Dec 20, 2007
Messages
251
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
Code:
=DLookUp("EmpName","tblEmployees","EmpNo=" & [FormName].[cboEmpNo])
 

raghavcacs

Board Regular
Joined
Mar 13, 2009
Messages
84
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

Active Member
Joined
Dec 20, 2007
Messages
251
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

New Member
Joined
Dec 9, 2009
Messages
2

ADVERTISEMENT

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

New Member
Joined
Dec 9, 2009
Messages
2
It sounds like REVVER knows a lot more about this than I do, though!
 

raghavcacs

Board Regular
Joined
Mar 13, 2009
Messages
84
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top