Complex Coding --> Please Help

[JFo]

Latest version, even got my validation working, very happy

Last two questions still stand as well as defaulting to last inspection values

That's me done for day, I'll be back tomorrow



thanks

Attachment 4794

[JFo]

BTW, I also saw that each piece of equipment was associated with a client. Can a piece of equipment be moved from one client to another at some point in time? If so, your tables are not properly structured to handle that possibility. It has now come to my attention that this is actually the case and I do need to allow for equipment to be moved from client--> client and from site --> site. I figure this simply means a clientEquipmentJunction table?

This will actually ruin all my forms

Thanks

[jzwp11]

Yes, modifying your table structure will impact your forms, that is why it is very important to work out your table structure first.

You are correct in that you will need something like an equipment-client junction table, but you will also have to track when the client receives the equipment and when they turn it in (now available for the next client). I don't know if you will have some equipment that will sit in storage for a time period. You would approach this much as an inventory system. Each move of equipment out or in would be a transaction to inventory. Hint: when the equipment is in your inventory, the client will be your own company

[JFo]

that is why it is very important to work out your table structure first This is seemingly impossible when the requirements change from the original constraints
Each move of equipment out or in would be a transaction to inventory. Not sure what you mean here, do I need another table... can't I just edit the equipment to transfer it to another client?

Also any thoughts on my other questions?

Thanks

[JFo]

Also I just noticed that nothing is going into tblChecklistAnswer?

[jzwp11]

Also I just noticed that nothing is going into tblChecklistAnswer?

Since you keep changing things, I'm not sure which form you are using to populate tblChecklistAnswer

that is why it is very important to work out your table structure first This is seemingly impossible when the requirements change from the original constraints

I have found that as a DB designer, it is best to discuss what functionality your users want to see and also try to anticipate what you may think they want to see in the future. It is also necessary to get a thorough understanding of the business process you are trying to model that will enable you to anticipate things to some degree. But even with all of that, you will not catch everything in your initial design, so you have to be prepared to do updates.

Each move of equipment out or in would be a transaction to inventory. Not sure what you mean here, do I need another table... can't I just edit the equipment to transfer it to another client?

I was thinking along these lines

tblEquipmentTransactions
-pkEquipTransID primary key, autonumber
-dteTrans (transaction date)
-fkEquipmentID foreign key to tblEquipment
-fkClientID foreign key to tblClients (you will have to have a record in tblClients for your own company to track equipment that you receive)

For example, if equipment #1 is sent to a client, you would have a record that has the date, equipmentID and the clientID associated with that transaction. When that client is done with the equipment, you will have another record with the date that happens, the equipmentID and your company's clientID. If you want to find out where each piece of equipment is as of today's date you would only need to find the most recent transaction date for each piece of equipment which can be done with a totals query using Max(dteTrans).

[Rainlover]

Since you keep changing things, I'm not sure which form you are using to populate tblChecklistAnswer

It may help to tidy up the code with some indentations white space etc. Currently it is vey hard to read.

Also some error trapping would be in order.

[JFo]

Since you keep changing things, I'm not sure which form you are using to populate tblChecklistAnswer Not sure myself, I am just trying to get things to work and integrate it into my system, not changed anything that was working previously intentionally. TBH I don't really understand the use of this form, maybe it is not required I will have to make sure there is any advantage in providing different common answers for each question, maybe a list of common answers over all equipment types with the allowance for unique answers for every question may suffice

It may help to tidy up the code with some indentations white space etc. Currently it is vey hard to read. OK not done any indenting you are correct I was just letting access do it automatically, I will try and do some


Also some error trapping would be in order. Again I have never done any error catching in access/ vb besides that already included in code I have ascertained, I seem to need it and will have to read up on this as well

Thanks

[JFo]

Here is latest version with indenting, hope this helps

Also would appreciate your recommendations on error catching I had a look online however not entirely sure about where I should be putting the error catching, frequency, etc

Thanks

Attachment 4819

[Rainlover]

Your indenting looks great. Not perfect but sooooooooooooooo much easier to read.

I have added some error trapping to the attached.

Have a look at the Module mdlErrorHandler. With this you can turn the error trapping On for use by the end User and Off in the development phase. The instructions are just above the First Public Const.

Then if you go to your Form frmCity and look at the first piece of Code.

Private Sub cmbStates_NotInList(NewData As String, Response As Integer)

Here you will see where I have added the Error Trapping.

If (conHandleErrors) Then On Error GoTo ErrorHandler

and at the bottom.

ExitProcedure:
Exit Sub
ErrorHandler:
DisplayError "cmbStates_NotInList", Me.Name
Resume ExitProcedure

For each Procedure change "cmbStates_NotInList" to the name of the current Procedure.

That's it.

[Rainlover]

Forgot to attach.

Also compile your Code. There is an error you need to fix.

[JFo]

Thanks Rain I will get on it now and add the error handling throughout

[Rainlover]

Have you thought about creating a work flow plan.

Think about this hypothetical.

The Boss gets a New Large Client with 600 pieces of equipment, located at 4 Different sites. You start your first Group of inspections in 6 weeks time for 30 pieces of equipment at Site "A". You then inspect another group of equipment at Site "B" in seven weeks time, and so on.

What is the easiest way to prepare for these upcoming inspections?

My approach would be to enter the New Client together with contact information and site details.

I would the add the equipment and allocate that equipment to the Client and the appropriate Site.

Finally I would create an Inspection list of the equipment to be inspected for the relevant sites for the appropriate day.

The above plan would require little or no code.

Do you have a plan that does something similar?

[JFo]

Currently I have not created a work plan as you suggest I have more created a work flow where the user can enter the information in a logical order

Historically the inspectors would be entering the data after the inspection, ideally during the inspection if possible. You could theoretically enter the client, site and main contact info however site specific contact info may only be available after the inspection

I doubt you could enter the equipment before the inspection is undertaken as I think different equipment will come to light as you move along

Allocating the equipment to the client is something that needs to be taken into consideration as now it is possible for a piece of equipment to move from client to client

Adding equipment to site is not an option in my current system as equipment table does not contain siteID

My current flow is essentially

Add or select client --> add or select site --> Add or select contact --> add or select inspection

with inspection open -->add or select equipment and add to inspection and conduct inspection on the equipment

and repeat this last process add or select equipment and add to inspection and conduct inspection on the equipment for all equipment in inspection

this is how it is designed at the moment however doesn't work perfectly yet and will obviously need to make a number of changes

Thanks

[JFo]

Thanks for Error handling module except it asks for frmNoBull, I created a blank form of this name however it just opens it everytime... Does this mean I have errors as it does not tell me what error or do I need this form?

Thanks