Point me in the direction of a good resource for learning vb validation

[JFo]

the piece of equipment has not been entered though, that is why you enter a new piece of equipment



So are you saying I can't do it from one form, this is what I have been trying to do for last few days? I had it working however I had to delete the relationships for it to work

I can do it easy come to think of it with an frmAddEquipment actually however I was trying to minimise the forms for data entry

Oh well seems like a wasted a fair few hours on this one

[Rainlover]

You don't need a special form just to ADD equipment. A form of Equipment could be used to both Add and Edit Equipment.

Most of the code available for Not on List will open the form in Add Mode.

[Rainlover]

Going back to the Relationships where you checked the Casade option.

If someone accidently deletes a Primary key, All of your dependant Tables will reflect this deletion so you could lose a lot of Data.

It is your choice but I would never do it.

The Casade Update is often used by people who do not use an Auto Number as the Primary Key. So again I would never use it in your situation.

[JFo]

I uderstand add/edit is the same form

however I am confused

the majority of the time the piece of equipment will not have been inspected before

From what I understand that you are suggesting from frmInspection it opens frmEquipment which contains a list of all equipment that has already been inspected if the equipment is not on this list it opens another form (lets call frmAddEditEquipment) that enters a new piece of equipment and then back to this junction form which then opens another form that goes through check-list (eg frmEquipPressureVessel)

Is this the way you are suggesting as I was trying to do it without having a frmAddEditEquipment?

Thanks

[JFo]

Going back to the Relationships where you checked the Casade option.

If someone accidently deletes a Primary key, All of your dependant Tables will reflect this deletion so you could lose a lot of Data.

It is your choice but I would never do it.

The Casade Update is often used by people who do not use an Auto Number as the Primary Key. So again I would never use it in your situation.

So would you remove for all relationships and enforce no cascading?

[Rainlover]

So would you remove for all relationships and enforce no cascading?

I would delete the cascading.

Keep the Relationships and Enforce Referential Intregity.

[JFo]

So would you remove for all relationships and enforce no cascading?

I would delete the cascading.

Keep the Relationships and Enforce Referential Intregity.

I have taken your advice

Thanks

[Rainlover]

I would have a separate form for Equipment.

This would be populated from the Records your company currently holds.

It is also good to have a separate form to fix data entry errors.

Same as Cities, Postcodes, Sites, Contacts, Clients etc.

A separate form for each of these.

It is not reasonable to do everything from the one form.

[JFo]

Ok that was extremely easy, feel like I wasted the last few days

I have not cleaned everything up and added all the bells and whistles (this can be done whenever and is good release when I am stuck on something and I wanted to show you what I came up with as well)

everything is working and I really want to get onto the coding for adding to table results

Where do I start?

Feelin much better than last few days of frustration



Attachment 4683

[Rainlover]

You still do not have a way to add many pieces of equipment to the one Inspection.

The sub form needs to be in Continious Forms or Datasheet.

[Rainlover]

In post 54 I wrote.

I also assume it is essential to do the updating of the results table by code running through the for loop, however I don't know how

Basically correct. You would need to APPEND the questions each time you select a new piece of Equipment. Then you will need to UPDATE these new records with the Default answers that you want. Either from the previous answers of if there are no answers then use the Default Answer Table or if you prefer just leave them blank.

The code will be tricky but at the moment you have not yet selected the equipment. When you solve this dilemma then you can move onto populating the Results table.

Do you understand what I said?

The way I see it you have ONE inspection with MANY unique pieces of equipment.

As you add each piece of equipment you need to APPEND to the Results Table the appropiate Questions for that piece of equipment.

Then you need to add the default Answers. But once you have the Questions the answers will be added in a similar way.

[JFo]

You still do not have a way to add many pieces of equipment to the one Inspection.

The sub form needs to be in Continious Forms or Datasheet.

I don't understand what you mean it works for me it just keeps going back to frmInspection and then you can add another piece of equipment

Thanks

[JFo]

actually it works great if I just open frmInspections and initiate it from there, however if I open frmMainMenu and go through the process it does not keep updating the junction table

[JFo]

actually it doesn't work my bad... AARRGGHHH

so frustrating it either enters inspectionid or equipmentid or wrong inspectionid but never all together with tbl controlsource or code

The sub form needs to be in Continious Forms or Datasheet. I didn't actually use a sub-form because I can't figure them out

[JFo]

I really don't understand, I can put the junctionID and two foreign keys (inspection and equipment IDs) in other tables and in text boxes and combo boxes however can't populate the junction table with them