Data Goes Invisible In A Synchronized Combo Box On A Continuous Form

[iui120887]

Hello guys,

I will try my best to explain the problem that would make sense...

I have a simple synchronized combo box which is associated with Roles and Statuses

The functionality of the synchronized combo box is correct, it's just that if I put the form into Continuous Form, and when you look at the form, some data from the Status is invisible (not deleted because if you look at Table1, it still stores the data). You have to click on to some Status to actually make those invisible statuses show up, and when some of the invisible statuses show up, the previous statuses that was showing up suddenly becomes invisible... I hope I am making sense here, I'm sure that most of you think that I might be going crazy, but it is true...

I will still post the table structure and codes that I have: (I am using a test db so some field and table names might not make sense or not named correctly)


Table1 -- Where Form1 is bound to

rr -- Number (Role)
ss -- Number (Status)

tblRole
RoleID -- PK
Role

tblStatusChange
StatusChangeID -- PK
Status

tblRoleStatus
RoleID -- FK to tblRole
StatusChangeID -- FK to tblStatusChange

Query4

SELECT
 tblRoleStatus.StatusChangeID,
 tblStatusChange.Status,
 tblRoles.RoleID
FROM
 (tblRoles INNER JOIN tblRoleStatus ON tblRoles.RoleID = tblRoleStatus.RoleID) INNER JOIN tblStatusChange ON tblRoleStatus.StatusChangeID = tblStatusChange.StatusChangeID
WHERE
 (((tblRoles.RoleID)=[Forms]![Form1].[Combo0]));

Form1
Format -- Continuous Form
Record Source -- Table1
On Current --

Private Sub Form_Current()
If Len(Trim(Nz(Combo0, "") & "")) = 0 Then
MsgBox "Please Enter a Role"
Combo0.SetFocus
Else
Combo2.Requery
End If
End Sub

Field Name: Role ComboBox Name: Combo0
Control Source: rr
Row Source:

SELECT
 tblRoles.RoleID,
 tblRoles.Role
FROM
 tblRoles;

After Update Event:

Combo2.Requery

Field Name: Status ComboBox Name: Combo2
Control Source: ss
Row Source:

SELECT
 Query4.StatusChangeID,
 Query4.Status,
 Query4.RoleID
FROM
 Query4
WHERE
 (((Query4.RoleID)=[Forms]![Form1]![Combo0]));

On Got Focus Event:

Private Sub Combo2_GotFocus()
If Len(Trim(Nz(Combo0, "") & "")) = 0 Then
MsgBox "Please Enter a Role"
Combo0.SetFocus
Else
Combo2.Requery
End If

End Sub

I hope I made sense with my explanation and with the codes, table and query setup that I posted... I really appreciate any help or guidance to fix the problem.

Thank you very much.

 

[MajP]

This is normal behavior. If you filter the combobox on a continous form it cannot display saved values that have been filtered out. My trick is to take a textbox and lay it overtop of the combo. Only the down arrow of the combo displays, because the text box covers up the "text box" portion of the the combo. Ensure the combo is "sent to back" and the text box "send to front". Bind them to the same control. You can then use the arrow to pull down and select.

 

[iui120887]

Hello MajP,

Thank you for your reply, I guess that the text box trick would work, although there is a small problem with it. Since it is only a text box and the control is bound to the table field name ss. When I view the continuous form, it only gives the Primary Key of the Status instead of the actual "Status Name". Compared to the drop down box that there is a Row Source that I could pull the actual Status Names.

I hope I made sense, thank you for your reply.

I uploaded my test database that maybe you could look at and help me figure out as well.

http://www.4shared.com/file/oL4-vsbB/sanitized_db.html

Thank you very much.

 

[MajP]

I did not look at it, but using the same trick you can then use a dlookup to show what you want. Make the textbox control source something like

=nz(dlookup("status","query4","statuschangeID = " & combo0),"")

 

[iui120887]

Hello MajP,

Thanks for your quick reply! I really appreciate it!

I will check the dlookup function first thing tomorrow morning when I get to work.

it's already 9pm EST and I just got inside my car when I got the email notification.

I will let you know the results tomorrow

Again, Thank you very much

 

[MajP]

Set the textbox to
enabled: yes
locked: yes

and put this code in the key down event
Me.Combo2.SetFocus
Me.Combo2.Dropdown

 

[MajP]

The dlookup should be something like
=nz(DLookUp("status","tblStatusChange","statuschangeID = " & nz([combo2],0)),"")

The inner NZ keeps the function from returning "error" on a new record. The Outer NZ is more unlikely but if you deleted a record from the tblStatusChange it would keep from throwing an error.

 

[iui120887]

Hello MajP,

As always, you're awesome!!

It does work, small little problem is the blinking of all the combo box fields when a value is changed! -- maybe this is something that can't be fixed.

Thank you very much again!

 

[MajP]

change the recordsource of the form to

SELECT Table1.rr, Table1.ss, tblStatusChange.Status
FROM Table1 INNER JOIN tblStatusChange ON Table1.ss = tblStatusChange.StatusChangeID;

then bind the textbox to Status. Ensure it is locked and enabled. The flicker goes away, and it is simpler.

 

[iui120887]

Awesome! Thanks MajP, it is much simpler and the flicker goes away!.

Now I will try to apply this to my actual Job Tracking database, and hopefully nothing major issue will arise!

Thanks again!