Combo box not displaying value for all records

[bettyboo]

Hi all

I got some really useful advice from here with my last question so I'm back for more! Only my second post so please bear with me - I'm very new to this so this will probably not be as concise as it could be (I'm having a bit of trouble expressing myself and am not sure what's important to include!).

I'm trying to build a database for a driving instructor acquaintance and have a form to enter pupil details, f_Pupils. Each pupil gets allocated an area and depending on what this is, there is a choice of driving instructors.

On f_Pupils, I have a combo box called cbArea, which contains area codes for which the source is a table, t_Area. You select the one you want and it stores it in t_Pupils (the underlying pupil table). I then have a second combo box, cbInstructor_All, where the user can select a driving instructor. The only values displayed are those instructors who cover the area previously selected. The Row source for cbInstructor_All is:

SELECT t_Instructors.Ins_ID, t_Instructors.Name, t_Allocation.Area FROM (t_Instructors INNER JOIN t_Allocation ON t_Instructors.Ins_ID=t_Allocation.Ins_ID) INNER JOIN t_Area ON t_Allocation.Area=t_Area.Area WHERE (((t_Allocation.Area)=Forms!t_Pupils!Area)) ORDER BY t_Instructors.Name; 

where:
- t_Instructors is a table that contains the instructor ID (primary key for that table) and the instructor name
- t_Allocation is a table that contains the area the instructor covers plus their ID number

My column count for cbInstructor_Allocated is 2, column widths are 0cm and 2.5cm.

The query part works OK - the only values that appear in the cbInstructor_Allocated list are those instructors who cover the area selected in cbArea.

The resulting value is stored in the underlying table t_Pupils, in a field called Instructor_Allocated, again with seemingly no problems. However, in f_Pupils, the instructor name only seems to be visible on some of the records, and I can't work out why. Sometimes you can see the instructor name, and sometimes it just appears blank. Either way, the instrcutor ID is still being stored in t_Pupils.

I had a look at some other posts and found some code which I tweaked and inserted into the After Update event:

Private Sub Instructor_allocated_AfterUpdate()
Me!Instructor_allocated = Me!Instructor_allocated.Column(0)
End Sub 

However this doesn't seem to do it either.

I don't even know if my above attempt was in the right ball park so I would appreciate any advice - I should also point out that a colleague helped me with the Row Source code above, as I don't want to give the impression that I know more than the teeney amount I actually do! What am I doing/not doing/breaking?

Thanks all!

[NeoPa]

Betty,

I wish most of our questioners could explain their situations as clearly as you do. Your efforts are appreciated.

However, the situation around selecting an instructor, and the instructor name sometimes showing and sometimes not, doesn't really have any information I can work with (Naturally enough - if you knew why you wouldn't post the question right).

What I would like to ask for though, is some example data that shows the process. Some data which works and some which doesn't. I'm assuming it's not as simple as the record not being populated in the [t_Instructors] table?

I would also ask - why are there separate tables for the instructor's name and his area? It's perfectly possible there is a good reason for this but it's an unusual design on the face of it.

[bettyboo]

Hello there

Thanks for replying to my question! The reason I have a separate table for instructors and areas is because each instructor has a number of areas they cover; and vice versa, a number of instructors cover the same areas. I have a table to store which instructors cover which areas, where both area and instructors are repeated, but never in the same combination.

The form with the problem is linked to my pupil record table. The user needs to record the area each pupil is in and then allocate them an instructor based on this. The correct value is being stored in the correct table - the list in my conbo box contains instructor names, pulled from the instructor table; the user selects one of the available names and the ID for this instructor is then stored in t_Pupils. However I want the instructor name to be visible in the combo box, but sometimes when I go back to a record in the form where I have previously populated the instructor name, although the ID is correctly stored in t_Pupils, the conbo box in f_Pupils is blank.

As far as examples of data which works and data which doesn't, I'm not sure what to give you - do you want data from the tables and/or some screen shots? Sorry if this is a stupid question - I don't want to give you a load of useless stuff! that doesn't explain anything!


:)

[NeoPa]

Really I just want some data that might help me to understand what may be going wrong.

Let's do it another way. Can you check a non-working scenario for me and tell me if the Instructor that is selected (whose name is NOT showing up correctly on the form) has a record in the [t_Instructors] table.

I'm hoping to determine whether this is a form (control) level design problem or a data level problem.

Another thing which may prove helpful would be meta data (info about the layout / structure) for the relevant tables (Those covering pupils; instructors and areas).

Click on the Reply button to get access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.
Table Name=[t_Pupil]

Field     Type    IndexInfo
PupilID   AutoNumber  PK
Family    String      FK
Name      String
Area      Number      FK

[FishVal]

Hello, bettyboo.

The most common reason for a combobox to not display bound table field content is that correspondent item was not retrieved to the combobox list.

Could you please post the combobox RowSource and any of your code (if you have such) which changes or may change combobox RowSource dynamically?

Regards,
Fish

P.S. Ok. I've read it more thoroughly. You didn't post what type of control you use in f_Pupils to display instructor name. My guess is that it is combobox, but you should better clarify it. :) All above relates to f_Pupil form control.

[bettyboo]

Really I just want some data that might help me to understand what may be going wrong.

Let's do it another way. Can you check a non-working scenario for me and tell me if the Instructor that is selected (whose name is NOT showing up correctly on the form) has a record in the [t_Instructors] table.

I'm hoping to determine whether this is a form (control) level design problem or a data level problem.

Another thing which may prove helpful would be meta data (info about the layout / structure) for the relevant tables (Those covering pupils; instructors and areas).

Click on the Reply button to get access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.
Table Name=[t_Pupil]

Field     Type    IndexInfo
PupilID   AutoNumber  PK
Family    String      FK
Name      String
Area      Number      FK

Hi NeoPa

Sorry for the radio silence, been up to my eyeballs in it with work stuff. Layout info of my tables below.

Table Name=[t_Pupil]

Field         Type      IndexInfo
PupilID       AutoNumber    PK
Forename      String
Surname       String
Area          Number        FK
InstructorID  Number        FK

Table Name=[t_Instructor]

Field    Type      IndexInfo
InsID    AutoNumber    PK
Name     String
Address  String
Car reg  String

Table Name=[t_Area]

Field  Type   IndexInfo
Area   String     PK

Table Name=[t_Allocation]

Field    Type      IndexInfo
AllocID  Autonumber    PK
Area     String        FK
InsID    Number        FK

I hope I've used the PK/FK stuff properly. The pupils table contains a fair few more fields than listed, but I don't think it would be relevant to list them as there is no problem with these fields and they don't have any impact on the instructor allocation stuff. Similarly, more info is recorded for instructors than I've listed, eg whether or not they've paid a deposit for their sign, whether they've paid their franchise fee etc. t_Area is just a single field containing the first half of a postcode. This field in this table is the row source for the cbArea combobox in f_Pupils. t_Allocation is where users record which areas each instructor covers. Each instructor ID is in there a multiple times, as is each area, but the two should never appear in the same combination. This table is used in the statement that is the row source for the field I'm having trouble with. Your answer has got me thinking, and as the correct instructor ID is being stored in t_Pupils, I assumed it's a problem with the way the form/control is working.

I've also realised from your question I could do more to work out when it doesn't work - I think I could test it more and try and record the scenarios better, and I would certainly need to do that before I give you an example, so I think I would be best off going away and doing that first, if that's OK with you!! I may come back if/when I get stuck though...

Thanks for your input thus far, I'll let you know how I get on...!

[bettyboo]

Hello, bettyboo.

The most common reason for a combobox to not display bound table field content is that correspondent item was not retrieved to the combobox list.

Could you please post the combobox RowSource and any of your code (if you have such) which changes or may change combobox RowSource dynamically?

Regards,
Fish

P.S. Ok. I've read it more thoroughly. You didn't post what type of control you use in f_Pupils to display instructor name. My guess is that it is combobox, but you should better clarify it. :) All above relates to f_Pupil form control.

Fish - it is a combobox that diplays the instructor name in f_Pupils, sorry for lack of clarity there. The Row Source I've given above, but I don't have any other code attached to the instructor allocated box itself. I do have an event procedure on the Area combobox though:

Private Sub cbArea_AfterUpdate()
    Me.Instructor_allocated = Null
    Me.Instructor_allocated.Requery
    'Me.Combo1 = Me.Combo1.ItemData(0)
End Sub

Code was courtesy of a colleague who helped with set the row source up as well.

Thanks for taking the time to reply :)

[NeoPa]

Hi NeoPa

Sorry for the radio silence, been up to my eyeballs in it with work stuff. Layout info of my tables below.
...

No worries. Not only does RL frequntly interfere with civilised forum conversations (no respect), but most of us have a bunch of threads that manage to keep us busy, so some quiet time is never a problem ;)

Congratulations BTW, on doing such a good job laying out the table metadata. Very few manage to follow the instructions as well (to my great chagrin). One hint I would give is that TABs will always cause problems in tabular layout when posted in these pages in [ CODE ] mode. Use spaces exclusively and you won't have the problems with aligning the columns (I've fixed your post to show this).

...
I hope I've used the PK/FK stuff properly. The pupils table contains a fair few more fields than listed, but I don't think it would be relevant to list them as there is no problem with these fields and they don't have any impact on the instructor allocation stuff. Similarly, more info is recorded for instructors than I've listed, eg whether or not they've paid a deposit for their sign, whether they've paid their franchise fee etc.
...

Technically, I should always explain that intelligent filtering of the RELEVANT info is what is required. Unfortunately this nearly always results in responses of such gibberish it's not worth the effort. I'm very pleased to see that you have grasped the point without even being asked :)

...
t_Area is just a single field containing the first half of a postcode. This field in this table is the row source for the cbArea combobox in f_Pupils. t_Allocation is where users record which areas each instructor covers. Each instructor ID is in there a multiple times, as is each area, but the two should never appear in the same combination. This table is used in the statement that is the row source for the field I'm having trouble with. Your answer has got me thinking, and as the correct instructor ID is being stored in t_Pupils, I assumed it's a problem with the way the form/control is working.

I've also realised from your question I could do more to work out when it doesn't work - I think I could test it more and try and record the scenarios better, and I would certainly need to do that before I give you an example, so I think I would be best off going away and doing that first, if that's OK with you!! I may come back if/when I get stuck though...

Thanks for your input thus far, I'll let you know how I get on...!

Reading this I had to check back to see if this was posted on my Birthday (The original post missed by 1 day). It's not often that people GET the idea so well, and even go off and do more work to ensure the question is as clear and accurate as possible.

Is that OK with me? Of course it is (and I'll be more than happy if you came back with further problems).

Just in case it can be of some help (you sound like you may already know most of it anyway), I'm leaving a link to Debugging in VBA which may help you now or in the future.

Good luck with this and welcome to Bytes!

[FishVal]

Ok.

I hope f_Pupils form is being displayed in single form view, because otherwise if it is being displayed in continuous forms or datasheet view, then combobox context filtering will be useless.

SELECT t_Instructors.Ins_ID, t_Instructors.Name, t_Allocation.Area FROM (t_Instructors INNER JOIN t_Allocation ON t_Instructors.Ins_ID=t_Allocation.Ins_ID) INNER JOIN t_Area ON t_Allocation.Area=t_Area.Area WHERE (((t_Allocation.Area)=Forms!t_Pupils!Area)) ORDER BY t_Instructors.Name;

RowSource query is definitely correct. The only point is that combobox list will not be reloaded automatically as soon as Forms!t_Pupils!Area changes.
There are two ways (maybe more) to reload combobox list:

• Call Combobox.Requery method
• Write to Combobox.RowSource property

So, when and where do you perform this operation?

Regards,
Fish

[bettyboo]

Hi both

Sorry it's taken me a while to reply/come back to update the thread. Fish, I read your last post and it also gave me some ideas.

I went back and tested the fields that weren't working and realised that the combobox seemed to display the instructors' names linked to the last entries before it was saved. There was no problem with storing the Instructor ID in t_Pupils, but the corresponding instructor name wasn't being remembered by the combobox unless I saved what I had just done. And then, the other instructor names that had previously been displaying OK were not showing up. IDs were still in the table though. At that point I read Fish's post and thought it could be the form wasn't refreshing the display in the box, so I put the following code in the current form event and it works!!

Private Sub Form_Current()
  Me.Instructor_allocated.Requery
End Sub

I had put this code on the combobox itself previously (both the before and after update events) and it hadn't worked at all, but it seems to be sorted now - so thank you both for ideas and questions that made me think about what could be wrong, You have preserved my sanity, as the thing was starting to drive me doolally...

:)

[NeoPa]

Good for you.

It's always nice to see members go off on their own and find a solution from ideas we've shared.