[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Populate a Listbox from a query record

Posted on 2011-10-27
12
Medium Priority
?
271 Views
Last Modified: 2012-05-12
Hi,

I have a form to select a contract.  When the contract is selected, it opens a second form
("frmMainTab") with the contract's information (pulled from a query record) shown in several textboxes.

Here's the code that opens the second form:
Private Sub List33_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMainTab", , , "ContractNameID = " & Me.List33
End Sub

The second form needs a list box to show the client board members for the contract.  All I can do so far is get it to show all the board members for all the contracts.

Thanks,

Rick


0
Comment
Question by:taborrg
  • 5
  • 5
  • 2
12 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036510
With the scenario you presented, I suggest to post a sample db so that we can assist your better.

We need more info on what are the field names of your table to pull out the list.

Sincerely,
Ed
0
 
LVL 1

Author Comment

by:taborrg
ID: 37036543
Hi Ed,

Here's the database.  It opens to the first form, double clicking in the first form's list box opens the second form and populates it with the query record.

Thanks,

Rick
Database2.accdb
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036590
See attached file and check if this is what you want.

Sincerely,
Ed
Database2.accdb
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036608
taborrg,

The sample database I posted only give you an idea on how to do it. You need to check your table relationships to achieve what you want.

Sincerely,
Ed
0
 
LVL 1

Author Comment

by:taborrg
ID: 37036624
Hi Ed,

Getting closer - It only shows one board member (as it should, I only have one entered per contract), but it seems to be random which one it shows, and usually not the right one.

Thanks,

Rick
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036900
Try this one.
Database2.accdb
0
 
LVL 1

Author Comment

by:taborrg
ID: 37036933
Hi MINDSUPERB,

The SQL statement looks interesting, but it doesn't populate the listbox with any data.

Ed's solution is very close, but it puts everyone in the list box, not just the Board Members for the selected contract.

Thanks.
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036970
As I posted at ID: 37036608, you really need to check your table relationships. In your board member table, you must have a ContractID as the foreign key.

In this way, when you need to display the list of board members on a specific contract, you can filter according to ContractID.

Ed
0
 
LVL 1

Author Comment

by:taborrg
ID: 37037192
It looks like that may be the only way to go.

The only problem is that the Board Member is attached to the Client.  And the Contract Name is attached to the client.  So basic design says I should be able to get there from here.

Maybe not.

Thanks

0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37037936
You have the proper relationships in place in your contracts query.

Set the listbox's rowsource to point to that query and base its criteria on the client and contract currently showing in the form with the following WHERE clause:

WHERE ((([ContractName Query].Clients.ClientID)=[forms]![frmMainTab]![txtClient]) AND (([ContractName Query].ContractName.ContractNameID)=[forms]![frmMainTab]![txtContractID]));

I added a couple of textboxes in the sample to show how this works.

You can set them to hidden and also hide the id columns in the listbox by setting their widths to zero.
Database2.accdb
0
 
LVL 1

Author Closing Comment

by:taborrg
ID: 37041760
mbizup,

Truly awesome.  Works perfectly - and now I know some serious SQL.  I have three more list boxes to add, and I 'm sure this will get me there.

Thanks,

Rick
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37042739
Glad to help out :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question