Answered by:
Newbie needs help.

Question
-
Just a very basic question that I need help with. Im only learning now to use access so my knowledge is very basic. Im using Access 2013 and I want to attach one line of vba code to a button. I already have that button set to save a record to a table and all I want it to do is to also set a value into a text box. I have tried to use the code builder but that only seemed to replace the save event. I also tried attaching the code through the run code function but that didnt work either. Any help would be great appreciated. Thanks.Friday, February 19, 2016 9:22 PM
Answers
-
Hi Colmanite,
The best way to keep track of when a record is added to a table is to create a Date/Time field in the table and give it the Default Value of Now(). If you only want to save the date without the time set the Default Value to Date().
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
- Marked as answer by Colmanite Saturday, February 20, 2016 5:30 PM
Friday, February 19, 2016 11:56 PM
All replies
-
Post the code you now have that works.
Build a little, test a little
Friday, February 19, 2016 9:41 PM -
Hey Colmanite,
Typically when you use an event you can do many things one right after the other as long as you keep the code between the Private Sub...Click() and the End Sub. As an example the below code on a Command Button can be used to set the properties on the Form so that only new records can be added. First it is calling a Function used to allow the user to review changes before storing them in the database. Then the next four set the properties, followed by a Requery of the Form to make sure you see the latest data in the Table, finally setting the focus on a specific control in the Form. Simple or low overhead items can be compact like this
Private Sub cmdAddNewRecord_Click()
If AP_Save() Then
Me.DataEntry = True
Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True
Me.Form.Requery
Me.txtListNumber.SetFocus
End If
End SubHowever sometimes there can be complex or high overhead items that require giving Access a little time to finish up 1 thing before starting another like
Function ap_Print() Dim frm As Form Set frm = Screen.ActiveForm Select Case frm.Name Case "Accounting" DoCmd.PrintOut acSelection DoEvents Case "Company" DoCmd.PrintOut acSelection DoEvents Case "MainForm" DoCmd.PrintOut acSelection DoEvents Case "CompanyUtility Form" DoCmd.PrintOut acSelection DoEvents Case "MSDSARListUpdate" DoCmd.PrintOut acSelection DoEvents Case Else MsgBox "Form " & frm.Name & " This Form is not for printing.", vbExclamation, "Invalid Form Selection" Exit Function DoEvents End Select DoEvents End Function
Note periodically there is a DoEvents call to give Access the time it needs to complete some actions before beginning others.
Whether it is a sub or function you need to keep your code between the beginning and end points.
Can you share with us more information on your error so we can try to help you with it?
Thank you!!!
Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012Friday, February 19, 2016 10:34 PM -
Thanks for your replies KCDW and Karl Dewey. So I have this database that stores customer orders for a cafe and what I want to do is when the customer clicks on the confirm order button I want today's date to insert into the textbox above the button so its saved in the table with the data (I'm doing this to make it as seamless as possible and make the have the customer do as little as possible and not having to do any typing). I have a screen shot of the form and on the right I have the code that I want to run, its the top code txtOrderDate.Value = Format(Date, "dd.mm.yyyy"). Sorry its such a large image. If there's any other way of setting the text box to today's date that would be very helpful. I have tried to use a load event but that just threw an error code 2448. This is a project for college so I dont really want the code as I want to learn how to do it so if you have any things that I could try mess around with myself or other ideas of how I can set the date I would appreciate that.Friday, February 19, 2016 10:54 PM
-
Hi Colmanite,
The best way to keep track of when a record is added to a table is to create a Date/Time field in the table and give it the Default Value of Now(). If you only want to save the date without the time set the Default Value to Date().
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
- Marked as answer by Colmanite Saturday, February 20, 2016 5:30 PM
Friday, February 19, 2016 11:56 PM -
Thanks h2fcell for your help.
Saturday, February 20, 2016 5:31 PM -
The best way to keep track of when a record is added to a table is to create a Date/Time field in the table and give it the Default Value of Now().
To datetime-stamp a row the return value of the Now() function should be *assigned* to the column in code. To datetime-stamp when a user begins to insert a row use the form's BeforeInsert event procedure, to do so when the row is first committed to the table use the AfterInsert event procedure.
You'll find this illustrated in ChangedRecordDemo.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
In this little demo file, whose real purpose is to illustrate how to detect actual changes to data rather than merely updates (which don't necessarily imply any change in values), if you select the option to open a 'Form to Time_Stamp Latest Change to Data' for instance, and navigate to a new record, you'll see the current datetime value inserted as the default value. Once you begin to insert data it will be updated to the current time. The image below of a variation on the form in an earlier version of the same file illustrates this by showing the different assigned and defaulted values in separate controls:
Ken Sheridan, Stafford, England
- Edited by Ken Sheridan Sunday, February 21, 2016 5:39 PM Clarified
Sunday, February 21, 2016 5:37 PM -
Hi Ken,
I agree in a muti-user environment any field with Now() default could be an issue if more than one user is entering data on a form bound to a table. Even an AutoNumber data type is an issue during that scenario.
For that reason I typically have a Save button on my unbound data entry forms with code (see below) to add the data to the table using AddNew while still keeping Now() as a Default Value for my CreatedOn field in my table. The chances of two or more users hitting the Save button at the exact same moment are slim and even if they do one will always get the record added before the other.
I hope “Newbie” Colmanite isn’t overwhelmed with this much information.
Private Sub cmdSaveQuote_Click() On Error GoTo cmdSaveQuote_Click_Err Dim SGNT_DB As DAO.Database Dim rstSGL As DAO.Recordset If Len(Nz(Me.cboAssgndTo, "return large number 22")) = 22 Then MsgBox "Needs to be Assigned." Else Set SGNT_DB = CurrentDb Set rstSGL = SGNT_DB.OpenRecordset("tblBookings") Me.tboBookingNum = Nz(DMax("BookingID", "tblBookings"), 0) + 1 rstSGL.AddNew rstSGL("created_by").Value = Me.tboCreatedBy rstSGL("StatusDef").Value = Me.tboStatusDef rstSGL("customer_id").Value = Me.tboCustID rstSGL("address1").Value = Me.cboAddress rstSGL("city").Value = Me.tbocity rstSGL("state").Value = Me.tboState rstSGL("zip").Value = Me.tboZip rstSGL("bussiness_phone").Value = Me.tboPhone rstSGL("fax_number").Value = Me.tboFax rstSGL("email").Value = Me.tboEmail rstSGL("AssgndTo").Value = Me.cboAssgndTo rstSGL("Notes").Value = Me.tboNotes rstSGL.Update End If cmdSaveQuote_Click_Exit: Exit Sub cmdSaveQuote_Click_Err: MsgBox Error$ Resume cmdSaveQuote_Click_Exit End Sub
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
- Edited by DriveEV Monday, February 22, 2016 12:12 AM
Sunday, February 21, 2016 11:20 PM -
I hope “Newbie” Colmanite isn’t overwhelmed with this much information.
I'm sure the OP is sufficiently discerning to distinguish the relevant from the irrelevant. But in relation to the original question, in the context of a simple bound form, the rule of thumb can be summarized as:
1. To insert the current *date* automatically, if it can be guaranteed that this won't be done close to midnight (which in the context of a café application might not be the case, and if there is the slightest doubt on this point this should not be done), the DefaultValue property of the column can legitimately be set to Date().
2. To insert the current *date/time* automatically, or, if inserting the current *date* and it cannot be guaranteed that this won't be done close to midnight, the return value of the Now function (for the date/time) or Date function (for the date only) should be assigned. To assign the date and save the current record via a command button, the code can simply be:
Me.txtOrderDate = VBA.Date
Me.Dirty = False
Ken Sheridan, Stafford, England
Monday, February 22, 2016 12:19 AM