Creating Updateable Datasheet Based on Mult. Queries

R

Robnauticus

Hello all,

I have a scheduling database that I am only using one table "DateLog"

It contains these fields:
  • JobID Int-PK
  • LotID Txt-PK
  • TaskID Int-PK - List from TaskList Table
  • TripID Int-PK
  • TaskDate Date
  • ForemanID Int - List from Foreman Table
  • TaskQty Int
  • TaskMisc1 Txt
  • TaskMisc2 Txt
  • Notes Memo

Every time a trip is taken to a job site, a record is created. I would like
to have a datasheet view of this table that will join all of the tasks for a
particular JobID and LotID together on the same record for easy viewing.
The list of tasks are static and will not change from job to job. The only
fields that needs to be seen are the JobID and LotID at the row header. The
rest of the data is the TaskDate for each task.

What I have attempted so far is to create a separate query for each TaskID
and then to join them by the JobID and LotID. I have gotten to the point
that I can put values into the datasheet, but I get an error that the primary
key cannot be null.

Am I approaching this the correct way? Is there a way I can point the
hidden key fields to the fields they are joined to? Access does not seem to
be smart enough to figure this out itsself and I am not sure how to tell it
to create the new record using the fields that it is related to.

This is very difficult to explain, any help would be greatly appreciated. I
will be glad to post further clarification if I did a bad job explaining what
I am trying to do.

Thanks,
Rob
 
K

Ken Snell \(MVP\)

Per your posted field list, you're using at least three tables in the
database < g >.

Post the SQL statement of the query that you're trying to use (the one
giving the error when you try to do an update). And show us some sample data
that you're entering on the form.
 
R

Robnauticus

SELECT DISTINCTROW r_FoamOrdAll.JobID, r_FoamOrdAll.LotID,
r_FoamOrdAll.TaskDate, r_FoamRecAll.TaskDate, r_FoamCoatAll.TaskDate,
r_SandDelAll.TaskDate, r_MatArrivalAll.TaskDate, r_FrameAll.TaskDate,
r_FrameAll.TaskQty, r_ScafUpAll.TaskDate, r_ScafUpAll.ForemanID,
r_ScafUpAll.TaskQty, r_LathAll.TaskDate, r_LathAll.ForemanID,
r_SheetRockAll.TaskDate, r_SheetRockAll.TaskMisc2, r_BrownAll.TaskDate,
r_BrownAll.ForemanID, r_BrownWaterAll.TaskDate, r_BrownWaterAll.ForemanID,
r_FoamInstallAll.TaskDate, r_FoamInstallAll.ForemanID, r_FinishAll.TaskDate,
r_FinishAll.ForemanID, r_FinishWaterAll.TaskDate, r_FinishWaterAll.ForemanID,
r_TouchUpAll.TaskDate, r_TouchUpAll.ForemanID, r_ScafDownAll.TaskDate,
r_ScafDownAll.ForemanID, r_ScafDownAll.TaskQty, r_CleanUpAll.TaskDate,
r_CleanUpAll.ForemanID, r_WalkAll.TaskDate, r_WalkAll.ForemanID
FROM ((((((((((r_FoamOrdAll LEFT JOIN r_FoamRecAll ON (r_FoamOrdAll.JobID =
r_FoamRecAll.JobID) AND (r_FoamOrdAll.LotID = r_FoamRecAll.LotID)) LEFT JOIN
r_FoamCoatAll ON (r_FoamRecAll.JobID = r_FoamCoatAll.JobID) AND
(r_FoamRecAll.LotID = r_FoamCoatAll.LotID)) LEFT JOIN r_SandDelAll ON
(r_FoamCoatAll.JobID = r_SandDelAll.JobID) AND (r_FoamCoatAll.LotID =
r_SandDelAll.LotID)) LEFT JOIN r_MatArrivalAll ON (r_SandDelAll.JobID =
r_MatArrivalAll.JobID) AND (r_SandDelAll.LotID = r_MatArrivalAll.LotID)) LEFT
JOIN r_FrameAll ON (r_MatArrivalAll.JobID = r_FrameAll.JobID) AND
(r_MatArrivalAll.LotID = r_FrameAll.LotID)) LEFT JOIN r_ScafUpAll ON
(r_FrameAll.JobID = r_ScafUpAll.JobID) AND (r_FrameAll.LotID =
r_ScafUpAll.LotID)) LEFT JOIN r_LathAll ON (r_ScafUpAll.JobID =
r_LathAll.JobID) AND (r_ScafUpAll.LotID = r_LathAll.LotID)) LEFT JOIN
r_SheetRockAll ON (r_LathAll.JobID = r_SheetRockAll.JobID) AND
(r_LathAll.LotID = r_SheetRockAll.LotID)) LEFT JOIN ((((r_BrownAll LEFT JOIN
r_BrownWaterAll ON (r_BrownAll.JobID = r_BrownWaterAll.JobID) AND
(r_BrownAll.LotID = r_BrownWaterAll.LotID)) LEFT JOIN r_FoamInstallAll ON
(r_BrownWaterAll.JobID = r_FoamInstallAll.JobID) AND (r_BrownWaterAll.LotID =
r_FoamInstallAll.LotID)) LEFT JOIN r_FinishAll ON (r_FoamInstallAll.JobID =
r_FinishAll.JobID) AND (r_FoamInstallAll.LotID = r_FinishAll.LotID)) LEFT
JOIN r_FinishWaterAll ON (r_FinishAll.JobID = r_FinishWaterAll.JobID) AND
(r_FinishAll.LotID = r_FinishWaterAll.LotID)) ON (r_SheetRockAll.JobID =
r_BrownAll.JobID) AND (r_SheetRockAll.LotID = r_BrownAll.LotID)) LEFT JOIN
r_TouchUpAll ON (r_FinishWaterAll.JobID = r_TouchUpAll.JobID) AND
(r_FinishWaterAll.LotID = r_TouchUpAll.LotID)) LEFT JOIN ((r_ScafDownAll LEFT
JOIN r_CleanUpAll ON (r_ScafDownAll.JobID = r_CleanUpAll.JobID) AND
(r_ScafDownAll.LotID = r_CleanUpAll.LotID)) LEFT JOIN r_WalkAll ON
(r_CleanUpAll.JobID = r_WalkAll.JobID) AND (r_CleanUpAll.LotID =
r_WalkAll.LotID)) ON (r_TouchUpAll.JobID = r_ScafDownAll.JobID) AND
(r_TouchUpAll.LotID = r_ScafDownAll.LotID);



You will see the table name "r_whatever" these are the queries that I
mentioned that only return records from the DateLog table with a certain
TaskID.




JobID LotID r_FoamOrdAll.TaskDate r_FoamRecAll.TaskDate r_FoamCoatAll.TaskDate r_SandDelAll.TaskDate r_MatArrivalAll.TaskDate r_FrameAll.TaskDate r_FrameAll.TaskQty r_ScafUpAll.TaskDate r_ScafUpAll.ForemanID r_ScafUpAll.TaskQty r_LathAll.TaskDate r_LathAll.ForemanID r_SheetRockAll.TaskDate TaskMisc2 r_BrownAll.TaskDate r_BrownAll.ForemanID r_BrownWaterAll.TaskDate r_BrownWaterAll.ForemanID r_FoamInstallAll.TaskDate r_FoamInstallAll.ForemanID r_FinishAll.TaskDate r_FinishAll.ForemanID r_FinishWaterAll.TaskDate r_FinishWaterAll.ForemanID r_TouchUpAll.TaskDate r_TouchUpAll.ForemanID r_ScafDownAll.TaskDate r_ScafDownAll.ForemanID r_ScafDownAll.TaskQty r_CleanUpAll.TaskDate r_CleanUpAll.ForemanID r_WalkAll.TaskDate r_WalkAll.ForemanID


JobID LotID r_FoamOrdAll.TaskDate r_FoamRecAll.TaskDate
1 1001 10/1/2004 10/1/2004 10/1/2004 10/1/2004
1 1002 10/1/2004 10/1/2004 10/1/2004 10/1/2004
1 1003 10/1/2004 10/1/2004 10/1/2004 10/1/2004
1 1004 10/1/2004 10/1/2004 10/1/2004 10/1/2004
1 1005 10/1/2004 10/1/2004 10/1/2004 10/1/2004
1 1006 10/1/2004 10/1/2004 10/1/2004 10/1/2004
 
K

Ken Snell \(MVP\)

The use of DISTINCTROW will make a query nonupdatable.

Additionally, it appears that the inclusion of so many fields from so many
tables will lead to a nonupdatable query.

See these articles for more information about what can make a query
nonupdatable (watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc
 
R

Robnauticus

Thanks for the pointer Ken,

I tried the DISTINCTROW on another's suggestion, still did not work. Also I
have read several of the articles you pointed me to and I tried to follow the
rules as much as possible.

Do you think if I included the other key fields in the query it may help? I
cannot tell when it says "primary key cannot be null" which field it is
referring to.

I appreciate the help!
 
R

Robnauticus

Do you think that my next step to achieve this would be to program a custom
form that would manually handle this data?

Any other suggestions on what I could do to achieve this would be awesome,

Thanks again Ken.

Rob
 
K

Ken Snell \(MVP\)

Didn't get a chance to prepare a reply to this tonite... (work interfered).
I'll post a reply tomorrow.
 
K

Ken Snell \(MVP\)

Because I don't know the exact table structure/details, I cannot give you a
specific suggestion. However, including the primary key fields from all the
tables may help you make the query updatable. However, I think that you may
find it still won't work for your needs because you are using so many
different tables.

I suggest that you consider using separate subforms to enter the data into
the children (related) tables, and let the main form be the data from the
parent table.
 
R

Robnauticus

Hey Ken,

Thanks for the help so far, I took away the distinctrow and changed the
dynaset to inconsistent updates. It allows me to update the records that
really exist in the datasheet. I also added all of the key fields from each
of the underlying queries.

I plan on using this on a datasheet style form, do you think that I could
write some event procedures to automatically fill in the key fields for each
missing record?

That seems like a fairly easy approach to get this thing to work. (silly
clients, always wanting the complicated stuff). Any thoughts would be
appreciated...

Thanks again,
Rob
 
K

Ken Snell \(MVP\)

Not knowing how your users will use the form, I will say that it is possible
to have VBA code write values into empty (or non-empty) textboxes, using
many different events.

As to which event, that will depend upon what your users will do.
Generically, you likely will want to use the Form's BeforeUpdate event to do
this. You can test to see if a control is "empty" (I usually check if
Len(Me.ControlName.Value & "") = 0 to see if it's empty), and if it is then
write data into it.
 
R

Robnauticus

I have been working on doing just that, it seems to be working just fine.

Thanks sooo much for the help.
 

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

dsum issue 2
Adding Customfield Value to a Process through PSI 1
parameter query and making a new record 4
Select dates 1
Creating a Search using a combo box and then creating a report. 4
Formatting Concatenated Fields 2
Button on Form to Run Report based on Query with Parameters 4
Creating Forms 0

Top