Quote Form with Options

[PHisaw]

Hi,

I have a main quote form with subforms for labor, parts and misc parts all
tied to a job number with several calculations on the main form from each of
the subforms. This works great as it is. Now users tell me they need to
have ability to create optional quotes for same job number. Is it possible
to have an option group on the form so that the first main default quote
would be option 1 and then if user needs to create another quote for the same
job number (job number is autonumber and primary from tgeninfo table) they
can press "Option 2" button and use the same (now cleared) form?

This is the general idea of what I want to do, but I'm not sure how to go
about doing it, if its even possible? If anyone has suggestions to make it
work, I would love to hear them.

Thanks,
Pam

 

[Tom van Stiphout]

On Thu, 29 Apr 2010 13:58:02 -0700, PHisaw

Before we worry about how to implement it, let's worry about the
database design. I understand we have this requirement: JobNumber is
currently unique and optional quotes need to reference the same
jobnumber. You have a few options:

1: JobNumber + a new field QuoteOption will be the new PK. QuoteOption
is 1 by default, but can be 2, 3 for optional quotes.

2: Quote table will have much fewer fields, mostly JobNumber,
QuoteOption, and most fields move to a QuoteInstance table in 1:M
relation, since now each Quote (an abstract entity) has many
QuoteInstances. You probably need an IsPrimary field in the
QuoteInstance table, or by convention the one with the lowest PK value
is the primary.

3: Quote table will get a new field ParentQuote. For the current
quotes the value is same as the current QuoteID (it points to itself),
but for optional quotes it points to the ParentQuote where it came
from.

4: Others?

Which option appeals to you the most, and why?

-Tom.
Microsoft Access MVP

 

[PHisaw]

Tom,

Thank you for taking time to reply. Option 2 & 3 were not real clear -
maybe because I've been working with this too long. As for option 1, I do
have a field for "option" on the table tQuoteMain. I've also included the
field on the subform tables as well. I've put the option group on the main
form and when I click on option 2, it will clear all boxes on the main form,
but does not clear the subforms for new data for option 2. I've tried
relating the option fields in the subform queries, but have had no success.

Can you please tell me what I need to do to make this work as simply as
possible?
Thanks again,
Pam

 

[Tom van Stiphout]

On Fri, 30 Apr 2010 08:05:01 -0700, PHisaw

Hi Pam,
Yes it does get somewhat technical sometimes. Sorry about that.
Database design is where most casual Access users have the most
problems, and IMHO it's the most important part to get right.

Given that you already have an Option field (I'm assuming a number
starting at 1) we're going to use my suggestion #1. You have to
include the Option field in the Primary Key, so it will be over the
combination of JobNumber and Option.

Once that is in place let's look at your form. I'm assuming it is in
Form View showing one JobNumber at a time. In the future it will show
one JobNumber/Option combination at a time.
For example Record 1 is JobNumber 123, Option 1.
Record 2 is JobNumber 123, Option 2.
How does the user create this second record? You seem to indicate by
selecting #2 from the option group. That seems like a bad idea,
because at least in theory there can be an unlimited number of
options. You create an Option Group with 5 options, and I'll show you
a job that needs 6. Maybe better to provide a button: "Create New
Option". It would look at the maximum option used so far (DMax
function) and add 1 to it.
When the button is clicked, you save the JobNumber information and
perhaps some other key information, go to a new record, populate the
JobNumber with the saved value, and set the Option field to whatever
DMax(...)+1 returns.

Now let's look at the subforms. The reason they are currently showing
the same data for all Options is that LinkMasterFIelds and
LinkParentFIelds are set to QuoteID only. We'll fix that in a minute.
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.
Once in place getting the subforms to work properly is simple: Set the
LinkMasterFields and LinkChildFields of the subform control to both
field names separated by a semicolon. That way when you scroll from
record 1 to 2 (using my earlier example) it's going to find
JobNumber=123, Option=2 and the subforms will initially be empty.

-Tom.
Microsoft Access MVP

 

[PHisaw]

Tom,

Thank you so much for your very detailed reply. I think I'm almost there
but have run into an error by not having enough code for the label you
suggested. I'm hoping you can help me with it.

I included Option field as primary key along with JobNumber on the table
tQuoteMain and tables backing subforms also have the JobNumber and Option
fields on them. I set the tables in the relationship layout as 1:M
(tQuoteMain:tsubformtables) The subforms are linked by JobNumber; Option. I
created a label (don't like the color of the command buttons) "Create New
Option" with the following code, but do not know how to write in everything
you stated I would need - save the JobNumber information and other key
information, go to a new record, populate JobNumber with saved value and set
Option field to whatever returns (the option field is populating).

Private Sub LblQuoteOptions_Click()
If Me.Dirty Then Me.Dirty = False
Me.Option = Nz(DMax("Option", "tquotemainform"), 0) + 1
End Sub

How do I go about saving the jobnumber and other information and go to a new
record and then populate jobnumber and other fields with the saved values? I
think it will work if I can just get the right code in the label.

I really appreciate your time and help.
Pam

 

[PHisaw]

Tom,

I'm still working on the code to make option quoting work. In the
AfterUpdate event for jobnumber on the form fmainquote I've added the
following:

Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """"

but when I click on the label, option changes to 2, but the job number goes
to 0 and then I get an error message that "record can't be changed or deleted
due to related record not in tgeneralinfo". The form fmainquote opens from
the form frepairs where jobnumber = jobnumber.

What am I doing wrong to get 0 in the job number field? I've spent all day
searching posts trying to get code to work, but still can't get it to work as
needed.

Thanks,
Pam

 

[John W. Vinson]

What am I doing wrong to get 0 in the job number field? I've spent all day
searching posts trying to get code to work, but still can't get it to work as
needed.

I would guess that in the Table design for the table, the Default Value
property for Job Number is 0 (Access stupidly assumes that all Number type
fields should default to zero; some should, but foreign keys definitely should
NOT). Try removing this default.

 

[PHisaw]

John,

Thanks for replying to post. The jobnumber field in the table tgeneralinfo
is an autonumber field and primary key for table. When I looked at the
properties at the bottom of the screen, I did not see "default value". We've
been running like this for many years now, but if I need to change this field
to a number+1 type, I can. Would you suggest this or could something else
possibly be wrong causing this error?

Thanks again for your help.
Pam

 

[John W. Vinson]

John,

Thanks for replying to post. The jobnumber field in the table tgeneralinfo
is an autonumber field and primary key for table. When I looked at the
properties at the bottom of the screen, I did not see "default value". We've
been running like this for many years now, but if I need to change this field
to a number+1 type, I can. Would you suggest this or could something else
possibly be wrong causing this error?

I suspect it's the jobnumber field in the *related* table. An Autonumber would
not have a default and should not cause this problem. I didn't go back through
your discussion with Tom and probably should not have jumped in without
understanding the whole situation!

The error message, though, suggests that it's a foreign key that's being set
to zero, not a primary key.

 

[PHisaw]

Do you have any suggestions as to what I need to do to correct it so that it
will work properly?