By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Storing data collected from form to a field

P: 76
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
Share this Question
Share on Google+
13 Replies


100+
P: 1,646
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

P: 76
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

100+
P: 1,646
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

P: 76
Does this have to be associated with a button? I could use any event I wanted right?
Dec 28 '06 #5

P: 76
Oh yea. You are teh super access guru.
Thank you very much.
Dec 28 '06 #6

100+
P: 1,646
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

P: 76
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
Expert Mod 15k+
P: 31,616
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

P: 76
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

P: 76
Am I a moderators nightmare or what. hehe.
Duh. insert menu>insert column. Up to 255 I suppose.
Jan 2 '07 #11

NeoPa
Expert Mod 15k+
P: 31,616
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

P: 76
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
Expert Mod 15k+
P: 31,616
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

Post your reply

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