none
Newbie needs help. RRS feed

  • 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 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

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

    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


    • 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