How to? - Auto populate 'text boxes' with values from selection made from another field. (1 Viewer)

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
Hi,
New to the forums and its been a while since i did any Access stuff but for the life of me i cant seem to find a solution , for what i believe is a simple issue.

I have an excel workbook that holds the value choice for the drop down box and what i would like it to do is populate 2 other fields in the table based on the selection made.
Workbook - 3 columns - Account number, Company Name, Postcode
(this workbook is static information)

Table - Account Code (drop down box) - Company Name - Postcode

Account Number is the drop-down and i'd like it to populate the corresponding Company/Postcode with the values from the columns next to it in the excel worksheet.

Thanks in advance,
o/
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:04
Joined
Sep 21, 2011
Messages
14,046
Bring all the data into the combo, then in the AfterUpdate of the combo
Code:
Me.Company = Me.Account.Column(1)
Me.Postcode = Me.Account.Column(2)
 

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
Bring all the data into the combo, then in the AfterUpdate of the combo
Code:
Me.Company = Me.Account.Column(1)
Me.Postcode = Me.Account.Column(2)
Gasman,

That works but i cant seem to get the POSTCODE to show although i'm pretty sure its column 2. Is there anyway to see what my 'variable' account_code contains/returns?

Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Is this in Excel? Maybe the column index is zero-based, like in Access. So, the first column is referenced as Column(0).
 

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
Hi. Welcome to AWF!

Is this in Excel? Maybe the column index is zero-based, like in Access. So, the first column is referenced as Column(0).
The COMBO BOX does retrieve its values from excel workbook/s so yes column(0) is the ACCOUNT CODE but although i understand the coding,
Code:
Private Sub Account_Code_AfterUpdate()

Me.Company_Name = Me.Account_Code.Column(1)
Me.Postcode = Me.Account_Code.Column(2)

End Sub
i'd like to see what ACCOUNT_CODE is/encompasses.
 

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
Thats what i'm trying to do, I know that ACCOUNT_CODE is valid but i cant seem to find what its dimension are...?
 

Minty

AWF VIP
Local time
Today, 18:04
Joined
Jul 26, 2013
Messages
10,355
In your combo Unhide the first column, make the colunm widths 2;2;2 and set the last width to 6
 

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
In your combo Unhide the first column, make the colunm widths 2;2;2 and set the last width to 6
Is that in the PROPERTY SHEET for the combo box? if so i cant see an 'unhide' option.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:04
Joined
May 7, 2009
Messages
19,169
check the Column Widths property of the combobox.
if the first column is 0, change its value to anything except 0.
 

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
OK, so if i'm in the right place it was 'blank' but i have changed it to 1 (which = 1cm)
prop sheet 090821.jpg
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:04
Joined
Sep 21, 2011
Messages
14,046
Your column count is 1, needs to be 3. Also show SQL for the combo, whichyou dot appear to have??
 

NRGZ

New member
Local time
Today, 18:04
Joined
Aug 9, 2021
Messages
15
Yes that worked. It now shows 3 columns for the dropdown (even though1 would be better....) and now populates the POSTCODE field. Very odd and i changed COLUMN WIDTH back to 'blank' and it formats the columns alot better.
just odd solution is all but hey ho!

Thanks to EVERYONE who helped.
o/
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:04
Joined
Sep 21, 2011
Messages
14,046
Normally, you will only show the column(s) you want, so if you had an autonumber in that table, mine would be
0;2.54;0;0
as the data would be AccountID,AccountName,CompanyName,CompPostCode as I only want to show the Account Name
 

Users who are viewing this thread

Top Bottom