populating text boxes from change to a Combo box 1

I have a form (MAC_addr_frm) with a combo box (DVcode)
which is populated by the table Device_Code_tbl.ID

I am trying to populate the following 3 text boxes on this form
from what device code is selected by the DVcode combo box:

1). pNum - to be populated from Device_Code_tbl.Asm_Num

2). Descr - to be populated from Device_Code_tbl.Descr

3). Laddr - to be populated from MAC_Addr_tbl.Lastaddr


So I created the following query (Mac_addr_qry):

SELECT Device_Code_tbl.Asm_Num, MAC_Addr_tbl.Lastaddr, Device_Code_tbl.Descr
FROM Device_Code_tbl INNER JOIN MAC_Addr_tbl ON Device_Code_tbl.ID = MAC_Addr_tbl.ID
WHERE (((Device_Code_tbl.ID)=[forms]![MAC_addr_frm]![DVcode]));

Then in an attempt to populate the 3 text boxes, I set the control source
for these to the following:

pnum =[Mac_Addr_qry]![Asm_Num]

Descr =[Mac_Addr_qry]![Descr]

Laddr =[Mac_Addr_qry]![Lastaddr]

In the OnChange event for the combo box DVcode, I put the following code:

Private Sub DVcode_Change()
DoCmd.OpenQuery "Mac_Addr_qry", acViewNormal, acReadOnly
Repaint
End Sub

So when I change the value in the DVcode combo box, the 3 text boxes
do not get updated and the Mac_Addr_qry results pop up...

What am I doing wrong and how can I correct this so that the text boxes
get updated and the query results does not pop up...

thanks

 

[PHV]

Use this RowSource for the combo DVcode:
SELECT D.ID,D.Asm_Num,M.Lastaddr,D.Descr FROM Device_Code_tbl D INNER JOIN MAC_Addr_tbl M ON D.ID=M.ID ORDER BY 1

And then the ControlSources,
for pnum:
=[DVcode].Column(1)
for Descr:
=[DVcode].Column(3)
for Laddr:
=[DVcode].Column(2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Works Great PHV

Thanks again for your help!

 

[robojeff]

My first reply was a quick thank you but as I looked at this, I wish to understand it better so that I can learn how to use it elsewhere.

This does work really good and hope you don't mind a couple follow up questions so that I can understand it a little bit better...

You suggested:
SELECT D.ID,D.Asm_Num,M.Lastaddr,D.Descr FROM Device_Code_tbl D INNER JOIN MAC_Addr_tbl M ON D.ID=M.ID ORDER BY 1

1. In this Select statement, you use D.ID & D.Asm_Num; is D. an actual table that you are creating or an alias, or why do you use this instead of just using Device_Code_tbl.Asm_Num?

2. I am guessing that if Lastaddr was in Device_Code_tbl instead of MAC_Addr_tbl then there would be no need for the INNER JOIN, but if this was the case how would the Select statement appear?

3. What does the [DVcode].Column(?) relate to and how are these indexed to the control?

Thanks again!

 

[PHV]

1. an alias
2. I don't know your DB scema
3. With the new RowSource the combobox has 4 columns


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886