DLookup

[randykinder]

I am attempting to use DLookup to auto fill a cell in my form depending on the value in a preceeding cell (combo box).

i.e. Table A: 2 columns - 1) company name, 2) company FEIN

Table B - multiple columns, combo boxes in table B linked to Table A so user can choose from list of 1) company name and 2) company FEIN

Form for Table B - want form to pull FEIN from Table A when company name is chosen.

Can't seem to get this to work. Anyone have any suggestions?
Thanks

 

[byurow]

My ways are usually pretty convoluted, but they work (for me). You could do something like this:

Dim lngCompanyNo As Long

lngCompanyNo = Me.cboCompanyName.column(0) 'Assuming that the company name combo box has 2 columns - column 0 is the company ID and column 1 is the company name

    Me.TxtFEIN = Nz(DLookup("TxtFEIN", "TblA", "CompanyID =" & lngCompanyNo))

Hope this makes sense! Good luck!

Brenda

 

[randykinder]

unfortunately I am asking because I do not know how to write code.

Have changed Table B - Combo box only for Legal Entity (the FEIN cell is no longer a combo box). I want the FEIN cell to choose the FEIN that corresponds to the Legal from Table A, after I have chosen from the Legal from combo box.

within the form - cell one = combo box = choose legal entity
cell two = FEIN = automatically pulls correct FEIN from Table A depending on legal entity chosen in cell one

 

[gazolba]

Dlookup is very s-l-o-w

 

[byurow]

gazolba, you are right, DLookup is very slow, but for one field it works fine. (I usually use queries to populate my fields but that is not what he asked for)

Randy,

if you want, you can send me the application and I would be happy to get your field to populate for you then show you what I did.

My e-mail is misty30706@aol.com

It would only take me a minute to do so.

Let me know.

Brenda

 

[byurow]

Randy,

Sorry for the delay, but I have just sent your database back. You were close! You needed to put the code on the OnClick event of the Legal Entity Combo box. You were already pulling the FEIN when you chose the Legal Entity - the first column of your Legal Entity Combo box. All I did was tell Access to take that value and place it in the FEIN combo box. I also created a Text box called FEIN and placed the value there as well. I'm not sure what your business rules are, but get the feeling you are not going to want your users changing the FEIN, so this way you can take the text box I created and lock it down so that the users cannot change the values.

Let me know if you need anything else or if this did not solve your problem.

Good luck!

Brenda