Problems Saving Multiple Unbound Fields to a Table

[TORQUE]

Hi,

Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having trouble.
I have tried keeping some of the fields bound and when I use the save button
it has been saving as 2 different records. This is unacceptable.

This is what I have, can anyone help me out with this?

================================================== =============

Private Sub Command226_Click()
On Error GoTo Err_Command226_Click
Dim Dbs As Database
Dim RstFrmAddEntry As Recordset
Dim QryFrmAddEntry As String

Set Dbs = OpenDatabase("C:\LOCATION OF MY FILE\File.mdb")
QryFrmAddEntry = "Table1"
Set RstFrmAddEntry = Dbs.OpenRecordset(QryFrmAddEntry, dbOpenDynaset)
With RstFrmAddEntry
.AddNew
!TripNumber = TripNumber.Value
!DateDispatched = DateDispatched.Value
!DateCompleted = DateCompleted.Value
!Advance = Advance.Value
!TripType = TripType.Value
!MilesPaid = MilesPaid.Value
!Origin = Origin.Value
!TripPay = TripPay.Value
!Drop1Loc = Drop1Loc.Value
!Drop1Type = Drop1Type.Value
!Drop1Rate = Drop1Rate.Value
!Drop1Miles = Drop1Miles.Value
!Drop1Pay = Drop1Pay.Value
!Drop2Loc = Drop2Loc.Value
!Drop2Type = Drop2Type.Value
!Drop2Rate = Drop2Rate.Value
!Drop2Miles = Drop2Mile.Value
!Drop2Pay = Drop2Pay.Value
!Drop3Loc = Drop3Loc.Value
!Drop3Type = Drop3Type.Value
!Drop3Rate = Drop3Rate.Value
!Drop3Miles = Drop3Miles.Value
!Drop3Pay = Drop3Pay.Value
!Drop4Loc = Drop4Loc.Value
!Drop4Type = Drop4Type.Value
!Drop4Rate = Drop4Rate.Value
!Drop4Miles = Drop4Miles.Value
!Drop4Pay = Drop4Pay.Value
!UndeckPay = UndeckPay.Value
!ReDeckPay = ReDeckPay.Value
!DelayType1 = DelayType1.Value
!DelayHrs1 = DelayHrs1.Value
!DelayPay1 = DelayPay1.Value
!DelayType2 = DelayType2.Value
!DelayHrs2 = DelayHrs2.Value
!DelayPay2 = DelayPay2.Value
!DelayType3 = DelayType3.Value
!DelayHrs3 = DelayHrs3.Value
!DelayPay3 = DelayPay3.Value
!LayOverRate = LayOverRate.Value
!LayoverHrs = LayoverHrs.Value
!LayoverPay = LayoverPay.Value
!OutOfRoute = OutOfRoute.Value
!OutOfRoutez = OutOfRoutez.Value
!Taxi = Taxi.Value
!Tolls = Tolls.Value
!ATM = ATM.Value
!Fuel = Fuel.Value
!Misc = Misc.Value
!TotalReimbursements = TotalReimbursements.Value
!Notes = Notes.Value
.Update
.Close
End With

Err_Command226_Click:
MsgBox Err.Description
Exit Sub

End Sub

================================================== ===========

Thanks in advance for your help.
Torque!

[pietlinden]

painfully stupid question, but why not just set the Required property
of the fields that don't need to be filled in to False, bind the form
to the table, and be done with it?

[TORQUE]

On 15 Apr 2006 17:54:31 -0700, pi********@hotmail.com wrote:

painfully stupid question, but why not just set the Required property
of the fields that don't need to be filled in to False, bind the form
to the table, and be done with it?

Didn't realize there were any stupid questions.
Since you seem to know so much, how about explainin this to me in such a way
that I might understand someone of your inteligence.

Thanks
TORQUE

[TORQUE]

On Sat, 15 Apr 2006 21:01:03 -0400, TORQUE <To****@home.org> wrote:

On 15 Apr 2006 17:54:31 -0700, pi********@hotmail.com wrote:

painfully stupid question, but why not just set the Required property
of the fields that don't need to be filled in to False, bind the form
to the table, and be done with it?

Didn't realize there were any stupid questions.
Since you seem to know so much, how about explainin this to me in such a way
that I might understand someone of your inteligence.

Thanks
TORQUE

Not all the fields will be used all the time, and other times they will be.
What I need is for the fields that have something in them to be recorded, and the ones
that are blank to be overlooked.

[(PeteCresswell)]

Per TORQUE:

This seems to be where im having trouble.

What errors are being thrown by what statements?

--
PeteCresswell

[TORQUE]

On Sat, 15 Apr 2006 21:24:58 -0400, "(PeteCresswell)" <[e-mail]> wrote:

Per TORQUE:

This seems to be where im having trouble.

What errors are being thrown by what statements?

When I click the SAVE button, an error pops up ( Object Required ).

Im assuming that it is because some fields are blank.

TORQUE

[rkc]

TORQUE wrote:

On Sat, 15 Apr 2006 21:01:03 -0400, TORQUE <To****@home.org> wrote:

On 15 Apr 2006 17:54:31 -0700, pi********@hotmail.com wrote:

painfully stupid question, but why not just set the Required property
of the fields that don't need to be filled in to False, bind the form
to the table, and be done with it?

Didn't realize there were any stupid questions.
Since you seem to know so much, how about explainin this to me in such a way
that I might understand someone of your inteligence.

Thanks
TORQUE

Not all the fields will be used all the time, and other times they will be.
What I need is for the fields that have something in them to be recorded, and the ones
that are blank to be overlooked.

So check if the controls have a value before you set the
field of the recordset equal to the corresponding control.
if len(DateCompleted.Value & vbnullstring) > 0 then _
!DateCompleted = DateCompleted.Value

[Larry Linson]

"TORQUE" <To****@home.org> wrote

painfully stupid question, but why not just set the Required property
of the fields that don't need to be filled in to False, bind the form
to the table, and be done with it?

Didn't realize there were any stupid questions.
Since you seem to know so much, how about
explaining this to me in such a way
that I might understand someone of your inteligence.

I'd suggest you read carefully before "smarting off" (as I note that your
response to pietlinden ended his participation in the thread, and he knows
his way around Access). The way I read his response, the question he is
describing as "painfully stupid" is his own, the one that asks "why not just
set the Required property of the fields that don't need to be filled in to
False, bind the form to the table, and be done with it?" If I am correct, if
you don't want your posting id to be killfiled by Piet and others, then you
really probably need to apologize to him (you can condition that apology on
my being correct in my understanding, if you wish).

For other good suggestions on effective use of the newsgroup, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

If you want an answer to the question you asked, you may need to be more
specific.

As I read the question, there are a number of Fields -- some are always
required, and others are optional. If my understanding is correct, you can
bind the Form to a Record in a Query or Table, set the Required property on
those Fields which are always Required to True, leave the Required property
on the other Fields as False, don't worry about Saving the record
yourself -- it will automatically be saved for you if you have "dirtied" it
(that is, changed the content of any Field) when you close the Form, move
off the Record to another, move the cursor from the main Form to a Subform
Control, or close the Database.

I'm not sure why you expect it to be useful to bind some fields, but write
from VBA code for others.

But, if I have misunderstood, please clarify. Specifically, you mention
Fields that are only "required" some of the time. If those are not always
"optional", then you need to be clear about how one might determine whethere
this is one of those "some of the times" that they are required.

Larry Linson
Microsoft Access MVP

[TORQUE]

Hi Larry,

First I want to say I appreciate your input here. For that matter anyone who
offers help without insulting the intelligence of the person asking for help.
On Sun, 16 Apr 2006 03:50:04 GMT, "Larry Linson" <bo*****@localhost.not> wrote:

painfully stupid question, but why not just set the Required property
of the fields that don't need to be filled in to False, bind the form
to the table, and be done with it?
Didn't realize there were any stupid questions.
Since you seem to know so much, how about
explaining this to me in such a way
that I might understand someone of your inteligence.

I'd suggest you read carefully before "smarting off" (as I note that your
response to pietlinden ended his participation in the thread, and he knows
his way around Access). The way I read his response, the question he is
describing as "painfully stupid" is his own, the one that asks "why not just
set the Required property of the fields that don't need to be filled in to
False, bind the form to the table, and be done with it?" If I am correct, if
you don't want your posting id to be killfiled by Piet and others, then you
really probably need to apologize to him (you can condition that apology on
my being correct in my understanding, if you wish).

For other good suggestions on effective use of the newsgroup, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

If you want an answer to the question you asked, you may need to be more
specific.

As I read the question, there are a number of Fields -- some are always
required, and others are optional. If my understanding is correct, you can
bind the Form to a Record in a Query or Table, set the Required property on
those Fields which are always Required to True, leave the Required property
on the other Fields as False, don't worry about Saving the record
yourself -- it will automatically be saved for you if you have "dirtied" it
(that is, changed the content of any Field) when you close the Form, move
off the Record to another, move the cursor from the main Form to a Subform
Control, or close the Database.

I'm not sure why you expect it to be useful to bind some fields, but write
from VBA code for others.

Is there a specific way that is better than others?
But, if I have misunderstood, please clarify. Specifically, you mention
Fields that are only "required" some of the time. If those are not always
"optional", then you need to be clear about how one might determine whethere
this is one of those "some of the times" that they are required.
Well, im not sure how you mean to be more clear. Some fields that have the same
name with the exception of having the numbers beside them, these are some of
the more common fields that may not always be needed, or may not apply to a
specific trip.
Larry Linson
Microsoft Access MVP

Thanks
TORQUE

[(PeteCresswell)]

Per TORQUE:

When I click the SAVE button, an error pops up ( Object Required ).

Im assuming that it is because some fields are blank.

It would help nail it down to know which statement the error was being thrown
on.

But, assuming it's a Null (not blank) field; it sounds like you need a bunch of
"IF" statements.

e.g.
----------------------------------------
With Me.chkWhatever
If Not IsNull(.Value) Then
MyRS.Whatever = .Value
End if
End With
----------------------------------------

alternatively:
----------------------------------------
With Me.chkWhatever
If Len(.Value & "") > 0 Then
MyRS.Whatever = .Value
End if
End With
----------------------------------------

How about expanding your error trapping code so that
it tells what line it's dying on?

e.g.
---------------------------------------------------
Private Sub Command226_Click()
On Error GoTo Command226_Click_err.
..
..
..
..
..

Command226_Click_xit:
Exit Sub

Command226_Click_err:
MsgBox "Line " & Erl & ": Error " & Err & "(" & Error$ & ")."
Resume Command226_Click_Err_xit
---------------------------------------------------

Also, save yourself a lot of grief later on and assign more meaningful control
names.

Command_266 and it's cohorts will drive you nuts three months from now when you
have to figure out what's going on in the code. "cmdSave" works a *lot*
better.

Finally, prefix all of those other object names with something that tells what
kind of control they are:
--------------------------
txt = TextBox
cmd = CommandButton
tgl = Toggle
chk = Checkbox
lst = ListBox
cbo = ComboBox
opt = OptionGroup
rad = RadioButton
frm = Form
qry = Query
tbl = core table
tlkp = lookup table
stbl = table used only by applicatin (no user data)
.... and so-forth
--------------------------

Some benefits from that practice:
-------------------------------------------------------------------------
1) Reading the code, you have an idea what kind of values to expect.
e.g. normally you would only expect True and False in a CheckBox and
code that provides for anything else - except as error trapping -
is suspect.

2) Sometimes MS Access can get confused between the control name and the
name of the .ControlSource. The prefixes remove any doubt as to
what is being referred to.

3) In the case of tables and queries, MS Access sometimes presents both
types of objects in the same list. Prefixes let you know which type
you're looking at.
-------------------------------------------------------------------------
--
PeteCresswell

[Larry Linson]

"TORQUE" <To****@home.org> wrote

First I want to say I appreciate your input here. For
that matter anyone who offers help without insulting
the intelligence of the person asking for help.
A primary point in my response was that Piet was _not_ insulting your
intelligence, nor was he calling your question "stupid". I do not see your
acceptance of that, nor the apology that I think is due Piet.

Neither am I calling your question stupid, but I am calling the information
you provided in the initial question and in this followup "insufficiently
complete and insufficiently detailed" for anyone here to be of much help.
Well, im not sure how you mean to be more clear.
You have to tell us what you have and what you want to do in specific,
detailed terms. I thought I was reasonably specific in telling you what we
need to be able to help you.
Some fields that have the same name with the exception
of having the numbers beside them, these are some of
the more common fields that may not always be needed,
or may not apply to a specific trip.

I have not the vaguest idea of what fields might be needed for your
particular application, nor how the code would determine whether a specific
field is or is not needed for a particular "trip". And, to be honest, none
of us here really have the time or energy to play guessing games as to what
someone is asking. I did that, in part, on my first response and you did not
reply as to whether my assumption was correct, incorrect, or whether it was
helpful.

If you want us to be able to help you (and that's why many of us participate
here), you need to give us the information we need. We don't know what your
application is, we don't know what data you are keeping, and we don't know
whether a field with the "same name but a number beside it" is entered at
the user's option or if depending on some other field, it _must_ be entered.
And the only way we are going to know is if you tell us. Sometimes, when we
ask for specific details, it is the person who was asking the question who
comes up with their own answer.

Could it be, for example, if the trip type is "Safari" or
"Mountainclimbing", another field, "Licensed Guide" would be required? Or,
is it just if there is an entry in "Licensed Guide", the assumption would be
that a licensed guide is required for that type of trip?

Larry Linson
Microsoft Access MVP

[David W. Fenton]

TORQUE <To****@home.org> wrote in
news:8g********************************@4ax.com:

Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having trouble.
I have tried keeping some of the fields bound and when I use the
save button it has been saving as 2 different records. This is
unacceptable.

Why are you using an unbound form in the first place? What do you
hope to gain by that? There must be a reason, as coding unbound
forms is much more complicated than using bound forms.

Second, in your code, you refer to the controls on the form by name
without specifying the form. You should refer to them as
Me!DateDispatched.Value or Me.DateDispatched.Value (I prefer the
former). And I see no reason to specify the .Value property, as this
is the default value of all controls, so Me!DateDispatched should
suffice,

Third, if you don't want to update fields that haven't changed or
don't have values, then just test for whether or not the field is
blank or unchanged. I usually do this with something like this:

If Nz(rs!Field,vbNullString) <> Nz(Me!Control,vbNullString) Then
rs!Field = Me!Control
End If

This can be automated in a loop through the controls if the
controlnames are the same as the names of the unbound controls:

Dim ctl As Control
Dim strName As String

For Each ctl In Me.Controls
strName = ctl.Name
If Nz(rs(strName),vbNullString) <> Nz(ctl,vbNullString) Then
rs(strName) = ctl
End If
Next ctl

Set ctl = Nothing

Of course, not all controls on your form may be used for editing
fields in your record, so you'd have to control that somehow. I
usually set up one or more custom collections, populated by looping
through Me.Controls (as above) and checking the .Tag property (which
I set to indicate which collection they belong in).

Last of all, the error you're having is probably happening because
you're attempting to close the recordset inside the With block.
Change the end from:

.Close
End With

to

End With
RstFrmAddEntry.Close

And add:

Set RstFrmAddEntry = Nothing
Dbs.Close
Set Dbs = Nothing

And it should work.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[TORQUE]

On Sun, 16 Apr 2006 16:14:10 GMT, "Larry Linson" <bo*****@localhost.not> wrote:

"TORQUE" <To****@home.org> wrote

First I want to say I appreciate your input here. For
that matter anyone who offers help without insulting
the intelligence of the person asking for help.
A primary point in my response was that Piet was _not_ insulting your
intelligence, nor was he calling your question "stupid". I do not see your
acceptance of that, nor the apology that I think is due Piet.

Well, I took it as an insult. If I misunderstood, I appologize.
Neither am I calling your question stupid, but I am calling the information
you provided in the initial question and in this followup "insufficiently
complete and insufficiently detailed" for anyone here to be of much help.

Well, im not sure how you mean to be more clear.
You have to tell us what you have and what you want to do in specific,
detailed terms. I thought I was reasonably specific in telling you what we
need to be able to help you.

I am trying to keep track of each trip I make, I always have 1 drop, but can have as many as 4.
I am just trying to keep up with each trip individually. Keeping track of All miles for each drop.

Sometimes I have delays, sometimes I do not.

Some fields that have the same name with the exception
of having the numbers beside them, these are some of
the more common fields that may not always be needed,
or may not apply to a specific trip.

BTW, I have Changed the form back to being bound. The reason I went with an unbound form was because of an
article I read on a website which mentioned something about the fact that sometimes a form will save fields automatically
before your actually ready to save the fields. Does this make any sense?

Anyhow, I changed it back, but, I have 10 fields that I want to do calculations automatically, which the calculating works fine,
but it will not save the results of the field to the table. I thought if I could not get those 10 to save to the table, I would be
able to create an entire form that I can save all the fields at 1 time with just a single button. This way has proven to be more
trouble thatn what I thought to be after visiting here: accessdatabasetips.com/unbound-forms-1.html
I have not the vaguest idea of what fields might be needed for your
particular application, nor how the code would determine whether a specific
field is or is not needed for a particular "trip". And, to be honest, none
of us here really have the time or energy to play guessing games as to what
someone is asking. I did that, in part, on my first response and you did not
reply as to whether my assumption was correct, incorrect, or whether it was
helpful.
My calculated fields are: TripPay, Drop1Pay, Drop2Pay, Drop3Pay, Drop4Pay, Delaypay1, DelayPay2, DelayPay3,
LayOverPay, and TotalReimbursements.
If you want us to be able to help you (and that's why many of us participate
here), you need to give us the information we need. We don't know what your
application is, we don't know what data you are keeping, and we don't know
whether a field with the "same name but a number beside it" is entered at
the user's option or if depending on some other field, it _must_ be entered.
And the only way we are going to know is if you tell us. Sometimes, when we
ask for specific details, it is the person who was asking the question who
comes up with their own answer.

Could it be, for example, if the trip type is "Safari" or
"Mountainclimbing", another field, "Licensed Guide" would be required? Or,
is it just if there is an entry in "Licensed Guide", the assumption would be
that a licensed guide is required for that type of trip?

Larry Linson
Microsoft Access MVP

Thanks Larry
TORQUE

[TORQUE]

On Sun, 16 Apr 2006 14:27:26 -0500, "David W. Fenton" <XX*******@dfenton.com.invalid> wrote:

TORQUE <To****@home.org> wrote in
news:8g********************************@4ax.com :

Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having trouble.
I have tried keeping some of the fields bound and when I use the
save button it has been saving as 2 different records. This is
unacceptable.
Why are you using an unbound form in the first place? What do you
hope to gain by that? There must be a reason, as coding unbound
forms is much more complicated than using bound forms.

Well, a website for tips on Access suggested it, so I thought it might work.
Seems to be more problems. www dot accessdatabasetips dot com slash forms dot html
Second, in your code, you refer to the controls on the form by name
without specifying the form. You should refer to them as
Me!DateDispatched.Value or Me.DateDispatched.Value (I prefer the
former). And I see no reason to specify the .Value property, as this
is the default value of all controls, so Me!DateDispatched should
suffice,
What is the whole ( Me! statements)? I dont understand this.
This is what I am working on " home dot carolina dot rr dot com slash pcmed"
Third, if you don't want to update fields that haven't changed or
don't have values, then just test for whether or not the field is
blank or unchanged. I usually do this with something like this:
I have changed the form back to the way it was and bound it to my table.
It's my calculated fields that I am trying to get them to be saved to the table.
If Nz(rs!Field,vbNullString) <> Nz(Me!Control,vbNullString) Then
rs!Field = Me!Control
End If

This can be automated in a loop through the controls if the
controlnames are the same as the names of the unbound controls:

Dim ctl As Control
Dim strName As String

For Each ctl In Me.Controls
strName = ctl.Name
If Nz(rs(strName),vbNullString) <> Nz(ctl,vbNullString) Then
rs(strName) = ctl
End If
Next ctl

Set ctl = Nothing

Of course, not all controls on your form may be used for editing
fields in your record, so you'd have to control that somehow. I
usually set up one or more custom collections, populated by looping
through Me.Controls (as above) and checking the .Tag property (which
I set to indicate which collection they belong in).

Last of all, the error you're having is probably happening because
you're attempting to close the recordset inside the With block.
Change the end from:

.Close
End With

to

End With
RstFrmAddEntry.Close

And add:

Set RstFrmAddEntry = Nothing
Dbs.Close
Set Dbs = Nothing

And it should work.

[Larry Linson]

I am going to top-post and leave the details below, for convenience.

Yes, saving calculated Controls (that is, if the calculation is specified in
the Control Source) is definitely a problem. Generally, it shouldn't be
done, but the calculations re-done at the time you need to use the value.
The exception to this would be a calculation done using factors that are not
available, or may have been changed, by the time the calculated value is
used.

You may not like this, but it _appears_ to me that you have an unnormalized
design, too, which can cause you problems in the long run. It's very likely
that you should have a Trip table with separate, related records for Drops
and Delays (though if a delay is associated with the drop, then it might be
a Field in the Drop record). Bear in mind that an unnormalized design is
likely to cause you more trouble later than fixing it now would be.

All that said... if you have values that are calculated based on an
expression in their Control Source, there isn't anywhere to specify the
Field in the RecordSource to which they are bound. The answer is to add
TextBoxes with their Visible property set to False/No which are bound to a
Field in the Recordsource, one for each Calculated Control that you want to
Save. Put VBA code in the AfterUpdate event of the Calculated Control, or
the LostFocus event, if AfterUpdate proves to be a problem to copy the value
of the Calculated Control to the Invisible Text Box.

And, if you do encounter problems later that relate to the design, we will
try to deal with those.

Now, I _think_ I understood the problem, and the implications as to the
specs... for example, if a Drop2 is entered, and a Delay 1 is entered, then
they are needed, but there's no way, other than their being entered to
determine that they are needed and _enforce_ that they are entered. If I
have misunderstood, please explain.

Larry Linson
Microsoft Access MVP
"TORQUE" <To****@home.org> wrote in message
news:u3********************************@4ax.com...

On Sun, 16 Apr 2006 16:14:10 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:

"TORQUE" <To****@home.org> wrote

> First I want to say I appreciate your input here. For
> that matter anyone who offers help without insulting
> the intelligence of the person asking for help.

A primary point in my response was that Piet was _not_ insulting your
intelligence, nor was he calling your question "stupid". I do not see
your
acceptance of that, nor the apology that I think is due Piet.

Well, I took it as an insult. If I misunderstood, I appologize.

Neither am I calling your question stupid, but I am calling the
information
you provided in the initial question and in this followup "insufficiently
complete and insufficiently detailed" for anyone here to be of much help.

> Well, im not sure how you mean to be more clear.

You have to tell us what you have and what you want to do in specific,
detailed terms. I thought I was reasonably specific in telling you what we
need to be able to help you.

I am trying to keep track of each trip I make, I always have 1 drop, but
can have as many as 4.
I am just trying to keep up with each trip individually. Keeping track of
All miles for each drop.

Sometimes I have delays, sometimes I do not.

> Some fields that have the same name with the exception
> of having the numbers beside them, these are some of
> the more common fields that may not always be needed,
> or may not apply to a specific trip.

BTW, I have Changed the form back to being bound. The reason I went with
an unbound form was because of an
article I read on a website which mentioned something about the fact that
sometimes a form will save fields automatically
before your actually ready to save the fields. Does this make any sense?

Anyhow, I changed it back, but, I have 10 fields that I want to do
calculations automatically, which the calculating works fine,
but it will not save the results of the field to the table. I thought if I
could not get those 10 to save to the table, I would be
able to create an entire form that I can save all the fields at 1 time
with just a single button. This way has proven to be more
trouble thatn what I thought to be after visiting here:
accessdatabasetips.com/unbound-forms-1.html

I have not the vaguest idea of what fields might be needed for your
particular application, nor how the code would determine whether a
specific
field is or is not needed for a particular "trip". And, to be honest, none
of us here really have the time or energy to play guessing games as to
what
someone is asking. I did that, in part, on my first response and you did
not
reply as to whether my assumption was correct, incorrect, or whether it
was
helpful.

My calculated fields are: TripPay, Drop1Pay, Drop2Pay, Drop3Pay, Drop4Pay,
Delaypay1, DelayPay2, DelayPay3,
LayOverPay, and TotalReimbursements.

If you want us to be able to help you (and that's why many of us
participate
here), you need to give us the information we need. We don't know what
your
application is, we don't know what data you are keeping, and we don't know
whether a field with the "same name but a number beside it" is entered at
the user's option or if depending on some other field, it _must_ be
entered.
And the only way we are going to know is if you tell us. Sometimes, when
we
ask for specific details, it is the person who was asking the question who
comes up with their own answer.

Could it be, for example, if the trip type is "Safari" or
"Mountainclimbing", another field, "Licensed Guide" would be required? Or,
is it just if there is an entry in "Licensed Guide", the assumption would
be
that a licensed guide is required for that type of trip?

Larry Linson
Microsoft Access MVP

Thanks Larry
TORQUE

[TORQUE]

On Mon, 17 Apr 2006 05:04:54 GMT, "Larry Linson" <bo*****@localhost.not> wrote:

I am going to top-post and leave the details below, for convenience.

Yes, saving calculated Controls (that is, if the calculation is specified in
the Control Source) is definitely a problem. Generally, it shouldn't be
done, but the calculations re-done at the time you need to use the value.
The exception to this would be a calculation done using factors that are not
available, or may have been changed, by the time the calculated value is
used.

You may not like this, but it _appears_ to me that you have an unnormalized
design, too, which can cause you problems in the long run. It's very likely
that you should have a Trip table with separate, related records for Drops
and Delays (though if a delay is associated with the drop, then it might be
a Field in the Drop record). Bear in mind that an unnormalized design is
likely to cause you more trouble later than fixing it now would be.
I dont mind you saying this, but, can you explain to me what you mean by
unnormalized design? Without input from people who know what they are
doing, I could not improve on my techniques. If you dont mind elaborating
some on the unnormalized design. I have some snaps of what I am working
on posted, maybe this may give you a better perspective of the design.
http colon slash slash home dot carolina dot rr dot com slash pcmed.
Hope it is okay to put the site in here the way I did.
All that said... if you have values that are calculated based on an
expression in their Control Source, there isn't anywhere to specify the
Field in the RecordSource to which they are bound. The answer is to add
TextBoxes with their Visible property set to False/No which are bound to a
Field in the Recordsource, one for each Calculated Control that you want to
Save. Put VBA code in the AfterUpdate event of the Calculated Control, or
the LostFocus event, if AfterUpdate proves to be a problem to copy the value
of the Calculated Control to the Invisible Text Box.
I believe I know what you are talking about. Let me know if I do or dont. I have
created a couple of extra text fields with the calculations under the fields where
I want the calculations to go. In order for me to get that calculation to save into
my table, I have to manually copy the answer into the proper fields.
I am actually very good with Excel and calculations. Access is proving to be
quite a challenge for me. But this is what keeps me going. I like a challenge.

( Put VBA code in the AfterUpdate event of the Calculated Control, or
the LostFocus event ) I understand what ur saying here, but do not know how
to do this sort of thing.
And, if you do encounter problems later that relate to the design, we will
try to deal with those.

Now, I _think_ I understood the problem, and the implications as to the
specs... for example, if a Drop2 is entered, and a Delay 1 is entered, then
they are needed, but there's no way, other than their being entered to
determine that they are needed and _enforce_ that they are entered. If I
have misunderstood, please explain.
I do think you are understanding my problem. And this was the reason I was trying
to come up with a [Save Button] to do the job. And I have done this, but with more
problems, It save the selected fields, but as a completely different entry, and not as
the same entry with all the other information. Does this make sense to you?
Larry Linson
Microsoft Access MVP

Once again I want to say Thankyou for your correspondence.
Robert Dodgens

"TORQUE" <To****@home.org> wrote in message
news:u3********************************@4ax.com.. .

On Sun, 16 Apr 2006 16:14:10 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:

"TORQUE" <To****@home.org> wrote

> First I want to say I appreciate your input here. For
> that matter anyone who offers help without insulting
> the intelligence of the person asking for help.

A primary point in my response was that Piet was _not_ insulting your
intelligence, nor was he calling your question "stupid". I do not see
your
acceptance of that, nor the apology that I think is due Piet.

Well, I took it as an insult. If I misunderstood, I appologize.

Neither am I calling your question stupid, but I am calling the
information
you provided in the initial question and in this followup "insufficiently
complete and insufficiently detailed" for anyone here to be of much help.

> Well, im not sure how you mean to be more clear.

You have to tell us what you have and what you want to do in specific,
detailed terms. I thought I was reasonably specific in telling you what we
need to be able to help you.

I am trying to keep track of each trip I make, I always have 1 drop, but
can have as many as 4.
I am just trying to keep up with each trip individually. Keeping track of
All miles for each drop.

Sometimes I have delays, sometimes I do not.

> Some fields that have the same name with the exception
> of having the numbers beside them, these are some of
> the more common fields that may not always be needed,
> or may not apply to a specific trip.

BTW, I have Changed the form back to being bound. The reason I went with
an unbound form was because of an
article I read on a website which mentioned something about the fact that
sometimes a form will save fields automatically
before your actually ready to save the fields. Does this make any sense?

Anyhow, I changed it back, but, I have 10 fields that I want to do
calculations automatically, which the calculating works fine,
but it will not save the results of the field to the table. I thought if I
could not get those 10 to save to the table, I would be
able to create an entire form that I can save all the fields at 1 time
with just a single button. This way has proven to be more
trouble thatn what I thought to be after visiting here:
accessdatabasetips.com/unbound-forms-1.html

I have not the vaguest idea of what fields might be needed for your
particular application, nor how the code would determine whether a
specific
field is or is not needed for a particular "trip". And, to be honest, none
of us here really have the time or energy to play guessing games as to
what
someone is asking. I did that, in part, on my first response and you did
not
reply as to whether my assumption was correct, incorrect, or whether it
was
helpful.

My calculated fields are: TripPay, Drop1Pay, Drop2Pay, Drop3Pay, Drop4Pay,
Delaypay1, DelayPay2, DelayPay3,
LayOverPay, and TotalReimbursements.

If you want us to be able to help you (and that's why many of us
participate
here), you need to give us the information we need. We don't know what
your
application is, we don't know what data you are keeping, and we don't know
whether a field with the "same name but a number beside it" is entered at
the user's option or if depending on some other field, it _must_ be
entered.
And the only way we are going to know is if you tell us. Sometimes, when
we
ask for specific details, it is the person who was asking the question who
comes up with their own answer.

Could it be, for example, if the trip type is "Safari" or
"Mountainclimbing", another field, "Licensed Guide" would be required? Or,
is it just if there is an entry in "Licensed Guide", the assumption would
be
that a licensed guide is required for that type of trip?

Larry Linson
Microsoft Access MVP

Thanks Larry
TORQUE

[David W. Fenton]

TORQUE <To****@home.org> wrote in
news:dk********************************@4ax.com:

On Sun, 16 Apr 2006 14:27:26 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

TORQUE <To****@home.org> wrote in
news:8g********************************@4ax.co m:

Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having trouble.
I have tried keeping some of the fields bound and when I use the
save button it has been saving as 2 different records. This is
unacceptable.
Why are you using an unbound form in the first place? What do you
hope to gain by that? There must be a reason, as coding unbound
forms is much more complicated than using bound forms.

Well, a website for tips on Access suggested it, so I thought it
might work. Seems to be more problems. www dot accessdatabasetips
dot com slash forms dot html

That site doesn't discuss the reason for using unbound forms, and
explicitly says it won't. There is no need to go to an advanced
method (which is what coding unbound forms is) unless the normal
approaches are causing problems. I"ve been coding Access apps for
clients since 1996, and I've written maybe a half dozen unbound
forms for data editing in that whole period.

Second, in your code, you refer to the controls on the form by
name without specifying the form. You should refer to them as
Me!DateDispatched.Value or Me.DateDispatched.Value (I prefer the
former). And I see no reason to specify the .Value property, as
this is the default value of all controls, so Me!DateDispatched
should suffice,

What is the whole ( Me! statements)? I dont understand this. . . .

Well, when you refer to a control without the Me, it's unclear what
it is. It could be a function, it could be a subroutine, any number
of things. The "Me" keyword refers to the module in which you're
doing your coding. In a form's VBA module, "Me" refers to the form
the module is attached to. In a report, Me refers to the report. It
can also be used in a class module, but that's an advanced topic --
it cannot be used in a standalone VBA module that is not a class
module.

I see that you were just copying the code from the website you cite
above. Let me tell you that the practice of leaving off the Me
reference shows that the site you were using was coded by someone
who is not very good at what he does. I have never seen a published
Access book use unqualified control references in that fashion. It's
something I've only seen in code produced by a programmer who was
unprofessional, and/or inexperienced.

Those terms would have applied to me back in 1996, but by reading
books on Access and code posted in this newsgroup, I learned to do
better.
. . . This is what I am working on " home dot carolina dot rr dot
com slash pcmed"
I don't see anything there except a list of home pages. What are you
intending to refer to?

Third, if you don't want to update fields that haven't changed or
don't have values, then just test for whether or not the field is
blank or unchanged. I usually do this with something like this:

I have changed the form back to the way it was and bound it to my
table.

That will make it easier.
It's my calculated fields that I am trying to get them to be saved
to the table.

Most calculated values oughtn't be stored. The only calculations you
should store are ones that are based on information gathered at the
time the record is edited that could change over time (such as a
sales tax amount, which might be based on a rate that changes over
time).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jeff Smith]

"TORQUE" <To****@home.org> wrote in message
news:50********************************@4ax.com...

On Mon, 17 Apr 2006 05:04:54 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:

You may not like this, but it _appears_ to me that you have an
unnormalized
design, too, which can cause you problems in the long run. It's very
likely
that you should have a Trip table with separate, related records for Drops
and Delays (though if a delay is associated with the drop, then it might
be
a Field in the Drop record). Bear in mind that an unnormalized design is
likely to cause you more trouble later than fixing it now would be.

I dont mind you saying this, but, can you explain to me what you mean by
unnormalized design? Without input from people who know what they are
doing, I could not improve on my techniques. If you dont mind elaborating
some on the unnormalized design. I have some snaps of what I am working
on posted, maybe this may give you a better perspective of the design.
http colon slash slash home dot carolina dot rr dot com slash pcmed.
Hope it is okay to put the site in here the way I did.

Try
http://www.devhood.com/tutorials/tut...tutorial_id=95

[TORQUE]

On Mon, 17 Apr 2006 14:18:33 -0500, "David W. Fenton" <XX*******@dfenton.com.invalid> wrote:

TORQUE <To****@home.org> wrote in
news:dk********************************@4ax.com :

On Sun, 16 Apr 2006 14:27:26 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

TORQUE <To****@home.org> wrote in
news:8g********************************@4ax.com :

Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having trouble.
I have tried keeping some of the fields bound and when I use the
save button it has been saving as 2 different records. This is
unacceptable.

Why are you using an unbound form in the first place? What do you
hope to gain by that? There must be a reason, as coding unbound
forms is much more complicated than using bound forms.

Well, a website for tips on Access suggested it, so I thought it
might work. Seems to be more problems. www dot accessdatabasetips
dot com slash forms dot html

That site doesn't discuss the reason for using unbound forms, and
explicitly says it won't. There is no need to go to an advanced
method (which is what coding unbound forms is) unless the normal
approaches are causing problems. I"ve been coding Access apps for
clients since 1996, and I've written maybe a half dozen unbound
forms for data editing in that whole period.

Second, in your code, you refer to the controls on the form by
name without specifying the form. You should refer to them as
Me!DateDispatched.Value or Me.DateDispatched.Value (I prefer the
former). And I see no reason to specify the .Value property, as
this is the default value of all controls, so Me!DateDispatched
should suffice,

What is the whole ( Me! statements)? I dont understand this. . . .

Well, when you refer to a control without the Me, it's unclear what
it is. It could be a function, it could be a subroutine, any number
of things. The "Me" keyword refers to the module in which you're
doing your coding. In a form's VBA module, "Me" refers to the form
the module is attached to. In a report, Me refers to the report. It
can also be used in a class module, but that's an advanced topic --
it cannot be used in a standalone VBA module that is not a class
module.

I see that you were just copying the code from the website you cite
above. Let me tell you that the practice of leaving off the Me
reference shows that the site you were using was coded by someone
who is not very good at what he does. I have never seen a published
Access book use unqualified control references in that fashion. It's
something I've only seen in code produced by a programmer who was
unprofessional, and/or inexperienced.

Those terms would have applied to me back in 1996, but by reading
books on Access and code posted in this newsgroup, I learned to do
better.

. . . This is what I am working on " home dot carolina dot rr dot
com slash pcmed"

I don't see anything there except a list of home pages. What are you
intending to refer to?

http://home.carolina.rr.com/pcmed/

Third, if you don't want to update fields that haven't changed or
don't have values, then just test for whether or not the field is
blank or unchanged. I usually do this with something like this:

I have changed the form back to the way it was and bound it to my
table.

That will make it easier.

It's my calculated fields that I am trying to get them to be saved
to the table.

Most calculated values oughtn't be stored. The only calculations you
should store are ones that are based on information gathered at the
time the record is edited that could change over time (such as a
sales tax amount, which might be based on a rate that changes over
time).