How do I pass information to create a new record.

[LadyAmethyst]

I have a Access 97 database with 2 forms. The first form
is frmEstimate the second form is frmDeposit. There are
2 tables tblEstimate and tblDeposit and JobID is the key
on both tables.

The frmEstimate has a Command Button called "Create/Find
Deposit".

Now the following code works fine if the record is
already in the tblDeposit but if there is no record I
want it to put 3 values in the new record from the
frmEstimate. I want it to pass the JobID, TotalCost,
and the JobDivision. Now even when I enter these values
myself and fill in the other fields, nothing gets saved
to the table. What am I missing?

Private Sub CreateFindDeposit_Click()
On Error GoTo Err_CreateFindDeposit_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmDeposit"

strLinkCriteria = "[JobID]=" & "'" & Me![JobID] & "'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_CreateFindDeposit_Click:
Exit Sub

Err_CreateFindDeposit_Click:
MsgBox Err.Description
Resume Exit_CreateFindDeposit_Click

End Sub

Now frmDeposit has a filter [JobID]='040904-01'
but nothing shows up in the JobID form field.
I have also tried the following OnOpen Expression
=[JobID]=[Forms]![frmEstimate]![JobID]
I also don't know how to pass it the other 2 fields
that it needs. Can anyone tell me what I'm doing
wrong?

Thanks

 

[Marshall Barton]

I have a Access 97 database with 2 forms. The first form
is frmEstimate the second form is frmDeposit. There are
2 tables tblEstimate and tblDeposit and JobID is the key
on both tables.

The frmEstimate has a Command Button called "Create/Find
Deposit".

Now the following code works fine if the record is
already in the tblDeposit but if there is no record I
want it to put 3 values in the new record from the
frmEstimate. I want it to pass the JobID, TotalCost,
and the JobDivision. Now even when I enter these values
myself and fill in the other fields, nothing gets saved
to the table. What am I missing?

Private Sub CreateFindDeposit_Click()
On Error GoTo Err_CreateFindDeposit_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmDeposit"

strLinkCriteria = "[JobID]=" & "'" & Me![JobID] & "'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_CreateFindDeposit_Click:
Exit Sub

Err_CreateFindDeposit_Click:
MsgBox Err.Description
Resume Exit_CreateFindDeposit_Click

End Sub

Now frmDeposit has a filter [JobID]='040904-01'
but nothing shows up in the JobID form field.
I have also tried the following OnOpen Expression
=[JobID]=[Forms]![frmEstimate]![JobID]

You need to use code to run that statement. The OnOpen
property can not take a VBA statement, only
[Event Procedure]
macro name
or a function call

Add the code to the form's Load event procedure:

If Me.NewRecord Then
Me.JobID = Forms!frmEstimate!JobID
Me.TotalCost = Forms!frmEstimate!TotalCost
Me.JobDivision = Forms!frmEstimate!JobDivision
End If

But those last two should not be bound fields, because then
the field values would be duplicated in the two tables.

 

[LadyAmethyst]

Thanks Marshall. It works great!

And by the way, the TotalCost and JobDivision fields
are display only, there are no duplicate fields to write
to in the Deposit table. It was the JobID that I needed
to get over to the new form in order to create the new
Deposit record. I just needed the other two to be able
to use on the print out.

The only problem now is that I can't seem to save the
record. I've tried using the Wizard to create a Save
Record command button but I keep getting the error

"The changes you requested to the table were not
successful because they would create duplicate values
in the index, primary key, or relationship. Change the
data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit
duplicate entries and try again."

I checked the tblDeposit and there is no record with
the same primary key value in the table. (And yes, I'm
sure because there is currently only 1 deposit record
in there at this time.) The only thing that I can figure
is that it must be trying to write the new record to the
tblEstimate. What do I need to do now?

I have a Access 97 database with 2 forms. The first form
is frmEstimate the second form is frmDeposit. There are
2 tables tblEstimate and tblDeposit and JobID is the key
on both tables.

The frmEstimate has a Command Button called "Create/Find
Deposit".

Now the following code works fine if the record is
already in the tblDeposit but if there is no record I
want it to put 3 values in the new record from the
frmEstimate. I want it to pass the JobID, TotalCost,
and the JobDivision. Now even when I enter these values
myself and fill in the other fields, nothing gets saved
to the table. What am I missing?

Private Sub CreateFindDeposit_Click()
On Error GoTo Err_CreateFindDeposit_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmDeposit"

strLinkCriteria = "[JobID]=" & "'" & Me![JobID] & "'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_CreateFindDeposit_Click:
Exit Sub

Err_CreateFindDeposit_Click:
MsgBox Err.Description
Resume Exit_CreateFindDeposit_Click

End Sub

Now frmDeposit has a filter [JobID]='040904-01'
but nothing shows up in the JobID form field.
I have also tried the following OnOpen Expression
=[JobID]=[Forms]![frmEstimate]![JobID]

You need to use code to run that statement. The OnOpen
property can not take a VBA statement, only
[Event Procedure]
macro name
or a function call

Add the code to the form's Load event procedure:

If Me.NewRecord Then
Me.JobID = Forms!frmEstimate!JobID
Me.TotalCost = Forms!frmEstimate!TotalCost
Me.JobDivision = Forms!frmEstimate!JobDivision
End If

But those last two should not be bound fields, because then
the field values would be duplicated in the two tables.

 

[Marshall Barton]

The only problem now is that I can't seem to save the
record. I've tried using the Wizard to create a Save
Record command button but I keep getting the error

"The changes you requested to the table were not
successful because they would create duplicate values
in the index, primary key, or relationship. Change the
data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit
duplicate entries and try again."

I checked the tblDeposit and there is no record with
the same primary key value in the table. (And yes, I'm
sure because there is currently only 1 deposit record
in there at this time.) The only thing that I can figure
is that it must be trying to write the new record to the
tblEstimate. What do I need to do now?

It sounds like the deposits form is bound (RecordSource) to
the estimates table??

Can you only have one deposit for an estimate? Since both
tables have the same value as their primary key, you have a
one to one relationship. That's fine if that's what you
want, but it seems unusual in this situation.

 

[LadyAmethyst]

It sounds like the deposits form is bound (RecordSource) to
the estimates table??

Can you only have one deposit for an estimate? Since both
tables have the same value as their primary key, you have a
one to one relationship. That's fine if that's what you
want, but it seems unusual in this situation.

Marshall,


The form was originally created with the Form Wizard by
going to the tblEstimate first to get the information
there and then putting in all of the tblDeposit fields.
It uses the following SQL statement to get the record:

SELECT DISTINCTROW tblEstimate.Division,
tblEstimate.JobID, tblEstimate.TotalCost,
tblDeposit.DepositDate, tblDeposit.AmountDepositRecieved,
tblDeposit.CompletionTotalDue, tblDeposit.ReceivedDate,
tblDeposit.CashCheckbox, tblDeposit.CheckCheckbox,
tblDeposit.CheckNumber, tblDeposit.CheckAmount,
tblDeposit.CommentsLine_1, tblDeposit.CommentsLine_2,
tblDeposit.CustomerSignature,
tblDeposit.AssociateSignature
FROM tblEstimate INNER JOIN tblDeposit ON
tblEstimate.JobID = tblDeposit.JobID;

This is working fine on the record that is already
in the tblDeposit.

So I went back used the Wizard to change the SQL
statement to check the tblDeposit first for a matching
record and the following is the new SQL statement:

SELECT DISTINCTROW [tblDeposit].[JobID],
[tblDeposit].[DepositDate], [tblDeposit].[AmountDepositRecieved],
[tblDeposit].[CompletionTotalDue], [tblDeposit].[ReceivedDate],
[tblDeposit].[CashCheckbox], [tblDeposit].[CheckCheckbox],
[tblDeposit].[CheckNumber], [tblDeposit].[CheckAmount],
[tblDeposit].[CommentsLine_1], [tblDeposit].[CommentsLine_2],
[tblDeposit].[CustomerSignature], [tblDeposit].[AssociateSignature],
[tblEstimate].[Division], [tblEstimate].[ TotalCost] FROM ([tblDeposit]
INNER JOIN [tblEstimate] ON [tblDeposit].[JobID]
=[tblEstimate].[JobID]);

What happens now is that it will find the existing record,
but now the On Load event doesn't work.

And to answer your question, there is generally only
one deposit because it is 65% of the total estimate.

I'm getting really frustrated!!

Thanks for your help,

LadyAmethyst

 

[Marshall Barton]

The form was originally created with the Form Wizard by
going to the tblEstimate first to get the information
there and then putting in all of the tblDeposit fields.
It uses the following SQL statement to get the record:

SELECT DISTINCTROW tblEstimate.Division,
tblEstimate.JobID, tblEstimate.TotalCost,
tblDeposit.DepositDate, tblDeposit.AmountDepositRecieved,
tblDeposit.CompletionTotalDue, tblDeposit.ReceivedDate,
tblDeposit.CashCheckbox, tblDeposit.CheckCheckbox,
tblDeposit.CheckNumber, tblDeposit.CheckAmount,
tblDeposit.CommentsLine_1, tblDeposit.CommentsLine_2,
tblDeposit.CustomerSignature,
tblDeposit.AssociateSignature
FROM tblEstimate INNER JOIN tblDeposit ON
tblEstimate.JobID = tblDeposit.JobID;

This is working fine on the record that is already
in the tblDeposit.

So I went back used the Wizard to change the SQL
statement to check the tblDeposit first for a matching
record and the following is the new SQL statement:

SELECT DISTINCTROW [tblDeposit].[JobID],
[tblDeposit].[DepositDate], [tblDeposit].[AmountDepositRecieved],
[tblDeposit].[CompletionTotalDue], [tblDeposit].[ReceivedDate],
[tblDeposit].[CashCheckbox], [tblDeposit].[CheckCheckbox],
[tblDeposit].[CheckNumber], [tblDeposit].[CheckAmount],
[tblDeposit].[CommentsLine_1], [tblDeposit].[CommentsLine_2],
[tblDeposit].[CustomerSignature], [tblDeposit].[AssociateSignature],
[tblEstimate].[Division], [tblEstimate].[ TotalCost] FROM ([tblDeposit]
INNER JOIN [tblEstimate] ON [tblDeposit].[JobID]
=[tblEstimate].[JobID]);

What happens now is that it will find the existing record,
but now the On Load event doesn't work.

And to answer your question, there is generally only
one deposit because it is 65% of the total estimate.

I don't understand why your query includes both tables.
Typically each form operates on data in a single table.
Try just setting the record source to the deposits table and
see where that gets us.

Actually, I am having trouble visualizing why you even need
two tables and two forms in a one-to-one relationship.

 

[LadyAmethyst]

I don't understand why your query includes both tables.
Typically each form operates on data in a single table.
Try just setting the record source to the deposits table and
see where that gets us.

Actually, I am having trouble visualizing why you even need
two tables and two forms in a one-to-one relationship.

Marshall,

Okay, I changed it to use only the tblDeposit. Now, I get
#Name? for the TotalCost textbox, and nothing in the
Division Combobox, and it still doesn't load the
JobID data.

To answer your questions.....
2 Tables, because that's the way it was built, and 2
forms, is because that's the way the user wants it.
The estimate and deposit are printed to Word forms.
As far as I can tell, there are no duplicate fields
in the tables except for the JobID and CustomerID.

So where do I go from here?

Thanks,
LadyAmethyst

 

[Marshall Barton]

Okay, I changed it to use only the tblDeposit. Now, I get
#Name? for the TotalCost textbox, and nothing in the
Division Combobox, and it still doesn't load the
JobID data.

I thought that the TotalCost and Division values were being
supplied in the Load event and displayed in UNBOUND
controls, so I can't see why you want a query to retrieve
those values. Just stick with the deposits table for now
and let's sort out the situation about the JobID field and
saving a record.

The JobId field should be coming from the deposits table for
existing records and be filled in by the Load event for new
records. If you unbind (set control source blank) the
TotalCost and Division text boxes, they will not display
anything for now, but they won't be getting in the way
either.

Once we've got the fundamental functionality issues
resolved, then we can move on to the display part, probably
by modifying the Load event procedure.

To answer your questions.....
2 Tables, because that's the way it was built, and 2
forms, is because that's the way the user wants it.
The estimate and deposit are printed to Word forms.
As far as I can tell, there are no duplicate fields
in the tables except for the JobID and CustomerID.

Ok, ok, I'll try to keep that stuff from cluttering up my
thinking.

So where do I go from here?

Just focus on the core issues. First we need to be able to
save a new record. Then we worry about getting the display
squared away before you even start to think about getting
Word involved.

 

[LadyAmethyst]

Marshall,

I took out the TotalCost and the Division from the On Load Event procedure and
the JobID did get passed. I can also now save the record in the Deposit
table.
Now the only part that I need to fix is obtaining the correct Division and
the
TotalCost to display in the Deposit form. I took out the Record Source on the
Deposit form for Division and TotalCost. The Division is now an unbound
combobox, and TotalCost was bound to Estimate TotalCost field; it is now an
unbound text box.

The print code is already basically working. The only part that will need
fixed will
be the part the passes the Division and TotalCost on the form to Word.

Okay, what do I need to do to get the Division and TotalCost fields to show
on the
Deposit Screen?

In case I haven't told you before, I really appreciate all your help!

Thanks,
LadyAmethyst

 

[Marshall Barton]

I took out the TotalCost and the Division from the On Load Event procedure and
the JobID did get passed. I can also now save the record in the Deposit
table.
Now the only part that I need to fix is obtaining the correct Division and
the
TotalCost to display in the Deposit form. I took out the Record Source on the
Deposit form for Division and TotalCost. The Division is now an unbound
combobox, and TotalCost was bound to Estimate TotalCost field; it is now an
unbound text box.

The print code is already basically working. The only part that will need
fixed will
be the part the passes the Division and TotalCost on the form to Word.

Okay, what do I need to do to get the Division and TotalCost fields to show
on the
Deposit Screen?

If I've managed to keep all this straight, you only want to
display the TotalCost and the Division values the same as
they are on the estimates form strictly as a convenience for
the user. If that's right, then I think all you need is to
set those two Deposit form text boxes to an expression that
does just that:
=Forms!frmEstimate!TotalCost
=Forms!frmEstimate!JobDivision

 

[LadyAmethyst]

If I've managed to keep all this straight, you only want to
display the TotalCost and the Division values the same as
they are on the estimates form strictly as a convenience for
the user. If that's right, then I think all you need is to
set those two Deposit form text boxes to an expression that
does just that:
=Forms!frmEstimate!TotalCost
=Forms!frmEstimate!JobDivision

Marshall,

Nope, besides displaying them, the TotalCost is used in a
calculation for 2 fields in the Deposit Screen. Each
of the text boxes (AmtDepositRequired and
CompletionTotal) are calculated with a Got Focus
function. The Division display also has an After Update
function that takes the DivisionID that was passsed and
displays the Division Name.

I also need to pass them to the print document function
along with the rest of the data on the Deposit screen.

The functions are already set up using the previous
field names.

So now, are you thoroughly confused too??

LadyAmethyst

 

[Marshall Barton]

Nope, besides displaying them, the TotalCost is used in a
calculation for 2 fields in the Deposit Screen. Each
of the text boxes (AmtDepositRequired and
CompletionTotal) are calculated with a Got Focus
function. The Division display also has an After Update
function that takes the DivisionID that was passsed and
displays the Division Name.

I also need to pass them to the print document function
along with the rest of the data on the Deposit screen.

The functions are already set up using the previous
field names.

So now, are you thoroughly confused too??

I think I am confused, but I'm not sure I know enough to be
able to tell you if it's thoroughly or not ;-)

When you talk of Got Focus and After Update events, I really
need to know which controls are involved and how they relate
to the ones with values from the other form. If it's just
calculations using the values from the other forms, then the
expressions I posted should be adequate. For example, the
Division Name could be displayed by just using a DLookup on
the DivisionID.

OTOH, if you want the user to be able to edit those values
without going back to the estimate form, we have several
additional issues to work out.

I thought you said you had the Word part of this all taken
care of. If not, I would rather not be befuddled by that -
yet.

 

[LadyAmethyst]

I think I am confused, but I'm not sure I know enough to be
able to tell you if it's thoroughly or not ;-)

When you talk of Got Focus and After Update events, I really
need to know which controls are involved and how they relate
to the ones with values from the other form. If it's just
calculations using the values from the other forms, then the
expressions I posted should be adequate. For example, the
Division Name could be displayed by just using a DLookup on
the DivisionID.

OTOH, if you want the user to be able to edit those values
without going back to the estimate form, we have several
additional issues to work out.

I thought you said you had the Word part of this all taken
care of. If not, I would rather not be befuddled by that -
yet.

Marshall,

Hey, I'm befuddled why can't I try befuddling you??
Okay, let's see how good my explaining skills are.

The original form field on frmDeposit for Division
had the Form Control Source from the SQL query called
Division. Then in the properties for Division the
Row Source Type was Table/Query then
Row Source was the following query:

SELECT DISTINCTROW tblDivisions.DivisionID,
tblDivisions.DivisionName FROM tblDivisions;

Then when some changes made were and things weren't
working, I tried an After Update function that did
nothing but this:

Forms!frmDeposit!Division = Forms![frmDivision].Column(1)

Okay, this doesn't work because the field name is
right now "Unbound".

Then as for the Word printing this is it:

'JDDivision is the Word Bookmark and Division the name
'of the field in the frmDeposit
..ActiveDocument.Bookmarks("JDDivision").SELECT
DisplayName = DLookup("[DivisionName]", "tblDivisions", "DivisionID =
Forms![frmDeposit]!Division")
..Selection.Text = (CStr(DisplayName))

Now for the TotalCost field that was bound to the field
by the Form Control Source from the SQL query.
TotalCost in now also an unbound field so the 2 calculations
that are set up can't find the field.

The 2 fields following the display of TotalCost both have
a Got Focus property that does a calculation

Private Sub AmtDepositRequired_GotFocus()
AmtDepositRcvd = (Me!TotalCost * 0.65)
AmtDepositRcvd = Int(AmtDepositRequired * 100 + 0.5) / 100
End Sub

Private Sub CompletionTotalDue_GotFocus()
CompletionTotalDue = (Me!TotalCost * 0.35)
CompletionTotalDue = Int(CompletionTotalDue * 100 + 0.5) / 100
End Sub

Then the Word printing function uses

If Forms![frmDeposit]!TotalCost = 0 Then
.ActiveDocument.Bookmarks("EstimateTotal").SELECT
.Selection.Text = " "
Else
.ActiveDocument.Bookmarks("EstimateTotal").SELECT
.Selection.Text = CStr("$") & (CCur(Forms![frmDeposit]!TotalCost))
End If

Now, all of this worked great on records already in the
database. The problems surfaced when I tried to enter
new records.

I've only included the Word stuff so that you could see
what I mean.

So, are you befuddled now??

LadyAmethyst

 

[Marshall Barton]

Hey, I'm befuddled why can't I try befuddling you??
Okay, let's see how good my explaining skills are.

The original form field on frmDeposit for Division
had the Form Control Source from the SQL query called
Division. Then in the properties for Division the
Row Source Type was Table/Query then
Row Source was the following query:

SELECT DISTINCTROW tblDivisions.DivisionID,
tblDivisions.DivisionName FROM tblDivisions;

Then when some changes made were and things weren't
working, I tried an After Update function that did
nothing but this:

Forms!frmDeposit!Division = Forms![frmDivision].Column(1)

Okay, this doesn't work because the field name is
right now "Unbound".

Then as for the Word printing this is it:

'JDDivision is the Word Bookmark and Division the name
'of the field in the frmDeposit
.ActiveDocument.Bookmarks("JDDivision").SELECT
DisplayName = DLookup("[DivisionName]", "tblDivisions", "DivisionID =
Forms![frmDeposit]!Division")
.Selection.Text = (CStr(DisplayName))

Now for the TotalCost field that was bound to the field
by the Form Control Source from the SQL query.
TotalCost in now also an unbound field so the 2 calculations
that are set up can't find the field.

The 2 fields following the display of TotalCost both have
a Got Focus property that does a calculation

Private Sub AmtDepositRequired_GotFocus()
AmtDepositRcvd = (Me!TotalCost * 0.65)
AmtDepositRcvd = Int(AmtDepositRequired * 100 + 0.5) / 100
End Sub

Private Sub CompletionTotalDue_GotFocus()
CompletionTotalDue = (Me!TotalCost * 0.35)
CompletionTotalDue = Int(CompletionTotalDue * 100 + 0.5) / 100
End Sub

Then the Word printing function uses

If Forms![frmDeposit]!TotalCost = 0 Then
.ActiveDocument.Bookmarks("EstimateTotal").SELECT
.Selection.Text = " "
Else
.ActiveDocument.Bookmarks("EstimateTotal").SELECT
.Selection.Text = CStr("$") & (CCur(Forms![frmDeposit]!TotalCost))
End If

Now, all of this worked great on records already in the
database. The problems surfaced when I tried to enter
new records.

I've only included the Word stuff so that you could see
what I mean.

So, are you befuddled now??

I think that just might have clarified things. Since
AmtDepositRequired is just a calculation based on TotalCost,
you can use a control source expression here too (no need
for an event procedure).

txtAmtDepositRequired:
=Int((TotalCost * 0.65) * 100 + 0.5) / 100

Similarly for txtTotalDue:

=txtTotalCost - txtAmtDepositRequired

I think I said it somewhere before, the Division name text
box can use the expression:

=DLookup("[DivisionName]", "tblDivisions", "DivisionID = "
& txtDivision)

Then when you send this stuff to the Word bookmarks, just
use the text box names. E,g,

.ActiveDocument.Bookmarks("JDDivision").SELECT
.Selection.Text = Me.txtDivisionName

Note that I prefix all my text box names with txt to
distinguish them from any fields with the same name.

So, either that should take care of all the issues, or I'm
really am befuddled ;-)

 

[LadyAmethyst]

So, are you befuddled now??

I think that just might have clarified things. Since
AmtDepositRequired is just a calculation based on TotalCost,
you can use a control source expression here too (no need
for an event procedure).

txtAmtDepositRequired:
=Int((TotalCost * 0.65) * 100 + 0.5) / 100

Similarly for txtTotalDue:

=txtTotalCost - txtAmtDepositRequired

I think I said it somewhere before, the Division name text
box can use the expression:

=DLookup("[DivisionName]", "tblDivisions", "DivisionID = "
& txtDivision)

Then when you send this stuff to the Word bookmarks, just
use the text box names. E,g,

.ActiveDocument.Bookmarks("JDDivision").SELECT
.Selection.Text = Me.txtDivisionName

Note that I prefix all my text box names with txt to
distinguish them from any fields with the same name.

So, either that should take care of all the issues, or I'm
really am befuddled ;-)

Marshall,

Clarified for you maybe, I'm totally lost! I guess the problem
is that I don't understand how to use Unbound textboxes.
Probably one of the reasons I don't have a good understanding
is that I have been self teaching how to work in Access.

Okay, first how to I display the TotalCost the current
textbox?

The Current Form is frmDeposit. One of the text boxes I
am trying to display data in has the following properties.
In the name properties is txtTotalCost. The control
source is blank now, so when the form is in Design View
the field says "Unbound", and in Form view it's blank.

Now the next thing that I need to understand, is how
do I put the TotalCost into this unbound textbox? And
since the only thing that gets passed to the On Load
Form is the JobID how do I get the correct TotalCost
displayed in this textbox? When the field was bound
to the tblEstimate TotalCost field I understood that.
Since it is no longer bound to the field I just don't
understand how to tell it to display the correct data.

Now just so you understand, AmtDepositRequired and
TotalDue ARE fields in tblDepsoit. So the calculations
will be stored. And I'm not understanding where to
put the "=Int((TotalCost * 0.65) * 100 + 0.5) / 100"
and the "=txtTotalCost - txtAmtDepositRequired".

The same thing goes for the txtDivisionComboBox, that
is the name in properties. Again the Control Source is
blank now and the field shows up as Unbound in design
view and blank in Form View. I also took the query out
so it no longer allows any choices. I suppose this could
be changed to a plain textbox. But I still don't
understand how to get the correct Division to display
since I couldn't get Division passed when I passed
the JobID.

So, can you explain this to me so that I understand
it?

LadyAmethyst

 

[Marshall Barton]

So, are you befuddled now??

I think that just might have clarified things. Since
AmtDepositRequired is just a calculation based on TotalCost,
you can use a control source expression here too (no need
for an event procedure).

txtAmtDepositRequired:
=Int((TotalCost * 0.65) * 100 + 0.5) / 100

Similarly for txtTotalDue:

=txtTotalCost - txtAmtDepositRequired

I think I said it somewhere before, the Division name text
box can use the expression:

=DLookup("[DivisionName]", "tblDivisions", "DivisionID = "
& txtDivision)

Then when you send this stuff to the Word bookmarks, just
use the text box names. E,g,

.ActiveDocument.Bookmarks("JDDivision").SELECT
.Selection.Text = Me.txtDivisionName

Note that I prefix all my text box names with txt to
distinguish them from any fields with the same name.

So, either that should take care of all the issues, or I'm
really am befuddled ;-)

Clarified for you maybe, I'm totally lost! I guess the problem
is that I don't understand how to use Unbound textboxes.
Probably one of the reasons I don't have a good understanding
is that I have been self teaching how to work in Access.

Okay, first how to I display the TotalCost the current
textbox?

The Current Form is frmDeposit. One of the text boxes I
am trying to display data in has the following properties.
In the name properties is txtTotalCost. The control
source is blank now, so when the form is in Design View
the field says "Unbound", and in Form view it's blank.

Now the next thing that I need to understand, is how
do I put the TotalCost into this unbound textbox? And
since the only thing that gets passed to the On Load
Form is the JobID how do I get the correct TotalCost
displayed in this textbox? When the field was bound
to the tblEstimate TotalCost field I understood that.
Since it is no longer bound to the field I just don't
understand how to tell it to display the correct data.

Now just so you understand, AmtDepositRequired and
TotalDue ARE fields in tblDepsoit. So the calculations
will be stored. And I'm not understanding where to
put the "=Int((TotalCost * 0.65) * 100 + 0.5) / 100"
and the "=txtTotalCost - txtAmtDepositRequired".

The same thing goes for the txtDivisionComboBox, that
is the name in properties. Again the Control Source is
blank now and the field shows up as Unbound in design
view and blank in Form View. I also took the query out
so it no longer allows any choices. I suppose this could
be changed to a plain textbox. But I still don't
understand how to get the correct Division to display
since I couldn't get Division passed when I passed
the JobID.

Oh boy! We're both confused :-\

We may even be talking about two different things here. My
first concern is that you have a TotalCost field in the
tblEstimate table and its value is entered and displayed on
the estimates form, which makes sense. The place where
you're losing me is when you say that the DepositRequired
and AmountDue fields are in the deposits table. Not only
should those not be in the deposits table, they should not
be in any table (because they are just simple calculated
values based on an existing field and can be recalculated
whenever you want to display them). Saving them to a table
would be a violation of the rules of normalization and is a
definite no-no in relational databases. Same thing for the
division name (only need to save the division ID (which is
in the estimates table, right?).

Another point of clarification is needed here. I'm starting
to get the impression that the deposit form can be navigated
from one estimate's deposit to a different estimate's
deposit while the estimate form is just sitting there
displaying the original estimate that started this whole
process. If this is true (I hope not), then you definitely
have some confusion in your user interface.

If that is not the case and the deposit form can only
display the one estimate's deposit data, then the unbound
controls on the deposit form are only used to display a copy
of (or values calculated from) data on the estimate form.
What I was describing before meant for you to place the
expressions I posted in the unbound text box control's
ControlSource property. The text box's are not bound to a
field in a table, but they are not blank either. These text
boxes will calculate, display and make the desired estimate
values available for further use in the deposit form.

 

[LadyAmethyst]

Okay Marshall here goes,

Oh boy! We're both confused :-\

We may even be talking about two different things here. My
first concern is that you have a TotalCost field in the
tblEstimate table and its value is entered and displayed on
the estimates form, which makes sense. The place where
you're losing me is when you say that the DepositRequired
and AmountDue fields are in the deposits table. Not only
should those not be in the deposits table, they should not
be in any table (because they are just simple calculated
values based on an existing field and can be recalculated
whenever you want to display them). Saving them to a table
would be a violation of the rules of normalization and is a
definite no-no in relational databases. Same thing for the
division name (only need to save the division ID (which is
in the estimates table, right?).

Well, as for the division ID you are correct (That's
because I did this one and I only really need to
understand how to make the Division name show up on
the other forms. Right now what I'm getting is the
Number of the Division. I've always done the conversion

Forms!frmDeposit!Division = Forms![frmDivision].Column(1)

see the only thing that's stored in the tblEstimate is the
number not the actual division name. Since I put the
following in the division text box,

=[Forms]![frmEstimate]![Division]

I'm getting the right number just not sure how or where
to do the conversion.

Anyway the database was set up to store both
the DepositRequired and the AmountDue. (If I get the
okay, I will try to change it but until/if I do, I have
to make it work the way it's setup for now.

Another point of clarification is needed here. I'm starting
to get the impression that the deposit form can be navigated
from one estimate's deposit to a different estimate's
deposit while the estimate form is just sitting there
displaying the original estimate that started this whole
process. If this is true (I hope not), then you definitely
have some confusion in your user interface.

Nope, not true, there is a command button to print
the deposit and then the user exits from the Deposit
Screen back to the Estimate it came from.

If that is not the case and the deposit form can only
display the one estimate's deposit data, then the unbound
controls on the deposit form are only used to display a copy
of (or values calculated from) data on the estimate form.
What I was describing before meant for you to place the
expressions I posted in the unbound text box control's
ControlSource property. The text box's are not bound to a
field in a table, but they are not blank either. These text
boxes will calculate, display and make the desired estimate
values available for further use in the deposit form.

So I'm now clear on almost everything!!

And everything works except the Division conversion. Can you
help me with doing the Division conversion?

Thanks bunches!

LadyAmethyst

 

[Marshall Barton]

OK! It sounds like you've got the "unbound" controls
straightened out and there's only couple little details to
finalize.

See below for specific issues.
--
Marsh
MVP [MS Access]

Okay Marshall here goes,

Oh boy! We're both confused :-\

We may even be talking about two different things here. My
first concern is that you have a TotalCost field in the
tblEstimate table and its value is entered and displayed on
the estimates form, which makes sense. The place where
you're losing me is when you say that the DepositRequired
and AmountDue fields are in the deposits table. Not only
should those not be in the deposits table, they should not
be in any table (because they are just simple calculated
values based on an existing field and can be recalculated
whenever you want to display them). Saving them to a table
would be a violation of the rules of normalization and is a
definite no-no in relational databases. Same thing for the
division name (only need to save the division ID (which is
in the estimates table, right?).

Well, as for the division ID you are correct (That's
because I did this one and I only really need to
understand how to make the Division name show up on
the other forms. Right now what I'm getting is the
Number of the Division. I've always done the conversion

Forms!frmDeposit!Division = Forms![frmDivision].Column(1)

see the only thing that's stored in the tblEstimate is the
number not the actual division name. Since I put the
following in the division text box,

=[Forms]![frmEstimate]![Division]

I'm getting the right number just not sure how or where
to do the conversion.

I don't see any need to use VBA to do this. You can use a
text box expression for this too. The DLookup we talked
about earlier would be one way to do it. But in this
situation, it would be more efficient to use:

=Forms!frmEstimate.Division.Column(1)
(if those are the real names of the form and combobox)

Anyway the database was set up to store both
the DepositRequired and the AmountDue. (If I get the
okay, I will try to change it but until/if I do, I have
to make it work the way it's setup for now.

Putting them in the deposit table is the spreadsheet way of
looking at the world, which is a potential disaster in the
database world. The most obvious problem arises when
someone edits the TotalCost field in the estimates form and
think they're done. The deposit table will still have the
old values and everything is out of whack. Tell whoever
thought saving those value was a good idea that they don't
know what they're talking about. If they argue with you,
explain that there is **no way** to guarantee that the
calculated values will always be synchronized with the total
cost value. The bottom line is that your database will not
be reliable.

On the other hand, if the calculated values do not exist in
any table, they can not possibly disagree with the total
cost value.

A simplified way of thinking about the rules of
normalization is when you need to change any value anywhere
in your database, you only have to edit one field in one
record. Think about it, it's a pretty deep statement.
Serious reflection on this may change your entire mindset on
storing data, any data stored anywhere (even data on paper
in a file cabinet).

To take this issue a step further, (assuming I'm not
confused here too) I don't think the division ID should be
in the deposit table either. It's already in the estimate
table and the deposit table has the estimate ID so it can
always use that to locate the division ID (and through that
the division name).

Nope, not true, there is a command button to print
the deposit and then the user exits from the Deposit
Screen back to the Estimate it came from.

Whew! That's a relief ;-)

So I'm now clear on almost everything!!

And everything works except the Division conversion. Can you
help me with doing the Division conversion?

I think I answered this above. Let me know if you're still
having trouble with this one.

 

[LadyAmethyst]

Marshall,

Here we go...

OK! It sounds like you've got the "unbound" controls
straightened out and there's only couple little details to
finalize.

See below for specific issues.
--
Marsh
MVP [MS Access]


LadyAmethyst wrote: ........

see the only thing that's stored in the tblEstimate is the
number not the actual division name. Since I put the
following in the division text box,

=[Forms]![frmEstimate]![Division]

I'm getting the right number just not sure how or where
to do the conversion.

I don't see any need to use VBA to do this. You can use a
text box expression for this too. The DLookup we talked
about earlier would be one way to do it. But in this
situation, it would be more efficient to use:

=Forms!frmEstimate.Division.Column(1)
(if those are the real names of the form and combobox)

Last night after I sent the reply, I figured out what you
meant with the DLookup and got it working too!

Putting them in the deposit table is the spreadsheet way of
looking at the world, which is a potential disaster in the
database world. The most obvious problem arises when
someone edits the TotalCost field in the estimates form and
think they're done. The deposit table will still have the
old values and everything is out of whack. Tell whoever
thought saving those value was a good idea that they don't
know what they're talking about. If they argue with you,
explain that there is **no way** to guarantee that the
calculated values will always be synchronized with the total
cost value. The bottom line is that your database will not
be reliable.

Okay, but the reason this was set up this way is that the
information IS used in an EXCEL spreadsheet. (Actually I
have a goal of trying to include the accounting functions
in this database eventually.)

On the other hand, if the calculated values do not exist in
any table, they can not possibly disagree with the total
cost value.

A simplified way of thinking about the rules of
normalization is when you need to change any value anywhere
in your database, you only have to edit one field in one
record. Think about it, it's a pretty deep statement.
Serious reflection on this may change your entire mindset on
storing data, any data stored anywhere (even data on paper
in a file cabinet).

To take this issue a step further, (assuming I'm not
confused here too) I don't think the division ID should be
in the deposit table either. It's already in the estimate
table and the deposit table has the estimate ID so it can
always use that to locate the division ID (and through that
the division name).

The division ID is NOT stored in the Deposit Table. It's only
displayed on the Deposit Form Screen. The ONLY place it is
stored is in the Estimate Table.

........

I think I answered this above. Let me know if you're still
having trouble with this one.

Nope! Got everything working now including the Word print.
(except for the fact my dollar values are dropping the last
zero i.e. $1.50 show up as $1.5 or or $150.00 shows up as $150.
They are correct in the database so I'm assuming it's happening
in Word.) but that's it.

Your help (and patience) have helped me tremendously and if
I had it I would award you a Gold Medal!

Thanks again,

LadyAmethyst

 

[Marshall Barton]

LadyAmethyst wrote:
[snip]

Okay, but the reason this was set up this way is that the
information IS used in an EXCEL spreadsheet. (Actually I
have a goal of trying to include the accounting functions
in this database eventually.)

That's no excuse ;-)

If those values are in the deposit table, you have the
possibility of sending the wrong numbers to Excel.

The "right" way to send the data to another application is
to export the results of a query, not a table. This is
almost always necessary anyway because the data in the
spreadsheets is almost certainly not normalized and you need
a query to denormalize it for you. Come on back when you
get to this stage and we'll try to avoid some the issues you
might run into when exporting data.

[snip] Got everything working now including the Word print.

(except for the fact my dollar values are dropping the last
zero i.e. $1.50 show up as $1.5 or or $150.00 shows up as $150.
They are correct in the database so I'm assuming it's happening
in Word.) but that's it.

When you assign the value of a control to your Word
bookmark, you probably need to format it:

.Selection.Text = Format(Me!TotalCost, "Currency")

Your help (and patience) have helped me tremendously and if
I had it I would award you a Gold Medal!

Gold medals are nice ;-)

But, the satifaction of successfully completing a difficult
task is even better.

Keep up the good work,