Autopopulate a textbox based on the lookup table

[pwalter83]

Hi,

I have a requirement to autopopulate a textbox (DIVISION_CD) based on the value in another textbox(LINE_CD).

A table - DIVISION already exists which contains the 2 columns - LINE_CD and DIVISION_CD. How can I use the DIVISION table as a lookup table to autopopulate the values in the DIVISION_CD textbox based on the matching value in the LINE_CD textbox.

Would somebody know on how to about this ? I have googled regarding this but have not been able to find any solution. Any suggestions would be appreciated.

Thanks,
Paul

[alansidman]

This should help

http://www.baldyweb.com/Autofill.htm

Alan

[pwalter83]

This should help

http://www.baldyweb.com/Autofill.htm

Alan

Thanks for the link but this is not what I am looking for.

What I need is that the textbox should autopopulate based on the value in the previous textbox and for that it should consult a lookup table(which already exists) for the matching value.

[orange]

Please show the table structures and some sample data.

[pwalter83]

Please show the table structures and some sample data.

Please find the table structure of the DIVISION table below:
----------------------------------------------
CREATE TABLE [dbo].[DIVISION](
[LINE_CD] [varchar](2)NULL,
[DIVISION_CD] [varchar](4)NULL
)ON [PRIMARY]
----------------------------------------------
The sample data for the above table:

----------------------------------
INSERT INTO DIVISION
VALUES('01', 'A432')
INSERT INTO DIVISION
VALUES('02', 'A442')
INSERT INTO DIVISION
VALUES('03', 'A452')
INSERT INTO DIVISION
VALUES('04', 'A462')
INSERT INTO DIVISION
VALUES('05', 'A472')
----------------------------------

The textbox - DIVISION_CD needs to be populated with the value based on the matching value of the LINE_CD textbox using the above table as a lookup table for looking up the values of DIVISION_CD column.

I hope I am able to explain my situation.

Thanks !

[orange]

Ok I see the Table, but what is the form where these text boxes are located?
What are you trying to do--it isn't clear.

[pwalter83]

Ok I see the Table, but what is the form where these text boxes are located?
What are you trying to do--it isn't clear.

Is there some way I can copy paste the form here ? I dont know how to.

Okay here is the explanation again:

There are 2 textboxes on the form- LINE_CD and DIVISION_CD. LINE_CD is already auto populated using a combo box. Now, whatever value is displayed in the LINE_CD textbox, based on the values stored in the lookup table(mentioned above), the other textbox- DIVISION_CD needs to be autopopulated. For example, if the LINE_CD value is displayed as '01' then the DIVISION_CD textbox should automatically show up 'A432' (referring to the sample data above).

I hope it is clear now.

[alansidman]

From what you are describing, this seems like what you are trying to do. Look at the attached and if this is not it, them try one more time to explain your needs. Open the form and select in the drop down, and your results are in the second box.

If this is what you are looking for, then I refer you to the original link -- because this is what I did.

[pwalter83]

From what you are describing, this seems like what you are trying to do. Look at the attached and if this is not it, them try one more time to explain your needs. Open the form and select in the drop down, and your results are in the second box.

If this is what you are looking for, then I refer you to the original link -- because this is what I did.

Thanks again !! The problem I am facing is that the LINE_CD column is already autopopulated through a combo box (located above it) and it is not editable by the user and displays only a single value. In other words, I don't have an option to bring in the values for DIVISION_CD in the LINE_CD textbox.

Please find my form attached. This would make the picture more clear. I should have already done that yesterday and saved you the extra trouble. The form is called- REGISTER_VESSEL.

Thanks for your help.

[pwalter83]

From what you are describing, this seems like what you are trying to do. Look at the attached and if this is not it, them try one more time to explain your needs. Open the form and select in the drop down, and your results are in the second box.

If this is what you are looking for, then I refer you to the original link -- because this is what I did.

Hi Alan,

I have reached to this point where I enter the DLOOKUP formula in the textbox which goes as :

=DLookUp("DIVISION_CD","dbo_DIVISION","Text41 = [LINE_CD]")

But the thing is the value in the DIVISION_CD textbox does not change when the LINE_CD value changes. Could you please give any pointers on this one ?

Thanks !

[orange]

Did you look at Alan's example? It seems to do what you're asking. If not, could you please try to explain in more detail.

[alansidman]

Crosspost at http://www.access-programmers.co.uk/...te=1&p=1121208

I have not been able to look at your example as I am currently on my machine that runs AC 2002. I will look later in the day when time allows.

[orange]

Alan,

Your example with =Text0.Column(1) and the dlookup approach yield the same result.

Is there an issue?

[pwalter83]

Alan,

Your example with =Text0.Column(1) and the dlookup approach yield the same result.

Is there an issue?

What Alan suggested was to add the field to the combobox to use it further in the textbox. This is where I have the problem, my textbox already uses an autopopulated value which neither can be edited nor can be used to add another field.

[orange]

10-4. It wasn't clear to me why his method wasn't acceptable. I opened his mdb and it seemed to work fine. Now that I reread post 9, I see the issue.
Thanks for clarifying.