Textbox input will auto complete another text box

[Brian88195]

Hi, i am a new user to access with background training in Access. I have searched for this answer, however i have been unable to find an answer that works for me. The question i have is, that i would like to type a Policy Number in one text box and the first character of the Policy Number indicates the company that it is associated with. I would like the associated company to be automatically generated in the following text box. I created a table that contains all of the companies and the first character of the policy will always be a letter that represents a specific company.



Example:

Policy number: x12345
x= Company ABC

Would like ABC to automatically generate in the text box labeled Company.

Please let me know if additional info or screen shots will be of any value to help solve this question.

Thanks

[CJ_London]

in your policy number afterupdate event put something like this code

Companytxtbox=dlookup("companyname","tblcompanies" ,"CompanyID='" & left(policynumber,1) & "'")

change all names to match what you have as your control, field and table names. If you have spaces (which is not a good thing) then you will need to surround names with square brackets

[Brian88195]

Thank you, Ajax.

this is the code that I came up with after changing my control, field and table names:

Text229=dlookup("CompanyAbreviation","tblCompanies " ,"CompanyName='" & left(Text182,1) & "'")

When I put this in the afterupdate event for the control where the Policy Number will be typed in it doesn't work for me.

The policy number is typed in a textbox (named Text182), I would like the company name to be generated in the following textbox (named Text229). the table where the companies are stored is named "tblCompanies", the abbreviation for the company is stored in a column labeled "CompanyAbreviation", the company name, which I would like to have populated in textbox "Text229" is stored in a column labeled "CompanyName"

I have a relationship setup between tblCompanies and tblNewTransaction for the companies.

Thanks for your help and let me know if I can provide you with additional information to help.

Thanks,
Brian

[CJ_London]

It looks to me tlike the way you have set you dlookup is to find a companyabbreviation for a company with is a single character - shouldn't these be the other way round? i.e.

Text229=dlookup("CompanyName","tblCompanies " ,"CompanyAbbreviation='" & left(Text182,1) & "'")

[June7]

The letter prefix is first letter of company name? So you can represent only 26 companies? You don't have more than one company name beginning with same letter?

Text229 = DLookup("CompanyAbreviation", "tblCompanies ", "Left(CompanyName, 1) ='" & Left(Text182, 1) & "'")

or

Text229 = DLookup("CompanyAbreviation", "tblCompanies ", "CompanyName Like '" & Left(Text182, 1) & "*'")

NOTE: abbreviation actually has two b's