Query from Combo Box must display records in Text Boxes 2

[frummel]

MS Access 2002:

I have a table called ClientData.
The table contains records: ClientName, ClientAddress, ClientAccountNumber.
In a Combo Box I select an entry from ClientName records.
What I want is: When selecting a ClientName, the ClientAddress should appear in a Text Box, and the same for the ClientAccountNumber.

How can I achieve this?

 

[Edski]

Hi frummel,
What you want to do is quite easy but first I need to pick you up on your terminology.

The table contains fields: ClientName, ClientAddress, ClientAccountNumber

In a Combo Box I select an entry from ClientName.

I'm assuming that your ComboBox is in a form NOT bound to your ClientData table. So just put this (assuming that the ComboBox field is called cboClientName behind the AfterUpdate Event for the ComboBox:

Sub cboClientName AfterUpdate()
  Me.txtClientAddress = Me.cboClientName.Column(1)
  Me.txtClientAccountNumber = Me.cboClientName.Column(2)
End Sub

Hope that helps.

 

[frummel]

Thanks for the info!

The table the info is in is called Klantgegevens and the fields look like below:

KlantNummer KlantNaam KlantAdres KlantPostcode KlantWoonplaats KlantRekeningNummer

The Form is on a Tab called KlantGegevens and contains a Combo Box called: cboKlantNaam
5 Text Boxes called: txtKlantNummer, txtKlantAdres, txtKlantPostCode, txtKlantWoonplaats, txtKlantRekeningNummer

I tried using your Event, after editing it to my needs:

Sub cboKlantNaam AfterUpdate()
  KlantGegevens.txtKlantNummer = KlantGegevens.cboKlantNaam.Column(1)
  KlantGegevens.txtKlantAdres = KlantGegevens.cboKlantNaam.Column(3)
  KlantGegevens.txtKlantPostcode = KlantGegevens.cboKlantNaam.Column(4)
  KlantGegevens.txtKlantWoonplaats = KlantGegevens.cboKlantNaam.Column(5)
  KlantGegevens.txtKlantRekeningNummer = KlantGegevens.cboKlantNaam.Column(6)
End Sub

Now I get an error message while selecting data from the Combo Box:

The expression After Update your entered as the event property setting produced the follwoing error:Expected: end of statement.

So I tried editing the Event to:

Sub cboKlantNaam_AfterUpdate()
  Klantgegevens.txtKlantNummer = Klantgegevens.cboKlantNaam.Column(1)
  Klantgegevens.txtKlantAdres = Klantgegevens.cboKlantNaam.Column(3)
  Klantgegevens.txtKlantPostcode = Klantgegevens.cboKlantNaam.Column(4)
  Klantgegevens.txtKlantWoonplaats = Klantgegevens.cboKlantNaam.Column(5)
  Klantgegevens.txtKlantRekeningNummer = Klantgegevens.cboKlantNaam.Column(6)
End Sub

and now the error message I get is:

Compile Error (Method or data member not found (Error 461)), and Visual Basic Editor opens and selects: Klantgegevens.txtKlantNummer = Klantgegevens.cboKlantNaam.Column(1)

What to do now?

 

[Edski]

Change all the Klantgegevens back to Me.

 

[t5amec]

I have a similar query...

I have two combo boxes(CboOrders & CboDescription) and I would like the information(Requisition) to appear on a text box(txtRequisition).

The two combo boxes are linked to seperate tables but the information I want to appear in txtRequisition is in the same table that is linked to CboDescription.

How can i make it so that when a revelant drop down is clicked in CboDescription, that the information in txtRequisition would automatically appear?

Make Sense? I hope so (-:

 

[Edski]

t5amec,
Unless I'm misunderstanding your question, the answer is on this page. As long as one of the fields selected in the combo box is the one you want to put in txtRequisition, there should be no problem. What have you tried already?

 

[t5amec]

Well I have done as you stated in your reply above but what happens is that instead of the Requisition data going into txtRequisition, the Description info that I select from the combo box is placed in txtRequisition instead.

Where am I going wrong?!

Make Sense? I hope so (-:

 

[Edski]

t5amec,
Post your AfterUpdate code. It's probably the Column index that you have wrong. Note that it starts at 0.

 

[t5amec]

what do you mean by it starts at 0?

Private Sub CboDescription_AfterUpdate()
  Me.txtRequisition = Me.CboDescription.Column(1)
End Sub

if i were to change it to 0 i would get the OrderId value
if i were to change it to 2 i would get the OrderNumberID value
and if i were to change it to 3 or greater i would get no data.

Make Sense? I hope so (-:

 

[Edski]

Why have you got the Orders info in the Descriptions combobox? Can you post the Row Source for the Combo Box please?

The index (of the combobox list) starts at 0, so 0 = OrderID, 1 = ???, 2 = OrderNumberID.

 

[frummel]

Hi there t5amec, thanks for your last advice to me. Everything seems to work now, except for one thing:

The data in Combo Box cboKlantNaam came from a Query.
When selecting from the Combo Box list, all fields of table KlantGegevens appear. After selecting one item, only KlantNaam appears.
How can I make the unwanted fields disappear from the list, without losing these fields needed for the AfterUpdate() event?

Hope I made clear what I mean...

 

[frummel]

Sorry Edski[/] all credits were supposed to go to you!

 

[t5amec]

CboOrders Source

SELECT [TblOrderEntry].[OrderNumberID], [TblOrderEntry].[LngOrderNumber] 
FROM TblOrderEntry 
ORDER BY [TblOrderEntry].[LngOrderNumber];

CboDescription Source

SELECT [TblOrderInformation].[OrderId], [TblOrderInformation].[Description], [TblOrderInformation].[OrderNumberID] 
FROM TblOrderInformation 
WHERE ((([TblOrderInformation].[OrderNumberID])=[Forms]![Form2]![CboOrders])) 
ORDER BY [TblOrderInformation].[Description];

The combo boxes have a linked field making me able to select one item of data enabling the other combo box for me to select another piece of data.


Make Sense? I hope so (-:

 

[Edski]

frummel,
I accept stars as well as credits! Just kidding...it's just that I've helped about 10 people today and haven't got one lousy star.
Now, if you look at the Properties for your combobox, you will see 3 called Column Count, Column Heads and Column Widths. Play around with them until you get what you want. You can set the widths to 0 if you don't want them to appear in the list. They will always be available fo rthe FaterUpdate as long as they are included in the Row Source.
Hope that's what you meant.

 

[Edski]

t5amec,
I thought you were hiding information from me!
So where's the Requisition data that you want to retrieve? And which table is it in? I think you said it was in the same table as tblOrderInformation?? If so, then you need to include the Requisition field in the Row Source for the cboDescription Combo Box and then reference it's index in the AfterUpdate Event. Capisce?

 

[frummel]

There you go Edski, one star for your posts here.
I have one question left:

I managed to set the Column Width, and now the unwanted info doesn't appear anymore.
However, the horizontal scrollingbar still appears.
How can I disable the scrolling bar?

 

[t5amec]

Edski, I understand what you mean but when I add the info to the code it doesn't seem to be making a difference at all and still showing the same description. I have tried altering the numbers on the code but once again, if i type 3 in then its blank.

Am i doing it right?!

SELECT [TblOrderInformation].[OrderId], [TblOrderInformation].[Description], [TblOrderInformation].[OrderNumberID] , [TblOrderInformation].[Requisition]
FROM TblOrderInformation 
WHERE ((([TblOrderInformation].[OrderNumberID])=[Forms]![Form2]![CboOrders])) 
ORDER BY [TblOrderInformation].[Description];

(page coding)

Private Sub CboOrders_AfterUpdate()
    CboOrders.Requery
End Sub

Private Sub CboDescription_GotFocus()
    If Len(Trim(Nz(CboOrders, "") & "")) = 0 Then
        MsgBox "Please Specify an Order First"
        CboDescription.SetFocus
    Else
        CboDescription.Requery
    End If
End Sub

Private Sub CboDescription_AfterUpdate()
  Me.txtRequisition = Me.CboDescription.Column(1)
End Sub

Make Sense? I hope so (-:

 

[Edski]

frummel,
Is this still related to the Combo Boxes?
Do you eman the scroll bars for the form? If so, then you can disable one or both very simply. It's in the Properties for the form. Can you see it?

t5amec,
You need to change this bit of code:

Private Sub CboOrders_AfterUpdate()
    CboDescription.Requery
End Sub

and then you wouldn't need the Else condition in the second bit of code above. Or just delete the first bit of code altogether.

Now then, the index is definitely 3, so I don't know why it's not giving you the description. Di you change the column count to 4?

 

[t5amec]

Edski, cheers dude. It now works. Took a while but I got there in the end.

:-D

 

[Edski]

Hi frummel,
It just hit me that you meant the horizontal scroll bar on the combo box, not the form. The trick is to size the control in the form to match the longest option in the Row Source (plus a tad extra for the drop down arrow) and then make the Column Widths of the control exactly the same as the List Width. If you only want to see one of the selected fields from the Row Source, set the Column Widths of the rest to 0 (separated by semi-colons (.
Was that what you meant?