By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

Using INNER JOIN to display the names instead of the IDs

kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1. 'Private Sub Form_Load()
  2. 'When the Working Region is selected, the appropriate Credit Region list will
  3. 'display in the drop down list of CboCreditReg
  4.  
  5.   With Me![cboCreditReg]
  6.    If IsNull(Me!cboWrkReg) Then
  7.       .RowSource = ""
  8.     Else
  9.    .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
  10.          "tblCreditRegion.CreditRegionName " & _
  11.          "FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
  12.          "ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
  13.          "WHERE [WrkRegIDFK]=" & Me!cboWrkReg
  14.  
  15.     End If
  16.     Call .Requery
  17.   End With
  18. End Sub
  19.  
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.
Mar 18 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 112
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.
Mar 19 '08 #2

kcdoell
100+
P: 230
Hello:

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblWrkRegion.WrkRegID, tblWrkRegion.WrkRegionName 
  2. FROM tblWrkRegion 
  3. WHERE (((tblWrkRegion.WrkRegID)=forms.dataentry.cbowrkreg)) 
  4. ORDER BY tblWrkRegion.WrkRegionName;
  5.  
Column count = 2
Column width = 0";1"

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


thanks for the insight.


Keith.
Mar 19 '08 #3

Expert 100+
P: 112
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 ;-).
Mar 19 '08 #4

kcdoell
100+
P: 230
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.
Mar 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.