Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 22nd, 2005, 10:52 AM
Authorized User
 
Join Date: Aug 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default List Boxes, Help!

I know Access very well, but I am new to VBA. My goal seems to me to be a simple one, but I'm having a lot of difficulty in achieving it. Hopefully someone can help.

I am making a software inventory database for the School System I work for. I have a tblSoftware, a qrySoftwareFullName, and a tblImages that I want to tie together in a form (frmImages) that will allow users to multi-select Software names from a list box (lstSource) and hit a button to save those selected items somehow (I'm assuming in memSelected), while also displaying only those selected ones in a second list box (lstDest).

I have tried some basic VBA and can get it to write the selected items to a memo field and even populate lstDest each time, but the problem comes when you try to view a different record in the form; it seems to be writing the selected items to every record's memo field, or can't seem to differentiate between records.

I don't know if I'm even approaching this the right way. I just want to have a list of what Software is on each Image. Any and all help would be greatly appreciated. Thanks!

 
Old August 22nd, 2005, 11:16 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jeff,
Do you have 3 tables? 1 for Images and 1 for Software titles and 1 for the relationships? Do you have a 1 to many relationship?

Kevin


dartcoach
 
Old August 22nd, 2005, 11:50 AM
Authorized User
 
Join Date: Aug 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have 2 tables: tblSoftware and tblImages.

I have my tblSoftware which feeds info. into the qrySoftwareFullName so that instead of just displaying "Office", it will display the expression "Microsoft Office 2003". Then I have the RowSource property of my lstSource set to this expression.

http://img261.imageshack.us/my.php?i...onships6nl.jpg

However, there is no relationship (as of now) between tblSoftware and tblImages. Because each image will contain/relate to many pieces of software, I do not know how to set up a relationship like that. Perhaps, as you seem to have suggested, I need to have a third table. What information would be contained in this "relationship table"?

Thanks so much for helping me.
 
Old August 22nd, 2005, 11:55 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jeff,
Let me make sure that I'm understanding what you want to do.
You want a form to choose an image, then populate a list box from the available software titles so you can multi-select what software is on that image?

Kevin

dartcoach
 
Old August 22nd, 2005, 11:59 AM
Authorized User
 
Join Date: Aug 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Exactly.

Ultimately, the goal is to be able to print out a report that says "This computer contains this manually-installed software: ... and this image, which contains this software: ...".

 
Old August 22nd, 2005, 12:03 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jeff,
Do you have a table for the computer info? I.E. Where it is, who's it is etc.? Can a computer have more than 1 image? If it's what I'm thinking, you have a network with different images that you put on individual computers, right?

Kevin

dartcoach
 
Old August 22nd, 2005, 12:06 PM
Authorized User
 
Join Date: Aug 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That is all correct.

We generally make one image per machine model type and apply that to all machines of that model in the district.

Also, I /do/ have a tblComputers that has the mentioned information such as User and Location, etc.

 
Old August 22nd, 2005, 12:12 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jeff,
Sorry for so many questions, but do you have maintenance forms for:
1. Computers
2. Images
3. Software Titles

If you do, you need to create a relationship between Computers and Image. You also need a relationship between Images and Software Titles.

If you don't know how to create a relationship, there is help in the Access help.

Once you have the relationships, you can create a form with subforms that shows the Computer Info, What Image is currently on it and what software is on that image.

Make sense?

Kevin

dartcoach
 
Old August 22nd, 2005, 12:37 PM
Authorized User
 
Join Date: Aug 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have "maintenance" forms for all my tables.

I have relationships between tblComputers and tblImages by way of the Image field in tblComputers.

http://img387.imageshack.us/img387/6...nships24iy.jpg

I also have a memSoftware field in my tblImages, but I can't make a relationship with it to the tblSoftware because I wish to have multiple software installs on a single image, and such a relationship (in my understanding) will not satisfy this requirement.

How do I link tblSoftware and tblImages? This is the big question, I guess. Also, thanks so much for the help; I don't mind the questions at all.

 
Old August 22nd, 2005, 12:44 PM
Authorized User
 
Join Date: Aug 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just found this online:

"In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key)."

It sounds like this might be what I need to do with Images and Software, as each Image can have many pieces of Software AND each piece of Software can be on many Images. Am I on the right track here?






Similar Threads
Thread Thread Starter Forum Replies Last Post
LIST BOXES Vision G Access 10 May 27th, 2006 01:45 AM
Dynamic List Boxes swhite Access VBA 10 September 11th, 2003 01:27 PM
SQL , List Boxes/Menu Boxes, DB's Ginzu3 Classic ASP Databases 1 June 30th, 2003 04:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.