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. 

Answer 1

Post the code you now have that works.

---

Build a little, test a little

Answer 2

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 Sub

However 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 2012

Answer 3

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.   

Answer 4

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.

Answer 5

Thanks h2fcell for your help. 

Answer 6

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().

Not the best.  That will usually (but not necessarily) be reliable when date-stamping a row with the return value of the Date function, as in most cases any time delay will not span midnight, but not when datetime-stamping.  The DefaultValue property assigns a value when the user navigates to an empty new record in a form, not when they begin to insert data or when the record is committed to the table.  This could be very significant in a multi-user environment where the relative times of insertion of rows might well be important.  A situation could very easily arise where user A navigates to a new record, but for some reason does not insert any data.  In the meantime User B does the same and inserts data.  If user A then inserts data their row will be stamped with the datetime value when they originally navigated to the empty record, so the value inserted into the column will be earlier than that in User B's row even though user B inserted their row prior to User A, resulting in data which inaccurately represents the reality.

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

Answer 7

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.

Answer 8

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