Datasheet Form

[Lenee]

I have created a datasheet form from a query everything is there. I am trying
to enter data here but it will not except it here. Any help would be greatly
appreciated.

 

[Jerry Whittle]

I'm betting that the query contains more than one table. This can be a
problem.

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

Information on primary keys and relationships would be a nice touch too.

 

[Lenee]

Jerry you are absolutely correct. I have 4 tables in my query. Here is the
information you are asking for.

Customer Table - Primary Key is CustomerID
Estimate Table - Primary Key is EstimateID
CutWork Table - Primary Key is CutworkID
Cut Table - No Primary Key

The CustomerID is in all of the tables and joined here.

SELECT CutT.CutCust2010, CutWorkT.Cutwk1, CutT.CutArea, CutT.CutOrdNum,
CustomerT.ABC, CustomerT.Address, CustomerT.City, CustomerT.State,
CustomerT.Zip, CustomerT.MapPage, CustomerT.MapPageLetter,
CustomerT.MapPageNum, CutWorkT.Cutwk2, CutT.CutPrice, CutT.CutEOW,
CutT.Cutcomments, EstimateT.SqFt, EstimateT.Services, CutT.CutLR,
CustomerT.CustomerID, CustomerT.MyCounter, CustomerT.FirstName,
CustomerT.LastName
FROM ((CustomerT INNER JOIN CutT ON CustomerT.CustomerID = CutT.CustomerID)
INNER JOIN EstimateT ON CustomerT.CustomerID = EstimateT.CustomerID) INNER
JOIN CutWorkT ON CustomerT.CustomerID = CutWorkT.CustomerID
WHERE (((CutT.CutCust2010)=Yes) AND ((CutWorkT.Cutwk1) Is Null))
ORDER BY CutT.CutArea, CutT.CutOrdNum;

I hope this helps you to help me.

 

[Steve]

Your query is probably not updateable. Open the query and look at the
navigation buttons. If the new record button os grayed out, your query is
not updateable. When a query is not updateable, you won't be able to add new
records to a form based on the query. For example, union queries and
crosstab queries are not updateable.

Steve
(e-mail address removed)

 

[Linq Adams via AccessMonster.com]

The reasons that a bound form will not allow records to be edited include:

1) AllowEdits for the form is set to No
2) The underlying query the form is based on is Read-Only
3) User doesn't have Write Permission for the folder where the data resides.
4) Form's Recordset Type is set to Snapshot

To see if #2 is the case, from the Objects Dialog box go to Queries and click
on the query that your form is based on to open it. Now try to edit a record
directly in the query. If you're unable to do so, this is the cause of your
problem.

If your query is Read-Only, follow this link to Allen Browne’s article
explaining the reasons this happens.

http://allenbrowne.com/ser-61.html

 

[Jerry Whittle]

That's just not going to work. Here's what to try.

1. Open the Relationships window and join the 4 tables there. Enable
Referential Integrity. If it won't let you, there may be a problem with data
especially with the Cut Table table as it doesn't have a primary key.

2. Create a form based on just the CustomerT table.

3. Create a subform on the above form based on the EstimateT. Since you have
the relationships built up above, the subform Wizard should do most of the
work.

4. Repeat for the other two tables.

It's possible that you will need sub-subforms, but your SQL statement seems
to say that the other 3 tables are linked directly to the CustomerT table.

 

[Lenee]

Jerry I was able to make the sub-forms but what I am trying to do is get
information from each table on 1 datasheet to enter data. I need to see
customer name with there area (different table) and the cut week (a third
table), so that i can enter the data in the cut week field for the customers
that were cut that week. I am looking to enter 90 to 120 entries in a day.
Doing the sub-forms still only lets me look at 1 record at a time. Am I
trying to do the impossibe here?

I did go to the Relationships and did as you suggested, even put in a
primary key, still didn't work.

Thanks for your help and any other advise.

 

[Lenee]

Steve you were right. Is there any way to make it updateable?

 

[Steve]

Lenee,

Your tables probably need a simple tweak! I presume you want to enter an
estimate for a customer. Is that correct? Please explain Cut Table and
CutWork Table. Is CutWork the details of a Cut? Could you respond back with
the fields in the Estimate Table, Cut Table and CutWork Table in this
format:

Estimate
EstimateID
<your fields>

Cut
<your fields>

CutWork
CutworkID
<your fields>

Steve

 

[John W. Vinson]

Jerry I was able to make the sub-forms but what I am trying to do is get
information from each table on 1 datasheet to enter data. I need to see
customer name with there area (different table) and the cut week (a third
table), so that i can enter the data in the cut week field for the customers
that were cut that week. I am looking to enter 90 to 120 entries in a day.
Doing the sub-forms still only lets me look at 1 record at a time. Am I
trying to do the impossibe here?

Try changing the subform's Default View property from "Single Form" to
"Continuous Forms". It's perfectly possible to see one "parent" table record
(Customer in your case) with multiple "child" table records on one screen, if
you design your queries correctly.

Your attempt to create One Great Master Query to do everything is, in this
case, doomed to failure since there is no physical or logical relationship
between the Estimate and the CutWork tables - each is related to the Customer,
but it appears that they are not related to each other.

 

[Lenee]

Steve

I need to see customer name (Customer Table) with there area (Cut table)
and the cut week (Cutwork table), so that i can enter the data in the cut
week field for the customers that were cut that week. I am looking to enter
90 to 120 entries in a day. Doing the sub-forms still only lets me look at 1
record at a time. Am I
trying to do the impossibe here? The Estimate table only has 2 fields that I
need for this query. I will be putting Customer information in the customer
table from a form, then the estimate information from a form, cut information
from a form, the nice neet idiot proof ones. I have those all made linked
together works great. Now trying to put this in a datasheet form to input the
cuts (date) quickly. I hope this isn't to confusing.

Here is the information you asked for. If you need anything else just let me
know.
Thanks for your help and advise.

Customer Table
CustomerID
MyCounter
ABC
FirstName
LastName
Address
City
MapPage
MapPageLetter
MapPageNum

Cut Table
CustomerID (Foreign Key)
CutCust2010
CutArea
CutOrdNum
CutPrice
CutEOW
CutComments
CutLR

CutWork Table
CustomerID (Foreign Key)
Cutwk1
Cutwk2
Cutwk3
Cutwk4
Cutwk5

Estimate Table
CustomerID (Foreign Key)
Services
SqFt


These are the fields that I am putting in my query to try and make a
datasheet form for quick entries in the cut weeks. All tables have a primary
key except for the Cut table, but I do have a field that Can be put as a
primary key.

 

[Steve]

Lenee,

It is very confusing!!!

1. What is a "Cut"?

2. What are CutCust2010 and CutArea in the Cut Table?

3. What are Cutwk1, Cutwk2, ..... Cutwk5 in the Cut Work Table?

4. What are Services and SqFt in the Estimate table?


The Cut Work Table is definitely wrong but I can't tell you how to change it
because everything is very confusing.

It's going to take some time to sort things out. Would you consider hiring
me to work with you to develop what you have into a proper database? I
provide help with Access, Excel and Word applications for a small fee. My
fee to help you would be very reasonable. Contact me offline at
(e-mail address removed) and let's get your database working.

Steve

 

[Gina Whipp]

Lenee,

Please explain a little of what business this applies to so we can get a
clearer picture. And for FREE we (and by that I don't mean Steve) will work
with you to get an updateable query (one that can be edited).

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve

I need to see customer name (Customer Table) with there area (Cut table)
and the cut week (Cutwork table), so that i can enter the data in the cut
week field for the customers that were cut that week. I am looking to enter
90 to 120 entries in a day. Doing the sub-forms still only lets me look at 1
record at a time. Am I
trying to do the impossibe here? The Estimate table only has 2 fields that I
need for this query. I will be putting Customer information in the customer
table from a form, then the estimate information from a form, cut
information
from a form, the nice neet idiot proof ones. I have those all made linked
together works great. Now trying to put this in a datasheet form to input
the
cuts (date) quickly. I hope this isn't to confusing.

Here is the information you asked for. If you need anything else just let me
know.
Thanks for your help and advise.

Customer Table
CustomerID
MyCounter
ABC
FirstName
LastName
Address
City
MapPage
MapPageLetter
MapPageNum

Cut Table
CustomerID (Foreign Key)
CutCust2010
CutArea
CutOrdNum
CutPrice
CutEOW
CutComments
CutLR

CutWork Table
CustomerID (Foreign Key)
Cutwk1
Cutwk2
Cutwk3
Cutwk4
Cutwk5

Estimate Table
CustomerID (Foreign Key)
Services
SqFt


These are the fields that I am putting in my query to try and make a
datasheet form for quick entries in the cut weeks. All tables have a primary
key except for the Cut table, but I do have a field that Can be put as a
primary key.

 

[John... Visio MVP]

It's going to take some time to sort things out. Would you consider hiring
me to work with you to develop what you have into a proper database? I
provide help with Access, Excel and Word applications for a small fee. My
fee to help you would be very reasonable. Contact me offline at
(e-mail address removed) and let's get your database working.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support! He also offers
questionable servies at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[Lenee]

Gina,

We are a Lawn Maintanence Co. which had 4 divisions within the company:
1. Mowing service
2. Fertilization service (LCP)
3. Misc Work - Mulching, trim shrubs, etc.
4. Hardscapes - Patios, walkways walls, etc.

We have the following customer base:
1. 600 cut cust.
2. 250 lcp cust.
3. 150 Misc cust.
4. 25 Hardscape cust.

Customers can be either a cut, lcp,misc or hardscape customer or any
combination of these.

We do approximately 2500 estimates per year.

Our mind set was to set up the tables as follows:

1. Customer Table - Name, rank, serial number
2. Estimate Table - Info pretaining to estimate done.
3. Cut Table - All infor pretaining to the cut side.
4. Cutwork T - 36 cut weeks (to enter dates cut) plus leaf removal weeks.
5. CutPayment T - Monthly bill and payment info.
6. LCP T - All information pretaining to LCP work.
7. Misc T - All info pretaining to Misc work.
8. Hardscape T - All info pretaining to Hardscape work.

There is a total of 309 fields in all of these tables and none of them are
duplicates. We have tried putting them all into 1 table, but it was to big.

The problem arises when we try to link 4 tables CustT, CutT, CutworkT and
EstimateT in a query.
We do most of our work in a Datasheet view/form since we cut approx 150
propertys a day. Inputing todays date takes approximately 1 minute in a
datasheet view vs 10 minutes in a form view.

Should we try to put this in 2 tables?
Table 1 Customer, Cut, Cutwork, Cutpayments & Estimate.
Table 2 LCP, Misc & Hardscape.

Also another issue is we are trying to set an account number using the DMAX
function to lookup the greatest number and increase by 1. Isn't happening
here.

We have been using Microsoft Works for the past 15 years or so and it has
been working fine, just a little limiting as far as the amount of filters and
reports.

Thanks for help and advice.

 

[Gina Whipp]

Lenee,

Okay, the problem with getting the information is that your tables are set
up incorrectly. Now, you have to tell me if you are able to adjust them.
If you are then you need to type in the tables and there names here OR you
can send it to me (get my eMail address from my website) and I will review
and send you a proper data model with which you can import your data into.
(I will not be able to look at it till tonight but I will look at it and get
it back to you.)

By the way, table being big? If you alot of records then that's not big...
if you mean alot of fields then that's a sign of an incorrect data model.
The way you are trying to link the the tables you will never get all the
information without a UNION query.

As a side note... everything I offered is FREE!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

We are a Lawn Maintanence Co. which had 4 divisions within the company:
1. Mowing service
2. Fertilization service (LCP)
3. Misc Work - Mulching, trim shrubs, etc.
4. Hardscapes - Patios, walkways walls, etc.

We have the following customer base:
1. 600 cut cust.
2. 250 lcp cust.
3. 150 Misc cust.
4. 25 Hardscape cust.

Customers can be either a cut, lcp,misc or hardscape customer or any
combination of these.

We do approximately 2500 estimates per year.

Our mind set was to set up the tables as follows:

1. Customer Table - Name, rank, serial number
2. Estimate Table - Info pretaining to estimate done.
3. Cut Table - All infor pretaining to the cut side.
4. Cutwork T - 36 cut weeks (to enter dates cut) plus leaf removal weeks.
5. CutPayment T - Monthly bill and payment info.
6. LCP T - All information pretaining to LCP work.
7. Misc T - All info pretaining to Misc work.
8. Hardscape T - All info pretaining to Hardscape work.

There is a total of 309 fields in all of these tables and none of them are
duplicates. We have tried putting them all into 1 table, but it was to big.

The problem arises when we try to link 4 tables CustT, CutT, CutworkT and
EstimateT in a query.
We do most of our work in a Datasheet view/form since we cut approx 150
propertys a day. Inputing todays date takes approximately 1 minute in a
datasheet view vs 10 minutes in a form view.

Should we try to put this in 2 tables?
Table 1 Customer, Cut, Cutwork, Cutpayments & Estimate.
Table 2 LCP, Misc & Hardscape.

Also another issue is we are trying to set an account number using the DMAX
function to lookup the greatest number and increase by 1. Isn't happening
here.

We have been using Microsoft Works for the past 15 years or so and it has
been working fine, just a little limiting as far as the amount of filters
and
reports.

Thanks for help and advice.

 

[Lenee]

Gina, I tried to go to your website but it wasn't working correctly. Not sure
if I have correct email address, will send a quick note to see if it is the
correct one.

I did give you the name of the tables. I am able to ajust them. Do you need
all of their field names? Not really sure what you need. Do you want me to
email you the whole database?

Table to big as in to many fields. That is why we are doing more tables.

 

[Gina Whipp]

Lenne,

Thanks for the heads up regarding my website... will go check that.
HOWEVER, you did send me an eMail and I responded. I stepped away from my
machine so I did not see it straight away! So if you just want to send it
please do!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina, I tried to go to your website but it wasn't working correctly. Not
sure
if I have correct email address, will send a quick note to see if it is the
correct one.

I did give you the name of the tables. I am able to ajust them. Do you need
all of their field names? Not really sure what you need. Do you want me to
email you the whole database?

Table to big as in to many fields. That is why we are doing more tables.