473,322 Members | 1,496 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Storing data collected from form to a field

In my form I have a text box. This textbox is not connected to the recordset that the rest of the form stores information in. I am using only to calculate data. I want to store that data in a field from the record set.
Is there a funtion that will do this?
Here is the textbox's controls source, simplified: =Nz([Text23],0)+Nz([Text27],0)
The output of this I want put into the recordset field LABOR.

Any help Thanks
James
Dec 28 '06 #1
13 1990
willakawill
1,646 1GB
In my form I have a text box. This textbox is not connected to the recordset that the rest of the form stores information in. I am using only to calculate data. I want to store that data in a field from the record set.
Is there a funtion that will do this?
Here is the textbox's controls source, simplified: =Nz([Text23],0)+Nz([Text27],0)
The output of this I want put into the recordset field LABOR.

Any help Thanks
James
Hi James,
We are shooting in the dark if we try to update the form recordset so we first need to look at the data source for the bound controls to see if it contains the field we want to update. Would you please look at the data tab for your form properties in design mode and tell me what the 'Record Source' property is?
Dec 28 '06 #2
In this test database it would be SELECT DADDY.* FROM DADDY; that is the built in query for the form. In the form I want to be able to store the calculated data in the field I define from DADDY.
Dec 28 '06 #3
willakawill
1,646 1GB
In this test database it would be SELECT DADDY.* FROM DADDY; that is the built in query for the form. In the form I want to be able to store the calculated data in the field I define from DADDY.
So I am assuming that the field LABOR is in the table DADDY and the name of your text box is Text0

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdate_Click()
  2.     Dim rs As Recordset
  3.     Dim marker As String
  4.  
  5.     marker = Me.Bookmark
  6.     Set rs = Me.RecordsetClone
  7.     Me.Text0.SetFocus
  8.     rs.Bookmark = marker
  9.     rs.Edit
  10.     rs("LABOR") = Me.Text0.Text
  11.     rs.Update
  12.     rs.Close
  13.     Set rs = Nothing
  14.     Me.Requery
  15.     Me.Bookmark = marker
  16. End Sub
If you place a button on your form and name it cmdUpdate you can place this code in the click event for that button. Go to the click event on the property sheet for the button and paste this code in the code module.

When you are looking at the visual basic code page you might have to go to Tools | References and put a check against microsoft DAO Object Library

Good luck
Dec 28 '06 #4
Does this have to be associated with a button? I could use any event I wanted right?
Dec 28 '06 #5
Oh yea. You are teh super access guru.
Thank you very much.
Dec 28 '06 #6
willakawill
1,646 1GB
Does this have to be associated with a button? I could use any event I wanted right?
You can't use any event you want. It needs to be an event within which we can still move the focus to the textbox.
Dec 28 '06 #7
I found a much better way to do it. Using conditional calculations in a form is not the way to go. I made a query of my test table and added the calculations there. This works much better because the table and form is updated everytime.
The only drawback that I can see so far is that you can't manually change that data in the form.

Here was the problem for those of you that are having this problem.

I have to define three products. To define these products I use four descriptors; quantity, size, type, and misc. I want to generate monthly reports on the quantity and type sold. In the form, I initially made some invisible IIf textboxes that would gather the quantity depending on type. I then used three more textboxes that contain a sum calculation using the Nz function. I was then stuck, because there is no nice way to get this calculated data into the table. Willakawill gave me a nice piece of code that would update the record set but it was not going to work in this "data entry" application.
So, instead of using the form to calculate data I went to a query. I followed the same process as creating a form; add the table, add the calculations, add the sum calculations. The actual syntax is almost identical except that in a query name of the field will be expr1, 2, 3, and so one. Make sure you use the Nz function. I thought I had failed because I had left them out. Then create a form using the query and presto. It works like a charm.

Thanks Willakawill for your help.

,
James
Dec 29 '06 #8
NeoPa
32,556 Expert Mod 16PB
James,
I'm pleased you got to where you wanted in the end.
It just goes to show how careful you need to be when posting a question though. Will answered your question rather than what you wanted per se.
What you ended up with is a better way of doing what you need than the way you were considering initially, definitely.
-Adrian.
Dec 30 '06 #9
A snag? Everything was working great in my test database using only three items defined by four fields. I tried it on the real thing when I got home and found that the query is not big enough when I have 8 items to define and calculate totals for. If you read above you will see the calculations I'm doing are conditional in that I only want totals from the quantity fields if they match a certain string. Three items multiplied by four defining fields is twelve and the corresponding calculations to get the totals is 36, that is four each to narrow the quantiity by type and then four more to total them. These 36 expressions plus the rest of the stuff in my table is way over the query limit I guess. I thought the limit off a query or table was 255 fields. What is it that causes the query to grow and shrink.

The main table I'm using has about 170 fields for various defining characteristics like date, customer, salesperson, and so on. If I put that data into a query and then start to enter my expressions I will stop me after 16 or so fields and there is no more blank spots.

170+32+36=238 238<255 argh?
Jan 2 '07 #10
Am I a moderators nightmare or what. hehe.
Duh. insert menu>insert column. Up to 255 I suppose.
Jan 2 '07 #11
NeoPa
32,556 Expert Mod 16PB
Am I a moderators nightmare or what. hehe.
Duh. insert menu>insert column. Up to 255 I suppose.
For lack of understanding what you're on about - does that mean you're sorted or not?
BTW Most members on here are moderator nightmares so don't feel too bad (Don't tell 'em I said so :hush: )
Jan 2 '07 #12
For lack of understanding what you're on about - does that mean you're sorted or not?
BTW Most members on here are moderator nightmares so don't feel too bad (Don't tell 'em I said so :hush: )
Yep. I'm all good on my form now. Next step, Reports. I'm sure to see you again. LOL.
Jan 2 '07 #13
NeoPa
32,556 Expert Mod 16PB
Yep. I'm all good on my form now. Next step, Reports. I'm sure to see you again. LOL.
I'll echo that :)
Good to see you're fixed for now and we'll see you when you come back.
Jan 2 '07 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: dave | last post by:
Hello there, I am at my wit's end ! I have used the following script succesfully to upload an image to my web space. But what I really want to be able to do is to update an existing record in a...
4
by: Justin Lebar | last post by:
Sorry about the huge post, but I think this is the amount of information necessary for someone to help me with a good answer. I'm writing a statistical analysis program in ASP.net and MSSQL7 that...
3
by: MS | last post by:
What's the best way to "store" and display a value in a text box that changes from day to day. An example of this would be where the name of the user is manually typed in after using the datbase,...
5
by: hfk0 | last post by:
Hi, I'm new to ASP.net, SQL Server and visual studio.net, and I'm having problem inserting and storing data from a web form to a SQL database. I created a simple ASP.NET web form, a simple SQL...
8
by: TORQUE | last post by:
Hi, I am having some trouble with recording a field on a form into my Table after formatting it to calculate several fields on the form. If i just put the amount in the field and have it linked...
6
by: (PeteCresswell) | last post by:
User wants to go this route instead of storing pointers in the DB and the documents outside. Only time I tried it was with only MS Word docs - and that was a loooong time ago - and it seemed to...
17
by: lokidog | last post by:
I am trying to automatically transfer data from one textbox to another between subforms within a 'main' form. I put this code into the Gotfocus eventprocedure: Private Sub Date_GotFocus() If...
19
Atli
by: Atli | last post by:
Introduction At some point, all web developers will need to collect data from their users. In a dynamic web page, everything revolves around the users input, so knowing how to ask for and collect...
0
by: canistel | last post by:
Hi, I have a little python webservice that I created, and in one of the methods I need to store some binary data that was "posted"... I want to do something like this, but it doesn't work. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.