Using INNER JOIN to display the names instead of the IDs

[kcdoell]

Hello:

I have a form that has 3 combo boxes.

CboDivision
cboWrkReg
cboCreditReg

In the Field Properties, of those combo boxes I used the “Default Value” to populate selections that were made via another form I created.

[Forms]![DataEntry]![CboDivision]
[Forms]![DataEntry]![cboWrkReg]
[Forms]![DataEntry]![cboCreditReg]

My problem is that the cboWrkReg & cboCreditReg are displaying the ID number instead of the Name that it is tied to it. Because the cboCreditReg needs to be a cascading combo box I put the following code into the Load Event of the form:

'Private Sub Form_Load()
'When the Working Region is selected, the appropriate Credit Region list will
'display in the drop down list of CboCreditReg
 
  With Me![cboCreditReg]
   If IsNull(Me!cboWrkReg) Then
      .RowSource = ""
    Else
   .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
         "tblCreditRegion.CreditRegionName " & _
         "FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
         "ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
         "WHERE [WrkRegIDFK]=" & Me!cboWrkReg
 
    End If
    Call .Requery
  End With
End Sub
 

Now the cboCreditReg displays the name but I still have my cboWrkReg displaying the ID.

Is there a way that I can get both my cboWrkReg and cboCreditReg to display the names by modifying slightly my code??

Any assistance would be greatly appreciated.

Thanks,

Keith.

[janders468]

What is the rowsource for cboWrkReg? I could be wrong on but it appears that you're adding too much in your select statement. If you want only the region name then only use that in the select statement for instance:
SELECT DISTINCT tblCreditRegion.CreditRegID #
FROM TblLocationsMM INNER JOIN tblCreditRegion
ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
WHERE [WrkRegIDFK]= & Me!cboWrkReg
The ID field does not need to be included in the select statement. That is just a guess though, the way you have presented the example it appears as though cboWrkReg shouldn't have anything and the cboCreditReg should show the ID. If you want all of your controls to be bound to the underlying query set your form's record source property to the query that contains the relevant data and then set the control's control source property to the relevant fields. If I've missed the point of it please clarify what you need, and I'll be more than happy to help.

[kcdoell]

Hello:

I was playing with this and went a different way. I set the rowsource of my cbowrkreg to the following:

SELECT tblWrkRegion.WrkRegID, tblWrkRegion.WrkRegionName 
FROM tblWrkRegion 
WHERE (((tblWrkRegion.WrkRegID)=forms.dataentry.cbowrkreg)) 
ORDER BY tblWrkRegion.WrkRegionName;
 

Column count = 2
Column width = 0";1"

And it worked. It was easier than I thought......


thanks for the insight.


Keith.

[janders468]

That will work, but forcing a combobox to have two columns and hiding the first seems a bit of an unnecessary work around, and will make the properties of the control unpredictable from a maintenance standpoint because the control appears to be something that it is not (a single column combobox). Just as philosophical/stylistic development point I wouldn't do that. I would only put the region column in your select statement and use only one column in the combobox which will then accurately reflect what you want. Obviously feel free to do it however you want, just giving some unsolicited advice ;-).

[kcdoell]

That will work, but forcing a combobox to have two columns and hiding the first seems a bit of ...................... ;-).

Yes, but my other queries are looking for the ID number on that form and not the Name. That is why I have taken that path.. I am not an expert on design, so I tend to fall back on what I have learned and know works for me. I am thinking that I would have to double back on my other functionality if I was to change things and that would cost me time.....

Thanks for the help... :-)

Keith.