Help, Access 2007 Is Being Mean to Me

ElJeffe

Okay, I need to create a database to help track the locations of a couple hundred pieces of hardware as we upgrade them all and send them back out into the field. Each piece of hardware needs to be tracked by serial number, and I need to know where it came from, where it is, and where it's going. I'm using Access, and I've never used it before, so I know roughly dick.

I have most of the information in the system in a couple of tables so far. The two tables I have are:

Air Force Bases
Each one has a BaseID as the primary key, and each record has such info as the base name, contact info, shipping address, and so on.

Ceilometers (the hardware being upgraded)
Each one has a ceilometerID as a primary key, and tracks info such as serial number, where it is, where it's going, where it came from, status, type, etc. The current location is linked to the BaseID for each base.

I'm trying to create a form for each base that lists all the contact info, the number of ceilometers that should be there, and each ceilometer that is currently there. Ideally, I would like each ceilometer that's there to show up by serial number, and when you click it, it takes you to a similar form for each ceilometer that shows you its history and other pertinent data.

I have no fucking clue how to do this. I also do not have any books on Access 2007, nor anybody around here who knows how to use this shit. Google is proving difficult to use since I don't really know how to refer to any of the things I want to do. So, some specific questions:

- I would like there to be a relationship between each instance of a BaseID referenced in a ceilometer record and the actual base record in the base table. I can create a relationship between, say, the current location and the BaseID, but it won't let me also create a relationship between the destination location and the BaseID. Am I not doing it right, or is there a reason I don't want to do this and Access is smarter than me, or what?

- Is there a way to reorganize the locations of various fields in a form beyond just rearranging them vertically? Like, I would like to have the contact name, contact email, and contact phone all on the same line, but I can't figure out how to do this.

- How do I even begin figuring out how to create links between records, of the sort where data elements contain hotlinks to other records (like, click on a ceilometer serial number, it takes you to the corresponding record).

VThornheart

AirForceBases
*BaseID
BaseName
ShippingAddress
ContactInfo
...

Ceilometers
*CeilometerID
SerialNumber
CurrentBaseID (FK to AirForceBases)
DestinationBaseID (FK to AirForceBases)
OriginBaseID (FK to AirForceBases)
Status
Type
...


Is this roughly what the table looks like? So you have a few one to many relationships between AirForceBases and Ceilometers? Just making sure. That should be okay to do, I don't see a problem with doing that.

I'm trying to create a form for each base that lists all the contact info, the number of ceilometers that should be there, and each ceilometer that is currently there. Ideally, I would like each ceilometer that's there to show up by serial number, and when you click it, it takes you to a similar form for each ceilometer that shows you its history and other pertinent data.

It sounds like what you need is a Report more than a Form. A form would be for data entry or modification, but you want something that's pretty much read only (unless I'm mistaking what you're asking for?)

Try this video, it seems to show a bit about the basics of making a report and may help you in this regard:

http://www.youtube.com/watch?v=ER6a5QPpZa8

- I would like there to be a relationship between each instance of a BaseID referenced in a ceilometer record and the actual base record in the base table. I can create a relationship between, say, the current location and the BaseID, but it won't let me also create a relationship between the destination location and the BaseID. Am I not doing it right, or is there a reason I don't want to do this and Access is smarter than me, or what?

It *should* let you do that (as it's reasonable to have more than one relationship between two tables in a Database), but Access itself may not let you. However, that is no reason that you can't go ahead and make informal relationships (as in the example of tables I put above, there's 3 relationships between the two tables... if only one can be defined formally in Access, the other two can still be informally defined and mandated via validation in the data entry forms for example)

Ah! I just tried it out, and in the "Relationships" view, you can define multiple relationships between a table. It creates what appears to be a new table in the Relationship view, but that appears to just be some kind of unusual interface decision on the part of Access: the relationship will still be there, it's just in the Relationship view it'll look like you have multiple instances of the "many" table in the one to many relationships.

- Is there a way to reorganize the locations of various fields in a form beyond just rearranging them vertically? Like, I would like to have the contact name, contact email, and contact phone all on the same line, but I can't figure out how to do this.

Oh, you should be able to move them around at will. Hmm, Access 2007's a bit wierd with it... previous ones let you move it at will. It appears you have to highlight all of the fields, right-click, go to layout, and select "remove". You can then move them at will. There may be an easier way to do this.

- How do I even begin figuring out how to create links between records, of the sort where data elements contain hotlinks to other records (like, click on a ceilometer serial number, it takes you to the corresponding record).

Here you're talking about something more interactive... indeed, like a form (now I see why you wanted a form instead of a Report). Is what you want to be able to select a Ceilometer and then be able to, while viewing it, also view the AirForceBase it belongs to then? You could add a button to the form you make for viewing a Ceilometer that opens the AirForceBase form... but that's going to require some VBA macroing. Which we can help with if you can lay out some more details.

VThornheart

Ooh, there is a nifty wizard now. When you add a button, it gives you a wizard that will automatically code up some things that you can do (such as open another existing form). You can use that to open the form that displays AirForceBase information, for example. In theory.

Admittedly, even with that new wizard, I think the prior versions of Access were more user friendly when it came to creating forms and reports the exact way you want them. =(

EDIT: In my haste, I forgot your original intent. You want to be able to view the Ceilometers in a list for each location and then when you click on the serial number of one, you'd like a form to come up with the details of the specific Ceilometer.

In that case... you could handle the "OnClick" event of a list box (that you would fill with Ceilometers for the current location), and then in the handling code call the form and load it witht he Ceilometers' info. The difficulty of this depends entirely on if you've done programming before, which I can't recall. =( Have you ever done programming? Visual Basic for Applications is what you have to use to accomplish this task. Let us know, I can find you some tutorials on how to use VBA if need be.

Also, if you've done VB programming before, remember that you can use any ActiveX Control that works in VB6. That means you have access to the much more robust "Microsoft ListView 6.0" control (which is standard on any machine made with Windows 98 or above), which provides more features in terms of showing columns, sorting, and extra event handling than the standard list box. You just have to make sure that users of your Access database then allow it to use ActiveX controls (it will prompt them).

ElJeffe

Okay, I'll try to implement everything you just mentioned. Thanks, you are my hero, assuming it all works.

I actually created the relationships like you mentioned and had it pop up a second window for the relation that was to "Bases_1" instead of "Bases" and I thought I'd done something wrong. Stupid, stupid Access.

As to the linking thing, I've done coding, but I'm not sure if this is going to warrant that. The DB is mostly for my use, and it's not that hard to just switch over to a form view of the ceilometers and search by serial number when I want to view it. If it's going to take me more than an hour to get the linking to work, it's probably too much hassle for the time it would save.

VThornheart

Glad to help! Let me know if there's any more help we can provide!

Aye, my guess is that they don't expect people to do advanced stuff like that (multiple one to many relationships between two tables) with their program. I think the whole "second table" thing was an easy way for them to implement a visual acknowledgement of the secondary relationships that they didn't originally realize they'd need. If they thought more people would do that, I'm sure they'd have implemented something like being able to just draw multiple of those GUI "relationship lines" between the same tables. There's obviously some business logic restriction keeping them from doing that though, so the "_1" duplicate is like a quick workaround that they never got around to finding a more permanent solution for.

Hmm... aye, if it's for your own purpose it may not be worth it. Heck, if it's for internal use only you can forsake the forms altogether and use a direct query. Once you've set up the relationships, try making a Query that returns all of the ceilometers for a given location. You'll get it in a non-elegant, but very functional column display that requires no programming short of building the query itself...

... which, if you need it, would be (given my example above):

SELECT Ceilometers.* FROM Ceilometers, AirForceBases WHERE (AirForceBases.BaseName = ?) AND (Ceilometers.CurrentBaseID = AirForcebases.BaseID)

The ? will let Access know that, when the query is run, it should prompt you for the value of the field. You then type in the Air Force base name, and it'll run that query and (assuming that all Air Force Bases have unique names) give you the results you're looking for (aka all of the information about all of the Ceilometers currently at that Air Force Base).

Seguer

If you can create a simple/normal form that lets you cycle through all the bases, you can then add a form inside this one (master form) that can show a bunch of Continuous forms (sub forms).

Each master form affects its own sub form by whatever value(s) you want, such as the ID and the relationship between the parts etc, therefore each master form can give you a list (in form view) of each of the related parts.

The sub form can be created within the other form in access and you make the form as if it were going to show one single part, but change it to be a continuous form (right click - properties somewhere, form type?)

Continuous forms show a single form many times in the same window for repeating data and yet allows you to add buttons etc (such as click me to view info on this part!)



If you need more than that, send me what you've done with your tables (junk data perhaps) and I'll see what I can do..

VThornheart

Ah, interesting! I didn't even know about that. Very cool!