Update a field value in a record

[sgfromny]

Can Someone please this newbie.... what Im trying to accomlish here is with a click of a button on a form getting its data from a query, that the date field on the current record is changed to the current date.

Am I even close? Thanks in advance.. Steve

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT * FROM [Shipto] WHERE [recieptno] = '" & Form_Carrier.RecieptNo

With rst
.Edit
.Fields("projected Install Date&quot = CurrentDate
.Update
.Close
End With

 

[gusbrunston]

[tt]
Hi:

If I understand correctly, you simply want one field, a date field, to be assigned today's date when you push a command button.

Your form is already bound to the appropriate query, right? Then it's not necessary to redefine using the SQL statement.

Just put this in the On Click event of the command button:

Private Sub MyCommandButton_OnClick
Me.NameOfDateField = Date()
End Sub

Then when the record is saved or exited, today's date will be entered through the query to your table.

You must, of course, substitute the appropriate control names for the command button and the date field.

I hope this is helpful.



[tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[vbajock]

If your going to use DAO you can do it in one shot with an update query using the Execute method of the database object:

Dim db As DAO.Database
Dim strSQL As String

strSQL ="UPDATE Shipto
strSql=StrSql + " Set [projected Install Date] = Now()
strSql=StrSql + " WHERE [recieptno] = '" & Form_Carrier.RecieptNo

db.execute strSql


There is a bunch of other way to do it, such as bound controls on the form, as well.

 

[gusbrunston]

[tt]
vbajock's post reminded me I didn't make clear that my answer assumed that your date field was bound to the underlying query. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[sgfromny]

I tried Gus's code, got an error
Used VBAjock's code, got no error, but it didnt put the date in the field..

I did neglect to say that the tables are linked, doe sthis matter?

 

[gusbrunston]

[tt]
Hi:

What error did you get? What was the number, and what did it say?

Copy and paste the code from your command button. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[sgfromny]

Gus,
After trying your code on a blank form, it worked. But My field names have spaces in them, I get an error in the code when I enter them. Also, Although it seems to put the date in a field, it doesnt update the record. Forgive me.. I am a total newbie to VBA and Access.. A little database I Created using wizards in access 2000 has taken on a life on its own has pushed me into area of programming totally unfamiliar with me. Im sure what you said is correct, Im also sure I dont know what im doing.