DLookUP Problem

[kcdoell]

Hello:

I am trying to do a Dlookup expression but it is not working. Basically I have 3 cascading combo box, with the first that has the following code set in the afterupdate event:

'When the Division is selected, the appropriate Working Region list will
'display in the drop down list of CboWrkReg
 
    With Me![cboWrkReg]
      If IsNull(Me!cboDivision) Then
        .RowSource = ""
         Else
            .RowSource = "SELECT DISTINCT [WrkRegID] " & _
                   "FROM TblJntALL " & _
                   "WHERE [DivisionID]=" & Me!cboDivision
 
    End If
     Call .Requery
End With
 
End Sub
 
 

This works but it is showing the WrkRegID (Num) instead of the WrkRegionName (Text). My table " TblJntALL " is joining my many to many relationships between Division Name and Working Region Name.

How would I get it to display the WrkRegionName if the WrkRegionName is ONLY in my table “tblWrkReg”???

Any help would be great.

Thanks,

Keith.

[FishVal]

Hi, Keith.

You need to use [TblJntALL] joined with [tblWrkReg].

.RowSource = "SELECT DISTINCT tblWrkReg.WrkRegID, " &_
                   "tblWrkReg.WrkRegionName " & _
                   "FROM TblJntALL INNER JOIN tblWrkReg " & _
                   "ON TblJntALL.WrkRegID = tblWrkReg.WrkRegID " & _
                   "WHERE [DivisionID]=" & Me!cboDivision
 

Regards,
Fish

[kcdoell]

Hello:

I posted the followiing:

 
Private Sub CboDivision_AfterUpdate()
 
'When the Division is selected, the appropriate Working Region list will
'display in the drop down list of CboWrkReg
 
    With Me![cboWrkReg]
      If IsNull(Me!cboDivision) Then
        .RowSource = ""
         Else
         .RowSource = "SELECT DISTINCT tblWrkRegion.WrkRegID, " & _
         "tblWrkRegion.WrkRegionName " & _
         "FROM TblJntALL INNER JOIN tblWrkRegion " & _
         "ON TblJntALL.WrkRegID = tblWrkRegion.WrkRegID " & _
         "WHERE [DivisionID]=" & Me!cboDivision
    End If
     Call .Requery
End With
 
End Sub
 
 

But the ID number is still displaying. Any ideas??

Thanks,

Keith

[FishVal]

Hi, Keith.

I guess the table field bound to [cboWrkReg] contains values from tblWrkRegion.WrkRegID. Am I right?
If so, then you need a multicolumn combobox.
In design view set the values of [cboWrkReg] properties to the following:
ColumnCount = 2
ColumnWidths = 0;<any non-zero value here>
BoundColumn = 1

Kind regards,
Fish

P.S. Why do you use DISTINCT predicate? Are there duplicated joints in the table?

[kcdoell]

Fish:

Thanks for getting back to me, I needed to step away to catch the train. Now I am back looking at this problem again. In my Join table I do have multiple entries due to the relationships. That is why I used the select distinct.

I am going to look at the bound properties now.

Thanks,

Keith

[kcdoell]

Hi, Keith.

I guess the table field bound to [cboWrkReg] contains values from tblWrkRegion.WrkRegID. Am I right?
If so, then you need a multicolumn combobox.
In design view set the values of [cboWrkReg] properties to the following:
ColumnCount = 2
ColumnWidths = 0;<any non-zero value here>
BoundColumn = 1

Kind regards,
Fish

P.S. Why do you use DISTINCT predicate? Are there duplicated joints in the table?

Fish:

You were correct! I set the columncount=2 and boom I saw the names. I never join tables like you did before.

Thanks a lot.......

I actually had this working but needed to change my tables since I discovered that 3 of my tables had many to many relationships. When I changed that, then my cascading problems surfaced again. In the past, I never had to create a DB with lots of relationships let alone many to many ones.

Thanks again,

Keith

[FishVal]

Hi, Keith.

I would like to pay your attention to what that sounds like a design problem (or maybe not.

..... In my Join table I do have multiple entries due to the relationships. That is why I used the select distinct......

As long as the table implementing many-to-many relationship between two others contains only unique combinations of FKs from that two tables, filtering by one from these FKs will return recordset containing no duplicates of the other FK.
If it is not so, then it may cause many unpredictable faults in future.

To prevent this I'm strongly recommending you to set multifield index ensuring the table to contain only unique combinations of FKs.

Regards,
Fish

[kcdoell]

Hi, Keith.

I would like to pay your attention to what that sounds like a design problem (or maybe not.....................................
To prevent this I'm strongly recommending you to set multifield index ensuring the table to contain only unique combinations of FKs.

Regards,
Fish

I am new at creating many to many relationships but this DB has put me in that situation. I believe that I have my tables set correctly. I will research multifield indexing but the link did not help me to understand it entirely............

The reality is that I have to move this project forward and I think the relationships are tight but me not being an expert, that might be a bold statement...

[FishVal]

I am new at creating many to many relationships but this DB has put me in that situation. I believe that I have my tables set correctly. I will research multifield indexing but the link did not help me to understand it entirely............

The reality is that I have to move this project forward and I think the relationships are tight but me not being an expert, that might be a bold statement...

Index is just a database engine mechanism to "hold inventory" on a table.
One of it aspects useful in your case that it can prevent information duplicating in table.

In table design view

• right-click to enter context menu
• choose [Indexes...]
• add to the first column new index name whatever you like
• add to the second column field names expected to be the parts of the index (both FKs)
• set Unique to Yes
• voile, the database engine itself will prevent FK combination duplication, sure you need to remove existing duplicates first

Regards,
Fish