Need form to display related record not just add new

[Pamela]

On frmInterestedParty, I have a code on field ClaimParty with an On Exit
event to open frmInterestedParty2 only if ClaimParty = "Claimant". I need
this form to display the related record, if there is one, or allow me to add
one if there isn't one already. Right now, it opens the form for adding a
new record only. The forms are related by common field ClaimID which is a
number field. Here is the code I have for the OnExit event:
Private Sub Claim_Party_Exit(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmInterestedParty2"

stLinkCriteria = "[ClaimID]=" & Me![ClaimID]
If [Claim Party] = "Claimant" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!ClaimID
End If

End Sub

I also have the OnLoad event of frmInterestedParty2 set as:
Me.ClaimID = Me.OpenArgs

I'd really appreciate any suggestions anyone has! Thanks!

 

[strive4peace]

Hi Pamela,

when the form opens, it is filtered to the records matching the
filter... the problem is for new records.

if the form you are calling it from will always be open, you can set the
default value of the ClaimID control (it must be on the form even if it
is not visible) to -->

forms![formname]!ClaimID

WHERE formname is the form that has the value you wish to copu
ClaimID is the controlname on formname

OR
on the BeforeInsert event of the new form, assign ClaimID the value from
the calling form if it is open


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Pamela]

Good Morning, Crystal!

Thanks so much for your reply. I have a couple other questions about it.
I've double checked my problem and the new form is, indeed, filtered, but it
isn't finding the related records. When I open the underlying table (which I
just remembered is a junction table) it shows MANY related records for the
ClaimID - the ClaimID field does fill in automatically. So it's kind of the
opposite problem. Another thing to consider is that the original form is an
InterestedParty and the new form is another InterestedParty - just with a
different designation. It seems to me, at minimum, the new form should at
least be finding the related first interested party that was entered... Does
that make sense? I've been scratching my head about this for more than
24hrs now so thank you so much for your continued help!!

Hi Pamela,

when the form opens, it is filtered to the records matching the
filter... the problem is for new records.

if the form you are calling it from will always be open, you can set the
default value of the ClaimID control (it must be on the form even if it
is not visible) to -->

forms![formname]!ClaimID

WHERE formname is the form that has the value you wish to copu
ClaimID is the controlname on formname

OR
on the BeforeInsert event of the new form, assign ClaimID the value from
the calling form if it is open


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

On frmInterestedParty, I have a code on field ClaimParty with an On Exit
event to open frmInterestedParty2 only if ClaimParty = "Claimant". I need
this form to display the related record, if there is one, or allow me to add
one if there isn't one already. Right now, it opens the form for adding a
new record only. The forms are related by common field ClaimID which is a
number field. Here is the code I have for the OnExit event:
Private Sub Claim_Party_Exit(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmInterestedParty2"

stLinkCriteria = "[ClaimID]=" & Me![ClaimID]
If [Claim Party] = "Claimant" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!ClaimID
End If

End Sub

I also have the OnLoad event of frmInterestedParty2 set as:
Me.ClaimID = Me.OpenArgs

I'd really appreciate any suggestions anyone has! Thanks!

 

[strive4peace]

Hi Pamela,

is ClaimID defined as a lookup field in the table design?

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

~~~

as for it not finding what was just entered...

before you do the OpenForm, include this statement:

if me.dirty then me.dirty = false

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

~~~

btw, it is not a good idea to have two forms open at the same time that
are based on the same table -- unless one of the forms is not used for
changing data, in which case the form RecordSet Type property should be
set to --> Snapshot


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Good Morning, Crystal!

Thanks so much for your reply. I have a couple other questions about it.
I've double checked my problem and the new form is, indeed, filtered, but it
isn't finding the related records. When I open the underlying table (which I
just remembered is a junction table) it shows MANY related records for the
ClaimID - the ClaimID field does fill in automatically. So it's kind of the
opposite problem. Another thing to consider is that the original form is an
InterestedParty and the new form is another InterestedParty - just with a
different designation. It seems to me, at minimum, the new form should at
least be finding the related first interested party that was entered... Does
that make sense? I've been scratching my head about this for more than
24hrs now so thank you so much for your continued help!!

Hi Pamela,

when the form opens, it is filtered to the records matching the
filter... the problem is for new records.

if the form you are calling it from will always be open, you can set the
default value of the ClaimID control (it must be on the form even if it
is not visible) to -->

forms![formname]!ClaimID

WHERE formname is the form that has the value you wish to copu
ClaimID is the controlname on formname

OR
on the BeforeInsert event of the new form, assign ClaimID the value from
the calling form if it is open


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

On frmInterestedParty, I have a code on field ClaimParty with an On Exit
event to open frmInterestedParty2 only if ClaimParty = "Claimant". I need
this form to display the related record, if there is one, or allow me to add
one if there isn't one already. Right now, it opens the form for adding a
new record only. The forms are related by common field ClaimID which is a
number field. Here is the code I have for the OnExit event:
Private Sub Claim_Party_Exit(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmInterestedParty2"

stLinkCriteria = "[ClaimID]=" & Me![ClaimID]
If [Claim Party] = "Claimant" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!ClaimID
End If

End Sub

I also have the OnLoad event of frmInterestedParty2 set as:
Me.ClaimID = Me.OpenArgs

I'd really appreciate any suggestions anyone has! Thanks!

 

[Pamela]

Thanks for that piece of code, but it still isn't working. The ClaimID field
is not a lookup field in the table jtblClaimIntParty. You mentioned that it
isn't a good design to have 2 forms open from the same table so perhaps there
is a better direction. For all records I need at least one InterestedParty,
for some I need 2 Interested Parties but they need to be related to the same
ClaimID (number field). I do have a ClaimParty designation field in
tblInterestedParty (which has been the tbl for ALL interested parties). I'm
dealing with car accidents and some accidents have only 1 party involved and
some have 2. I'm still very (as you can tell) new to all of this and my
understanding of db normalization may be off...This is a brand new db that
I'm building from scratch so I'm completely open to modifications or
suggestions. Thanks again for your continued help! : )

Hi Pamela,

is ClaimID defined as a lookup field in the table design?

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

~~~

as for it not finding what was just entered...

before you do the OpenForm, include this statement:

if me.dirty then me.dirty = false

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

~~~

btw, it is not a good idea to have two forms open at the same time that
are based on the same table -- unless one of the forms is not used for
changing data, in which case the form RecordSet Type property should be
set to --> Snapshot


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Good Morning, Crystal!

Thanks so much for your reply. I have a couple other questions about it.
I've double checked my problem and the new form is, indeed, filtered, but it
isn't finding the related records. When I open the underlying table (which I
just remembered is a junction table) it shows MANY related records for the
ClaimID - the ClaimID field does fill in automatically. So it's kind of the
opposite problem. Another thing to consider is that the original form is an
InterestedParty and the new form is another InterestedParty - just with a
different designation. It seems to me, at minimum, the new form should at
least be finding the related first interested party that was entered... Does
that make sense? I've been scratching my head about this for more than
24hrs now so thank you so much for your continued help!!

Hi Pamela,

when the form opens, it is filtered to the records matching the
filter... the problem is for new records.

if the form you are calling it from will always be open, you can set the
default value of the ClaimID control (it must be on the form even if it
is not visible) to -->

forms![formname]!ClaimID

WHERE formname is the form that has the value you wish to copu
ClaimID is the controlname on formname

OR
on the BeforeInsert event of the new form, assign ClaimID the value from
the calling form if it is open


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Pamela wrote:
On frmInterestedParty, I have a code on field ClaimParty with an On Exit
event to open frmInterestedParty2 only if ClaimParty = "Claimant". I need
this form to display the related record, if there is one, or allow me to add
one if there isn't one already. Right now, it opens the form for adding a
new record only. The forms are related by common field ClaimID which is a
number field. Here is the code I have for the OnExit event:
Private Sub Claim_Party_Exit(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmInterestedParty2"

stLinkCriteria = "[ClaimID]=" & Me![ClaimID]
If [Claim Party] = "Claimant" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!ClaimID
End If

End Sub

I also have the OnLoad event of frmInterestedParty2 set as:
Me.ClaimID = Me.OpenArgs

I'd really appreciate any suggestions anyone has! Thanks!

 

[strive4peace]

Hi Pamela,

It is starting to sound like your data structure is not normalized...
Here is something you can do to document that for us so that we can
better guide you:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
'click HERE
'press F5
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub

'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by Duane Hookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub

'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~
***********************************
***********************************

Create General Module, Reference DAO Library, Run Code
---


*** How to Create a General Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste <smile>) the code in

for this code, make sure you have a reference to a Microsoft DAO Library

once the code is in the module sheet, from the menu -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

then, save. You will be prompted for a module name. Call it -->
mod_ShowFields

the code you will want to paste in starts with

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library

and goes down to the last -->

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

any line starting with a single quote ' is a comment and it will be green

as the instructions say, you will need to reference a Microsoft DAO
library (it could be selected already but you will have to look). do
this BEFORE you try to compile the code


*** DAO Library Reference ***

from the menu -->
Tools, References... from a module window

if there is not a DAO Library checked near the top of the list, scroll
to Microsoft DAO 3.6 Object Library and check it

~~~~~~~~~~~~~~~~~~~~``
once you have done this...

click in the *RunShowFieldsForAllTables* sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Pamela]

Hi Crystal!

I really hope this is what you wanted...I never got it to do anything when I
pressed Ctrl G but the screen had a split so I could see these pop up after
running it. Obviously I'm not sure what it all means... When I did the Debug
& Compile, I did get 1 error that referenced a Me.cboClaimCoID.Undo and said
that it couldn't find cboClaimCoID...I even double checked my spelling and
such and it IS a field on my form. It is part of a code I was given from
someone else to us in doing a NotInList event for my combo to add in the new
entry...The entries below aren't even (with the exception of the tblVehicle)
tables or data I'm using yet -- I just loaded them in in trying to set my
ideas up for the overall design of the db. There are no forms,
relationships, connections or even data in/to them currently. I really hope
this helps you to see something else that's going on in my db...if there's
anything else you need, don't hesitate...I just really again want to thank
you for all of your time and effort that you are putting in with me!!


21 T-Top Panels, 1 (Boolean), 1
22 T-Top Glass Panels, 1 (Boolean), 1

tblRowLocation
==========================
0 InspectionID, 4 (Long), 4
1 RowLocation, 10 (Text), 50
2 Lot#, 10 (Text), 50

tblSafety
==========================
0 Safety ID, 4 (Long), 4
1 Anti-Lock Brakes, 1 (Boolean), 1
2 Anti-Lock Brakes Rear, 1 (Boolean), 1
3 4 Wheel Disc Brakes, 1 (Boolean), 1
4 Auto Load Level, 1 (Boolean), 1
5 Dual Mirrors, 1 (Boolean), 1
6 Fog Lamps, 1 (Boolean), 1
7 Airbag, 1 (Boolean), 1
8 Dual Airbags, 1 (Boolean), 1
9 Side Airbags, 1 (Boolean), 1
10 Head Airbags, 1 (Boolean), 1
11 Rear Side Airbags, 1 (Boolean), 1
12 A Pillar Airbags, 1 (Boolean), 1
13 B Pillar Airbags, 1 (Boolean), 1
14 Rear Wiper, 1 (Boolean), 1
15 Intermittent Wipers, 1 (Boolean), 1
16 Alarm, 1 (Boolean), 1
17 LoJack, 1 (Boolean), 1

tblSeating
==========================
0 Seating ID, 4 (Long), 4
1 Cloth Seats, 1 (Boolean), 1
2 Leather Seats, 1 (Boolean), 1
3 Heated Seats, 1 (Boolean), 1
4 Heated Seats Frt & Rear, 1 (Boolean), 1
5 Bucket Seats, 1 (Boolean), 1
6 Split Bench, 1 (Boolean), 1
7 Reclining Seats, 1 (Boolean), 1
8 Vinyl Seats, 1 (Boolean), 1
9 Captains Chairs 2, 1 (Boolean), 1
10 Captains Chairs 4, 1 (Boolean), 1
11 Captains Chairs 6, 1 (Boolean), 1
12 2nd Row Captains, 1 (Boolean), 1
13 2nd Row Buckets, 1 (Boolean), 1
14 3rd Row Seats, 1 (Boolean), 1
15 Power 3rd Seat, 1 (Boolean), 1
16 Retractable Seats, 1 (Boolean), 1
17 12 Passenger Seating, 1 (Boolean), 1
18 15 Passenger Seating, 1 (Boolean), 1

tblState
==========================
0 State ID, 10 (Text), 2

tblTimeExpense
==========================
0 Time ExpenseBillID, 4 (Long), 4
1 TotalMiles, 10 (Text), 50
2 #FreeMiles, 10 (Text), 50
3 FreeMileageZone, 1 (Boolean), 1
4 Time&ExpenseHours, 10 (Text), 50
5 Parking/Tolls, 10 (Text), 50
6 #ofPhotos, 10 (Text), 50

tblTotalLoss
==========================
0 TotalLossID, 4 (Long), 4
1 ValuationReturnMethod, 10 (Text), 50
2 Request#, 10 (Text), 50
3 InstantValue, 10 (Text), 50
4 ValuationProduct, 10 (Text), 50
5 FillOutT/LForm, 10 (Text), 50
6 SalvageMovement, 10 (Text), 50
7 CollectingStorage, 10 (Text), 50
8 HiddenDamageAmt, 10 (Text), 50
9 MidNADA alue, 10 (Text), 50
10 ValuationCalledIn, 10 (Text), 50

tblTruckOption
==========================
0 TruckOptionID, 4 (Long), 4
1 5thWheel, 1 (Boolean), 1
2 TonneauCover, 1 (Boolean), 1
3 HardTonneau, 1 (Boolean), 1
4 GrilleGuard, 1 (Boolean), 1
5 Winch, 1 (Boolean), 1
6 CamperShell, 1 (Boolean), 1
7 Bedliner, 1 (Boolean), 1
8 SprayOnBedliner, 1 (Boolean), 1
9 ToolBox, 1 (Boolean), 1
10 HydraulicLiftgate, 1 (Boolean), 1
11 SlidingRearWindow, 1 (Boolean), 1
12 PowerSlidingRearWindow, 1 (Boolean), 1
13 FiberglassCap, 1 (Boolean), 1
14 3rdAccessDoor, 1 (Boolean), 1
15 ExtraTank, 1 (Boolean), 1
16 UtilityBed, 1 (Boolean), 1
17 WheelFlares, 1 (Boolean), 1
18 FlatBed, 1 (Boolean), 1
19 ExtendedMirrors, 1 (Boolean), 1

tblVanOption
==========================
0 VanOptionID, 4 (Long), 4
1 Refrigerator, 1 (Boolean), 1
2 RaisedRoof, 1 (Boolean), 1
3 Television, 1 (Boolean), 1
4 BayWindows, 1 (Boolean), 1
5 DualPowerSlideDoors, 1 (Boolean), 1
6 PowerSlideRtDoor, 1 (Boolean), 1
7 PowerSlideLtDoor, 1 (Boolean), 1
8 SlideDriverSideDoor, 1 (Boolean), 1
9 Ladder, 1 (Boolean), 1
10 MiniBlinds, 1 (Boolean), 1

tblVehicle
==========================
0 VehicleID, 4 (Long), 4
1 VehicleOptionID, 4 (Long), 4
2 InspectionID, 4 (Long), 4
3 AssignmentID, 4 (Long), 4
4 VehicleYear, 3 (Integer), 2
5 VehicleMake, 10 (Text), 50
6 VehicleModel, 10 (Text), 50
7 VehicleSubmodel, 10 (Text), 50
8 VehicleVIN, 10 (Text), 50
9 VehicleMileage, 10 (Text), 50
10 VehicleLicense, 10 (Text), 50
11 VehicleColor, 10 (Text), 50
12 VehicleBodystyle, 10 (Text), 50
13 Vehicle#ofPassengers, 10 (Text), 50
14 VehicleEngineSize, 10 (Text), 50
15 VehicleCylinders, 10 (Text), 50
16 VehicleTransOptions, 10 (Text), 50
17 VehicleTransmission, 10 (Text), 50
18 #ofGears, 10 (Text), 50
19 LicenseState, 10 (Text), 50
20 LicenseExp, 10 (Text), 50
21 Lienholder, 10 (Text), 50
22 Leased, 10 (Text), 50

tblVehicleAdjustment
==========================
0 Adjustment ID, 4 (Long), 4

tblVehicleCondition
==========================
0 Condition ID, 4 (Long), 4
1 Comments, 10 (Text), 50

tblVehicleOption
==========================
0 Vehicle Option ID, 4 (Long), 4
1 Truck Option ID, 4 (Long), 4
2 Van Option ID, 4 (Long), 4
3 Convenience Option ID, 4 (Long), 4
4 Ext/Glass Option ID, 4 (Long), 4
5 Performance ID, 4 (Long), 4
6 Power ID, 4 (Long), 4
7 Radio/End ID, 4 (Long), 4
8 Roof ID, 4 (Long), 4
9 Safety ID, 4 (Long), 4
10 Seating ID, 4 (Long), 4
11 Wheel ID, 4 (Long), 4
12 Refurbishment ID, 4 (Long), 4
13 Condition ID, 4 (Long), 4
14 Adjustment ID, 4 (Long), 4

tblVehicleType
==========================
0 VehicleTypeID, 4 (Long), 4
2 Make, 10 (Text), 50
3 Model, 10 (Text), 50

tblWheel
==========================
0 Wheel ID, 4 (Long), 4
1 Alum/Alloy Wheels, 1 (Boolean), 1
2 Locking Wheel Covers, 1 (Boolean), 1
3 Wire Wheels, 1 (Boolean), 1
4 Wire Wheel Covers, 1 (Boolean), 1
5 Spoked Alum Wheels, 1 (Boolean), 1
6 Styled Steel Wheels, 1 (Boolean), 1
7 A/M Alum/Alloy Wheels, 1 (Boolean), 1
8 Chrome Wheels, 1 (Boolean), 1
9 A/M Chrome Wheels, 1 (Boolean), 1
10 Chrome Alloy Wheels, 1 (Boolean), 1
11 Rally Wheels, 1 (Boolean), 1
12 Deluxe Wheel Covers, 1 (Boolean), 1
13 Auto Lock Hubs, 1 (Boolean), 1
14 Manual Lock Hubs, 1 (Boolean), 1
15 Wide Tires, 1 (Boolean), 1
16 Dual Rear Wheels, 1 (Boolean), 1
17 20" Or Larger Wheels, 1 (Boolean), 1
18 Full Wheel Covers, 1 (Boolean), 1
19 Spoke Aluminum Wheels, 1 (Boolean), 1
20 Locking Wheels, 1 (Boolean), 1

 

[strive4peace]

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~~
"There are no forms, relationships, connections or even data in/to them
currently. "

the good thing is that you already know pretty much what you want to
track -- you just need to structure it better before you build.

You seem to be starting forms ... don't. Get the structure right first
or you are just wasting your time. Also, before forms, you need to set
up relationships.

Read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

pay close attention to the Normalization and Relationship sections


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

*
have an awesome day
*

 

[Pamela]

Hi Crystal!

Thanks for the table structure help...I'll be sure to make those changes
before I get into any of the forms for them. Did you find anything that
would help with the tables I working with currently? My db consists of a
total of 43 tables and more than half weren't mentioned in that report/module
I pasted. (I do apologize as someone else corrected my use of "field" when
referring to a "control" so I'll be sure to try to get that right for you).
Do you have any other ideas of how I can set this up differently to be able
to handle my car accident parties (insureds & claimants)? Thanks again, I
recognize you put a lot of time and thought into this already for me!!

Pamela

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~~
"There are no forms, relationships, connections or even data in/to them
currently. "

the good thing is that you already know pretty much what you want to
track -- you just need to structure it better before you build.

You seem to be starting forms ... don't. Get the structure right first
or you are just wasting your time. Also, before forms, you need to set
up relationships.

Read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

pay close attention to the Normalization and Relationship sections


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

*
have an awesome day
*

Hi Crystal!

I really hope this is what you wanted...I never got it to do anything when I
pressed Ctrl G but the screen had a split so I could see these pop up after
running it. Obviously I'm not sure what it all means... When I did the Debug
& Compile, I did get 1 error that referenced a Me.cboClaimCoID.Undo and said
that it couldn't find cboClaimCoID...I even double checked my spelling and
such and it IS a field on my form. It is part of a code I was given from
someone else to us in doing a NotInList event for my combo to add in the new
entry...The entries below aren't even (with the exception of the tblVehicle)
tables or data I'm using yet -- I just loaded them in in trying to set my
ideas up for the overall design of the db. There are no forms,
relationships, connections or even data in/to them currently. I really hope
this helps you to see something else that's going on in my db...if there's
anything else you need, don't hesitate...I just really again want to thank
you for all of your time and effort that you are putting in with me!!


21 T-Top Panels, 1 (Boolean), 1
22 T-Top Glass Panels, 1 (Boolean), 1

tblRowLocation
==========================
0 InspectionID, 4 (Long), 4
1 RowLocation, 10 (Text), 50
2 Lot#, 10 (Text), 50

tblSafety
==========================
0 Safety ID, 4 (Long), 4
1 Anti-Lock Brakes, 1 (Boolean), 1
2 Anti-Lock Brakes Rear, 1 (Boolean), 1
3 4 Wheel Disc Brakes, 1 (Boolean), 1
4 Auto Load Level, 1 (Boolean), 1
5 Dual Mirrors, 1 (Boolean), 1
6 Fog Lamps, 1 (Boolean), 1
7 Airbag, 1 (Boolean), 1
8 Dual Airbags, 1 (Boolean), 1
9 Side Airbags, 1 (Boolean), 1
10 Head Airbags, 1 (Boolean), 1
11 Rear Side Airbags, 1 (Boolean), 1
12 A Pillar Airbags, 1 (Boolean), 1
13 B Pillar Airbags, 1 (Boolean), 1
14 Rear Wiper, 1 (Boolean), 1
15 Intermittent Wipers, 1 (Boolean), 1
16 Alarm, 1 (Boolean), 1
17 LoJack, 1 (Boolean), 1

tblSeating
==========================
0 Seating ID, 4 (Long), 4
1 Cloth Seats, 1 (Boolean), 1
2 Leather Seats, 1 (Boolean), 1
3 Heated Seats, 1 (Boolean), 1
4 Heated Seats Frt & Rear, 1 (Boolean), 1
5 Bucket Seats, 1 (Boolean), 1
6 Split Bench, 1 (Boolean), 1
7 Reclining Seats, 1 (Boolean), 1
8 Vinyl Seats, 1 (Boolean), 1
9 Captains Chairs 2, 1 (Boolean), 1
10 Captains Chairs 4, 1 (Boolean), 1
11 Captains Chairs 6, 1 (Boolean), 1
12 2nd Row Captains, 1 (Boolean), 1
13 2nd Row Buckets, 1 (Boolean), 1
14 3rd Row Seats, 1 (Boolean), 1
15 Power 3rd Seat, 1 (Boolean), 1
16 Retractable Seats, 1 (Boolean), 1
17 12 Passenger Seating, 1 (Boolean), 1
18 15 Passenger Seating, 1 (Boolean), 1

tblState
==========================
0 State ID, 10 (Text), 2

tblTimeExpense
==========================
0 Time ExpenseBillID, 4 (Long), 4
1 TotalMiles, 10 (Text), 50
2 #FreeMiles, 10 (Text), 50
3 FreeMileageZone, 1 (Boolean), 1
4 Time&ExpenseHours, 10 (Text), 50
5 Parking/Tolls, 10 (Text), 50
6 #ofPhotos, 10 (Text), 50

tblTotalLoss
==========================
0 TotalLossID, 4 (Long), 4
1 ValuationReturnMethod, 10 (Text), 50
2 Request#, 10 (Text), 50
3 InstantValue, 10 (Text), 50
4 ValuationProduct, 10 (Text), 50
5 FillOutT/LForm, 10 (Text), 50
6 SalvageMovement, 10 (Text), 50
7 CollectingStorage, 10 (Text), 50
8 HiddenDamageAmt, 10 (Text), 50
9 MidNADA alue, 10 (Text), 50
10 ValuationCalledIn, 10 (Text), 50

tblTruckOption
==========================
0 TruckOptionID, 4 (Long), 4
1 5thWheel, 1 (Boolean), 1
2 TonneauCover, 1 (Boolean), 1
3 HardTonneau, 1 (Boolean), 1
4 GrilleGuard, 1 (Boolean), 1
5 Winch, 1 (Boolean), 1
6 CamperShell, 1 (Boolean), 1
7 Bedliner, 1 (Boolean), 1
8 SprayOnBedliner, 1 (Boolean), 1
9 ToolBox, 1 (Boolean), 1
10 HydraulicLiftgate, 1 (Boolean), 1
11 SlidingRearWindow, 1 (Boolean), 1
12 PowerSlidingRearWindow, 1 (Boolean), 1
13 FiberglassCap, 1 (Boolean), 1
14 3rdAccessDoor, 1 (Boolean), 1
15 ExtraTank, 1 (Boolean), 1
16 UtilityBed, 1 (Boolean), 1
17 WheelFlares, 1 (Boolean), 1

 

[strive4peace]

Hi Pamela,

applying the same principles... keep separate things in separate
tables... think about your car accident information. You didn't list
those tables, so I did not comment on them -- would help if you would
list them... to just get those tables: make a blank database, link to
the tables where your information is, put the code in, and run it.

I am taking a guess here...

Think of the nouns that you have; each of these is a separate table

Vehicles
Accidents
Accidentees (don't know what to call them... people?)
Insurance Policies
Claims

....once I see your fields, I can give you better information <smile>


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!

Thanks for the table structure help...I'll be sure to make those changes
before I get into any of the forms for them. Did you find anything that
would help with the tables I working with currently? My db consists of a
total of 43 tables and more than half weren't mentioned in that report/module
I pasted. (I do apologize as someone else corrected my use of "field" when
referring to a "control" so I'll be sure to try to get that right for you).
Do you have any other ideas of how I can set this up differently to be able
to handle my car accident parties (insureds & claimants)? Thanks again, I
recognize you put a lot of time and thought into this already for me!!

Pamela

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~~
"There are no forms, relationships, connections or even data in/to them
currently. "

the good thing is that you already know pretty much what you want to
track -- you just need to structure it better before you build.

You seem to be starting forms ... don't. Get the structure right first
or you are just wasting your time. Also, before forms, you need to set
up relationships.

Read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

pay close attention to the Normalization and Relationship sections


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

*
have an awesome day
*

Hi Crystal!

I really hope this is what you wanted...I never got it to do anything when I
pressed Ctrl G but the screen had a split so I could see these pop up after
running it. Obviously I'm not sure what it all means... When I did the Debug
& Compile, I did get 1 error that referenced a Me.cboClaimCoID.Undo and said
that it couldn't find cboClaimCoID...I even double checked my spelling and
such and it IS a field on my form. It is part of a code I was given from
someone else to us in doing a NotInList event for my combo to add in the new
entry...The entries below aren't even (with the exception of the tblVehicle)
tables or data I'm using yet -- I just loaded them in in trying to set my
ideas up for the overall design of the db. There are no forms,
relationships, connections or even data in/to them currently. I really hope
this helps you to see something else that's going on in my db...if there's
anything else you need, don't hesitate...I just really again want to thank
you for all of your time and effort that you are putting in with me!!


21 T-Top Panels, 1 (Boolean), 1
22 T-Top Glass Panels, 1 (Boolean), 1

tblRowLocation
==========================
0 InspectionID, 4 (Long), 4
1 RowLocation, 10 (Text), 50
2 Lot#, 10 (Text), 50

tblSafety
==========================
0 Safety ID, 4 (Long), 4
1 Anti-Lock Brakes, 1 (Boolean), 1
2 Anti-Lock Brakes Rear, 1 (Boolean), 1
3 4 Wheel Disc Brakes, 1 (Boolean), 1
4 Auto Load Level, 1 (Boolean), 1
5 Dual Mirrors, 1 (Boolean), 1
6 Fog Lamps, 1 (Boolean), 1
7 Airbag, 1 (Boolean), 1
8 Dual Airbags, 1 (Boolean), 1
9 Side Airbags, 1 (Boolean), 1
10 Head Airbags, 1 (Boolean), 1
11 Rear Side Airbags, 1 (Boolean), 1
12 A Pillar Airbags, 1 (Boolean), 1
13 B Pillar Airbags, 1 (Boolean), 1
14 Rear Wiper, 1 (Boolean), 1
15 Intermittent Wipers, 1 (Boolean), 1
16 Alarm, 1 (Boolean), 1
17 LoJack, 1 (Boolean), 1

tblSeating
==========================
0 Seating ID, 4 (Long), 4
1 Cloth Seats, 1 (Boolean), 1
2 Leather Seats, 1 (Boolean), 1
3 Heated Seats, 1 (Boolean), 1
4 Heated Seats Frt & Rear, 1 (Boolean), 1
5 Bucket Seats, 1 (Boolean), 1
6 Split Bench, 1 (Boolean), 1
7 Reclining Seats, 1 (Boolean), 1
8 Vinyl Seats, 1 (Boolean), 1
9 Captains Chairs 2, 1 (Boolean), 1
10 Captains Chairs 4, 1 (Boolean), 1
11 Captains Chairs 6, 1 (Boolean), 1
12 2nd Row Captains, 1 (Boolean), 1
13 2nd Row Buckets, 1 (Boolean), 1
14 3rd Row Seats, 1 (Boolean), 1
15 Power 3rd Seat, 1 (Boolean), 1
16 Retractable Seats, 1 (Boolean), 1
17 12 Passenger Seating, 1 (Boolean), 1
18 15 Passenger Seating, 1 (Boolean), 1

tblState
==========================
0 State ID, 10 (Text), 2

tblTimeExpense
==========================
0 Time ExpenseBillID, 4 (Long), 4
1 TotalMiles, 10 (Text), 50
2 #FreeMiles, 10 (Text), 50
3 FreeMileageZone, 1 (Boolean), 1
4 Time&ExpenseHours, 10 (Text), 50
5 Parking/Tolls, 10 (Text), 50
6 #ofPhotos, 10 (Text), 50

tblTotalLoss
==========================
0 TotalLossID, 4 (Long), 4
1 ValuationReturnMethod, 10 (Text), 50
2 Request#, 10 (Text), 50
3 InstantValue, 10 (Text), 50
4 ValuationProduct, 10 (Text), 50
5 FillOutT/LForm, 10 (Text), 50
6 SalvageMovement, 10 (Text), 50
7 CollectingStorage, 10 (Text), 50
8 HiddenDamageAmt, 10 (Text), 50
9 MidNADA alue, 10 (Text), 50
10 ValuationCalledIn, 10 (Text), 50

tblTruckOption
==========================
0 TruckOptionID, 4 (Long), 4
1 5thWheel, 1 (Boolean), 1
2 TonneauCover, 1 (Boolean), 1
3 HardTonneau, 1 (Boolean), 1
4 GrilleGuard, 1 (Boolean), 1
5 Winch, 1 (Boolean), 1
6 CamperShell, 1 (Boolean), 1
7 Bedliner, 1 (Boolean), 1
8 SprayOnBedliner, 1 (Boolean), 1
9 ToolBox, 1 (Boolean), 1
10 HydraulicLiftgate, 1 (Boolean), 1
11 SlidingRearWindow, 1 (Boolean), 1
12 PowerSlidingRearWindow, 1 (Boolean), 1
13 FiberglassCap, 1 (Boolean), 1
14 3rdAccessDoor, 1 (Boolean), 1
15 ExtraTank, 1 (Boolean), 1
16 UtilityBed, 1 (Boolean), 1
17 WheelFlares, 1 (Boolean), 1

 

[Pamela]

Hi Crystal!
Thinking in terms of nouns is very helpful. I think (I hope) I've done a
pretty good job on these. I'll list some of them out here for you. I don't
understand much of what your code did but since all of my tables are in the
same db, why didn't these other tables get included? I would do as you
suggested with creating a new db and linking the tables but I don't know how
to "link" the tables...sorry.
tblClaim-ClaimID(pk),AdjusterID,InsuranceCoID,ClaimNumber,DateofLoss,LossType, PolicyNumber, LossCity, LossState, LossZip, LossFacts
tblClientCo-ClientCoID(pk),ClientCoName,ClientCoAddress,ClientCoCity,
ClientCoState, ClientCoZip, ClientCoPhone, ClientCoFax,ClientCoEmail,
ClientCoType
tblAdjuster-AdjusterID(pk),AdjusterFirstName,AdjusterLastName,AdjusterPhone,
AdjusterExtn,AdjusterEmail
tblAssignment-AssignmentID(pk),ClaimID(fk), AssignmentNumber,DateReceived,
DateCompleted,AssignmentType,TimeReceived
jtblClaimIntParty-ClaimID(pk),InterestedPartyID(pk)-this is a junction table
between the Claim and Interested Parties (or the people) which have a
many-to-many relationship and each claim can have many parties and each party
could have many claims
tblInterestedParty-InterestedPartyIDpk),FirstName,LastName,Address,City,State, Zip, Phone, Phone2, Phone3, ClaimParty(to designate insured or claimant)
tblVehicle-VehicleID(pk), AssignmentID(fk), Year, Make, Model, Submodel,
VIN, Mileage, License, Bodystyle, Enginesize, Cylinders, Transmission
tblInspection-InspectionID(pk), VehicleID(fk), AppraiserID(fk),
DateInspected, OwnerPresent, InspectionType
tblInspectionLocation-InspLocID(pk),InspectionID(fk), InspectionLocation,
InspectionLocationName, InspectionAddress,InspectionCity,
InspectionState,InspectionZip, InspectionPhone, InspectionContact
tblRepairer-RepairerID(pk),Name, Address, City, State, Zip, Phone, Fax, TIN,
Contact, RepairerType
jtblInspRepairer - InspLocID, RepairerID - again, this is a junction table
as this is a many-to-many relationship.
These are the tables I'm working with now. I'll try to break it down for
you as far as our business...maybe this will help you to see what's happening
in reference to my InterestedParty problem. Or, again, if you have a better
idea of how to run that. I had thought to have separate tables for insureds
and claimants but that seemed to go against the normalization rules I was
reading.
Thank you again, you really have been such a jewel and have an immense
amount of patience!

Hi Pamela,

applying the same principles... keep separate things in separate
tables... think about your car accident information. You didn't list
those tables, so I did not comment on them -- would help if you would
list them... to just get those tables: make a blank database, link to
the tables where your information is, put the code in, and run it.

I am taking a guess here...

Think of the nouns that you have; each of these is a separate table

Vehicles
Accidents
Accidentees (don't know what to call them... people?)
Insurance Policies
Claims

....once I see your fields, I can give you better information <smile>


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!

Thanks for the table structure help...I'll be sure to make those changes
before I get into any of the forms for them. Did you find anything that
would help with the tables I working with currently? My db consists of a
total of 43 tables and more than half weren't mentioned in that report/module
I pasted. (I do apologize as someone else corrected my use of "field" when
referring to a "control" so I'll be sure to try to get that right for you).
Do you have any other ideas of how I can set this up differently to be able
to handle my car accident parties (insureds & claimants)? Thanks again, I
recognize you put a lot of time and thought into this already for me!!

Pamela

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~~
"There are no forms, relationships, connections or even data in/to them
currently. "

the good thing is that you already know pretty much what you want to
track -- you just need to structure it better before you build.

You seem to be starting forms ... don't. Get the structure right first
or you are just wasting your time. Also, before forms, you need to set
up relationships.

Read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

pay close attention to the Normalization and Relationship sections


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

*
have an awesome day
*





Pamela wrote:
Hi Crystal!

I really hope this is what you wanted...I never got it to do anything when I
pressed Ctrl G but the screen had a split so I could see these pop up after
running it. Obviously I'm not sure what it all means... When I did the Debug
& Compile, I did get 1 error that referenced a Me.cboClaimCoID.Undo and said
that it couldn't find cboClaimCoID...I even double checked my spelling and
such and it IS a field on my form. It is part of a code I was given from
someone else to us in doing a NotInList event for my combo to add in the new
entry...The entries below aren't even (with the exception of the tblVehicle)
tables or data I'm using yet -- I just loaded them in in trying to set my
ideas up for the overall design of the db. There are no forms,
relationships, connections or even data in/to them currently. I really hope
this helps you to see something else that's going on in my db...if there's
anything else you need, don't hesitate...I just really again want to thank
you for all of your time and effort that you are putting in with me!!


21 T-Top Panels, 1 (Boolean), 1
22 T-Top Glass Panels, 1 (Boolean), 1

tblRowLocation
==========================
0 InspectionID, 4 (Long), 4
1 RowLocation, 10 (Text), 50
2 Lot#, 10 (Text), 50

tblSafety
==========================
0 Safety ID, 4 (Long), 4
1 Anti-Lock Brakes, 1 (Boolean), 1
2 Anti-Lock Brakes Rear, 1 (Boolean), 1
3 4 Wheel Disc Brakes, 1 (Boolean), 1
4 Auto Load Level, 1 (Boolean), 1
5 Dual Mirrors, 1 (Boolean), 1
6 Fog Lamps, 1 (Boolean), 1
7 Airbag, 1 (Boolean), 1
8 Dual Airbags, 1 (Boolean), 1
9 Side Airbags, 1 (Boolean), 1
10 Head Airbags, 1 (Boolean), 1
11 Rear Side Airbags, 1 (Boolean), 1
12 A Pillar Airbags, 1 (Boolean), 1
13 B Pillar Airbags, 1 (Boolean), 1
14 Rear Wiper, 1 (Boolean), 1
15 Intermittent Wipers, 1 (Boolean), 1
16 Alarm, 1 (Boolean), 1
17 LoJack, 1 (Boolean), 1

tblSeating
==========================
0 Seating ID, 4 (Long), 4
1 Cloth Seats, 1 (Boolean), 1
2 Leather Seats, 1 (Boolean), 1
3 Heated Seats, 1 (Boolean), 1
4 Heated Seats Frt & Rear, 1 (Boolean), 1
5 Bucket Seats, 1 (Boolean), 1
6 Split Bench, 1 (Boolean), 1
7 Reclining Seats, 1 (Boolean), 1
8 Vinyl Seats, 1 (Boolean), 1
9 Captains Chairs 2, 1 (Boolean), 1
10 Captains Chairs 4, 1 (Boolean), 1

 

[strive4peace]

Hi Pamela,

thank you ... but let me tell you how to link to tables -- would also
like to see data type and size...

1. make a blank database

2. Link to the tables you want to document

~~~
with Access 2003 or lower:
3. File, Get External Data, Link Tables...
4. navigate to your database
5. select the tables you want
6. Link
~~~

with Access 2007:
3. External data tab on Ribbon
4. click Access icon in the Import group
5. choose --> link to data source by creating a linked table
6. click the Browse button to choose the database file
7. click OK to see a list of tables

~~~

then, create the general module again, compile the code, then run it,
and paste what is in the Immediate window into your message <smile>


the reason they did not show up in the Debug (Immediate) window is that
the number of lines are limited... so they actually got analyzed, but
then it was overwritten since you have so many tables!


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!
Thinking in terms of nouns is very helpful. I think (I hope) I've done a
pretty good job on these. I'll list some of them out here for you. I don't
understand much of what your code did but since all of my tables are in the
same db, why didn't these other tables get included? I would do as you
suggested with creating a new db and linking the tables but I don't know how
to "link" the tables...sorry.
tblClaim-ClaimID(pk),AdjusterID,InsuranceCoID,ClaimNumber,DateofLoss,LossType, PolicyNumber, LossCity, LossState, LossZip, LossFacts
tblClientCo-ClientCoID(pk),ClientCoName,ClientCoAddress,ClientCoCity,
ClientCoState, ClientCoZip, ClientCoPhone, ClientCoFax,ClientCoEmail,
ClientCoType
tblAdjuster-AdjusterID(pk),AdjusterFirstName,AdjusterLastName,AdjusterPhone,
AdjusterExtn,AdjusterEmail
tblAssignment-AssignmentID(pk),ClaimID(fk), AssignmentNumber,DateReceived,
DateCompleted,AssignmentType,TimeReceived
jtblClaimIntParty-ClaimID(pk),InterestedPartyID(pk)-this is a junction table
between the Claim and Interested Parties (or the people) which have a
many-to-many relationship and each claim can have many parties and each party
could have many claims.
tblInterestedParty-InterestedPartyIDpk),FirstName,LastName,Address,City,State, Zip, Phone, Phone2, Phone3, ClaimParty(to designate insured or claimant)
tblVehicle-VehicleID(pk), AssignmentID(fk), Year, Make, Model, Submodel,
VIN, Mileage, License, Bodystyle, Enginesize, Cylinders, Transmission
tblInspection-InspectionID(pk), VehicleID(fk), AppraiserID(fk),
DateInspected, OwnerPresent, InspectionType
tblInspectionLocation-InspLocID(pk),InspectionID(fk), InspectionLocation,
InspectionLocationName, InspectionAddress,InspectionCity,
InspectionState,InspectionZip, InspectionPhone, InspectionContact
tblRepairer-RepairerID(pk),Name, Address, City, State, Zip, Phone, Fax, TIN,
Contact, RepairerType
jtblInspRepairer - InspLocID, RepairerID - again, this is a junction table
as this is a many-to-many relationship.
These are the tables I'm working with now. I'll try to break it down for
you as far as our business...maybe this will help you to see what's happening
in reference to my InterestedParty problem. Or, again, if you have a better
idea of how to run that. I had thought to have separate tables for insureds
and claimants but that seemed to go against the normalization rules I was
reading.
Thank you again, you really have been such a jewel and have an immense
amount of patience!

Hi Pamela,

applying the same principles... keep separate things in separate
tables... think about your car accident information. You didn't list
those tables, so I did not comment on them -- would help if you would
list them... to just get those tables: make a blank database, link to
the tables where your information is, put the code in, and run it.

I am taking a guess here...

Think of the nouns that you have; each of these is a separate table

Vehicles
Accidents
Accidentees (don't know what to call them... people?)
Insurance Policies
Claims

....once I see your fields, I can give you better information <smile>


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!

Thanks for the table structure help...I'll be sure to make those changes
before I get into any of the forms for them. Did you find anything that
would help with the tables I working with currently? My db consists of a
total of 43 tables and more than half weren't mentioned in that report/module
I pasted. (I do apologize as someone else corrected my use of "field" when
referring to a "control" so I'll be sure to try to get that right for you).
Do you have any other ideas of how I can set this up differently to be able
to handle my car accident parties (insureds & claimants)? Thanks again, I
recognize you put a lot of time and thought into this already for me!!

Pamela

:

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~~
"There are no forms, relationships, connections or even data in/to them
currently. "

the good thing is that you already know pretty much what you want to
track -- you just need to structure it better before you build.

You seem to be starting forms ... don't. Get the structure right first
or you are just wasting your time. Also, before forms, you need to set
up relationships.

Read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

pay close attention to the Normalization and Relationship sections


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

*
have an awesome day
*





Pamela wrote:
Hi Crystal!

I really hope this is what you wanted...I never got it to do anything when I
pressed Ctrl G but the screen had a split so I could see these pop up after
running it. Obviously I'm not sure what it all means... When I did the Debug
& Compile, I did get 1 error that referenced a Me.cboClaimCoID.Undo and said
that it couldn't find cboClaimCoID...I even double checked my spelling and
such and it IS a field on my form. It is part of a code I was given from
someone else to us in doing a NotInList event for my combo to add in the new
entry...The entries below aren't even (with the exception of the tblVehicle)
tables or data I'm using yet -- I just loaded them in in trying to set my
ideas up for the overall design of the db. There are no forms,
relationships, connections or even data in/to them currently. I really hope
this helps you to see something else that's going on in my db...if there's
anything else you need, don't hesitate...I just really again want to thank
you for all of your time and effort that you are putting in with me!!


21 T-Top Panels, 1 (Boolean), 1
22 T-Top Glass Panels, 1 (Boolean), 1

tblRowLocation
==========================
0 InspectionID, 4 (Long), 4
1 RowLocation, 10 (Text), 50
2 Lot#, 10 (Text), 50

tblSafety
==========================
0 Safety ID, 4 (Long), 4
1 Anti-Lock Brakes, 1 (Boolean), 1
2 Anti-Lock Brakes Rear, 1 (Boolean), 1
3 4 Wheel Disc Brakes, 1 (Boolean), 1
4 Auto Load Level, 1 (Boolean), 1
5 Dual Mirrors, 1 (Boolean), 1
6 Fog Lamps, 1 (Boolean), 1
7 Airbag, 1 (Boolean), 1
8 Dual Airbags, 1 (Boolean), 1
9 Side Airbags, 1 (Boolean), 1
10 Head Airbags, 1 (Boolean), 1
11 Rear Side Airbags, 1 (Boolean), 1
12 A Pillar Airbags, 1 (Boolean), 1
13 B Pillar Airbags, 1 (Boolean), 1
14 Rear Wiper, 1 (Boolean), 1
15 Intermittent Wipers, 1 (Boolean), 1
16 Alarm, 1 (Boolean), 1
17 LoJack, 1 (Boolean), 1

tblSeating
==========================
0 Seating ID, 4 (Long), 4
1 Cloth Seats, 1 (Boolean), 1
2 Leather Seats, 1 (Boolean), 1
3 Heated Seats, 1 (Boolean), 1
4 Heated Seats Frt & Rear, 1 (Boolean), 1
5 Bucket Seats, 1 (Boolean), 1
6 Split Bench, 1 (Boolean), 1
7 Reclining Seats, 1 (Boolean), 1
8 Vinyl Seats, 1 (Boolean), 1
9 Captains Chairs 2, 1 (Boolean), 1
10 Captains Chairs 4, 1 (Boolean), 1

 

[Pamela]

Hi Crystal!

Here is the result that code produced. I really appreciated also learning
to import those tables...just another great tip you've given!!

jtblClaimIntParty
==========================
0 ClaimID, 4 (Long), 4
1 InterestedPartyID, 4 (Long), 4

jtblInspRepairer
==========================
0 InspLocID, 4 (Long), 4
1 RepairerID, 4 (Long), 4

tblAdjuster
==========================
0 AdjusterID, 4 (Long), 4
1 CompanyID, 4 (Long), 4
2 AdjusterFirstName, 10 (Text), 50
3 AdjusterLastName, 10 (Text), 50
4 AdjusterPhone, 10 (Text), 50
5 AdjusterExtn, 10 (Text), 50
6 AdjusterFax, 10 (Text), 50
7 AdjusterEmail, 10 (Text), 50

tblAppraiser
==========================
0 Appraiser ID, 4 (Long), 4
1 AppaiserFirstName, 10 (Text), 50
2 AppraiserLastName, 10 (Text), 50
3 AppraiserAddress, 10 (Text), 50
4 AppraiserCity, 10 (Text), 50
5 State, 10 (Text), 50
6 AppraiserZip, 10 (Text), 50
7 AppraiserPhone, 10 (Text), 50
8 AppraiserCell, 10 (Text), 50
9 AppraiserFax, 10 (Text), 50

tblAssignment
==========================
0 AssignmentID, 4 (Long), 4
1 ClaimCoID, 4 (Long), 4
2 ClaimID, 4 (Long), 4
3 VehicleID, 4 (Long), 4
4 Time&ExpenseID, 4 (Long), 4
5 AssignmentNumber, 10 (Text), 50
6 DateAssignmentReceived, 10 (Text), 50
7 DateAssignmentCompleted, 10 (Text), 50
8 AppraisalType, 10 (Text), 50
9 CAPNumber, 10 (Text), 50
10 TimeAssignmentReceived, 10 (Text), 50

tblCity
==========================
0 CityName, 10 (Text), 50

tblClaim
==========================
0 ClaimID, 4 (Long), 4
1 AdjusterID, 4 (Long), 4
3 InsuranceCoID, 4 (Long), 4
5 PoliceReportID, 4 (Long), 4
6 ClaimNumber, 10 (Text), 50
7 DateofLoss, 10 (Text), 50
8 LossType, 10 (Text), 50
9 PolicyNumber, 10 (Text), 50
10 OktoPay, 10 (Text), 50
11 LossCity, 10 (Text), 50
12 LossState, 10 (Text), 50
13 LossZip, 10 (Text), 50
14 PointofImpact, 10 (Text), 50
15 SecondaryPointofImpact, 10 (Text), 50
16 FactsofLoss, 12 (Memo), 0

tblClientCo
==========================
0 ClientCoID, 4 (Long), 4
1 ClientCoName, 10 (Text), 50
2 ClientCoAddress, 10 (Text), 50
3 ClientCoCity, 10 (Text), 50
4 ClientCoState, 10 (Text), 50
5 ClientCoZip, 10 (Text), 50
6 ClientCoPhone, 10 (Text), 50
7 ClientCoFax, 10 (Text), 50
8 ClientCoEmail, 10 (Text), 50
9 CompanyType, 10 (Text), 50

tblInspection
==========================
0 InspectionID, 4 (Long), 4
1 AppraiserID, 4 (Long), 4
2 AssignmentID, 4 (Long), 4
3 VehicleID, 4 (Long), 4
4 PropertyID, 4 (Long), 4
5 RepairerID, 4 (Long), 4
6 DateInspected, 10 (Text), 50
7 OwnerPresent, 10 (Text), 50
8 Lot#, 10 (Text), 50
9 RowLocation, 10 (Text), 50
10 InspectionType, 10 (Text), 50
11 InterestedPartyID, 4 (Long), 4

tblInspectionLocation
==========================
0 InspLocID, 4 (Long), 4
1 InspectionID, 4 (Long), 4
2 RepairerID, 4 (Long), 4
3 InspectionLocation, 10 (Text), 50
4 InspectionLocationName, 10 (Text), 50
5 InspectionAddress, 10 (Text), 50
6 InspectionCity, 10 (Text), 50
7 InspectionState, 10 (Text), 50
8 InspectionZip, 10 (Text), 50
9 InspectionPhone, 10 (Text), 50
10 InspectionContact, 10 (Text), 50

tblInterestedParty
==========================
0 InterestedPartyID, 4 (Long), 4
1 InterestedPartyFirstName, 10 (Text), 50
2 InterestedPartyLastName, 10 (Text), 50
3 InterestedPartySuffix, 10 (Text), 50
4 InterestedPartyAddress, 10 (Text), 50
5 InterestedPartyCity, 10 (Text), 50
6 InterestedPartyState, 10 (Text), 50
7 InterestedPartyZip, 10 (Text), 50
8 InterestedPartyPhone 1, 10 (Text), 50
9 InterestedPartyPhone 2, 10 (Text), 50
10 InterestedPartyPhone 3, 10 (Text), 50
11 InterestedPartyAddlContact Name, 10 (Text), 50
12 Claim Party, 10 (Text), 50

tblRepairer
==========================
0 RepairerID, 4 (Long), 4
1 RepairerType, 10 (Text), 50
2 RepairerName, 10 (Text), 50
3 RepairerAddress, 10 (Text), 50
4 RepairerStreet, 10 (Text), 50
5 RepairerCity, 10 (Text), 50
6 RepairerState, 10 (Text), 50
7 RepairerZip, 10 (Text), 50
8 RepairerPhone, 10 (Text), 50
9 RepairerFax, 10 (Text), 50
10 RepairerTIN, 10 (Text), 50

tblVehicle
==========================
0 VehicleID, 4 (Long), 4
1 VehicleOptionID, 4 (Long), 4
2 InspectionID, 4 (Long), 4
3 AssignmentID, 4 (Long), 4
4 VehicleYear, 3 (Integer), 2
5 VehicleMake, 10 (Text), 50
6 VehicleModel, 10 (Text), 50
7 VehicleSubmodel, 10 (Text), 50
8 VehicleVIN, 10 (Text), 50
9 VehicleMileage, 10 (Text), 50
10 VehicleLicense, 10 (Text), 50
11 VehicleColor, 10 (Text), 50
12 VehicleBodystyle, 10 (Text), 50
13 Vehicle#ofPassengers, 10 (Text), 50
14 VehicleEngineSize, 10 (Text), 50
15 VehicleCylinders, 10 (Text), 50
16 VehicleTransOptions, 10 (Text), 50
17 VehicleTransmission, 10 (Text), 50
18 #ofGears, 10 (Text), 50
19 LicenseState, 10 (Text), 50
20 LicenseExp, 10 (Text), 50
21 Lienholder, 10 (Text), 50
22 Leased, 10 (Text), 50

Hi Pamela,

thank you ... but let me tell you how to link to tables -- would also
like to see data type and size...

1. make a blank database

2. Link to the tables you want to document

~~~
with Access 2003 or lower:
3. File, Get External Data, Link Tables...
4. navigate to your database
5. select the tables you want
6. Link
~~~

with Access 2007:
3. External data tab on Ribbon
4. click Access icon in the Import group
5. choose --> link to data source by creating a linked table
6. click the Browse button to choose the database file
7. click OK to see a list of tables

~~~

then, create the general module again, compile the code, then run it,
and paste what is in the Immediate window into your message <smile>


the reason they did not show up in the Debug (Immediate) window is that
the number of lines are limited... so they actually got analyzed, but
then it was overwritten since you have so many tables!


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!
Thinking in terms of nouns is very helpful. I think (I hope) I've done a
pretty good job on these. I'll list some of them out here for you. I don't
understand much of what your code did but since all of my tables are in the
same db, why didn't these other tables get included? I would do as you
suggested with creating a new db and linking the tables but I don't know how
to "link" the tables...sorry.
tblClaim-ClaimID(pk),AdjusterID,InsuranceCoID,ClaimNumber,DateofLoss,LossType, PolicyNumber, LossCity, LossState, LossZip, LossFacts
tblClientCo-ClientCoID(pk),ClientCoName,ClientCoAddress,ClientCoCity,
ClientCoState, ClientCoZip, ClientCoPhone, ClientCoFax,ClientCoEmail,
ClientCoType
tblAdjuster-AdjusterID(pk),AdjusterFirstName,AdjusterLastName,AdjusterPhone,
AdjusterExtn,AdjusterEmail
tblAssignment-AssignmentID(pk),ClaimID(fk), AssignmentNumber,DateReceived,
DateCompleted,AssignmentType,TimeReceived
jtblClaimIntParty-ClaimID(pk),InterestedPartyID(pk)-this is a junction table
between the Claim and Interested Parties (or the people) which have a
many-to-many relationship and each claim can have many parties and each party
could have many claims.
tblInterestedParty-InterestedPartyIDpk),FirstName,LastName,Address,City,State, Zip, Phone, Phone2, Phone3, ClaimParty(to designate insured or claimant)
tblVehicle-VehicleID(pk), AssignmentID(fk), Year, Make, Model, Submodel,
VIN, Mileage, License, Bodystyle, Enginesize, Cylinders, Transmission
tblInspection-InspectionID(pk), VehicleID(fk), AppraiserID(fk),
DateInspected, OwnerPresent, InspectionType
tblInspectionLocation-InspLocID(pk),InspectionID(fk), InspectionLocation,
InspectionLocationName, InspectionAddress,InspectionCity,
InspectionState,InspectionZip, InspectionPhone, InspectionContact
tblRepairer-RepairerID(pk),Name, Address, City, State, Zip, Phone, Fax, TIN,
Contact, RepairerType
jtblInspRepairer - InspLocID, RepairerID - again, this is a junction table
as this is a many-to-many relationship.
These are the tables I'm working with now. I'll try to break it down for
you as far as our business...maybe this will help you to see what's happening
in reference to my InterestedParty problem. Or, again, if you have a better
idea of how to run that. I had thought to have separate tables for insureds
and claimants but that seemed to go against the normalization rules I was
reading.
Thank you again, you really have been such a jewel and have an immense
amount of patience!

Hi Pamela,

applying the same principles... keep separate things in separate
tables... think about your car accident information. You didn't list
those tables, so I did not comment on them -- would help if you would
list them... to just get those tables: make a blank database, link to
the tables where your information is, put the code in, and run it.

I am taking a guess here...

Think of the nouns that you have; each of these is a separate table

Vehicles
Accidents
Accidentees (don't know what to call them... people?)
Insurance Policies
Claims

....once I see your fields, I can give you better information <smile>


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*





Pamela wrote:
Hi Crystal!

Thanks for the table structure help...I'll be sure to make those changes
before I get into any of the forms for them. Did you find anything that
would help with the tables I working with currently? My db consists of a
total of 43 tables and more than half weren't mentioned in that report/module
I pasted. (I do apologize as someone else corrected my use of "field" when
referring to a "control" so I'll be sure to try to get that right for you).
Do you have any other ideas of how I can set this up differently to be able
to handle my car accident parties (insureds & claimants)? Thanks again, I
recognize you put a lot of time and thought into this already for me!!

Pamela

:

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

 

[strive4peace]

Hi Pamela,

this is great! I do have comments, but want to reserve them until you
describe a bit about the claim process ... don't want to steer you
wrong; you know your business <smile>, all we can do is advise you on
suggestions for structuring your data once you impart some of your
knowledge, and ways to use Access efficiently

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!

Here is the result that code produced. I really appreciated also learning
to import those tables...just another great tip you've given!!

jtblClaimIntParty
==========================
0 ClaimID, 4 (Long), 4
1 InterestedPartyID, 4 (Long), 4

jtblInspRepairer
==========================
0 InspLocID, 4 (Long), 4
1 RepairerID, 4 (Long), 4

tblAdjuster
==========================
0 AdjusterID, 4 (Long), 4
1 CompanyID, 4 (Long), 4
2 AdjusterFirstName, 10 (Text), 50
3 AdjusterLastName, 10 (Text), 50
4 AdjusterPhone, 10 (Text), 50
5 AdjusterExtn, 10 (Text), 50
6 AdjusterFax, 10 (Text), 50
7 AdjusterEmail, 10 (Text), 50

tblAppraiser
==========================
0 Appraiser ID, 4 (Long), 4
1 AppaiserFirstName, 10 (Text), 50
2 AppraiserLastName, 10 (Text), 50
3 AppraiserAddress, 10 (Text), 50
4 AppraiserCity, 10 (Text), 50
5 State, 10 (Text), 50
6 AppraiserZip, 10 (Text), 50
7 AppraiserPhone, 10 (Text), 50
8 AppraiserCell, 10 (Text), 50
9 AppraiserFax, 10 (Text), 50

tblAssignment
==========================
0 AssignmentID, 4 (Long), 4
1 ClaimCoID, 4 (Long), 4
2 ClaimID, 4 (Long), 4
3 VehicleID, 4 (Long), 4
4 Time&ExpenseID, 4 (Long), 4
5 AssignmentNumber, 10 (Text), 50
6 DateAssignmentReceived, 10 (Text), 50
7 DateAssignmentCompleted, 10 (Text), 50
8 AppraisalType, 10 (Text), 50
9 CAPNumber, 10 (Text), 50
10 TimeAssignmentReceived, 10 (Text), 50

tblCity
==========================
0 CityName, 10 (Text), 50

tblClaim
==========================
0 ClaimID, 4 (Long), 4
1 AdjusterID, 4 (Long), 4
3 InsuranceCoID, 4 (Long), 4
5 PoliceReportID, 4 (Long), 4
6 ClaimNumber, 10 (Text), 50
7 DateofLoss, 10 (Text), 50
8 LossType, 10 (Text), 50
9 PolicyNumber, 10 (Text), 50
10 OktoPay, 10 (Text), 50
11 LossCity, 10 (Text), 50
12 LossState, 10 (Text), 50
13 LossZip, 10 (Text), 50
14 PointofImpact, 10 (Text), 50
15 SecondaryPointofImpact, 10 (Text), 50
16 FactsofLoss, 12 (Memo), 0

tblClientCo
==========================
0 ClientCoID, 4 (Long), 4
1 ClientCoName, 10 (Text), 50
2 ClientCoAddress, 10 (Text), 50
3 ClientCoCity, 10 (Text), 50
4 ClientCoState, 10 (Text), 50
5 ClientCoZip, 10 (Text), 50
6 ClientCoPhone, 10 (Text), 50
7 ClientCoFax, 10 (Text), 50
8 ClientCoEmail, 10 (Text), 50
9 CompanyType, 10 (Text), 50

tblInspection
==========================
0 InspectionID, 4 (Long), 4
1 AppraiserID, 4 (Long), 4
2 AssignmentID, 4 (Long), 4
3 VehicleID, 4 (Long), 4
4 PropertyID, 4 (Long), 4
5 RepairerID, 4 (Long), 4
6 DateInspected, 10 (Text), 50
7 OwnerPresent, 10 (Text), 50
8 Lot#, 10 (Text), 50
9 RowLocation, 10 (Text), 50
10 InspectionType, 10 (Text), 50
11 InterestedPartyID, 4 (Long), 4

tblInspectionLocation
==========================
0 InspLocID, 4 (Long), 4
1 InspectionID, 4 (Long), 4
2 RepairerID, 4 (Long), 4
3 InspectionLocation, 10 (Text), 50
4 InspectionLocationName, 10 (Text), 50
5 InspectionAddress, 10 (Text), 50
6 InspectionCity, 10 (Text), 50
7 InspectionState, 10 (Text), 50
8 InspectionZip, 10 (Text), 50
9 InspectionPhone, 10 (Text), 50
10 InspectionContact, 10 (Text), 50

tblInterestedParty
==========================
0 InterestedPartyID, 4 (Long), 4
1 InterestedPartyFirstName, 10 (Text), 50
2 InterestedPartyLastName, 10 (Text), 50
3 InterestedPartySuffix, 10 (Text), 50
4 InterestedPartyAddress, 10 (Text), 50
5 InterestedPartyCity, 10 (Text), 50
6 InterestedPartyState, 10 (Text), 50
7 InterestedPartyZip, 10 (Text), 50
8 InterestedPartyPhone 1, 10 (Text), 50
9 InterestedPartyPhone 2, 10 (Text), 50
10 InterestedPartyPhone 3, 10 (Text), 50
11 InterestedPartyAddlContact Name, 10 (Text), 50
12 Claim Party, 10 (Text), 50

tblRepairer
==========================
0 RepairerID, 4 (Long), 4
1 RepairerType, 10 (Text), 50
2 RepairerName, 10 (Text), 50
3 RepairerAddress, 10 (Text), 50
4 RepairerStreet, 10 (Text), 50
5 RepairerCity, 10 (Text), 50
6 RepairerState, 10 (Text), 50
7 RepairerZip, 10 (Text), 50
8 RepairerPhone, 10 (Text), 50
9 RepairerFax, 10 (Text), 50
10 RepairerTIN, 10 (Text), 50

tblVehicle
==========================
0 VehicleID, 4 (Long), 4
1 VehicleOptionID, 4 (Long), 4
2 InspectionID, 4 (Long), 4
3 AssignmentID, 4 (Long), 4
4 VehicleYear, 3 (Integer), 2
5 VehicleMake, 10 (Text), 50
6 VehicleModel, 10 (Text), 50
7 VehicleSubmodel, 10 (Text), 50
8 VehicleVIN, 10 (Text), 50
9 VehicleMileage, 10 (Text), 50
10 VehicleLicense, 10 (Text), 50
11 VehicleColor, 10 (Text), 50
12 VehicleBodystyle, 10 (Text), 50
13 Vehicle#ofPassengers, 10 (Text), 50
14 VehicleEngineSize, 10 (Text), 50
15 VehicleCylinders, 10 (Text), 50
16 VehicleTransOptions, 10 (Text), 50
17 VehicleTransmission, 10 (Text), 50
18 #ofGears, 10 (Text), 50
19 LicenseState, 10 (Text), 50
20 LicenseExp, 10 (Text), 50
21 Lienholder, 10 (Text), 50
22 Leased, 10 (Text), 50

Hi Pamela,

thank you ... but let me tell you how to link to tables -- would also
like to see data type and size...

1. make a blank database

2. Link to the tables you want to document

~~~
with Access 2003 or lower:
3. File, Get External Data, Link Tables...
4. navigate to your database
5. select the tables you want
6. Link
~~~

with Access 2007:
3. External data tab on Ribbon
4. click Access icon in the Import group
5. choose --> link to data source by creating a linked table
6. click the Browse button to choose the database file
7. click OK to see a list of tables

~~~

then, create the general module again, compile the code, then run it,
and paste what is in the Immediate window into your message <smile>


the reason they did not show up in the Debug (Immediate) window is that
the number of lines are limited... so they actually got analyzed, but
then it was overwritten since you have so many tables!


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal!
Thinking in terms of nouns is very helpful. I think (I hope) I've done a
pretty good job on these. I'll list some of them out here for you. I don't
understand much of what your code did but since all of my tables are in the
same db, why didn't these other tables get included? I would do as you
suggested with creating a new db and linking the tables but I don't know how
to "link" the tables...sorry.
tblClaim-ClaimID(pk),AdjusterID,InsuranceCoID,ClaimNumber,DateofLoss,LossType, PolicyNumber, LossCity, LossState, LossZip, LossFacts
tblClientCo-ClientCoID(pk),ClientCoName,ClientCoAddress,ClientCoCity,
ClientCoState, ClientCoZip, ClientCoPhone, ClientCoFax,ClientCoEmail,
ClientCoType
tblAdjuster-AdjusterID(pk),AdjusterFirstName,AdjusterLastName,AdjusterPhone,
AdjusterExtn,AdjusterEmail
tblAssignment-AssignmentID(pk),ClaimID(fk), AssignmentNumber,DateReceived,
DateCompleted,AssignmentType,TimeReceived
jtblClaimIntParty-ClaimID(pk),InterestedPartyID(pk)-this is a junction table
between the Claim and Interested Parties (or the people) which have a
many-to-many relationship and each claim can have many parties and each party
could have many claims.
tblInterestedParty-InterestedPartyIDpk),FirstName,LastName,Address,City,State, Zip, Phone, Phone2, Phone3, ClaimParty(to designate insured or claimant)
tblVehicle-VehicleID(pk), AssignmentID(fk), Year, Make, Model, Submodel,
VIN, Mileage, License, Bodystyle, Enginesize, Cylinders, Transmission
tblInspection-InspectionID(pk), VehicleID(fk), AppraiserID(fk),
DateInspected, OwnerPresent, InspectionType
tblInspectionLocation-InspLocID(pk),InspectionID(fk), InspectionLocation,
InspectionLocationName, InspectionAddress,InspectionCity,
InspectionState,InspectionZip, InspectionPhone, InspectionContact
tblRepairer-RepairerID(pk),Name, Address, City, State, Zip, Phone, Fax, TIN,
Contact, RepairerType
jtblInspRepairer - InspLocID, RepairerID - again, this is a junction table
as this is a many-to-many relationship.
These are the tables I'm working with now. I'll try to break it down for
you as far as our business...maybe this will help you to see what's happening
in reference to my InterestedParty problem. Or, again, if you have a better
idea of how to run that. I had thought to have separate tables for insureds
and claimants but that seemed to go against the normalization rules I was
reading.
Thank you again, you really have been such a jewel and have an immense
amount of patience!


:

Hi Pamela,

applying the same principles... keep separate things in separate
tables... think about your car accident information. You didn't list
those tables, so I did not comment on them -- would help if you would
list them... to just get those tables: make a blank database, link to
the tables where your information is, put the code in, and run it.

I am taking a guess here...

Think of the nouns that you have; each of these is a separate table

Vehicles
Accidents
Accidentees (don't know what to call them... people?)
Insurance Policies
Claims

....once I see your fields, I can give you better information <smile>


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*





Pamela wrote:
Hi Crystal!

Thanks for the table structure help...I'll be sure to make those changes
before I get into any of the forms for them. Did you find anything that
would help with the tables I working with currently? My db consists of a
total of 43 tables and more than half weren't mentioned in that report/module
I pasted. (I do apologize as someone else corrected my use of "field" when
referring to a "control" so I'll be sure to try to get that right for you).
Do you have any other ideas of how I can set this up differently to be able
to handle my car accident parties (insureds & claimants)? Thanks again, I
recognize you put a lot of time and thought into this already for me!!

Pamela

:

Hi Pamela,

Great! Yes, looking at your structure, we can guide you much better <smile>

"Me.cboClaimCoID.Undo...IS a field on my form"

I find it easiest to make the control NAME the same as the field name
for bound controls. If you do this, then you will be able to use your
fieldname, since it will also be the control name -- but Access wants
the Name of the CONTROL on the form you are behind (that is what 'Me'
means), not the name of the field... Property Sheet --> first property
listed on both the 'Other' and the 'All' tabs

Don't use spaces or special characters (underscore _ is ok) in your
names, so rename Lot# to something like 'Lot' (since you are storing it
as text and not a number, you should not use anything in the fieldname
that indicates it is a number anyway). Rename [Safety ID] to [SafetyID]
without a space

[Anti-Lock Brakes] should not have spaces or a dash

Rather than the structure you have for your Safety table, it would be
best to make a table to list the item types

SafetyItems
- SafeItemID, autonumber
- SafetyItem, text

record would be as follows:

1, "Anti-Lock Brakes"
2, "Anti-Lock Brakes Rear" --> does this mean that 1 is on the front?
If so, it should be specified that way

but, you may also wish to write your descriptions such that the most
important classification is first:

1, "Brakes, Anti-Lock"
2, "Brakes, Anti-Lock, Rear"
3, "Brakes, Disc, 4-wheel"

this way, when the list is sorted, it makes better sense

and then, your Inspections table would have a related table for the
applicable items


I see you also have a table tor Seating options. With this new
information, then I would, INSTEAD, suggest these tables:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text

records:
1, "Safety"
2, "Seating"

then, you would make an Items table:

Items
- ItemID, autonumber -- PK
- ItemCatID, long integer -- FK to ItemCats
- Item, text

records like this:

1, 1, "Brakes, Anti-Lock"
2, 1, "Brakes, Anti-Lock, Rear"
3, 1, "Brakes, Disc, 4-wheel"
4, 2, "Cloth Seats"
5, 2, "Leather Seats"

you may even with to make subcategories. You can do this by expaning
the ItemCats table to this:

ItemCats
- ItemCatID, autonumber -- PK
- ItemCategory, text
- ItemCatID_, long integer -- 'parent' category (corresponds to ItemCatID)
- Lvl, integer (Level is a reserved word) -- you probably do not want to
go down more than 1 level, but if you do have a need, this structure
could accomodate it

records:
1, "Safety", NULL, 0
2, "Seating", NULL, 0
3, "Brakes", 1, 1
4, "Airbags", 1, 1
etc


PK = Primary Key
FK = Foreign Key

States: I find it best to store the 2-character state abbreviation
instead of an ID field. You can have a States table for looking up such
as this:

States
St, text, 2 -- PK
Statename, text, 30

and, if you deal with Canadian clients, add this field to the States table:

Ctry, text, 2

and then add this field to the States table. Tell you what, if you are
interested in getting the tables I have already set up, let me know and
I will put then on the net for you.

Don't use & in a fieldname ...Time&ExpenseHours

once again, you should have a table with ExpensesTypes ...

ExpenseTypes
- ExpTypID, autonumber --PK
- ExpenseType, text

records:
1, "Miles"
2, "Parking"
3, "Tolls"
etc

and then, I am assuming you also have a Jobs table with JobID to
attribute the expense to if it is billable:

Expenses
- ExpenseID, autonumber -- PK
- JobID, long integer, FK to Jobs (if billable)
- ExpTypID, long integer, FK to ExpenseTypes
- ExpAmount, currency
- ExpQty, number -- ie: number of hours, etc
- RateID, long integer -- FK to Rates

Rates
- RateID, autonumber -- PK
- Rate, currency
- RateDescr, text -- (rate description)

Always check names to make sure you are not using a reserved word:

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Pamela]

Hi Crystal!

When an auto insurance claim is filed, the insurance company needs photos
and a damage appraisal of all vehicles/property involved. The employee of
the insurance company that handles the claim is the Adjuster. Assignments
are then sent for these inspections. Some insurance companies send directly
to us and some send to a claim company who, in turns, sends it to us. Hence,
we have 2 different types of clients sending work. So just to wrap that up
for you again, all assignments have insurance companies and adjusters - not
all have claim companies. All claims have assignments. Some claims may have
multiple assignments if there are multiple vehicles and owners
correspondingly.

Vehicle owners or "accidentees" (cute), are what I'm calling Interested
Parties. The party who bought the insurance policy from the company that
sent us the assignment is the insured and the other party who is filing a
claim against this insured is the claimant. So party A buys an insurance
policy from Allstate, for instance, and party A and party B then have an
accident (regardless of fault) and party A reports it to Allstate, party A is
the insured and party B is the claimant. All claims have an insured, not all
have a claimant.

Now I think that I should add that while I get pretty anxious because of my
level (or lack thereof) I recognize that if I'm taking so much time to put a
db together, I should build it to the ultimate - not just what may be
expedient. That's why I have the junction table between interested parties
and claims - an interested party may have more than one claim. But also,
there are times that an interested party has more than 1 vehicle per claim -
if a truck is towing a trailer and they both get damaged, for instance. That
would be 2 assignments, 1 claim, 1 interested party. Complicated, I know...

All assignments have 1 vehicle/property. While we don't do big property
losses, we do get some where the insured runs into a fence or gate and then
we have to appraise that damage also. I'd ultimately need to set it up to
accomodate that also but I was already feeling in-over-my-head so I was
holding that off originally.

All vehicles then have an inspection. Oftentimes they have more than 1
inspection that can be at different locations. An initial inspection may be
at the residence and then they take it to the body shop and find more damage
once the repairs begin...

Sorry, I'm getting dragged off...I hope this gives you help!! Thanks again!!

 

[strive4peace]

Hi Pamela,

fantastic! this is exactly what we need to best help you. I will have
to look at this later, though -- I am making another video tutorial for
YouTube

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Pamela,

I see these tables:


Photos
- PhotoID, autonumber -- PK
- ClaimID, long -- FK to Claims
- VehicleID, long -- FK to Vehicles
- PhotoFilename, text, 255 -- path and filename to photo

Claims
- ClaimID, autonumber -- PK
- ClaimDate, date
- PoliceReportID, long -- just copied this from you -- assume there is a
PoliceReports table you are linking to using this field.
- ClaimNumber, text
- LossDate, date
- LosTypID, long -- FK to LossTypes
- other claim header info

Not sure if you need to store more than one or two points of impact, I
imagine you might -- so this may need to go into a related table instead
of storing PointofImpact and SecondaryPointofImpact in the Claims table

LossFacts
- LosFactID, autonumber -- PK
- ClaimID, long -- FK to Claims
- LossFact, text, 255
- LosFactNum, integer -- number, for ordering

LossTypes
- LosTypID, autonumber -- PK
- LossType, text

Vehicles
- VehicleID, autonumber -- PK
- VIN -- make a unique index on this field
- fields for make, model, etc
- PID_owner, long -- FK to People

you will need related tables to describe the Vehicle features, as
mentioned in previous posts.

People
- PID, autonumber -- PK
- PplTypID, long -- FK to PeopleTypes
- NameMain, text (LastName for Humans, Company Name for Companies)
- Firstname, text (only applicable to Humans)
- DOB, date (only applicable to Humans)
- etc

all humans and companies will go in the People table -- just like a
phone book has everybody. This is the concept that I am demonstrating
in my YouTube video tutorials -- working on the tutorial to plan a
database right now, but it probably won't be posted for at least a few
days. This is an important concept to give your database greater
flexibility. Also, by doing it this way, you have an automatic path to
all the contact information such as address, phone, email, website -- to
have zero, one, or more of each

I did not make a ClientCo table -- not sure if it is covered by one of
these other tables; don't quite understand how this comes in

PeopleTypes
- PplTypID, autonumber -- PK
- PplType, text

ClaimVehicles
- ClaVehID, autonumber -- PK
- ClaimID, long -- FK to Claims
- VehicleID, long -- FK to Vehicles

Addresses
- AddrID, autonumber
- PID, long -- FK to People
- Address1, text
- Address2, text
- City, text, 30
- St, text, 2
- Zip, text, 10 (this way, the field can accomodate foreign post codes
as well)
- ZipExt, text, 4
- Ctry, text, 2, DefaultValue --> "US" (Country)

storing City as text here vs having a Cities table and using CityID is
up to you. If your records keep using the same city all the time, you
may want to do this. Personally, I prefer not to -- have tried it both
ways. Think of this as an analogy: Mike is a common name and probably
in a lot of your records. Should we make a table of Names and use a
NameID for Mike?

Phones
- PhoneID, autonumber -- PK
- PID, long -- FK to People
- PhoTypID, long -- FK to PhoneTypes
- Phone, text
- PohExt, text, 5 (phone extension if applicable)

PhoneTypes
- PhoTypID, autonumber -- PK
- PhoneType, text (ie: home, work, cell, primary, alternate, fax, mobile)

eMails
- emailID, autonumber -- PK
- PID, long -- FK to People
- Email, text
- EmaTypID, long -- FK to EmailTypes

EmailTypes
- EmaTypID, autonumber -- PK
- EmailType, text (ie: home, work, primary, alternate, relative)

AdjCompanies
- AdjCompID, long -- PK -- comes from People.PID
- other fields specific to adjustor companies that are not stored in People

Adjustors
- AdjID, long -- PK -- comes from People.PID
- AdjCompID, long -- FK to AdjCompanies

ClaimAdjustors
- ClaimAdjID, autonumber -- PK
- ClaimID, long -- FK to Claims
- AdjID, long -- FK to Adjustors

this way, it is possible for more than one adjustor to work on a claim

Appraisers
- AppraiserID, long -- PK -- comes from People.PID

ClaimAppraisers
- ClaimAppID, autonumber -- PK
- ClaimID, long -- FK to Claims
- AppraiserID, long -- FK to Appraisers


ClaimParties
- ClaimPtyID, autonumber -- PK
- ClaimID, long -- FK to Claims
- PID, long -- FK to People
- ParTypID, long -- FK to PartyTypes
- PartyNum, integer, default value --> 1

if there are multiple parties for a particular type, then you would
change PartyNum. Make a unique index on the combination of ClaimID,
ParTypID, and PartyNum

PartyTypes
- ParTypID, autonumber -- PK
- PartyType, text (ie: insured, claimant)

InsCarriers (Insurance Carriers)
- InsCarID, autonumber -- PK
- InsCarrier, text (ie: AllState)

PeopleInsurance
- PInsCarID, autonumber -- PK
- InsCarID, long -- FK to InsCarriers
- PID, long -- FK to People
- PolicyNumber, text


I know I have left some tables out, like Assignment, Inspection, and
Repair tables, but I am not clear this and did not want to give you bad
information ... hopefully, you can see how to do that by understanding
why I have suggested these tables.

"if I'm taking so much time to put a db together, I should build it to
the ultimate - not just what may be expedient."

good! I am glad you feel that way. This is NOT an easy database, so
plan for it to take lots of time.

As long as your tables each describe just one thing (noun or event), or
is a junction table, you should have the flexibility you will need to
carry you far into the future <smile>



Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*