Prev: Update - Microsoft Responds to the Evolution of Community
Next: Hide and Unhide AC 2007 Navigation Pane
From: PHisaw on 10 May 2010 14:06
Bruce,

I already have a main table that holds all job info, tgeninfo, and is
related to the main quote table, tmainquoteform, in the relationship window.
I have a form for all the general information based on tgeninfo and a button
when clicked takes user to the main form for quotes, frepairquotemainform,
which includes the subforms for labor, parts and misc parts. These subforms
are backed by tables for each that are related in a 1:M relationship in the
relationship window also. I really don't want a subform of the main quote
that contains other subforms on the main form for the general information.
The subforms are linked with Master and Child fields to the main quote form
by JobNumber and Option. In my previous message, it was an error on my part
when I stated Tom suggested relating the main table to the subforms. I meant
the main form to the subforms. Here is his original suggestion which I hope
will shed more light on what I wish to accomplish.

"In the database design you have tables like
LaborItemsForQuote(LaborItemID, QuoteID, ...), PartsForQuote (PartID,
QuoteID, Qty, ...), etc. They are currently related 1:M (shorthand for
one-to-many) to the main Quotes table via the QuoteID field or some
such. Since we have changed the main table to have a 2-field primary
key, that will have to change: QuoteID + Option will be the foreign
key in each of those related tables, and you need to update the
Relationships window. Don't forget to check the box to Enforce the
relationships."

I've set my tables as such, but I cannot figure the code needed as he
suggested to save the field info from the previous record, apply it to the
same field for the new record and increase the option number based on the
last one used.

Does any of this help explain what I want db to do? If not, I may have to
move on to creating basically duplicate forms, queries and tables and
limiting users to only 1, maybe 2, additional options which would seem
redundant and more bloat to the db.

Thanks,
Pam

"BruceM via AccessMonster.com" wrote:

> If a job may have several quotes, quote information should be in a related
> table:
>
> tblJob
> JobID (primary key, or PK; could be the job number)
> Customer
> etc.
>
> tblQuote
> QuoteID (PK)
> JobID (related to JobID in tblJob)
> other Quote fields as needed
>
> Establish the relationship between tblJob and tblQuote in the Relationships
> window. Build a main form based on tblJob, with a subform based on tblQuote.
> Set the Link Child and Link Master properties of the subfoirm control to
> JobID.
>
> It may seem like semantics, but I doubt Tom would have suggested "to have the
> main table relate to the subforms with JobNumber and Option fields". Tables
> relate to other tables, so I expect his reference to relationships was about
> relating one table to another. The subforms may be based on the related
> tables, but we can't see your database, so can only infer how it is
> constructed.
>
> Any discussion of this sort needs to start with the table structure, which
> depends on the real-world situation behind the database. What is the
> database supposed to do?
>
> PHisaw wrote:
> >Bruce,
> >
> >Thank you for replying. Per your message,
> >
> >> I'm not sure what this line of code is supposed to do:
> >>
> >> Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """"
> >
> >In researching on how to save field info and then repopulate field with
> >saved info from previous record, this line of code is what I came across, but
> >obviously it isn't working.
> >Users have requested the ability to have more than one quote option for the
> >same job number. Tom's suggestion was to have the main table relate to the
> >subforms with JobNumber and Option fields - both PK's. Then a button on the
> >main form that would save the job number from the previous record, insert it
> >in the new record (new option, same job number) and increase the option by 1
> >based on last option number.
> >I have a main quote table, tquotemainform, which supplies frepairquotemain
> >form. On it are several subforms - labor, parts, and misc parts and all are
> >related by JobNumber (autonumber and PK) and now by Option (also a PK - I'll
> >change the name to something other than the reserved word).
> >John Vinson responded that it may be the jobnumber in the related table. I
> >asked how I could go about resolving the problem, but received no response.
> >I just need to know if this is acutally possible as in example below with
> >both fields being PKs.
> >
> >JobNumber Option
> >9549 1
> >9549 2
> >9550 1
> >9551 1
> >9551 2
> >
> >Again, thanks for your help.
> >Pam
> >
> >> I don't know what exactly was in the original string, but I see a few things
> >> in the code you posted.
> >[quoted text clipped - 56 lines]
> >> >Thank in advance,
> >> >Pam
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Update - Microsoft Responds to the Evolution of Community
Next: Hide and Unhide AC 2007 Navigation Pane