473,695 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problems Saving Multiple Unbound Fields to a Table

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_Clic k()
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.OpenRecords et(QryFrmAddEnt ry, dbOpenDynaset)
With RstFrmAddEntry
.AddNew
!TripNumber = TripNumber.Valu e
!DateDispatched = DateDispatched. Value
!DateCompleted = DateCompleted.V alue
!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.Valu e
!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.Valu e
!Drop3Pay = Drop3Pay.Value
!Drop4Loc = Drop4Loc.Value
!Drop4Type = Drop4Type.Value
!Drop4Rate = Drop4Rate.Value
!Drop4Miles = Drop4Miles.Valu e
!Drop4Pay = Drop4Pay.Value
!UndeckPay = UndeckPay.Value
!ReDeckPay = ReDeckPay.Value
!DelayType1 = DelayType1.Valu e
!DelayHrs1 = DelayHrs1.Value
!DelayPay1 = DelayPay1.Value
!DelayType2 = DelayType2.Valu e
!DelayHrs2 = DelayHrs2.Value
!DelayPay2 = DelayPay2.Value
!DelayType3 = DelayType3.Valu e
!DelayHrs3 = DelayHrs3.Value
!DelayPay3 = DelayPay3.Value
!LayOverRate = LayOverRate.Val ue
!LayoverHrs = LayoverHrs.Valu e
!LayoverPay = LayoverPay.Valu e
!OutOfRoute = OutOfRoute.Valu e
!OutOfRoutez = OutOfRoutez.Val ue
!Taxi = Taxi.Value
!Tolls = Tolls.Value
!ATM = ATM.Value
!Fuel = Fuel.Value
!Misc = Misc.Value
!TotalReimburse ments = TotalReimbursem ents.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!
Apr 16 '06 #1
18 3790
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?

Apr 16 '06 #2
On 15 Apr 2006 17:54:31 -0700, pi********@hotm ail.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
Apr 16 '06 #3
On Sat, 15 Apr 2006 21:01:03 -0400, TORQUE <To****@home.or g> wrote:
On 15 Apr 2006 17:54:31 -0700, pi********@hotm ail.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.
Apr 16 '06 #4
Per TORQUE:
This seems to be where im having trouble.


What errors are being thrown by what statements?

--
PeteCresswell
Apr 16 '06 #5
On Sat, 15 Apr 2006 21:24:58 -0400, "(PeteCresswell )" <x@y.Invalid> 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
Apr 16 '06 #6
rkc
TORQUE wrote:
On Sat, 15 Apr 2006 21:01:03 -0400, TORQUE <To****@home.or g> wrote:

On 15 Apr 2006 17:54:31 -0700, pi********@hotm ail.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(DateComplet ed.Value & vbnullstring) > 0 then _
!DateCompleted = DateCompleted.V alue
Apr 16 '06 #7
"TORQUE" <To****@home.or g> 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
Apr 16 '06 #8
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*****@localh ost.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
Apr 16 '06 #9
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_Clic k()
On Error GoTo Command226_Clic k_err.
..
..
..
..
..

Command226_Clic k_xit:
Exit Sub

Command226_Clic k_err:
MsgBox "Line " & Erl & ": Error " & Err & "(" & Error$ & ")."
Resume Command226_Clic k_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
Apr 16 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1847
by: InDeSkize | last post by:
Hello my programming Super Heroes. I think this one is a no brainer, me being the one with no brain. I have a form that is used for data entry. The goal is to have an unbound form that only enters the record when you hit the "Save Record" button. I've been using a form that enters right into the table, and I'm running into too many problems with Multiple Primary Keys and
6
7118
by: Andy | last post by:
Hello, I am having many problems with setting up a parameter query that searches by the criteria entered or returns all records if nothing is entered. I have designed an unbound form with 3 fields on it: Date (DateSpan1 and DateSpan2), Originator, and GroupName. I have added a button that triggers a query and uses those fields as its parameter criteria to populate a form. The user must be allowed to either enter all of the
5
2943
by: David | last post by:
Hi I seem to be getting nowhere with this. I am opening a form which will be used to input Notes into different fields in a table. My problem is changing the unbound field name to the field name in the associated table that this form is based on. Is there anyway I can do this with vba ? I am using a different button object to open this form for the field it is to update. button1 updates unbound field name from text1 to 101Commnets...
3
2752
by: google | last post by:
I'm developing an application for use within my company in Access 2003. I'm new to '03, the application I did for my former employer was in '97. The two applications have similar functionality (we're sales offices, and I'm doing things such as associate directories, commission calculations, order tracking, etc.). 2003 seems to have a few extra features, but I seem to continually run in to oddities that seem like they SHOULD work, but...
6
10489
by: ApexData | last post by:
I have 2 tables: Table1 and Table2. Neither one has a primary key because each table will only have 1-record. My form is a SingleForm unbound with tabs (my desire here). Using this form, in the Record Source I originally had the statement TABLE1, and all worked fine. Now I want to open the second table as well, and this is where my problem is.
2
9444
by: angie | last post by:
I need to figure out how to create a user interface to search a query, but here's the bad part...I need to account for criteria on at least 7 of the fields. Here's what I'm thinking I need to do: Create an unbound form with unbound fields for all 7 of the fields in the query. Then in the query create parameters that refer to those fields. But it's not that simple because the user needs the flexibility of filling in as many or as few...
2
1383
by: keri | last post by:
Hi everyone, I've started a new topic so as not to get people confused. I apologise for the number of posts i've put on here over the last few days - i really am trying to learn this stuff but my brain is getting a bit frazzled. I have a form based on the table appointments with fields as below AppointmentNo (primary key - autonumber)
2
3061
by: Presto | last post by:
I am making a front end mdb so users can enter new members data. I can then import this into the master database on the backend and erase the existing info on the front end to keep the data reasonably secure. ( I borrowed the code from http://www.databasedev.co.uk/unbound-forms-add-data.html example) I have 4 tables that will be updated from this form: Members ContactInfo Payments
2
2063
by: Radu | last post by:
Hi. I have an Access application which does lots of things by going through some PINS in some large tables. This application is installed on multiple computers, and works fine, with one exception: For reporting purposes, I need to keep a "central" table where each application would save some data. Currently I'm using a LINKED excel file named "SharedAccumulator" and after each PIN successfully processed a record is added in the...
0
8565
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9113
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8977
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8841
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6488
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5838
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4339
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4577
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.