Subforms

G

Guest

I have created a main form with customer information another form with
contract information, another form from purchase order # and a PO information
form. They each come from respective tables.
Customer Form
Contract Form
Purchase Order # Form
Purchase Order Details Form

I have a purchase order details form as a sub form to it's purchase order #
form then those 2 forms as a subform on the contract form.

Everything works fine at this point--I can add new items to the PO Details,
I can add a new PO to the Contract. All are master-child linked to the
customer contact ID #. When I place this form as a subform on the main
customer information form, it will not allow me to add any new information to
any of the subforms.

I have tried placing them individually to the main form and it still does
not allow me to add new information. They all work ok until they are placed
on to the main form. I have verified that "Allow Additions" is enabled.

The main form was developed from a query. Should I recreate this from
directly from the customer information table? The information is the same.
I have spent a lot of time trying to figure out why this is not working and I
don't want to spend a lot of time recreating the form and it all be in vain!
Does anyone have any ideas of what the problem could be?
 
S

strive4peace

Hi ronnie,

it is best to base each form/subform on just one table (or query based
on just one table). Problems happen when your query uses tables in a
parent/child relationship as parent records need to be created before
child records

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
G

Guest

I recreated the form based on the table, however, the exact same thing
happens. It will not allow me to add any new records to the purchase orders
or contract subforms--I can add a new record to the main form, but cannot
apply contracts or purchase orders.
 
S

strive4peace

ShowFieldsForAllTables
---


Hi Ronnie,

how are your tables related and what fields do they contain?

you can use this code to document your fields and then you will need to
explain how the tables relate

~~~~~~~~~~~~~~~~~

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()
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 DuaneHookom
'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

'~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
G

Guest

Sales Commission Details Table
==========================
0 Sales Commission Details, 4 (Long), 4
1 SC ID, 4 (Long), 4
2 SC CoRep ID, 4 (Long), 4
3 Customer, 4 (Long), 4
4 Sales Type, 4 (Long), 4
5 Contract ID, 4 (Long), 4
6 Commission, 4 (Long), 4
7 Commission Co-Rep, 4 (Long), 4
8 Outside Commission Rec, 4 (Long), 4
9 Commission Date, 8 (Date), 8
10 PO#, 10 (Text), 50

Sales Commission Table
==========================
0 Sales Commission ID, 4 (Long), 4
1 Rep ID, 4 (Long), 4

Sales Type Table
==========================
0 Sales Type #, 4 (Long), 4
1 Sales Type, 10 (Text), 50

Shower Table
==========================
0 Shower ID, 4 (Long), 4
1 Shower Type, 10 (Text), 50
2 Shower Manufacturer, 4 (Long), 4

Siding Type Table
==========================
0 Siding ID, 4 (Long), 4
1 Siding Type, 10 (Text), 50
2 Slider Manufacturer, 4 (Long), 4

Staircases-Railing Table
==========================
0 Staircases-Railing ID, 4 (Long), 4
1 Staircase-Railing Type, 10 (Text), 50
2 Stair Manufacturer, 4 (Long), 4

Switchboard Items
==========================
0 SwitchboardID, 4 (Long), 4
1 ItemNumber, 3 (Integer), 2
2 ItemText, 10 (Text), 255
3 Command, 3 (Integer), 2
4 Argument, 10 (Text), 255

tbl_Contacts
==========================
0 ContactID, 4 (Long), 4
1 CustomerYes, 1 (Boolean), 1
2 CustomerDate, 8 (Date), 8
3 EnteredDate, 8 (Date), 8
4 Entered by, 10 (Text), 50
5 Phone Call, 1 (Boolean), 1
6 InitFirstName, 10 (Text), 25
7 InitMiddleName, 10 (Text), 25
8 InitLastName, 10 (Text), 25
9 InitAddress1, 10 (Text), 50
10 InitAddress2, 10 (Text), 50
11 InitCity, 10 (Text), 25
12 InitState, 10 (Text), 5
13 InitZip, 10 (Text), 15
14 InitHomePhone, 10 (Text), 20
15 InitOfficePhone, 10 (Text), 20
16 InitCellPhone, 10 (Text), 20
17 InitFax, 10 (Text), 20
18 InitEmail, 12 (Memo), 0
19 InitOccupation, 10 (Text), 50
20 H1Address1, 10 (Text), 50
21 H1Address2, 10 (Text), 50
22 H1City, 10 (Text), 25
23 H1State, 10 (Text), 5
24 H1Zip, 10 (Text), 15
25 H1HomePhone, 10 (Text), 20
26 H1OfficePhone, 10 (Text), 20
27 H1CellPhone, 10 (Text), 20
28 H1Fax, 10 (Text), 20
29 H1Email, 12 (Memo), 0
30 H2FirstName, 10 (Text), 25
31 H2MiddleName, 10 (Text), 25
32 H2LastName, 10 (Text), 25
33 H2OfficePhone, 10 (Text), 20
34 H2CellPhone, 10 (Text), 20
35 H2Email, 12 (Memo), 0
36 H2Co-buyer occupation, 10 (Text), 50
37 RelationshipID, 4 (Long), 4
38 HowID, 4 (Long), 4
39 Have You been on our Website, 1 (Boolean), 1
40 Referred by?, 10 (Text), 50
41 OwnProperty?Yes, 1 (Boolean), 1
42 OwnProperty?No, 1 (Boolean), 1
43 Property Value, 4 (Long), 4
44 MSTBYes, 1 (Boolean), 1
45 MSTBNo, 1 (Boolean), 1
46 HouseSizeMin, 4 (Long), 4
47 HouseSizeMax, 4 (Long), 4
48 LiveWhere, 10 (Text), 50
49 PriceRangeMinimum, 5 (Currency), 8
50 PriceRangeMaximum, 5 (Currency), 8
51 WhentoBuild, 10 (Text), 50
52 HouseTypeID, 4 (Long), 4
53 HouseModelID, 4 (Long), 4
54 InformationPacketSent?, 1 (Boolean), 1
55 DatePacketSent, 8 (Date), 8
56 Notes, 12 (Memo), 0
57 Homeowners Manual Given?, 1 (Boolean), 1
58 Date Homeowners Manual, 8 (Date), 8
59 RepID, 4 (Long), 4
60 Co Rep ID, 10 (Text), 50
61 Start of Construction, 8 (Date), 8
62 C of O Date, 8 (Date), 8
63 Date Complete, 8 (Date), 8
64 Square Footage 1st, 4 (Long), 4
65 Square Footage 2nd, 4 (Long), 4
66 Bedrooms, 4 (Long), 4
67 Bathrooms, 4 (Long), 4
68 Master on 1st Floor, 1 (Boolean), 1
69 Dead Lead, 1 (Boolean), 1
70 Income, 10 (Text), 50
71 Marital Status, 10 (Text), 50
72 Reason for Interest, 10 (Text), 50
73 Spec House, 1 (Boolean), 1
74 POID, 4 (Long), 4

tbl_Contract
==========================
0 ContractID, 4 (Long), 4
1 ContactID, 4 (Long), 4
2 Contract Month Look-up, 10 (Text), 50
3 Contract Day Look-up, 4 (Long), 4
4 Contract Year Look-up, 4 (Long), 4
5 Lot Location, 10 (Text), 50
6 Lot Number, 10 (Text), 50
7 Section Number, 10 (Text), 50
8 Municipality, 4 (Long), 4
9 County, 4 (Long), 4
10 Septic System, 5 (Currency), 8
11 House Model, 4 (Long), 4
12 Prevailing Interest Rate, 4 (Long), 4
13 #Years, 10 (Text), 10
14 At Signing, 4 (Long), 4
15 Sales Type, 4 (Long), 4
16 Sales Commission Details #, 10 (Text), 50
17 PO ID#, 4 (Long), 4

tbl_HouseModel
==========================
0 HouseModelID, 4 (Long), 4
1 HouseModel, 10 (Text), 50

tbl_HouseType
==========================
0 HouseTypeID, 4 (Long), 4
1 HouseType, 10 (Text), 50

tbl_How
==========================
0 HowID, 4 (Long), 4
1 How, 10 (Text), 50

tbl_Relationship
==========================
0 RelationshipID, 4 (Long), 4
1 Relationship, 10 (Text), 50

tbl_Rep
==========================
0 RepID, 4 (Long), 4
1 RepLastName, 10 (Text), 20
2 RepFirstName, 10 (Text), 15

Toilet Table
==========================
0 Toilet ID, 4 (Long), 4
1 Toilet Type, 10 (Text), 50
2 Toilet Manufacturer, 4 (Long), 4

Township Table
==========================
0 Township ID, 4 (Long), 4
1 Township, 10 (Text), 50

Windows Table
==========================
0 Windows ID, 4 (Long), 4
1 Windows Type, 10 (Text), 50
2 Windows Manufacturer, 4 (Long), 4
 
S

strive4peace

Hi Ronnie,

Great! This helps. I noticed you have 2 spaces in [Sales Commission
ID] -- it is a good idea not to use spaces at all ... SalesCommissionID
is just as easy to read...

I am assuming [SC ID] is a short form of [Sales Commission ID] -- it is
best to keep fieldnames the same if they contain the same data. SCID
would be a better name, in my opinion, because it is shorter. One
consideration when naming fields is how wide the data is beneath them --
an ID will be perhaps 4 or 5 characters... [Sales Commission ID] has 20
characters.

[Sales Type #] is a double bad name for a field because it has spaces as
well as a special character, #

PO# could just be PO

Underscores _ do not cause problems and they are ok to use in names

It is also best not to use spaces (or special characters) in tablenames.
I would call [Sales Type Table] --> SalesTypes

Instead of a table for Toilets and another table for Windows, it would
be good to have a table for Items (Toilet, Window, Door, etc) and
another table for HouseItems with HouseID and ItemID

Your tables are not normalized. For infomation on how to set up tables
and to help you understand Access a bit better, send me an email and
request my 30-page Word document on Access Basics (for Programming) --
it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. I do also send out the first 3 chapters of a
book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...
~~~~~~~~~~~~~~~~~~

If you want to restrucuture your database before continuing, it would be
a good idea. More time spent now in planning and setup will save you
countless hours and headaches down the road.

If you do not want to restructure:

1. we need to know how your tables are related.

2. And we need to know what is the RecordSource for the forms in
question. If any are based on a query, what is the SQL for the query?
When you open the query (design or datasheet), from the menu, choose -->
View, SQL


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

strive4peace

ps

on [Sales Commission ID]

that is 2 spaces between Commission and ID (3 spaces in altogether) --
could cause problems if you did not realize this...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi Ronnie,

Great! This helps. I noticed you have 2 spaces in [Sales Commission
ID] -- it is a good idea not to use spaces at all ... SalesCommissionID
is just as easy to read...

I am assuming [SC ID] is a short form of [Sales Commission ID] -- it is
best to keep fieldnames the same if they contain the same data. SCID
would be a better name, in my opinion, because it is shorter. One
consideration when naming fields is how wide the data is beneath them --
an ID will be perhaps 4 or 5 characters... [Sales Commission ID] has 20
characters.

[Sales Type #] is a double bad name for a field because it has spaces as
well as a special character, #

PO# could just be PO

Underscores _ do not cause problems and they are ok to use in names

It is also best not to use spaces (or special characters) in tablenames.
I would call [Sales Type Table] --> SalesTypes

Instead of a table for Toilets and another table for Windows, it would
be good to have a table for Items (Toilet, Window, Door, etc) and
another table for HouseItems with HouseID and ItemID

Your tables are not normalized. For infomation on how to set up tables
and to help you understand Access a bit better, send me an email and
request my 30-page Word document on Access Basics (for Programming) --
it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. I do also send out the first 3 chapters of a
book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...
~~~~~~~~~~~~~~~~~~

If you want to restrucuture your database before continuing, it would be
a good idea. More time spent now in planning and setup will save you
countless hours and headaches down the road.

If you do not want to restructure:

1. we need to know how your tables are related.

2. And we need to know what is the RecordSource for the forms in
question. If any are based on a query, what is the SQL for the query?
When you open the query (design or datasheet), from the menu, choose -->
View, SQL


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Sales Commission Details Table
==========================
0 Sales Commission Details, 4 (Long), 4
1 SC ID, 4 (Long), 4
2 SC CoRep ID, 4 (Long), 4
3 Customer, 4 (Long), 4
4 Sales Type, 4 (Long), 4
5 Contract ID, 4 (Long), 4
6 Commission, 4 (Long), 4
7 Commission Co-Rep, 4 (Long), 4
8 Outside Commission Rec, 4 (Long), 4
9 Commission Date, 8 (Date), 8
10 PO#, 10 (Text), 50

Sales Commission Table
==========================
0 Sales Commission ID, 4 (Long), 4
1 Rep ID, 4 (Long), 4

Sales Type Table
==========================
0 Sales Type #, 4 (Long), 4
1 Sales Type, 10 (Text), 50

Shower Table
==========================
0 Shower ID, 4 (Long), 4
1 Shower Type, 10 (Text), 50
2 Shower Manufacturer, 4 (Long), 4

Siding Type Table
==========================
0 Siding ID, 4 (Long), 4
1 Siding Type, 10 (Text), 50
2 Slider Manufacturer, 4 (Long), 4

Staircases-Railing Table
==========================
0 Staircases-Railing ID, 4 (Long), 4
1 Staircase-Railing Type, 10 (Text), 50
2 Stair Manufacturer, 4 (Long), 4

Switchboard Items
==========================
0 SwitchboardID, 4 (Long), 4
1 ItemNumber, 3 (Integer), 2
2 ItemText, 10 (Text), 255
3 Command, 3 (Integer), 2
4 Argument, 10 (Text), 255

tbl_Contacts
==========================
0 ContactID, 4 (Long), 4
1 CustomerYes, 1 (Boolean), 1
2 CustomerDate, 8 (Date), 8
3 EnteredDate, 8 (Date), 8
4 Entered by, 10 (Text), 50
5 Phone Call, 1 (Boolean), 1
6 InitFirstName, 10 (Text), 25
7 InitMiddleName, 10 (Text), 25
8 InitLastName, 10 (Text), 25
9 InitAddress1, 10 (Text), 50
10 InitAddress2, 10 (Text), 50
11 InitCity, 10 (Text), 25
12 InitState, 10 (Text), 5
13 InitZip, 10 (Text), 15
14 InitHomePhone, 10 (Text), 20
15 InitOfficePhone, 10 (Text), 20
16 InitCellPhone, 10 (Text), 20
17 InitFax, 10 (Text), 20
18 InitEmail, 12 (Memo), 0
19 InitOccupation, 10 (Text), 50
20 H1Address1, 10 (Text), 50
21 H1Address2, 10 (Text), 50
22 H1City, 10 (Text), 25
23 H1State, 10 (Text), 5
24 H1Zip, 10 (Text), 15
25 H1HomePhone, 10 (Text), 20
26 H1OfficePhone, 10 (Text), 20
27 H1CellPhone, 10 (Text), 20
28 H1Fax, 10 (Text), 20
29 H1Email, 12 (Memo), 0
30 H2FirstName, 10 (Text), 25
31 H2MiddleName, 10 (Text), 25
32 H2LastName, 10 (Text), 25
33 H2OfficePhone, 10 (Text), 20
34 H2CellPhone, 10 (Text), 20
35 H2Email, 12 (Memo), 0
36 H2Co-buyer occupation, 10 (Text), 50
37 RelationshipID, 4 (Long), 4
38 HowID, 4 (Long), 4
39 Have You been on our Website, 1 (Boolean), 1
40 Referred by?, 10 (Text), 50
41 OwnProperty?Yes, 1 (Boolean), 1
42 OwnProperty?No, 1 (Boolean), 1
43 Property Value, 4 (Long), 4
44 MSTBYes, 1 (Boolean), 1
45 MSTBNo, 1 (Boolean), 1
46 HouseSizeMin, 4 (Long), 4
47 HouseSizeMax, 4 (Long), 4
48 LiveWhere, 10 (Text), 50
49 PriceRangeMinimum, 5 (Currency), 8
50 PriceRangeMaximum, 5 (Currency), 8
51 WhentoBuild, 10 (Text), 50
52 HouseTypeID, 4 (Long), 4
53 HouseModelID, 4 (Long), 4
54 InformationPacketSent?, 1 (Boolean), 1
55 DatePacketSent, 8 (Date), 8
56 Notes, 12 (Memo), 0
57 Homeowners Manual Given?, 1 (Boolean), 1
58 Date Homeowners Manual, 8 (Date), 8
59 RepID, 4 (Long), 4
60 Co Rep ID, 10 (Text), 50
61 Start of Construction, 8 (Date), 8
62 C of O Date, 8 (Date), 8
63 Date Complete, 8 (Date), 8
64 Square Footage 1st, 4 (Long), 4
65 Square Footage 2nd, 4 (Long), 4
66 Bedrooms, 4 (Long), 4
67 Bathrooms, 4 (Long), 4
68 Master on 1st Floor, 1 (Boolean), 1
69 Dead Lead, 1 (Boolean), 1
70 Income, 10 (Text), 50
71 Marital Status, 10 (Text), 50
72 Reason for Interest, 10 (Text), 50
73 Spec House, 1 (Boolean), 1
74 POID, 4 (Long), 4

tbl_Contract
==========================
0 ContractID, 4 (Long), 4
1 ContactID, 4 (Long), 4
2 Contract Month Look-up, 10 (Text), 50
3 Contract Day Look-up, 4 (Long), 4
4 Contract Year Look-up, 4 (Long), 4
5 Lot Location, 10 (Text), 50
6 Lot Number, 10 (Text), 50
7 Section Number, 10 (Text), 50
8 Municipality, 4 (Long), 4
9 County, 4 (Long), 4
10 Septic System, 5 (Currency), 8
11 House Model, 4 (Long), 4
12 Prevailing Interest Rate, 4 (Long), 4
13 #Years, 10 (Text), 10
14 At Signing, 4 (Long), 4
15 Sales Type, 4 (Long), 4
16 Sales Commission Details #, 10 (Text), 50
17 PO ID#, 4 (Long), 4

tbl_HouseModel
==========================
0 HouseModelID, 4 (Long), 4
1 HouseModel, 10 (Text), 50

tbl_HouseType
==========================
0 HouseTypeID, 4 (Long), 4
1 HouseType, 10 (Text), 50

tbl_How
==========================
0 HowID, 4 (Long), 4
1 How, 10 (Text), 50

tbl_Relationship
==========================
0 RelationshipID, 4 (Long), 4
1 Relationship, 10 (Text), 50

tbl_Rep
==========================
0 RepID, 4 (Long), 4
1 RepLastName, 10 (Text), 20
2 RepFirstName, 10 (Text), 15

Toilet Table
==========================
0 Toilet ID, 4 (Long), 4
1 Toilet Type, 10 (Text), 50
2 Toilet Manufacturer, 4 (Long), 4

Township Table
==========================
0 Township ID, 4 (Long), 4
1 Township, 10 (Text), 50

Windows Table
==========================
0 Windows ID, 4 (Long), 4
1 Windows Type, 10 (Text), 50
2 Windows Manufacturer, 4 (Long), 4
Click to expand...
Click to expand...
 
G

Guest

Contact Table
Has an auto number for the Contact ID as the primary key
Several other fields such as phone etc. are in this table
(I recently tried putting in a look-up for contract # and PO# to see
if this would make a difference in the form-subform problem—it did not help)

Contract Table
Has an auto number for the ContractID as the primary.
It then has a lookup for the Contact ID and customer last name. (SQL
Statement)

Purchase Order Table
Has an auto number for the PO # as the primary key
It then has a look up for the Contact ID and the customer last name &
A look up for the Contract ID

Purchase Order Details Table
Has an auto number for the PO item
number as the primary key
It then has a look up for the PO #
A look-up for items
A lookup for the Contract ID
Other fields for quantity, price & description

Sales Commission Table
Has an auto number for the SC ID as the primary key
It then has a look up for the Sales Rep ID and Rep’s name

Sales Commission
Details Table
Has an auto number for the Details on commission paid to a sales rep.
It has a look-up for the Contract ID, SC ID, Contact ID-Name and PO ID

I had this working at one point, but I’m not sure what I changed. I have
created a form for the Contract table with a subform of the Purchase Order
table with a subform of the Purchase Order Details table. I can add records
to any of these forms with this set-up. When I place any of these forms on
the Main Contact (customer) form whether individually or as coordinated
subforms, I am unable to add a new record to any of these subforms.

The Purchase order Form is linked to the Purchase Order Details form by POID#
The Purchase Order Form is linked to the Contract Form by Contract ID#
The Contract Form links to the Contact Form by Contact ID#.

--
Ronnie


strive4peace said:
ps

on [Sales Commission ID]

that is 2 spaces between Commission and ID (3 spaces in altogether) --
could cause problems if you did not realize this...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi Ronnie,

Great! This helps. I noticed you have 2 spaces in [Sales Commission
ID] -- it is a good idea not to use spaces at all ... SalesCommissionID
is just as easy to read...

I am assuming [SC ID] is a short form of [Sales Commission ID] -- it is
best to keep fieldnames the same if they contain the same data. SCID
would be a better name, in my opinion, because it is shorter. One
consideration when naming fields is how wide the data is beneath them --
an ID will be perhaps 4 or 5 characters... [Sales Commission ID] has 20
characters.

[Sales Type #] is a double bad name for a field because it has spaces as
well as a special character, #

PO# could just be PO

Underscores _ do not cause problems and they are ok to use in names

It is also best not to use spaces (or special characters) in tablenames.
I would call [Sales Type Table] --> SalesTypes

Instead of a table for Toilets and another table for Windows, it would
be good to have a table for Items (Toilet, Window, Door, etc) and
another table for HouseItems with HouseID and ItemID

Your tables are not normalized. For infomation on how to set up tables
and to help you understand Access a bit better, send me an email and
request my 30-page Word document on Access Basics (for Programming) --
it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. I do also send out the first 3 chapters of a
book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...
~~~~~~~~~~~~~~~~~~

If you want to restrucuture your database before continuing, it would be
a good idea. More time spent now in planning and setup will save you
countless hours and headaches down the road.

If you do not want to restructure:

1. we need to know how your tables are related.

2. And we need to know what is the RecordSource for the forms in
question. If any are based on a query, what is the SQL for the query?
When you open the query (design or datasheet), from the menu, choose -->
View, SQL


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Sales Commission Details Table
==========================
0 Sales Commission Details, 4 (Long), 4
1 SC ID, 4 (Long), 4
2 SC CoRep ID, 4 (Long), 4
3 Customer, 4 (Long), 4
4 Sales Type, 4 (Long), 4
5 Contract ID, 4 (Long), 4
6 Commission, 4 (Long), 4
7 Commission Co-Rep, 4 (Long), 4
8 Outside Commission Rec, 4 (Long), 4
9 Commission Date, 8 (Date), 8
10 PO#, 10 (Text), 50

Sales Commission Table
==========================
0 Sales Commission ID, 4 (Long), 4
1 Rep ID, 4 (Long), 4

Sales Type Table
==========================
0 Sales Type #, 4 (Long), 4
1 Sales Type, 10 (Text), 50

Shower Table
==========================
0 Shower ID, 4 (Long), 4
1 Shower Type, 10 (Text), 50
2 Shower Manufacturer, 4 (Long), 4

Siding Type Table
==========================
0 Siding ID, 4 (Long), 4
1 Siding Type, 10 (Text), 50
2 Slider Manufacturer, 4 (Long), 4

Staircases-Railing Table
==========================
0 Staircases-Railing ID, 4 (Long), 4
1 Staircase-Railing Type, 10 (Text), 50
2 Stair Manufacturer, 4 (Long), 4

Switchboard Items
==========================
0 SwitchboardID, 4 (Long), 4
1 ItemNumber, 3 (Integer), 2
2 ItemText, 10 (Text), 255
3 Command, 3 (Integer), 2
4 Argument, 10 (Text), 255

tbl_Contacts
==========================
0 ContactID, 4 (Long), 4
1 CustomerYes, 1 (Boolean), 1
2 CustomerDate, 8 (Date), 8
3 EnteredDate, 8 (Date), 8
4 Entered by, 10 (Text), 50
5 Phone Call, 1 (Boolean), 1
6 InitFirstName, 10 (Text), 25
7 InitMiddleName, 10 (Text), 25
8 InitLastName, 10 (Text), 25
9 InitAddress1, 10 (Text), 50
10 InitAddress2, 10 (Text), 50
11 InitCity, 10 (Text), 25
12 InitState, 10 (Text), 5
13 InitZip, 10 (Text), 15
14 InitHomePhone, 10 (Text), 20
15 InitOfficePhone, 10 (Text), 20
16 InitCellPhone, 10 (Text), 20
17 InitFax, 10 (Text), 20
18 InitEmail, 12 (Memo), 0
19 InitOccupation, 10 (Text), 50
20 H1Address1, 10 (Text), 50
21 H1Address2, 10 (Text), 50
22 H1City, 10 (Text), 25
23 H1State, 10 (Text), 5
24 H1Zip, 10 (Text), 15
25 H1HomePhone, 10 (Text), 20
26 H1OfficePhone, 10 (Text), 20
27 H1CellPhone, 10 (Text), 20
28 H1Fax, 10 (Text), 20
29 H1Email, 12 (Memo), 0
30 H2FirstName, 10 (Text), 25
31 H2MiddleName, 10 (Text), 25
32 H2LastName, 10 (Text), 25
33 H2OfficePhone, 10 (Text), 20
34 H2CellPhone, 10 (Text), 20
35 H2Email, 12 (Memo), 0
36 H2Co-buyer occupation, 10 (Text), 50
37 RelationshipID, 4 (Long), 4
38 HowID, 4 (Long), 4
39 Have You been on our Website, 1 (Boolean), 1
40 Referred by?, 10 (Text), 50
41 OwnProperty?Yes, 1 (Boolean), 1
42 OwnProperty?No, 1 (Boolean), 1
43 Property Value, 4 (Long), 4
44 MSTBYes, 1 (Boolean), 1
45 MSTBNo, 1 (Boolean), 1
46 HouseSizeMin, 4 (Long), 4
47 HouseSizeMax, 4 (Long), 4
48 LiveWhere, 10 (Text), 50
49 PriceRangeMinimum, 5 (Currency), 8
50 PriceRangeMaximum, 5 (Currency), 8
51 WhentoBuild, 10 (Text), 50
52 HouseTypeID, 4 (Long), 4
53 HouseModelID, 4 (Long), 4
54 InformationPacketSent?, 1 (Boolean), 1
55 DatePacketSent, 8 (Date), 8
56 Notes, 12 (Memo), 0
57 Homeowners Manual Given?, 1 (Boolean), 1
58 Date Homeowners Manual, 8 (Date), 8
59 RepID, 4 (Long), 4
60 Co Rep ID, 10 (Text), 50
61 Start of Construction, 8 (Date), 8
62 C of O Date, 8 (Date), 8
63 Date Complete, 8 (Date), 8
64 Square Footage 1st, 4 (Long), 4
65 Square Footage 2nd, 4 (Long), 4
66 Bedrooms, 4 (Long), 4
67 Bathrooms, 4 (Long), 4
68 Master on 1st Floor, 1 (Boolean), 1
69 Dead Lead, 1 (Boolean), 1
70 Income, 10 (Text), 50
71 Marital Status, 10 (Text), 50
72 Reason for Interest, 10 (Text), 50
73 Spec House, 1 (Boolean), 1
74 POID, 4 (Long), 4

tbl_Contract
==========================
0 ContractID, 4 (Long), 4
1 ContactID, 4 (Long), 4
2 Contract Month Look-up, 10 (Text), 50
3 Contract Day Look-up, 4 (Long), 4
4 Contract Year Look-up, 4 (Long), 4
5 Lot Location, 10 (Text), 50
6 Lot Number, 10 (Text), 50
7 Section Number, 10 (Text), 50
8 Municipality, 4 (Long), 4
9 County, 4 (Long), 4
10 Septic System, 5 (Currency), 8
11 House Model, 4 (Long), 4
12 Prevailing Interest Rate, 4 (Long), 4
13 #Years, 10 (Text), 10
14 At Signing, 4 (Long), 4
15 Sales Type, 4 (Long), 4
16 Sales Commission Details #, 10 (Text), 50
17 PO ID#, 4 (Long), 4

tbl_HouseModel
==========================
0 HouseModelID, 4 (Long), 4
1 HouseModel, 10 (Text), 50

tbl_HouseType
==========================
0 HouseTypeID, 4 (Long), 4
1 HouseType, 10 (Text), 50

tbl_How
==========================
0 HowID, 4 (Long), 4
1 How, 10 (Text), 50

tbl_Relationship
==========================
0 RelationshipID, 4 (Long), 4
1 Relationship, 10 (Text), 50

tbl_Rep
==========================
0 RepID, 4 (Long), 4
1 RepLastName, 10 (Text), 20
2 RepFirstName, 10 (Text), 15

Toilet Table
==========================
0 Toilet ID, 4 (Long), 4
1 Toilet Type, 10 (Text), 50
2 Toilet Manufacturer, 4 (Long), 4

Township Table
==========================
0 Township ID, 4 (Long), 4
1 Township, 10 (Text), 50

Windows Table
==========================
0 Windows ID, 4 (Long), 4
1 Windows Type, 10 (Text), 50
2 Windows Manufacturer, 4 (Long), 4
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

lookup fields , LinkMasterFields, LinkChildFields
---

Hi Ronnie,

thank you

"...tried putting in a look-up for ..."

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

so, if you have used lookup fields in the table design, change the
Display property in the respective tables to textbox (or checkbox if yes/no)

~~~ LinkMasterFields, LinkChildFields ~~~

the way you link a subform to a mainform is by using the
LinkMasterFields and LinkChildFields properties of the subform control


from the design view of the main form

1. turn on the properties window
(r-click anywhere and choose Properties from the shortcut menu)

2. click ONE time on the subform control

3. click on the DATA tab of the Properties window

LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;maincontrolname
LinkChildFields --> MainID;childcontrolname

where MainID is an autonumber field (usually) in the parent table and a
Long Integer field in the child table

the link field properties actually use controls, not fields -- so the
controls you reference must be ON each of the respective forms and the
NAME property is what you need to reference -- if a control is bound, I
usually make the Name of the control the same as the ControlSource (what
is in it)

It is common to set the Visible property to No for the control(s) used
in LinkChildFields

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

Personally, I edit subforms directly, not from inside the main form -- I
have had trouble with Access putting property changes in the wrong place
for RowSources and RecordSource. Since it happens there occassionally,
for major changes, I go to the design view of the "sub"form directly
from the databse window when the main form is closed.

~~~

check your link fields to ensure you are using the name property of the
respective controls and ensure that each referenced control is actually
on the form/subform (its visible property can be false)



Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
G

Guest

Thanks for all your help. I have decided to rebuild based on your tips--very
helpful. I've been working all day. My tables are all in, some forms and
queries and all seem to be doing what they are supposed to. Thanks again!!!
 
S

strive4peace

you're welcome, Ronnie ;) happy to help

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Doubling amount on Purchase orders 2
Many-to-Many relationship, forms & subforms 3
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Continous Forms and preventing a new record being created 2
how to create new record automatically 2
Form/Subform 2
Subform causing duplicate records? 7
Update Two Tables Using One Form Contents 0

Top