AccessDiscussion 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 .
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!
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.
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"?
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?
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: ...".
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?
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.
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.
"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?
List Boxes, Help!
Linear Mode
