Contact Center DB

[Dal Jeanis]

As long as the Post/Country/Network information is stable, it shouldn't be too much of a risk to have the Country Name duplicated on the Post record, and the Network Name duplicated on the Country record and the Post record.

On the call/enquiry record, however, you should only be storing the PostID.



As I said, you can have an UNBOUND country selector combo box, that limits the Post selection in a BOUND Post Selector combo box, which is bound to the PostID but displays the Post Name.

The effect is similar to having the lookup field, but it isn't a characteristic of the underlying table - if you look at the table, you'll just see the PostID.

[tduffield89]

Ok I have made the changes to my tables but I am struggling with the combo box, can you paste the code please.

[Dal Jeanis]

Sure. Can you upload a screenshot and give the names of the combo boxes?

[tduffield89]

CountryCombo
PostCombo

CountryCombo is unbound as suggested and PostCombo is bound to the PostID but lets the user select the Post. What I need is for the CountryCombo to limit the selections in the PostCombo based on the selection made in the CountryCombo.

[Dal Jeanis]

Try these settings for the combo boxes

CountryCombo
   Row Source = "SELECT CT.[Country ID], CT.Country, CT.[Network ID] FROM [Country Table] AS TC ORDER BY TC.Country;"
   Bound Column = 1     (the CountryID)
   Column Width = 0";1.5";0"  ' The middle width should be whatever shows the Country name well

PostCombo
   Row Source = PostCombo.RowSource = "SELECT TP.[Posts ID], TP.Post FROM [Post Table] as TP ORDER BY TP.Post;"
   Bound Column = 1     (the PostID)
   Column Width = 0";1.5"     ' The second width should be whatever shows the Post name well

and this code in the after update of CountryCombo

CountryCombo_AfterUpdate
   If Me.CountryCombo.Listindex = -1 Then
      Me.PostCombo.RowSource = "SELECT TP.[Posts ID], TP.Post FROM [Post Table] as TP ORDER BY TP.Post;"
   
   Else
      Me.PostCombo.RowSource = "SELECT TP.[Posts ID], TP.Post FROM [Post Table] as TP WHERE TP.[Country ID] = " & CountryCombo.Value & " ORDER BY TP.Post;"
'     Me.PostCombo = Me.PostCombo.ItemData(0)  ' Don't know if this line is useful or not
   End iF

[tduffield89]

I have copied it into the SQL but can't get it to work
What does the CT, TC, TP stand for

[Dal Jeanis]

That's the problem with aircode - no compiler to catch typos.

Change to

FROM [Country Table] AS CT

The word AS introduces an alias for the immediately previous token. The alias can/must be used everywhere else in the query that the token (tablename or fieldname) previously could have been used.

There's a slight exception in that the GROUP and ORDER clauses can't use an alias that's set in the SELECT list, because GROUPing and sorting into ORDER occurs before the aliasing occurs in the SELECT.

[tduffield89]

Ok CountryCombo works fine but it is not impacting the sections in PostCombo.

CountryCombo
SELECT tblCountries.CountryID AS Expr1, tblCountries.CountryName AS Expr2, tblCountries.NetworkID AS Expr3
FROM tblCountries
ORDER BY tblCountries.CountryName;

After update event
CountryCombo_AfterUpdate
If Me.CountryCombo.ListIndex = -1 Then
Me.PostCombo.RowSource = "SELECT tblPosts.[PostID], tblPosts.PostName FROM [tblPosts] ORDER BY tblPosts.PostName;"

Else
Me.PostCombo.RowSource = "SELECT tblPosts.[PostID], tblPosts.PostName FROM [tblPosts] WHERE tblPosts.[PostCountryID] = " & CountryCombo.Value & " ORDER BY tblPosts.PostName;"
End If

PostCombo


SELECT tblPosts.PostsID, tblPosts.PostName
FROM tblPosts
ORDER BY tblPosts.PostName;

[Dal Jeanis]

Looks like the default property (the value of the bound column) is a better choice. Try this:

Else
    Me.PostCombo.RowSource = "SELECT tblPosts.[PostID], tblPosts.PostName FROM [tblPosts] WHERE tblPosts.[PostCountryID] = " & CountryCombo & " ORDER BY tblPosts.PostName;"
End If

If that doesn't fix the issue, then set a break on the first executable line of the afterupdate event, and see what's happening. What's the value of each of the items on those lines when they are executed?

PS you don't need the "AS EXPRx" portions of the CountryCombo SQL.