I have a problem with a large and confidential database, so I've replicated the problem on a much smaller scale, thus simultaneously satisfying myself that it's not corruption and having something I can share with you wonderful people who (I hope) are going to have an answer for me! I’m using Access 97 on Windows XP but the problem occurs on a computer running Win 98 as well.
This is going to take a while so I hope you have the patience to follow it! I have four tables in my database, as follows:
TableClients: clientid (primary)
TableFiles: fileid (primary), client (combobox on clientid)
TableInventors: inventorid (primary)
TableLink: file (primary, combobox on fileid), inventor (primary, combobox on inventorid)
Every field is of the type integer.
So basically, a client has files, and each file has at least one inventor, and inventors only work for one client but can be inventor on multiple files.
Form FormFiles is based on TableFiles and contains the controls fileid, client.
Query1 contains the fields tablelink.file, tablelink.inventor, tablefiles.client.
Subform SubformInventors is based on Query1 and contains the combobox TableLink.inventor, called ComboInventor. It sits happily on FormFiles, linked using TableFiles.fileid and TableLink.file, displaying the inventors for each file.
What I need to do is limit the dropdown of ComboInventor to inventors that work for the client that has the file. I want to do this by limiting it to all the inventors for all files of that client.
So the first question is: have I set up these tables right? Would it be better if I linked the inventors to TableClients as well as to TableFiles? If I do that, I can’t see how to link the forms.
Second question is, if the tables are OK, then what’s wrong with my code? I’m limiting ComboInventor using the following in the SubformInventors module:
Private Sub ComboInventor_enter ()
Dim intID as Integer
Dim strCli as String
intID = parent!client
strCli = “SELECT DISTINCT tableinventors.inventorid FROM tablefiles INNER JOIN (tableinventors INNERJOIN tablelink ON tableinventors.inventorid = tablelink.inventor) ON tablefiles.fileid = tablelink.file WHERE (((tablefiles.client)=” & intID & “));”
ComboInventor.RowSource = strCli
I know the code’s basically OK, because if I substitute, for example, the number 2 instead of intID, everything’s fine. But if I put in intID, I get the following message:
The Microsoft Jet database engine cannot find the input table or query ‘~sq_cSubformInventors~sq_cComboInventor’. Make sure it exists and that its name is spelled correctly.