Linking Combo Box & Textbox

[crow]

I am trying to get a textbox to display a certain value when a choice is made in a combo box beside it. I have created a table with the two values wanted but I can't seem to get it to work. Any help?

 

[John Big Booty]

I am trying to get a textbox to display a certain value when a choice is made in a combo box beside it. I have created a table with the two values wanted but I can't seem to get it to work. Any help?

The data you want to show in your TextBox must be selected by the query that populates your ComboBox (but not necessarily displayed). Then in the after update event put the following code;

Me.TextBox=ComboBox.Column(x)

where x is the column number that holds the data you wish to display in the text box. Bear in mind that the first column of the ComboBox is numbered as Zero. The TextBox will also need to be unbound.

 

[John Big Booty]

You might also want to think about adding a refresh statement to the code if you want the data to become visible as soon as the ComboBox has lost focus.

 

[crow]

Linking Combo & Textbox

I am really thick tonight. Where do I actually put this code in the textbox properties? My textbox is called PricesDisplay My Combo box is called Sizes. There are several values in the combo box which need to line up with several vales in my table and be displayed in the textbox depending on size chosen to disply proper price. HELP!

Me.PricesDisplay=Sizes.Column(1)

 

[John Big Booty]

Put your code in the after update event of the ComboBox.

Bear in mind that Column(0) of Your ComboBox is probably the value that is stored in the table your ComboBox refrences and Column(1) is probably the data that is displayed in the ComboBox ( I guess the description of your item; Size?) You will need to make sure that the query that populates the Combobox is picking up the Price (?) this will be Column(2) if you don't want that shown in the ComboBox drop down format your Column Widths to something like this 0cm;2.62cm;0cm

 

[boblarson]

You might also want to think about adding a refresh statement to the code if you want the data to become visible as soon as the ComboBox has lost focus.

If put in the afterupdate event of the combo box, it shouldn't be necessary to use a refresh. I have yet needed to use it and I use that method of displaying multiple items from a combo into text boxes all of the time.

 

[John Big Booty]

If put in the afterupdate event of the combo box, it shouldn't be necessary to use a refresh. I have yet needed to use it and I use that method of displaying multiple items from a combo into text boxes all of the time.

You're correct Bob, I'm not quite sure why I thought the refresh was needed

 

[boblarson]

You're correct Bob, I'm not quite sure why I thought the refresh was needed

Because it was MONDAY

 

[John Big Booty]

Because it was MONDAY

I'm not sure if I glad I can't use that as an excuse or not it's TUESDAY here

 

[MamadouNdaw]

I hate to bump this old topic but I'm doing the same thing, but it is not cooperating. I have made it so the proper price comes up when I enter a number in the combo box, but it changes all the prices in the list. I included a picture to make it clearer....

As you can see, the total column also decided not to work. I've had this working to an extent with the following vb code:

Private Sub ProduitIDBox_AfterUpdate()
If Not IsNull(Me![ProduitID]) Then
Me![Quantité] = 1
Me.[PrixUnitaire] = GetPrixUnitaireProduit(Me![ProduitID])
Me![Remise] = 0
Me![StatutID] = Payé_StatutDétailVente
End If
End Sub

and....

Function GetPrixUnitaireProduit(lProduitID) As Currency
GetPrixUnitaireProduit = DLookup("[PrixUnitaire]", "tblListeProduit", "[ProduitID] = '" & lProduitID & "'")
End Function



But, with this code, it only seems to pick the first price in the list... so there are no errors, but it's always 200. Any advice?

 

[boblarson]

from your screenshot it looks like Produit and Service controls are not BOUND to a field. Are you sure that they are bound?

 

[ajetrumpet]

Another note:

Have you seen this or this???

 

[MamadouNdaw]

Yes, the two ID combo boxes do have controls.

That template that you posted does exactly what I want, but I can't seem to get it working with my database. This is the adjusted code:

Private Sub ServiceIDBox_AfterUpdate()
Me.PrixUnitaireBox.Value = DLookup("[PrixUnitaire]", "tblListeService", "[ServiceID] = Forms![frmDétailVente]!ServiceIDBox") 'Populate the Item's Price.
End Sub

Here are a couple more pictures of my table setup 'tblListeService' and my form, frmDétailVente.

 

[ajetrumpet]

Function GetPrixUnitaireProduit(lProduitID) As Currency
GetPrixUnitaireProduit = DLookup("[PrixUnitaire]", "tblListeProduit", "[ProduitID] = '" & lProduitID & "'")
End Function



But, with this code, it only seems to pick the first price in the list... so there are no errors, but it's always 200. Any advice?

This is my first piece of advice:

*For simple lookups like this, don't require the program to move between a Private Sub and another function, unless it's absolutely necessary, which I wouldn't think, in any case, is necessary. For one thing, it'll start to confuse you. And another, it really shouldn't be necessary. And third, it deters people away from helping you! (More to come...)

 

[ajetrumpet]

Private Sub ServiceIDBox_AfterUpdate()
Me.PrixUnitaireBox.Value = DLookup("[PrixUnitaire]", "tblListeService", "[ServiceID] = Forms![frmDétailVente]!ServiceIDBox") 'Populate the Item's Price.
End Sub

I'm not really sure about this really. The only other things I think I would say are:

* Try changing the recordsource of the form to the actual table name, instead of an SQL statement.

* If you have a multi-column combo box for the "Service" field in the form, try referencing the correct column instead of the entire control in your DLookup function...

Private Sub ServiceIDBox_AfterUpdate()

   Me.PrixUnitaireBox.Value = DLookup("[PrixUnitaire]", "tblListeService", 
      "[ServiceID] = Forms![frmDétailVente]!ServiceIDBox.[B][U]Column(1)[/U][/B]")

End Sub

It looks like, from the pictures, it has more than one column in it.

As for the price populating all of the records with the same number, could this be a consequence of a continuous form I wonder?? Just a thought...

 

[MamadouNdaw]

Ok, new development. So this form where I'm attempting to lookup the price happens to be a subform. You knew that from the picture, so...

When I open the sub form independently in form view, it actually works perfectly with the old code. But, when I open the parent form in form view, and then try to use the subform combo box, i get the following error:

-----------------------------------------------------------------------------------
Runtime error 2450

Microsoft office cannot find the form 'frmDétailVenteSubForm' referred to in a macro, expression or query.
-----------------------------------------------------------------------------------

So, obviously it has something to do with the subform, but why? So, above, I made an error in my code with the 'frmDétailVente' reference as technically it is the subform that contains the ServiceID object.

By the way, adding the column(1) argument resulted in a syntax error. But, yeah it does have more than one column. Anyway, why would it work differently as an independently opened form versus as a subform?

Thanks for the suggestions so far

 

[boblarson]

If on a subform it would be:

Forms!YourMainFormNameHere.YourSubformCONTAINERnameHere.Form.ServiceIDBox.Column(1)")

When changing the names to your actual form and subform, then it needs to have the container (control which houses the subform on the main form) name referenced and not the subform name. If they are named the same that's okay, but if they aren't you use the subform container control name instead of the subform name.

 

[ajetrumpet]

Yeah, that was not very cool of me. If it is indeed a subform, then you do have to reference it from outside itself. Like this (and like Bob has said)...

Private Sub ServiceIDBox_AfterUpdate()

   Me.PrixUnitaireBox.Value = DLookup("[PrixUnitaire]", "tblListeService", 
      "[ServiceID] = Forms![frmDétailVente]!SubFormContainerName.Form!ServiceIDBox.[B][U]Column(1)[/U][/B]")

End Sub

When I open the sub form independently in form view, it actually works perfectly with the old code.

That's because the code, the way you wrote it, is simplified to reference a single object.

So, obviously it has something to do with the subform, but why?

Ask Microsoft, I have no idea. It's almost like evaluating an anomaly. Objects inside of objects / code inside of code / building blocks on top of building blocks.

Anyway, why would it work differently as an independently opened form versus as a subform?

Subforms are really quite complicated, I don't think people realize how complex they really are, until they run into a problem with them. But anyway, as far as I know, there is only one type of subform in this program, and that is the type that you can insert into a form from the design toolbox (you can insert these things from the toolbox, or you can actually drag and drop objects to create them). And, it is just a control, nothing else (just like a text box, or combo box). The only difference they possess is their ability to hold a lot more data than any other control. And, they can actually represent other objects too (like tables and queries). That's probably part of the problem with them.

Anyway, to shorten this story, there is one thing I always tell myself: Subforms are just forms, nothing else. They are only different when they are spun into the world of "nesting". A subform on its own, is just a form, with a name, and its own little place in the Forms! collection.

Other important things about them
*They are database objects and form controls, at the same time.
*When someone says "Subform container", they are talking about the form control part of it, not the object you see in the database window.
*If they represent other objects in the database, any manipulation of them reflects back to their source, just like editing a query results in edits to its source (the table that was queried).

 

[boblarson]

Anyway, why would it work differently as an independently opened form versus as a subform?

A subform is displayed on a main form by means of a special control (which I normally call its CONTAINER). It is different in that when a form is used on another form as a subform, it then actually essentially becomes a part of the main form's CONTROLS instead of a form. When a subform is loaded it isn't even counted by Access as being in the forms collection, but instead as a control.

That is why it has different syntax. So, when on the mainform you have a few properties of the container that you can reference but if you are wanting any of the form's properties, you need to tell it so by prefacing it with .Form. so that it knows you want a property of the embedded form and not of the container itself.

I hope that helps clear it up. For more info you can check out my website Quick Tutorial here:
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
and a reference here:
http://www.mvps.org/access/forms/frm0031.htm

 

[ajetrumpet]

Well Bob,

I hope we got that point across. I'll call my publisher about our posts. Maybe we can make a buck here, although the preface can't be as short as ".Form", so you'll have to come up with something better.