Subform is not linking to master field or main form

[Tpeter05]

I am a newbie to access, atleast the design side. I have taken some courses but am still learning my way around things. The issue I am having is with one of my subforms. I have a main form with a combo box to choose the person. This is linking with my other subform correctly and displaying the information it should. I have created an additional subform on tabs. This subform's Record Source is from a table that was imported from excel. It has the same employee ID numbers used as the the query in the main form.

I am told that the issue may be with the fact that my ID field in my subform table does not have spaces in front of it and the ID field of the main form source does. Does anyone have any suggestions for this or am I way off base here?

ID numbers are either 4 or 5 characters.

 

[jzwp22]

Welcome to the forum!

It sounds like your ID field is a text datatype. Although this can be done, databases are generally more efficient with numeric key fields.

You are correct that the spaces will cause an issue because the values of the key fields in the two related tables much match exactly. Having spaces in one and not the other will cause a "no match" and thus no link, so you need to trim those spaces. For the field with the spaces, you want to use the trim function trim(yourfieldname). If it were me, I would probably run an update query to get rid of all the spaces in all of the tables and then make sure any new values are trimmed before they are stored then you will not have to worry about it. The other thing about key fields is that they should have no significance to the user, so if your current text based key fields have some significance to the user, it would be better to create new primary and foreign key fields (numeric this time) and do all your joining/linking with those. You can keep the text value but you would only need it in your main table once you have set up the numeric keys.

 

[Tpeter05]

Thank you very much for your help! Will the trim function work if the table with the spaces is from another database? It's a dbo table and linked to an external db.

 

[jzwp22]

Not sure, but if you are bringing the data into Access via a query, the trim() function can be used in the query.