Subform Lookup

[gilescarey]

Hi
I am new to Access form design and am trying to do something that I didn't think would be too hard, but am having trouble with it so would appreciate some input.

I have a table SiteList which has details in a number of fields on various Archaeological Sites. These all have a unique SiteID.

I have a table of Bibliographical details, called References. These all have a unique RefID. This also holds the exact page number of the references.

I have a table LinkTable which holds connections between SiteList and References. This is a one-to-many relationship, so that one Site can have multiple References, and one Reference can be featured on multiple Sites.

I have designed a form which displays all info from SiteList, with a References site form which displays the appropriate References for that site, together with page numbers - very useful.

I want to use the subform for Site Input. Basically this needs to include being able to add a link in LinkTable between that site id and that ref id where a Reference already exists. If I want to add a new reference, I should be able to do this, and create linkages between the Site and Reference.

Ideally as well, I would like to be able to lookup, by typing the first few letters of the reference to see whether it already exists.

Could somebody point me in the right direction of how to go about this, please



Hope this makes sense, but please contact me if it requires clarification.

Thanks in advance,
Giles

[goodguy]

You mention a Link Table and a one-to-many relationship at the same time; did you mean a many-to-many relationship instead? See attached sample.

[gilescarey]

Hi
Many thanks for your reply, that was exactly what I was looking for. However, i altered your database to reflect the fields of my tables, and redesigned the forms, but unfortunately now I am getting the error when opening up frmSites "the expression is typed incorrectly or is too complex to evaluate." and Table.References doesn't seem to display, unfortunately - previously this was doing exactly what I wanted in terms of lookup and adding new refs.

Any pointers gratefully appreciated.

Thanks for your help in this one.

[gilescarey]

Oh I attach so you can see what I am attempting.

[goodguy]

Something is wrong with your frmSites. It opens fine without the subform but when i create a new subform for references, the problem persists. However, when I created a new Sites form with the wizard, the subform came up automatically and worked fine.