Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 22nd, 2005, 10:52 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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!

Reply With Quote
  #2 (permalink)  
Old August 22nd, 2005, 11:16 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
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
Reply With Quote
  #3 (permalink)  
Old August 22nd, 2005, 11:50 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.
Reply With Quote
  #4 (permalink)  
Old August 22nd, 2005, 11:55 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
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
Reply With Quote
  #5 (permalink)  
Old August 22nd, 2005, 11:59 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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: ...".

Reply With Quote
  #6 (permalink)  
Old August 22nd, 2005, 12:03 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
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
Reply With Quote
  #7 (permalink)  
Old August 22nd, 2005, 12:06 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.

Reply With Quote
  #8 (permalink)  
Old August 22nd, 2005, 12:12 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
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
Reply With Quote
  #9 (permalink)  
Old August 22nd, 2005, 12:37 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.

Reply With Quote
  #10 (permalink)  
Old August 22nd, 2005, 12:44 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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?

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 11:38 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.