UPDATE Table with multiple textboxes

I have three unbound textboxes on a form.  Each textbox is used to input the 
amount of money A,B,C deposited.  textbox1 is labeled A, textbox2 labeled 
B,....

When the user clicks a button, I need to update the table to add a row for A 
then add a new row for B and then C.

Below is the code I have so far for the update, but I can't figure out how 
to repeat the update for B & C.  

Lastly, the table has a field named 'memo'.  I would like to add the textbox 
caption to that field on each record.   So, for the first record, the deposit 
field would be whatever number is in the Textbox labled A and the memo would 
be A.

here is the code i have for the update.....

Private Sub Command10_Click()
Dim mycon As New ADODB.Connection
Dim adors As New ADODB.Recordset
Set mycon = CurrentProject.Connection
adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic
adors.AddNew
adors!amount = Me.Text0
adors!memo = Me.Label1.Caption
adors.Update
adors.Close
mycon.Close
End Sub

thanks
0
Utf
3/8/2010 10:34:01 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
985 Views

Similar Articles

[PageSpeed] 32

gator wrote:
> I have three unbound textboxes on a form.  Each textbox is used to
> input the amount of money A,B,C deposited.  textbox1 is labeled A,
> textbox2 labeled B,....
>
> When the user clicks a button, I need to update the table to add a
> row for A then add a new row for B and then C.
>
> Below is the code I have so far for the update, but I can't figure
> out how to repeat the update for B & C.
>
> Lastly, the table has a field named 'memo'.  I would like to add the
> textbox caption to that field on each record.   So, for the first
> record, the deposit field would be whatever number is in the Textbox
> labled A and the memo would be A.
>
> here is the code i have for the update.....
>
> Private Sub Command10_Click()
> Dim mycon As New ADODB.Connection
> Dim adors As New ADODB.Recordset
  Dim i as integer
> Set mycon = CurrentProject.Connection
> adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic

For i = 1 to 3

   > adors.AddNew
   > adors!amount = Me.Text0
   > adors!memo = Me.Label1.Caption
   > adors.Update
next i
> adors.Close
> mycon.Close
> End Sub
>
> thanks


Even without knowing why you want to do this and what value a table with A, 
B, C and some numbers has, this is a bad way to do it. 


0
Mike
3/9/2010 4:17:59 AM
why is this such a bad way to do it?  What is the harm?

"Mike Painter" wrote:

> gator wrote:
> > I have three unbound textboxes on a form.  Each textbox is used to
> > input the amount of money A,B,C deposited.  textbox1 is labeled A,
> > textbox2 labeled B,....
> >
> > When the user clicks a button, I need to update the table to add a
> > row for A then add a new row for B and then C.
> >
> > Below is the code I have so far for the update, but I can't figure
> > out how to repeat the update for B & C.
> >
> > Lastly, the table has a field named 'memo'.  I would like to add the
> > textbox caption to that field on each record.   So, for the first
> > record, the deposit field would be whatever number is in the Textbox
> > labled A and the memo would be A.
> >
> > here is the code i have for the update.....
> >
> > Private Sub Command10_Click()
> > Dim mycon As New ADODB.Connection
> > Dim adors As New ADODB.Recordset
>   Dim i as integer
> > Set mycon = CurrentProject.Connection
> > adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic
> 
> For i = 1 to 3
> 
>    > adors.AddNew
>    > adors!amount = Me.Text0
>    > adors!memo = Me.Label1.Caption
>    > adors.Update
> next i
> > adors.Close
> > mycon.Close
> > End Sub
> >
> > thanks
> 
> 
> Even without knowing why you want to do this and what value a table with A, 
> B, C and some numbers has, this is a bad way to do it. 
> 
> 
> .
> 
0
Utf
3/9/2010 3:18:01 PM
I have three persons depositing money every day and I have three textboxes on 
a form representing where I input each person's deposit amount with each 
textbox's label captioned with the person's name to indicate where to put 
each person's amount.  The code will add records to the table of the amount 
and person's name based on the textbox and label.  

how does the for next loop pick up textbox2 and then textbox3?

"Mike Painter" wrote:

> gator wrote:
> > I have three unbound textboxes on a form.  Each textbox is used to
> > input the amount of money A,B,C deposited.  textbox1 is labeled A,
> > textbox2 labeled B,....
> >
> > When the user clicks a button, I need to update the table to add a
> > row for A then add a new row for B and then C.
> >
> > Below is the code I have so far for the update, but I can't figure
> > out how to repeat the update for B & C.
> >
> > Lastly, the table has a field named 'memo'.  I would like to add the
> > textbox caption to that field on each record.   So, for the first
> > record, the deposit field would be whatever number is in the Textbox
> > labled A and the memo would be A.
> >
> > here is the code i have for the update.....
> >
> > Private Sub Command10_Click()
> > Dim mycon As New ADODB.Connection
> > Dim adors As New ADODB.Recordset
>   Dim i as integer
> > Set mycon = CurrentProject.Connection
> > adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic
> 
> For i = 1 to 3
> 
>    > adors.AddNew
>    > adors!amount = Me.Text0
>    > adors!memo = Me.Label1.Caption
>    > adors.Update
> next i
> > adors.Close
> > mycon.Close
> > End Sub
> >
> > thanks
> 
> 
> Even without knowing why you want to do this and what value a table with A, 
> B, C and some numbers has, this is a bad way to do it. 
> 
> 
> .
> 
0
Utf
3/9/2010 3:28:06 PM
On Tue, 9 Mar 2010 07:28:06 -0800, gator <gator@discussions.microsoft.com>
wrote:

>I have three persons depositing money every day

And none of them ever go on vacation, and none of them will ever take another
job, and you'll never under any circumstances have a fourth person?

-- 

             John W. Vinson [MVP]
0
John
3/9/2010 5:17:25 PM
I must be missing something obvious.  

All I'm trying to do is create a direct user friendly interface to enter 
some data where code will update the table.   If someone goes on vacation, 
then the textbox would be zero.  If someone quits, the textbox is ignored or 
deleted.  If someone joins, a textbox will be added.  Is it overkill or what?

"John W. Vinson" wrote:

> On Tue, 9 Mar 2010 07:28:06 -0800, gator <gator@discussions.microsoft.com>
> wrote:
> 
> >I have three persons depositing money every day
> 
> And none of them ever go on vacation, and none of them will ever take another
> job, and you'll never under any circumstances have a fourth person?
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/9/2010 8:45:01 PM
On Tue, 9 Mar 2010 12:45:01 -0800, gator <gator@discussions.microsoft.com>
wrote:

>I must be missing something obvious.  
>
>All I'm trying to do is create a direct user friendly interface to enter 
>some data where code will update the table.   If someone goes on vacation, 
>then the textbox would be zero.  If someone quits, the textbox is ignored or 
>deleted.  If someone joins, a textbox will be added.  Is it overkill or what?

Yes. It's overkill. A very simple Continuous Form with a combo box for the
person entering the payment and a textbox to enter it will let you put in the
data with *NO CODE AT ALL*, no extra effort for the user, and no ongoing form
and program maintenance effort on your part.

That said... change your code to

Private Sub Command10_Click()
Dim mycon As New ADODB.Connection
Dim adors As New ADODB.Recordset
Set mycon = CurrentProject.Connection
adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic
adors.AddNew
adors!amount = Me.Text0
adors!memo = Me.Label1.Caption
adors.Update
adors.AddNew
adors!amount = Me.Text1
adors!memo = Me.Label2.Caption
adors.Update
adors.AddNew
adors!amount = Me.Text2
adors!memo = Me.Label3.Caption
adors.Update
adors.Close
mycon.Close
End Sub

Since there are only three, I don't see any benefit in looping; just
bruteforce three blocks of code for your there blocks of controls.

You will of course have to change the control names from Text0/1/2 and
Label1/2/3 to the actual names of the controls on the form (which you can see
but I cannot).
-- 

             John W. Vinson [MVP]
0
John
3/9/2010 9:02:58 PM
Reply:

Similar Artilces:

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

Capture number of records updated?
We have several processes that we run on a daily or monthly basis that I am trying to automate. Each one has a series of delete, append, and/or update queries, sometimes as many as 15 per process. I am trying to find out if there is a way to suppress the query messages but still capture how many records each query impacted. I know how to run the queries from code, and how to turn off the warnings (DoCmd.SetWarnings False), but then I can't see how many records were deleted/appended/updated. I need to see these totals to spot check them and make sure there is not a problem. I ...

Too many problems while updating information online
Several of my accounts are unable to update information online using MS Money 2007 Deluxe - ING Direct - Bestbuy Card (HSBC) - MBNA - Bank of America This is frustrating!! Anyone else with the same problems? Just keep trying, eventually it will work. If you're in a bind, download the QIF or OFX files from the institutions web sites -- B of A works better that way anyway :) Paul McKenna <Paul McKenna@discussions.microsoft.com> wrote: >Several of my accounts are unable to update information online using MS Money >2007 Deluxe > >- ING Direct >- Bestbuy Card (HSBC...

Windows update site problems
So whats happening with the windows update site ?I keep getting a message telling me there is a problem with the web page (am trying to update new installation of XP Home),is it me or the site,and when will it be sorted out? -- Be happy,Strat4axe "stat4axe" <strat4axe@hotmail.com(donotspam)> wrote in message news:36618CFD-EF82-4F82-A7C3-90CF2DDCCEA5@microsoft.com... > So whats happening with the windows update site ?I keep getting a message > telling me there is a problem with the web page (am trying to update new > installation of XP Home),is it me o...

Updating Boolean Field with onChange
I am having difficulty with setting a value of a new boolean field using the OnChange scripting against a picklist. If anyone has some sample code / formatting information that I can use it would be greatly appreciated. Also, I also need to know if it is possible to stop the default value for the Revenue (isrevenuesystemcalculated) field on the opportunity form being set to "Yes" when the "Convert Lead" function is used. We can set the default value to "No" for user created Opportunities but when Convert Lead is used it seems to ignore the default value. Again, a...

RE: Use that security update
--dedtgpho Content-Type: multipart/related; boundary="yzkfzsfqtv"; type="multipart/alternative" --yzkfzsfqtv Content-Type: multipart/alternative; boundary="qeviqcxczwaquy" --qeviqcxczwaquy Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to hel...

Multiple Domain e-mail addresses
We are running Exchange Server 2003 Enterprise addition and I am having a bit of a "brain-freeze" at the moment... Where in the Exchange Admin do I configure the server to accept e-mail from more than one domain. For example, we currently accept e-mail from mycurrentdomain.ca and would also like to accept email from mycurrentdomain.com. Please note that I have updated all of our DNS MX records as required, just need to make the changes on the Exchange server. Thanks in advance... Recipient polices-email address policy. "Raymond Spitzingle" <ray(nospam)@stuff...

Outlook 2003 Folder & Favorite-folder synch/update problems
I've been using Outlook since 1998 and I make my living at software support, so I'm usually hard to stump, but I'm sucking wind on this one. The problems below all started on the remote Outlook copy, so I have to imagine they're connected to the remote setup and OST files. But I've been through the settings and tried to re-synch, cleared the folder store and recreated it, etc., and nothing seems to correct the problems. Can anyone suggest a systematic way to diagnose and fix what's happening? Here's the story: I'm running Outlook 2003 (all current patches: ...

Multiple accounts #3
I have 2 Hotmail and 1 MSN accounts set up in Outlook 2002. It shows that it sends and receives all 3 accounts successfully, but when I go to the Inbox of the Hotmail accounts, there aren't any messages. Thanks, Rick It says it send and received fine, but problems occur when you set up more than one HTTP account, so it is recommended that you only use one per profile ...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Outlook/Access 2003
I've been doing a lot of research and purchased (but haven't completely read) Sue Mosher's book. However, before I spend much more time I'd like to find out if my idea is even feasible. The basic idea is to create an appointment system for multiple therapists that can be used by a receptionist and other staff. Using Outlook and Exchange is very nice but the appointment data needs to end up in an Access table immediately. We can purchase SBS 2003 with Exchange 2003 or we can buy a third party tool for calendar sharing whichever would be the easiest to use. I currently...

Suggest a solution
Hi all. I have a table named Articles that has the fields: ArticleID, ArticleName, Price and so on.... What I want to do is to make a new table that would hold some related Property records for each article. For example... Color, Weight, Length,.. This is not a problem, but what I want is to be able to change fields names for the second table (like PropertyID, PropertyName) and be able to search among those values from query. This is the most important part: ---------------------------------------------------------------- I would like to have them as a field in query from ...

Pivot Table with Quarter Subtotals across the top
Hello, I'm constantly creating pivot tables that show sales information by date across the top from left to right. I would love to insert a subtotal at the end of each calender quarter to subtotal the last three months. As it stands right now I just drop in the "Month" field from the data into the pivot table and the months flow through the pivot table. Is it possible to insert a quarter subtotal, say "1Q07" that will sum Jan-07, Feb-07 & Mar-07? Thanks in advance, Dza In 2007- Select any date heading, on the options ribbon click Group Field in the Group g...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

auto filter data within pivot table
Hello, Does anyone know how can we filter data within a pivot table? or sort the data from ascending to decending order withing a pivot table??? Can we do that? It doesnt seems to work?! Thanks. Elaine. You can add fields to the page area, and use them to filter the pivot table's data. To sort a field, select a cell in a column, and click the A-Z button on the Excel toolbar. elaine wrote: > Does anyone know how can we filter data within a pivot table? or sort > the data from ascending to decending order withing a pivot table??? > Can we do that? > > It doesnt se...

getting the error when updating file xls file.
Hi. We are using exchange 2003 server and office XP but know we start to Upgrade our office to office 2003 Know we are face problem. When our user try to make changes to file in public folder there can not make the changes it say “file is read only” but that user can make the changes or edit that same file from the P.C were office XP is installed. Do I have to install some patch if yes can you please send that patch because in trying from last two day and I can not find it. Note: If possible can you reply on parag.lopes@madinatjumeirah.com Just a guess... Are you s...

Comments entered on Task Updates to Manager: Where does it get sto
There is a checkbox option on the My Tasks page for team members to select and send comments about task updates to their managers. Anyone know where these task comments can be reviewed other than when the PM opens it up to approve? It does not seem to be retained anywhere like the Notes get retained. Hi Kahuna, PM can see the comments with Approvals -> Task Updates -> Go To -> Applied Requests and Errors, clicking on the task name. The approval history is listed there including comments. A team member can see them with clicking in the task name in 'My Tasks&#...

Out of Office Assistant replies multiple times
The situation: The user of a fully patched Outlook 2003 SP1 sets an Out of Office reply using the Out Of Office Assistant.. The result: The first time someone sends a message while the OOA reply is turned on, they get the full text of the OOA reply as it is configured. The second and subsequent emails from that same sender gets a truncated version of the OOA message. In my testing, my second message (which isn't supposed to be replied to), got me 2 truncated replies. Examples from my testing: 1st reply: "I am out of the office and will return on Monday, February 28, 2005. At th...

look way to map form at CRM frontend to tables at CRM database
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! ...

Can i move multiple lines between workbooks
I have a .csv format file listing speed cameras, and the different speeds are identified by two characters at the end of each line - 30, 40, 50 etc. I would like to create separate files for each speed, but Excel won't allow me to cut/copy several lines to created a new file/workbook. Is there any way round this? Martin Ellis Sun, 16 Sep 2007 03:28:00 -0700 from Martin Ellis <MartinEllis@discussions.microsoft.com>: > I have a .csv format file listing speed cameras, and the different speeds are > identified by two characters at the end of each line - 30, 40, 50 etc...

Editing more than 1 table at a time with SQL.
Say I have two tables in a DB called TABLE1, and TABLE2. TABLE1 [IDNUMBER] [FIRSTNAME] 1 Ryan 2 Jennifer 3 Monica TABLE2 [IDNUMBER] [LASTNAME] 1 Johnson 2 Snipes 3 Smith My question is would I be able to use SQL in code to Edit, Delete or Insert data to both tables in one string rather than one table at a time? For instance what if I want to change both tables with a [IDNUMBER] = 2 to a [IDNUMBER] = 4, OR change the ...

Table headers in a list box (Custom Report)
I have a big table with many fields (more than fifty). They are not in one table but related. Now user's come with a requirement of a separate type of report everyday which has different fields. because of which i have to design a report for them with the required query. I was therefore wondering can i make a form which has two list boxes the one on left side will have all the fields of the table (only headers needed, not data). Users can select multiple no. of fields from that list box which will appear in another list box. Users should be able to then select the order of fi...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

How do I use the "Correlation" dialog box with multiple ranges?
I'm using data in an Excel 2003 in a workbook, trying to see if there is any correlation between specific offices in which people work and the length of time they have been employed, their age the amount of sickleave taken and/or the amount of sickleave balance they have. I'm attempting to use Data Analysis and the Correlation dialog box. Although the dialog description tells me that I can use multiple comparison ranges, I don't see how to do it. Thanks for any help. Paulf6 - Arrange your data in list (database) format, i.e., names in the top row (Time Employed, Age, ...)...