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

[JFo]

Thanks Rain... any quick fix for my current set-up? I have not done any queries for over 5 years I was pretty much doing all my coding in VB and all my code is on my save buttons

So Do I have to use queries? I can't even tell what tables these forms are bound to because they are bound to queries?

Sorry in over my head with this part? need to go back to basics

[Rainlover]

Open the form in design.

Go to the Data tab where you will see Record Source.

Click on the elipse. The three dots.

This will take you to the query.

There is no need to use save as the information is automatically saved when the record looses focus. (Close of Form or move to another record)

[JFo]

Back again you must be sick of me...

I am really struggling with this addition to multiple tables (with the relationships in tact). I think I have almost exactly copied your table and form design for this area and I still can't get it to work. I must be missing something as I have looked through all settings and switched multiple times and tried passing values and just keep hitting same issue.

Very frustrating. I cannot add to junction table or pass values (cannot find subform) with the relationships restored

I really want to move onto the code for filling the results table, but can't get past this

here is my current state

Attachment 4670

apologies for very little progress in this section, am I on the right track?

[Rainlover]

Just need a bit of clarity here.

Is not the object at the moment to populate the Junction Table "tblEquipmentInspectionJunction" with both foregin keys.

Namely EquipmentID and InspectionID.

If so then the Main Form would be from tblInspections and the sub be from the Join Table plus tblEquipment.

The sub would be linked to the main via InspectionID.

The lines in bold have not been tested but I think that they are correct.

I have no idea why you are using code. I can only assume that some time ago you learnt a different language and you are trying to apply this knowledge to Access.

[Rainlover]

In your Relationships you have ticked both casade options.

Do you fully understand what this does?

[JFo]

Just need a bit of clarity here.

Is not the object at the moment to populate the Junction Table "tblEquipmentInspectionJunction" with both foregin keys.

Yes that is what I am trying to do, I had it working but I had to delete the relationships

Namely EquipmentID and InspectionID.

If so then the Main Form would be from tblInspections and the sub be from the Join Table plus tblEquipment.

The sub would be linked to the main via InspectionID.

The lines in bold have not been tested but I think that they are correct.

I will try this way
I have no idea why you are using code. I can only assume that some time ago you learnt a different language and you are trying to apply this knowledge to Access.

Yes that is correct however it is also the only way I know how to do it

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

[JFo]

In your Relationships you have ticked both casade options.

Do you fully understand what this does?

No perhaps not fully I just assume that if something is deleted or updated in any of the tables that have the PK/ FK then they are cascaded or propagated through all tables that have the relationship (so you don't have to go and delete from separate tables

[JFo]

Is not the object at the moment to populate the Junction Table "tblEquipmentInspectionJunction" with both foregin keys.

Yes that is what I am trying to do, I had it working but I had to delete the relationships

Not to forget this form also needs to update the equipment table with equipmenttype, equipmentID, clientid, etc

[Rainlover]

Yes that is correct however it is also the only way I know how to do it

Well I guess now is the time to learn something different.

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.


Not to forget this form also needs to update the equipment table with equipmenttype, equipmentID, clientid, etc

I don't believe this to be correct. Why would you change the records in tblEquipment.

[Mclaren]

This is a fascinating read.

Have dowloaded the latest version and am eager to get home and go through it.

[Rainlover]

This is a fascinating read.

Have dowloaded the latest version and am eager to get home and go through it.

Have Fun.

Please feel free to jump in.

[JFo]

Not to forget this form also needs to update the equipment table with equipmenttype, equipmentID, clientid, etc

I don't believe this to be correct. Why would you change the records in tblEquipment.

Sorry I meant populate not update/ this form populates both the equipmentinspectionjunction and equipment tables is what I am trying to do

[Rainlover]

Populate with what?

[JFo]

OK we start at new inspection this is all fine started at frmInspection. For every piece of equipment in the inspection frmEquipment will open

frmEquipment will add a new piece of equipment so it must enter the relevant equipment details into tblEquipment (EquipmentID, EquipTypeID, ClientID, etc)

Furthermore the equipment must also somehow be linked to the current inspection therefore it must also enter the data into the junction table (equipmentInspectionID, equipmentID and inspectionID)

it should then open the form for the specific type of equipment which enters all values from relevant checklist into tblResults (eg frmEquipPressureVessel)

This process is to be completed for every piece of equipment in an inspection (not to make frmEquipment more difficult for now but can also edit later and equipment could already exist from previous inspection, just want to get it working the simple way for now)

[Rainlover]

The equipment Details should be entered on a completly separate Form and therefore stored in the equipment table.

All you are doing with the Junction Form and Sub Form that you are currently working on, is selecting a piece of equipment that has already been entered.

If you want you could use the "Not on List" event to open the Equipment Form.

I can send you code for that if you neeed it.