Cascading combo boxes not reloading for previously made records

[lemmiwinks]

I'm making a database for my science lab. We're making proteins called antibodies. We're organizing the data to be entered based on who is doing the work and what "plate" their samples are from. In other words, many people are working with their own plates to make their own antibodies.



I made a form for entering new antibodies. On the form I have an unbound combo box that allows the person to select their name, the rowsource is a query that selects the PK from a user table. The combo box below is for selecting plates; the rowsource is a query that selects the PK from a plates table and only shows plates that have criteria for the userID selected in the first combo box on the form (userID is a FK on my plates table). This second combo box is also bound to a FK of plateID on my antibodies table. Then the user can enter a name for their antibody (in a text box that is bound to AntibodyName on the antibody table) and it will have a FK value associated for the plate it came from in the antibody table. I figured out how to do the requery code, and I believe my data is going into the tables how I expected.

The one minor cosmetic issue I'm having is that when I open the form and browse through old records, the two combo boxes are blank. My guess for this is that it is because the first user combo box is unbound and therefore contains a null value on loading, the second combo box that filters the plates based on the user is blank because the query for it relies on a value being in that first combo box (even though there is a value for this second bound box in the record source). I'm unfamiliar with vba syntax, but I've done some programming long ago and understand some principles. Is there a way to do a check on loading of the record, to see if my value for the combo box for plates (the second and bound in my cascade of two) isn't null, and if so it could display that value and then is there a way to reverse filter this to the associated user in the first combo box as well?

Or are my combo boxes not showing data for previously made records based on some other flaw?

Hopefully my problem makes sense. If not, let me know and I'll try and give more details on what I'm doing.

[NTC]

redo your application like this:

set up (or change) the fields in the target table to be fields that are 'look up' field type. the wizard will step you thru which other table they are looking up.....do not attempt to make them cascading now - just make sure they are selecting from the correct table and the correct key is going into the field.

then return to your form; delete the existing comboboxes; drag in these fields from the field list - they will be drop downs automatically (comboboxes) because of their field type. Now go into their underlying query (record/row source)here in the form - and modify their query to be cascading (just like you did before).

should all work with these changes.

[Missinglinq]

Before you go using Lookup Fields at the Table-level you might want to look at this:

The Evils of Lookup Fields in Tables

I know of no experienced Access developer that advocates their usage, and certainly not for use by newbies.

Linq ;0)>

[NTC]

One can certainly try it in a copy of one's db - and if you don't like it - can still go another path in another copy of the db. It works. It is provided by Microsoft. I don't find a post that warns against it - yet does not offer a working alternative solution to be particularly helpful to a novice...... I see nothing wrong in recommending a Microsoft provided feature.

[lemmiwinks]

redo your application like this:

set up (or change) the fields in the target table to be fields that are 'look up' field type. the wizard will step you thru which other table they are looking up.....do not attempt to make them cascading now - just make sure they are selecting from the correct table and the correct key is going into the field.

then return to your form; delete the existing comboboxes; drag in these fields from the field list - they will be drop downs automatically (comboboxes) because of their field type. Now go into their underlying query (record/row source)here in the form - and modify their query to be cascading (just like you did before).

should all work with these changes.

I changed the plateID in my antibodies table to a lookup using plateID and platename from the plates table, and changed the userID in my plates table to a lookup using the userID and username from my users table. Then on the antibodies form I replaced the old unbound users combo box with the username one in my plates table, and replaced the old plateID combo box with the platename from my antibody table, and it works.

How is it working exactly? When I look at my cboUsers box on the form now it says it is bound to User, but I don't have that field in my antibodies table. Is access storing the value on my antibodies table and then hiding it from me?

[June7]

If the field is available for the ControlSource then it is included in the form's RecordSource. Is the RecordSource a join of tables?

I also don't like Lookups in tables. As a developer when I view tables I want to see the real value, not alias. Users should not work directly with tables and queries. If you are the developer and the only user, that is another matter.

[NTC]

Glad to hear it works. How it works is this: in the form look at the combobox's properties - and you will see its 'Control Source' is the field bound into the form's record source; while the 'Row Source' is the lookup table. So one looks up at the row source and it enters the selected value into the control source.

What some complain about is that, in its default set up, a lookup field will enter the key value while displaying the text (non key value). And some find this confusing. One can alter this if you do not want the key value in your table by forcing it out by modifying the design manually after the wizard is done doing its thing. However simple awareness that this is how it works is sufficient to work with these field types efficiently - - after all the only alternative for those biased against look up fields is to manually link to another table (and probably its key field !!) which is the exact same thing. Look up fields display the tables & their linking in the Relationships tool as well of course in the control source of the field type in the table properties. So there should be no mystery or difficulty in working with them.

hope this helps

[lemmiwinks]

If the field is available for the ControlSource then it is included in the form's RecordSource. Is the RecordSource a join of tables?

I also don't like Lookups in tables. As a developer when I view tables I want to see the real value, not alias. Users should not work directly with tables and queries. If you are the developer and the only user, that is another matter.

I think you're right about the record source and join of tables. Before, the form's record source was only tblAntibodies, and now it is a query that says: SELECT tblAntibodies.*, tblPlates.UserID FROM tblPlates INNER JOIN tblAntibodies ON tblPlates.PlateID=tblAntibodies.PlateID;
Which I think is saying the record source is all of the fields in the table for antibodies, and also the userID from the plates table, and this is related to the antibodies table through the FK plateID in the antibodies table and the PK plateID in the plates table. Is that right?

I didn't know I could do this type of magic. I was under the impression that it's a one form for one table kind of deal in terms of inputting data on a form; that's why I originally had the userID combo box as unbound since it wasn't in my antibody table. I'll go back and redo the microsoft training on joins since it was confusing the first time I went through it. If anyone has any other good links on joins I'd appreciate it, thanks.

[June7]

Yes, use form to enter data to one table. However, can (depending on table relationship) include other table in join to view related data but don't edit data in the other table. Set textboxes bound to other table fields as Locked Yes and TabStop No.

[lemmiwinks]

I think I spoke too soon about this working for me... Bounding the username box to the value in another table corrected the problem that when I would go back and look at old records, the two cascading combo boxes now show the correct information.

However, when I try and enter new data on the form I'm getting an error. I can select the first combo box of my username, just fine. My next combo box "plates" then restricts then filters the plates possible based on the previously selected user like I want, but when I make a selection from here I'm getting an error 3341: The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship.

I'm guessing this is due to some relationships and rules that I'm not cognizant of now that I've made the first combo box bound to a field a table different from the one the form is bound to...

I'll upload my database since I'm not sure if I'm being clear. My problem is in frmAntibodies. I previously had the username combo box as unbound, and hence previously made records weren't showing anything for it, nor the plates box below it, when I would look back through them (even though the second combo box of plates is bound to the tblAntibodies). It worked just fine for adding new entries, though.

[June7]

I am looking at frmAntibodies. There are two comboboxes on the main form. They are both bound. If purpose of comboboxes is to input filter criteria then they should be UNBOUND, otherwise will change value in record. The RecordSource of main form is a join of tblPlates and tblAntibodies. The subforms link to tblAntibodies and tblAntibodies links to tblPlates. Plate combobox should definitely not be bound to autonumber PlateID field - that causes error message. Why is the Username combobox bound to UserID of tblPlates? Do you really want to allow changing the user associated Plate record via this form?

Is the main form supposed to allow edit of data - which table? Only one table per form for editing.

[lemmiwinks]

The purpose of the first combo box username is to filter the possible plates. On my very first post I originally had it unbound, but after making the changes that were suggested by others it made it bound to another table. I have since changed it back to being unbound because like you said, I don't want people being able to change this on the plate table.

The second combo box is bound to the FK plateID in the antibodies table. I did this so that each antibody would be associated with a plate ID and have all the characteristics of that plate and its relationships. The idea was that in the future I could then query things like specific cell types and mouse types and those antibodies would show up because they are related to the plates with those characteristics.

I've gone back to what I originally had in my first post, which is an unbound combo box for user, but kept a bound box for the plateID FK. Now the only record source is the table antibodies. However, this still causes the two combo boxes to be blank when I go back through previously recorded records. I know the plateID on the antibodies table has a value, but because the form combo box for this references the unbound combo box of user, it shows it as blank. Is there any way I can do coding or is there a function when a record loads that says something like if the plateID value is not null, then display it, other wise take from the combobox username on the form? And if it isn't null, then make the combo box username have a value based on a query that joins the plateID value and its related userID value?

[June7]

I looked at Plate combobox again and correction, it is not bound to PK PlateID as I thought so don't know why errors. However, Username was bound to UserID from tblPlates.

True, the Plate combobox will not show value because RowSource is a lookup. The combobox is displaying alias, not the actual field value. The alias might not be available because RowSource is filtered on the value in Username combobox. If Username is blank then there are no items in the Plate list. A catch-22 situation and one reason lookups are a pain.

Alternatives:

1. don't use lookup - set PlateName (it's a nice, short identifier) as PK and save that value in tblAntibodies, eliminate the autonumber PlateID

2. have a textbox display the plate info from the Plate table included in form RecordSource (Locked Yes and TabStop No)

3. code in form Current event to populate unbound Username combobox with userID associated with the PlateID