Problem with relationships between 2 tables/ queries

[pam0566]

I am trying to set up a vehicle inv file [access 2003 db but using access 2007]--the tblvehicles includes info about a particular car, the tblequip which is linked by an equipid decribes detailed equipment about that car. My problem is that somehow i must have the relationships screwy... when i enter frmvehicles page, and enter a vehicle, i click on a command button to open the equip page, enter equip, but when i go back its gone, and keeps wanting me to add new... nothing is savin to the queries. Any ideas? I am enclosing a copy of my DB so you can see what i mean. thanks

 

[CraigDolphin]

Hi Pam,

Well, I had a quick look at your db and I think you're right: the relationships and tables need a lot of work. But that's not the sole cause of the issue you're talking about.

I would have fixed it for you but something in your db may be corrupted as it causes Access 2003 to crash whenever I attempt to add something to your forms. It also fails to compile, and many of the code module subs are not properly linked to the relevant controls on the forms.

In short, The vehicle table needs a unique id field....preferably an autonumber. Currently you're using an integer field with a default value being the current date (?!?) This means that if you try to enter two vehicles on the same day, your primary key will not accept the second entry.

The equip table need a unique id field also, preferably autonumber again. However, for the db to know which vehicle this row is supposed to be associated with, you also need to include a foreign key in the equipment table.

So, if your Vehicles table's primary key were VehicleID (autonumber), you'd also need a long integer field in the equipment table called VehicleID.

Now, you need to include these fields on their respective forms (although you can hide them from the user if you like).

Then, on the equipment form, you set the default value for that field to be equal to the value of the VehicleID field on the (open) vehicle form.

That way, when you start entering your equipment values, the db stores the vehicleid of the currently viewed vehicle in the equipment table, alongside your selected values.

The last part of this, is in the code run by the equip-command button on your vehicles form.

When you open the form, you need to tell the db to open the form at the equipment record where the correct vehicleId is stored.


So, the code should look something like:

if Me.VehicleID & "" = "" Then
'do nothing because vehicle form is on a blank, new record and no 
'autonumber has yet been assigned
Else
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stLinkCriteria = "[VehicleID]=" & Me.VehicleID

    stDocName = "frmequip"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End if

This assumes that the control on your vehicle form that is bound to the VehicleID field is also named VehicleID

But you really should revisit your table structure and do some reading on table normalization and primary key/foreign key relationships.

And I hope whatever is plaguing the version of the db you posted is not also present in your local copy or you might find yourself battling db corruption as well.

 

[pam0566]

thanks for input. will try that. i do have a question/ favor to ask. i noticed fri my computer on 2003 was crashing. i have access 2003 and 2007 on my computer, i am in process of switching everyone over to 2007 but havent gotten them all yet. i created a new db, imported the tables, queries, forms etc. did not do anything with relationships yet... can u try and open and see if you still have a prob.? i wonder if its just my computer ....figured might as well work on that too before i start doing a lot of changes. will try what you suggesed on relationships and let you know if i can get it to work.. thanks!

 

[pam0566]

ok i am stuck. on the button to open equip, i have an embedded macro to open the form where: "[vehicleid]=[Forms]![frmequip]![vehicleid]" but when i click on button nothing happens. can u help me with this ?

 

[pam0566]

ok i just re-read your post. the code should go in command button.. so my question is where should i set the default : [vehicleid]=[Forms]![frmvehicles]![vehicleid] or should it be: [vehicleid]=[Forms]![frmequip]![vehicleid]

 

[pam0566]

ok i re read and think i did what you advised me.. but i get a compile error. not sure what needs to be changed, i copied and pasted. i am including a copy of db... this one has the changes you suggested, disregard teh one from earlier. ... thanks

 

[pam0566]

ok, i had to change the code a little [to get correct name of vehilceID field]- but now form opens, no more compiling error -- BUT i am not getting the right record. i 'think' my relationships are ok? my [vehicleid] field on the frmequip page is not populating.. i think that is why.? when i try to use the command button to open the vehicle form i get 27 (dup record) not the one... and that field is empty. Can you take a look at this and see what i missed? thanks !

 

[CraigDolphin]

Hi Pam,

sorry...have been slammed at work and haven't been in the office for a while. I'll try to take a look at it tomorrow afternoon when I get back in from the field.

Cheers,
Craig

 

[pam0566]

thanks, i have been working on it all day and not getting it right.

 

[pam0566]

ok still having trouble . and i think its relationships.. the vehicleid field doesnt show 1 to 1.. when i pull up the cmd button to open frm equip, i get 3 vehicles. but equip all the same

 

[pam0566]

on the frmvehiclesALL i got teh command button to open the right equip form by adding an embedded macro. / so its just adding the new equip to the table frmvehicles with the code that i am having trouble with. i would like to be able to add, and then go back and view as well. if that is possible--

 

[pam0566]

I "think" the problem is in my frmequip [vehicleid] field. i have the default value set to : [vehicleid]=[Forms]![frmvehicles]![vehicleid], so when i click the cmdbutton for add new equip, i should get the vehicle id to be the same as the form i am on, but its not working. should there be a requery in there somewhere do you think?

 

[CraigDolphin]

Hi Pam,

well, have a look at the attached. I've fixed up several small issues. Have a look at how the default works, and uses a combo box to display the year, make, and model without needing it to be in the source query for the form.

Also, look at the stLinkCriteria in the code in the command button to see how that is supposed to look.

Note that I've modified your qeVehicleEquip query to eliminate all the unecessary joins and subqueries. You may or may not wish to reinstate some of the calculated fields from qeEquip, but as far as I can tell you're not actually using them in the form so I see little point in sending that data across the network.

One of the issues you had stems from a lack of naming conventions. Your Make field in tblVehicles actually stores the foreign key value of the MakeID primary key field in tblMakes. In your relationships view you have the foreign key field incorrectly joined to the text Make field in tblMakes.

You also employ outer joins when joining some tables in the relationship view: (like tblEquip to tblVehicle). It is fairly rare to need to do this in my experience, and I think you're actually not using this correctly in your relationships. Also, you should enable referential integrity on your joins to avoid leaving orphan records in tables where no corresponding primary key exists in the parent table.

You will notice that I have removed the outer join, and enabled referential integrity for the join between tblVehicles and tblEquip.

I strongly advise you to read up on the topic of table normalization before you attempt your next database. Also, make a point of deciding on a standard naming convention before creating your tables. Usually, foreign key fields always bear the same name as the primary key field they are linked to. This helps prevents errors like the erroneous join between your two tables with mismatched datatypes like I encountered with your db.

If there's something you don;t understand about what I've done, feel free to post back with your questions. I am slammed with work overload right now and am out in the intertidal most days during the summer so I may be slow to get back to you. But others may step in with an answer before I get back so it's worth posting. And if not, I'll try to answer when I get some free time.

Cheers,
Craig

 

[MSAccessRookie]

It also fails to compile...

I hate to sound like a totally uninformed person, but I am new to VB and cannot locate a reference to Compile. Is that somehow related to the Compact/Repair option?

 

[pam0566]

Thanks craig. when i try to add a new vehicle, and click on command to enter the equipment, everything is fine til i go to save it. then i get an error "You cannot add or change a reccord, because a related reccord is required in teh table 'tblvehicles'.

 

[ajetrumpet]

hey Pam...I used the Vehicles form to enter a record, and never got the message. is that what you're referring to ?

 

[pam0566]

No, after i enter a vehicle in the frmvehicles, i click on teh enter /view equiptment command button to enter the corresponding equip, and when i try to close the equip form i get that error.

 

[ajetrumpet]

Pam,

That is because there is a one to many relationship formed between the vehicles table, and the equipment table. You have to save the new vehicle record before you open the equipment form. Might be inconvenient, but the current setup will not do it for you unless you go about it in that way.

That's the reason for the error message...

 

[pam0566]

Aj, is there a way to put the "save" function into the same command button, so clicking on the enter equip command button will save the frmvehicles then open the other?

 

[ajetrumpet]

of course there is...just take the code that is behind the SAVE button right now, and duplicate in at the BEGINNING of all of the rest of the code behind the "open equipment" button. It's that easy.