p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Combo box and Text box (http://p2p.wrox.com/showthread.php?t=20946)

Rudner November 8th, 2004 01:47 AM

Combo box and Text box
 
Hi friends,

I used a combo box to populate the data from my table and I did it. My code goes like this. This code is written on the form load.

    rsCategory.MoveFirst
    Do Until rsCategory.EOF
        Combo1.AddItem rsCategory.Fields("CATEGORYNAME").Value
        rsCategory.MoveNext
    Loop

My problem is this. If I click the CATEGORYNAME in the combo box, I want the CATEGORYID will be written in the Text6.Text.

Ex: CATEGORYID CATEGORYNAME
      1 Analgesic
      2 Antipyritec

Hope u get my point.

Thank u

Rudner



wscheiman November 8th, 2004 11:19 AM

Hi,

Here's the easiest way to do it - it goes under the AfterUpdate() for the combo box:

Private Sub Combo1_AfterUpdate()
   If IsNull(Me.Combo1) Then
      Exit Sub
   End If

   Me.Text6 = Me.Combo1.Column(0)

End Sub

Attach this code within your event properties under the AfterUpdate for the combo box.

The first part will ensure that a NULL value will not be copied to the text box, in the remove event that a NULL value could be selected in the combo box. The second part will copy the data from the 1st column (Column 0) in your combo box to the text box. If you specified Me.Combo1.Column(1), the category name would have been copied to your textbox.

If you don't use the .Column property, it will copy the value from
whatever value is specified as your BoundColumn for the combo box. One thing to remember is that when you use the .Column property, the values start with 0, 1, 2, 3, etc. However, the Bound Column property starts with 1, 2, 3, etc.

Hope that helps.

:)

Rudner November 8th, 2004 10:44 PM

Thank wscheiman,

Yoy gave me the idea. I solved my problem but I'm not sure if this is the right solution. Please advise me..

Private Sub Combo1_Click()
    If IsNull(Combo1.Text) Then 'This is your code
      Exit Sub
    End If

    rsCategory.MoveFirst 'This is my code, please check
    Do Until rsCategory.EOF
        If UCase(Combo1.Text) = UCase(rsCategory.Fields("CATEGORYNAME").Value) Then
           Text6.Text = rsCategory.Fields("CATEGORYID").Value
        End If
        rsCategory.MoveNext
    Loop
End Sub


Thank you..

Rudner


wscheiman November 10th, 2004 01:35 PM

Hi Rudner,

From what it looks like (just to be sure), this is the basic sequence of events as I understand them:

 - The combo box Combo1 is populated using the .AddItem method
 - The user clicks on the combo box and selects one of the items
   in the list
 - Once the item has been selected in the combo box, the category
   ID from the combo box is to be copied into the text box named
   Text6.

I was assuming that you are doing this in MS-Access - if you're doing this in VB, please let me know. I DO want to get you the answer you're actually looking for. :D

Working with assumptions for now, if you're using Access, first of
all you can set the following properties for your Combo1 combo box:
 - RowSource = SELECT CATEGORYID, CATEGORYNAME FROM CATEGORY
 - ColumnCount = 2
 - BoundColumn = 1 (assuming you want to store the category ID in the combo box)

I was assuming MS Access when I sent you my initial reply.

Regardless if it's VB or Access, it appears that you want to copy the CATEGORYNAME from the combo box to the textbox only AFTER the user has made a selection. If that is the case, please keep in mind that the Click() event runs when a user clicks on the field. The reason that I indicated using the AfterUpdate event in Access is that it will not kick off until AFTER the user has updated the value from its original value. For example, if the combo box was blank or NULL, and then the user made a selection, the AfterUpdate event will automatically kick off and THAT is when you'd want to update the contents of the texbox. Therefore, you probably don't want to use the Click event.

The other part of the situation occurs as when you bring up an existing record (assuming you're using Access). If the Text6 textbox is an unbound field, you will want to display the CATEGORYNAME value if there is a CATEGORYID present in the Combo1 combo box. In that case, you would put the following code into your Form_Current() event:

Private Sub Form_Current()
   If Not IsNull(Me.Combo1) Then
      Me.Text6 = Me.Combo1.Column(0)
   End If

End Sub

Actually, I could go on an on (as I have), but if you can let me know if you're using VB or Access for the combo box, I can hopefully give you a more efficient answer. :)

Please let me know when you get a chance.

Thanks!

bfurman November 16th, 2004 02:57 PM


- RowSource = SELECT CATEGORYID, CATEGORYNAME FROM CATEGORY
 - ColumnCount = 2
 - BoundColumn = 1 (assuming you want to store the category ID in the combo box)
I agree with this code just in Combo Box Properties change columnwidth 0 for 1st colunmn
ex.:0";1.5"
bfurman



All times are GMT -4. The time now is 12:10 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.