Update table with calculated control 1

[Overmyhead2]

I want to update table.field1 with the data that is in my form's text box1 as each records updates. I believe that I need to attach some code to an event but not sure of the syntax.
Victoria

 

[RickSpr]

If your form's Record Source is that table, or if it's a query that contains field1, you just need to set the text box's Control Source to field1. Updates to the text box will then be automatically stored in the table.

If field1 is not in the form's Record Source, you probably want to use the form's AfterUpdate (and possibly AfterInsert, if you're allowing records to be padded). In this case, you'll have to be able to identify the record you want to update, either by having its primary key or by some unique combination of fields it contains. You can then use code like the following:
Dim db As Database
Set db = CurrentDb()
db.Execute "UPDATE Table SET field1 = '" & Me!TextBox1 & "' WHERE " & (criteria string that selects the record)
Rick Sprague

 

[JerryDennison]

I strongly recommend you do not store a calculated expression in your table. This goes against both good db design as well as the Forms of Data Normalization. It is far better to calculate the field on demand. If you'll add the calculation to the underlying query of your form's recordsource then it will appear as a field in your field list and can be used to display the results automatically and will always be current based on the most recent data in the fields the calculation is based on.

 

[Overmyhead2]

This is what I have and I am getting an error--Varible not defined.

Private Sub subASPData_Enter()
Dim db As Database
Set db = CurrentDb()
db.Execute "UPDATE ASPData SET ASPData.ServeDate ='" & Me!DateString& "' WHERE "
ASPNames.ASNumber = [Forms]![ASPData]![ASNumber]
End Sub

If I leave the where statement out I get the date in all records.

I have 2 tables ASPNames, ASPData. One to many linked with the ASNumber field. My Main form (ASPData) is based on table ASPNames with a subform(subASPData) based on a query showing records in table ASPData that match Month and Year criteria. My heading has Month and Year combo boxes. The subform has a Day field. An unbound text box(DateString) on the main form has as its record source [Month]+[DAY]+[Year].
I need this datestring to go to the ASPData table in the field ServeDate.I need a good reference book for syntax. Any sugestions?
Victoria

 

[RickSpr]

The best reference for syntax is the Help file. I think you may want something with a little more scope than just a syntax reference.

When you write the db.Execute statement, approach it this way. Execute is going to take a string argument containing a SQL statement and pass it to the database engine. You can code the string argument as an expression that evaluates to a string, but once evaluated, the completed string must be a complete and valid SQL statement.

Part of what you wrote (the part ASPNames.ASNumber = [Forms]![ASPData]![ASNumber]) was written as part of the string expression. The VBA code would evaluate this as True or False, and then stick the string "True" or "False" into the SQL string. You didn't want VBA to evaluate this expression, you wanted the database engine to do that, so this comparison should wind up as part of the string.

In general, anything outside the quotes is evaluated and turned into a string by VBA, and that string will then be concatenated with the quoted parts to make a SQL statement that the database engine can understand.

What you need is something like this:
db.Execute "UPDATE ASPData SET ASPData.ServeDate = " _
& "#" & Me!DateString & "#" _
& " WHERE ASNumber = " & [Forms]![ASPData]![ASNumber]

I put the "ASNumber =" part in quotes, because I want the database engine to do the comparison as it reads each row. But I left the "[Forms]![ASPData]![ASNumber]" part outside of the quotes, so that VBA would translate the reference to the form control into a number, turn the number into a string, and append that to the SQL statement.

Note: This assumes that ServeDate is defined as a Date/Time field, and that DateString can be translated into a Date value in your locale (that is, that it contains appropriate delimiters and the month, day, and year are in a standard order). Jet SQL requires "#" delimiters around date constants, so I had to put those into the string. Rick Sprague

 

[Overmyhead2]

Thanks for your thorough explaination. Still alittle lost. I tried the string above. It is stopping on the WHere clause. My criteria string that selects the record is not working.
( Error 3061. Too few parameters. Expected 1. )
I believe my criteria is all wrong.
I think I want WHERE the main form ASNumber = the subform ASNumber. I wish I could just say for current record.

Victoria

 

[jfhewitt]

Try the AddNew method.

 

[RickSpr]

I'm not sure what you mean by "it's stopping on the WHERE clause". Access doesn't highlight clauses, it highlights statements. If the WHERE clause is highlighted, you must have forgotten the underscore at the end of the previous line.

Copy and paste your statement in a message and I'll take a look at it.

No, you don't need "WHERE the main form ASNumber = the subform ASNumber". That would update all the records the subform displays, not just the ones with the current ASNumber.

By the way, let me double check -- you want to update all the subform table records that have the same ASNumber as the current record on the main form, right? Not just the one record that's current on the subform? Rick Sprague

 

[Overmyhead2]

I mean that if I rem out the where clause then all records in the table are updated to my datestring (same date in every record). So my table is updating the field ServeDate. Now I need to get the where clause to work for a single record only.
The user is entering a DAY in the subform and it is concatenated with the Month and Year in the mainform to form a formated datestring -- 01/18/01 in an unbound text box on the mainform. I want table ASPData.ServeDate to = this datestring per record. Each record will have same MONTH and YEAR just different DAYs. It is almost working, I am stuck on the where clause.

Private Sub subASPData_Enter()
Dim db As Database
Set db = CurrentDb()
db.Execute "UPDATE ASPData SET ASPData.ServeDate = " _
& "#" & Me!DateString & "#" _
& " WHERE ASNumber = " & [Forms]![ASPData]![ASNumber]
End Sub

Victoria

 

[Overmyhead2]

Thanks ... It is working . I used:
Private Sub Free_AfterUpdate()

Dim db As Database
Set db = CurrentDb()

ServeDate = Forms![ASPData]!DateString

End sub

I used this on a contrl that the user will always touch.
Victoria

 

[RickSpr]

I'm glad you got it working, though I don't see anything wrong with the WHERE clause.

But my answer was the wrong one, and your solution was the right one. In my first post, I said "If your form's Record Source is that table...you just need to set the text box's Control Source to field1." I was on the right track with that, but I didn't understand your problem well enough, and you didn't understand what I was saying. We both got off on the other solution, and we never should have gone there. Rick Sprague