Solved

Populating a Text field with A combo box

Posted on 2008-06-09
36
317 Views
Last Modified: 2013-11-28
Hi!!!

I've been searching this site for about 3 days now trying to get my problem solved.  Here's the situation:  I have a combo box that I want to populate 2 other text boxes.  I got the column count down and everything else.  It was working fine for the first text box, but didn't work for the second textbox.  and now, I don't know what I did to stop it from working!!!  I attached the Code (there are 2 different codes, as you can see).

For the first one (FirstName) the highlight is on "Me.LastName =  Me.FirstName.Column(1)
For the second one (ProductDescription), the highlight is on "Me.Text29 = Me.ProductDescription.Column(2)"

Please Help!!!!  Thanks So Much!!
Private Sub FirstName_AfterUpdate()

If IsNull(Me.FirstName) Then Exit Sub

Me.LastName = Me.FirstName.Column(1)

Me.Extension = Me.FirstName.Column(2)
 
 

End Sub
 
 

Private Sub ProductDescription_AfterUpdate()

If IsNull(Me.ProductDescription) Then Exit Sub

Me.UnitPrice = Me.ProductDescription.Column(1)

Me.Text29 = Me.ProductDescription.Column(2)

End Sub

Open in new window

0
Comment
Question by:MissC_9880
  • 14
  • 13
  • 5
  • +1
36 Comments
 
LVL 75
ID: 21746874
Code looks fine.

Are you sure it shouldn't be

Column(0
Column(1)

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21746879
what is the rowsource of your combo box?

you also you to set the column count of the combo to the number of columns of the row source.
0
 
LVL 75
ID: 21746884
typo ...

Column(0)
Column(1)

instead of

Column(1)
Column(2)

Column numbers in vba code are zero based, so physical Column 1 is Column(0),  2 is (1) and so on.

What error or problem are you getting/ having ?

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21746885
<you also you to set the column count of the combo to the number of columns of the row source.>

you also have to set the column count of the combo to the number of columns of the row source.

0
 

Author Comment

by:MissC_9880
ID: 21746896
Well, I didn't put column(0) because column(0) is used for the combo box itself.  And I want column 1 & 2 to populate the text boxes.
0
 

Author Comment

by:MissC_9880
ID: 21746912
Okay, my row source for both combo boxes is from a query that has only 3 columns in it.  I have already changed the column count to 3 in for both combo boxes.
0
 
LVL 75
ID: 21746919
"used for the combo box itself"

Meaning ?

What is not working right exactly ?

mx
0
 
LVL 75
ID: 21746925
"query that has only 3 columns in it."

Are you all of those columns are visible in the query output ?

mx
0
 

Author Comment

by:MissC_9880
ID: 21746930
The error message says "The value you entered isn't valid for this field"
and it highlight's what I mentioned above.

So the combo box is labeled "First Name", when I choose a name from the combo box, the value from column(0) is in the combo box and the value for column(1) will go one of the text boxes and so on.

Am I doing this right???  Crap, I'm starting to confuse myself now!!
0
 

Author Comment

by:MissC_9880
ID: 21746940
Yes, all columns are visible in the query output
0
 
LVL 75
ID: 21746966
What is the Bound column for the combo boxes?  Seems it should be 1 (physical col 1) ?

Are your text boxes bound to a field (ie, do they have Control Sources) ?

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21746973
MissC_9880,
post the query here

if it is like this

select id,firstname,lastname from tablex

column(0) is the id
column(1)  is the first name
column(2)  is the lastname
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21746977
and you should remove  the control source of the combo it should be blank
0
 

Author Comment

by:MissC_9880
ID: 21746980
The Bound coumn is 0 and all text boxes have control sources.
0
 
LVL 75
ID: 21746988
Change the Bound column to 1 on the Combo boxes.

mx
0
 
LVL 75
ID: 21746993
On a combo box, having a Bound column of zero is for special use - - the ListIndex instead of the Column value.

mx
0
 

Author Comment

by:MissC_9880
ID: 21747002
If I change the bound column to 0 then I need to change my query to add in the ID.

Here's the query for one:
SELECT Employees.FirstName, Employees.LastName, Employees.Extension
FROM Employees;

And the query for the other:
SELECT Products.ProductName, Products.UnitPrice, Products.AmountInStock
FROM Products;
0
 

Author Comment

by:MissC_9880
ID: 21747060
oops, I meant change th column to 1
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75
ID: 21747068
In your Combo boxes ... do you *want* to store the value selected, then display also the other columns in the text boxes ?  

mx
0
 

Author Comment

by:MissC_9880
ID: 21747076
Yes
0
 
LVL 75
ID: 21747099
ok ... from what you are say, your settings for both combo boxes s/b this:

Bound Colunn: 1          *****
Column Count: 3

And your original code should work.  I think the issue is your Bound Column was set to zero (0) - it needs to be 1 - which is physical column 1 in the query - unlike in code

mx
0
 

Author Comment

by:MissC_9880
ID: 21747206
Okay, I got you on that....
So I changed everything (the bound column and column count).
But it's still giving me an error message.

I'm getting "The value you entered isn't valid for this field" for the first one still and it highlights the 3rd line in the code.

For the ProductDescription_AfterUpdate(), I'm getting "You can't assign a value to this object", and the "Me.Text29 = ....." line is highlighted.

Private Sub FirstName_AfterUpdate()

If IsNull(Me.FirstName) Then Exit Sub

Me.LastName = Me.FirstName.Column(1)

Me.Extension = Me.FirstName.Column(2)
 
 

End Sub
 
 

Private Sub ProductDescription_AfterUpdate()

If IsNull(Me.ProductDescription) Then Exit Sub

Me.UnitPrice = Me.ProductDescription.Column(1)

Me.Text29 = Me.ProductDescription.Column(2)

End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21747265
Private Sub ProductDescription_AfterUpdate()
If IsNull(Me.ProductDescription) Then Exit Sub

Msgbox Me.ProductDescription.Column(1)  '<-- to make sure you are getting the right value

' if Me.UnitPrice  is currency, then use:
Me.UnitPrice = CCur(Me.ProductDescription.Column(1))   '<-- becuas    'Column(1) gives you as a string

'Do the same for the following line:
Me.Text29 = Me.ProductDescription.Column(2)

End Sub
0
 

Author Comment

by:MissC_9880
ID: 21747352
It's still giving me the error.  In the Message box, it gives me the correct value; when I click "ok", it gives me the error stating "you can't assign a value to this object".  And it still highlights the "Me.Text29..." line.   This field isn't currency.  I have it set to the general number format.
0
 
LVL 75
ID: 21747366
"Me.LastName = Me.FirstName.Column(1)"

What is the Control Source for the LastName text box ?   and In your table, what is the data type for the LastName field?  I would assume Text ?

And for

"Me.Text29 = ....." line is highlighted.

Is Text29 a Calculated Control ?  Meaning the Control Source has an expression like

=SomeExpression

?

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21747378
did you do:

Me.Text29 = CDbl(Me.ProductDescription.Column(2))

?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21747390


        v--- I didn't know what is the variable type for this
Me.Text29 = CDbl(Me.ProductDescription.Column(2))
                                               ^-- because this gives you the result as text
                                                     you need to convert, in this case to a double
                                                     in order for it to work.
0
 

Author Comment

by:MissC_9880
ID: 21747443
MX:
The control source for LastName is ... "LastName" (I'm assuming I'm gonna have to change that!!)
and No, Text29 is not a calculated control.  In the Table, that it corresponds with, the data type is Number.

eghtebas:
I tried that, no luck.  didn't do anything for me.  
It's still giving me the error "you can assign a value to this object"
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21747465
try

Me.Text29 = 112

see if it takes it. If not, try:

Me.Text29.SetFocus
Me.Text29 = 112

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21747468
Is Me.Text29 bound?

I guess I need to read the previous posts to find this out.
0
 
LVL 75
ID: 21747501
Lets cut to the chase:

Can you zip up the MDB, then attach the file for upload here  ... removing any sensitive data of course?  

mx
0
 

Author Comment

by:MissC_9880
ID: 21747539
Okay, zipped the DB...

Thanks for the help!!!
DB1.zip
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 21747622
ok.

First problem:
In the table, the field FirstName is a Numeric Data Type ... and you are trying to put a text value in this field via the Combo box (and the text box LastName).  You cannot do that.

And ... you have the same Control Source for Extension - First Name !!  There is no field in the table called Extension either.

So, really ... your setup is not exactly making sense.  

I *think* what you want to do is put an Employee ID in the table.  You really do not want to store the First and Last Names ... just the ID.

Anyway ... I've attached the mdb with some mods - to give you the idea ... the way I think you are intending.  You do really need any code in the combo AfterUpdate.

mx


db1-MX01.zip
0
 
LVL 75
ID: 21747637
Note ... we may want to change what is Displayed in the Combo box ... right now it's the First Name ... but I also added a text box for that ... so, you can decide that.

mx
0
 

Author Closing Comment

by:MissC_9880
ID: 31465567
Holy crap!!  The light bulb finally went off in my head.  I think I had the First and Last name as a number data type because of the lookup wizard.  Right after I read your post, I figured where the screw up was at.  Thanks so much!!   Well, and I figured out why the other one wasn't working. Arrrgghh!!!  Okay, I got it.  Thanks again!!!
0
 
LVL 75
ID: 21747830
you are welcome ...

mx
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now