Subforms and Join Tables

[starbird]

I will start off by apologizing for the lengthy post in advance.

I have 2 tables. One table, tbl_ClientRequest, which stores requirement requests from a client. The other table, tbl_Solutions, stores solutions available.

One solution can possibly solve more than one request, and one request can have many solutions.

What I want to have is a form that lists all the requests (one at a time) and then lists in a subform the solutions associated with the request. I also want the user to be able to select additional solutions in the subform.

I have used a join table to store the Request ID's and the Solution IDs.

So it looks something like this

tbl_ClientRequests
==================
ID Request
------------------
cr1 request
cr2 request

tbl_Solutions
==================
ID Solution
------------------
s1 solution
s2 solution
s3 solution

tbl_Join
==================================
ID RequestID SolutionID
----------------------------------
1 cr1 s1
2 cr1 s2
3 cr2 s1
4 cr2 s3

I have the subform set up now, and it will allow the user to view and add solutions. But I also want to display the solution description as well. Right now, it just shows #Name?

Any suggestions?

Thanks!

 

[Rolliee]

Let the wiz do your main form, then extend the size of the mainform and put a subform at the bottom. Just answer the questions as you go. If you have trouble with this email me and ask for a sample.

rollie@bwsys.net

 

[BSman]

Since your solutions are standard statements (rather than free form text), the easiest way is to use a combo box for the solution in your solutions subform that is driven by tbl_Join. The combo box will use tbl_Solutions as its source, returning both the SoltuionID and the Solution; however set the display width as 0";2" (or whatever width will display your solution text). This way, the combo box actually displays the text of the solution, since the user needs to know the solution, not the code.

 

[starbird]

Thank you both for your input.

BSMan - That is close, except that the user would want to see the Solution ID, as it is meaninful. is there a way to display both on the form once selected?

 

[BSman]

Absolutely. Just change the display width of the first column from 0" to .5" (or whatever is appropriate). And, if only the first column is then display, change the source for the combo box to something like this:

SELECT [SolutionID], [Solution], [SolutionID] & " " & [Solution]

You would now have three columns. Set the display widths to something like 0";0";3". The first column is the data tied to the combo box.

 

[starbird]

Great, thanks!