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

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.

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!

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 ?

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]

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

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 !

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

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

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

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--

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?

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

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?

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'.

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

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.

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...

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?

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. :)

Just the DoCmd.RunCommand acCmdSaveRecord part? or the whole thing starting from private sub - end sub

I got it.. just the one line! Great... it works! yeah!!!!!!!! Question.. someone mentioned about my data base needs to be compiled. Where do i find to do that?

i'm not sure what they're talking about Pam. I think Craig was the one that put it together, wasn't he?

To clarify, I only helped debug some of the issues Pam was having with a particular task she was wanting to achieve. I did NOT build this db.

But yes, Adam is right about the cause of the error.
You could also have alternatively used the line of code

Me.Dirty = False near the start of the command button code to force the record to be saved.


The compile error can be seen if you open a form in design view, then open the vba editor to look at some of your code. There's a menu called Debug, in the vba editor. The top item in that menu is the Compile option. This essentially runs through all your vba code looking for potential bugs and errors that might cause your db to have a hissyfit.

For example, a failure to compile will ruin any attempt to convert your Mdb file into a MDe file for distribution.

In this case, there is a letter 'p' in the code module outside of any of the subs or functions that should not be there.

Hope that explains that.

It does, and thank you both so much for helping me clear some of these problems up!!!!

I got it.. just the one line! Great... it works! yeah!!!!!!!! Question.. someone mentioned about my data base needs to be compiled. Where do i find to do that?

May I ask what exactly it was that you did to resolve this? I have a similar issue.

msrookie, what was your problem exactly? not sure i understand, other than you said similar issue.

msrookie, what was your problem exactly? not sure i understand, other than you said similar issue.

I want to prevent a user from leaving an input form without first saving the record. I have (at present), declared the form to be Modal, but would prefer another solution that saves the data (or at least asks the user to save the data via msgbox), if the form is changed without being closed (loss of focus?).

msrookie, add "DoCmd.RunCommand acCmdSaveRecord " in front of code on your close command .. should force a save before closing. i added it to a Command button that opened a form, right before the code that opened form and it works great. see earlier post from ajetrumpet.

msrookie, add "DoCmd.RunCommand acCmdSaveRecord " in front of code on your close command .. should force a save before closing. i added it to a Command button that opened a form, right before the code that opened form and it works great. see earlier post from ajetrumpet.

Ok, I put this code in and realized that a user could enter the form, and then lose focus without changing anything. This is not likely, but it might happen. What happens then, a blank record?

i am not sure, you could test it and see, i havent run into that. I would assume a blank record, but not positive, and you wouldnt want blank records in there

If your form is in data entry mode, and the user doesn't type anything into it, then no new record is created in the table.

A record is only created when the user types something or changes the state of a bound option/check box etc. However, the record is not saved to the table until the form closes, or the user moves to a new record, or if some code is triggered that explictly forces the record to be saved to the table (as in the example above)

Hopefully that helps clear up your confusion. :)