odd subform which is on the one side of a one to many relationship

[WindandWater]

Hello All,

I have a database which is a trouble ticket system called quicksheets.
I have a main form based on this table and I have some subforms
based on tables which are on the many side of the relationship.

Here is the part that has me stumped. I have one table that is for
jobs which many quicksheets could be associated to just one Job.
I have this table bound to a subform which is a single form.

I have linked these two table by the primary key of jobs table with
jobID and it is the foreign key in the Quicksheets table.
I have played around with the Master /Child links where I thinkk it
should be on JobID for both. I have tried using the primary key of
quicksheets, QuicksheetID but that changes my relationshiip
making quicksheet on the one side and jobs on the many side....

I know that the main form is getting confused because it expects the job
field
to be populated before the Quicksheet information to be entered. I can see
on the main form that JobID does not update when I put in Job data in the
subform.

I am wondering how do I get the jobID field to populate first ?
Maybe I need to flip it around and make my Jobs table the main
control source for the form, then embed the quicksheets and then
embed my other subforms within the quicksheet subforms?

Any suggestions?

Thank you,

Johnny

 

[WindandWater]

Hello Bonnie,

Yes, I will watch the multiple posting. It was not intentional, somehow
there were errors in my browser and I thought it did not post.

About the database, it is possible to have trouble tickets with out jobs. In
fact, the
trouble tickets are the main record keepers for this database. The jobs
merely
are the way that funding is acquired to fix the trouble ticket (quicksheet).

So, I tried making a new form with jobs as the main control source. The
problem that I see is, how to make the Quicksheets (trouble ticket) without
having it associated with a job.

Thank you,

Johnny

 

[WindandWater]

Hi Bonnie,

This sounds like a good fit for my form then I only add jobs when one
is needed.
So, far I created a button to add a new record but I am stuck with were
to configure it to get the query? Do I need some fields on the form to
bind to the query?

Thank You,
Johnny

No that won't work. You would have to set up a job on the form to have a
subform. The form would have the master and the subform the child in the
relationship. I would probably generate a job on all of them and have some
type of indicator that it is for in-house only (not for payment).

Otherwise, if you only selectively want a job generated - create an append
query that get's its details from the trouble ticket form. Add a button to
the trouble ticket form to run the append query only when you want a job
record. So in the field value (what you want to append), you would put:

forms!whateverformname.whatevercontrol


Bonnie
http://www.dataplus-svc.com

Hello Bonnie,

Yes, I will watch the multiple posting. It was not intentional, somehow
there were errors in my browser and I thought it did not post.

About the database, it is possible to have trouble tickets with out jobs. In
fact, the
trouble tickets are the main record keepers for this database. The jobs
merely
are the way that funding is acquired to fix the trouble ticket (quicksheet).

So, I tried making a new form with jobs as the main control source. The
problem that I see is, how to make the Quicksheets (trouble ticket) without
having it associated with a job.

Thank you,

Johnny

Hi Johnny,

[quoted text clipped - 43 lines]

Johnny

 

[WindandWater]

Bonnie,

That is really cool! So, I have the button working and its
appending to the correct table that I'm testing on.

There is one problem:
I have narrowed it down to the query it self:

PARAMETERS [enter jobNumber] Text ( 255 );
INSERT INTO tblJobsCopy ( jobNumber, quicksheetID )
SELECT tblJobs.jobNumber, Forms!frmQuicksheet!quickSheetID AS Expr1
FROM tblJobs
WHERE (((tblJobs.jobNumber)=[enter jobNumber]));

It doesn't create a new job unless it is already existing in my tblJobs.
Currently my tblQuicksheets is on the many side of a one to many
relationship to Jobs. I have referential integrity and cascade updates
checked.

I have tried to turn off the referential integrity, but I think its more my
lack
of skill with SQL.

Thank you,

Johnny

In the onclick event put:

DoCmd.OpenQuery "QueryName"

Bonnie
http://www.dataplus-svc.com

Hi Bonnie,

This sounds like a good fit for my form then I only add jobs when one
is needed.
So, far I created a button to add a new record but I am stuck with were
to configure it to get the query? Do I need some fields on the form to
bind to the query?

Thank You,
Johnny

No that won't work. You would have to set up a job on the form to have a
subform. The form would have the master and the subform the child in the

[quoted text clipped - 35 lines]

Johnny

 

[WindandWater]

I am so close, but I am missing something.
My objective is a subform with a series of fields
that would be filled out about the job.

So far we have built a query that accoplishes part of that.
I was trying to make the append query be like a parameter
query to ask for the the job number, but actually if I could
build a subform with bound fields to the append query that would be great.
It appears that append querries don't work that way, as I try to
build subforms and controls I don't see the append query.


So, how can I take that query and use it to enter data to the
jobs table?

Thank you so Much!

Johnny

It's the WHERE statement. You don't need a WHERE statement. You are
creating a new record based on the current record in your form. I'm assuming
the Forms!frm.quickSheetID is that field - name it JobNumber instead of the
default Expr1 and it will append to that field. Only add what is on the form
(which will result in one record) or some calculated value that you might
want to populate (also one record).

Bonnie
http://www.dataplus-svc.com

Bonnie,

That is really cool! So, I have the button working and its
appending to the correct table that I'm testing on.

There is one problem:
I have narrowed it down to the query it self:

PARAMETERS [enter jobNumber] Text ( 255 );
INSERT INTO tblJobsCopy ( jobNumber, quicksheetID )
SELECT tblJobs.jobNumber, Forms!frmQuicksheet!quickSheetID AS Expr1
FROM tblJobs
WHERE (((tblJobs.jobNumber)=[enter jobNumber]));

It doesn't create a new job unless it is already existing in my tblJobs.
Currently my tblQuicksheets is on the many side of a one to many
relationship to Jobs. I have referential integrity and cascade updates
checked.

I have tried to turn off the referential integrity, but I think its more my
lack
of skill with SQL.

Thank you,

Johnny

In the onclick event put:

[quoted text clipped - 19 lines]

Johnny

 

[WindandWater]

Bonnie,

So, when I removed the Where clause,
it removed my parameter query which is where I was entering the job number.
I am not clear how to get that value into the append query.

You saw that I had an expression getting the field value of QuicksheetID.
That is the (primary key of Quicksheet), it would be used to to populate the
same
field QuicksheetID FK in jobs table. Inaddition, I have four other fields
that I would
possibly need to populate in the jobs table.

Can anyone recommend what is the best way to do this? I am thinking either
use
parameter query or some sort of unbound fields that I load into the append
query?

Thank you,

Johnny

Right now I am trying to just use my one Ticket form to call an append query
to the jobs table. I do not have a Job number field in the Quicksheet table
to enter the data. This is why I was trying to use the parameter query.

Johnny

You are going back to where we were before. If you have a subform and you
don't want a job it won't work. Why doesn't the append query work? You were
doing two things in that query - asking for the job number and adding info
from the form? Which do you want to do? If you just want to run an append
query that you give a number to, you can do that. It requires an extra step
but I am not sure were you are going now.

Bonnie
http://www.dataplus-svc.com

I am so close, but I am missing something.
My objective is a subform with a series of fields
that would be filled out about the job.

So far we have built a query that accoplishes part of that.
I was trying to make the append query be like a parameter
query to ask for the the job number, but actually if I could
build a subform with bound fields to the append query that would be great.
It appears that append querries don't work that way, as I try to
build subforms and controls I don't see the append query.

So, how can I take that query and use it to enter data to the
jobs table?

Thank you so Much!

Johnny

It's the WHERE statement. You don't need a WHERE statement. You are
creating a new record based on the current record in your form. I'm assuming

[quoted text clipped - 38 lines]

Johnny

 

[WindandWater]

Hello All,

I just wanted to follow up and tell you that I did get this update working.
So, my main form or Parent table is Quicksheets and my child table is
Jobs. Jobs is a subform of Quicksheets. Normally this would be a one to
many, with jobs on the many side of the relationship. In this situation
jobs are on the one side and quicksheet are on the many side.

So, my solution was to make a macro which runs an update query to
add my Primary key jobID to the Quicksheet table. This occurs on the
after update event for the subform Jobs. I don't know if this is the best
event to trigger the Macro. Please let me know if my method seems too
cumbersome or if perhaps my database design is flawed.

Thank you,

Johnny

Create a small form with 1 unbound control labeled JobID. Put the code to
run the append query in the OnExit event of that control. In your original
form button, change the OnClick event to open this small form instead of
running the append query. In the query, refer to the unbound control of the
small form for the value of your JobID. Now when you click your button, the
small form with open, you will input the JobID you want, exit the control and
the query will run - adding the JobID and whatever other values you want.

Have you considered making the JobID an autonumber type field?

Bonnie,

So, when I removed the Where clause,
it removed my parameter query which is where I was entering the job number.
I am not clear how to get that value into the append query.

You saw that I had an expression getting the field value of QuicksheetID.
That is the (primary key of Quicksheet), it would be used to to populate the
same
field QuicksheetID FK in jobs table. Inaddition, I have four other fields
that I would
possibly need to populate in the jobs table.

Can anyone recommend what is the best way to do this? I am thinking either
use
parameter query or some sort of unbound fields that I load into the append
query?

Thank you,

Johnny

Right now I am trying to just use my one Ticket form to call an append query
to the jobs table. I do not have a Job number field in the Quicksheet table
to enter the data. This is why I was trying to use the parameter query.

Johnny

[quoted text clipped - 31 lines]

Johnny