Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms (Access 2002)

    I have a data entry form. I have a button that creates a new record. Is there a way to have some fields populate with the same data as the last record? For example, a machine "WR54" entered in a form record would be the default in a new record. I frequently need to enter info on the same machine several times.

    Thanks,
    Craig.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    When entering data in an access form, if you press CTRL-' (APOSTROPHE) you get the same value as entered in the same field in the previous record.
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms (Access 2002)

    Francois' suggestion works fine if you want to duplicate one or two fields.

    If you want to duplicate a lot of fields, for example all fields except one, you can use code.

    There are several ways to do this.

    One way is to set the default value of a field to the current value in the AfterUpdate event of the form. Be aware that you have to surround the value in quotes if it is a string field, e.g.
    [MachineName].DefaultValue = Chr$(34) & [MachineName] & Chr$(34)

    Another way is to use the Current event. If the user is on a new record (NewRecord = True), make a clone of the recordset of the form and move to the last record. Set all desired controls on the form to the corresponding value in the recordset clone.

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    Hi Hans,

    Would you use DAO to do what you mentioned in both cases ?

    In the second case, why do you need to create a clone of the recordset, can't you refer to the last record directly and use the field values to populate the new record ?

    Is there an easy way to set *all* the controls to their corresponding values without explicitly naming each of them ?

    I would be grateful if you could post some sample code for both of the cases you mentioned,

    TIA

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms (Access 2002)

    Attached is a database (zipped) with an example based on the Form Samples database that Microsoft made available for Access 97. You'll have to convert it to XP. There is one table, one form (without code) and one module. I believe you can replace RecordsetClone with Recordset.Clone in XP.

    The crucial part in this example is the text box AutoFillNewRecordFields on the form. If its default value contains field names, those fields will be duplicated. If it's empty, ALL fields will be duplicated.

    I hope it works in XP.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms (Access 2002)

    A clone of a recordset in ADO is NOT the same thing as a recordsetclone in DAO. Cloned ADO recordsets are independent of one another. A DAO recordsetclone is a copy of the form's current recordset. To answer the earlier question, you can't use the current recordset because you need to move back to the previous record. If you do that with the current recordset, you'll no longer be on the record you want to copy *to*. That's why you have to use a different recordset to lookup the previous values.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    Many thanks Hans.

    I didn't notice that the original post was for XP - I am using 2k, so it should be fine ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms (Access 2002)

    It should be - I don't have either 2000 or XP - but I suppose you'll have to convert it to/save it as an Access 2000 database before you can work with it.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    Hans:
    Thanks to you and others who have responded to my post.

    In using the database attachment you sent, I can enter "Machine" as the default property of the invisible text box and the new form repeats the data in the new record -- great. I've not been able to add a second field to the text box default value so both fields will update with previous record data.

    What context must I use?

    Thanks,
    Craig.

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    Firstly - thanks to Hans for that sample app - it illustrates the code perfectly. One comment - I presume the purpose of using a function (rather than a sub in a form module) triggered by the <font color=448800>On Current</font color=448800> event of the form is so that the same code can be used from any other forms. Is there any point in setting the <font color=448800>Has Module</font color=448800> property to "No" if there is no code associated with a form or is that unnecessary ? I just wondered if it was good practice to do that or whether it has no effect on the size of the .mdb.

    To answer Craig's question - you can either set the <font color=448800>Default Value</font color=448800> property of the "AutoFillNewRecordFields" Text Box to e.g. <font color=red>"ContactName;Address"</font color=red> or you can put something like <font color=red>=trim("ContactName;Address")</font color=red> into the <font color=448800>Control Source</font color=448800> property. I don't know which method would be preferred or if one is better than the other, but they both seem to work fine.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms (Access 2002)

    For anyone who wants to follow the thread on which version of Access you're using, Support4John's post has been moved here.
    Charlotte

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    Hi Adrian

    Before I saw your solution the following worked for me:

    Like you, I don't know which method is preferred/more efficient/provides flexabilty, one of attributes if Access, many ways to accomplish the same thing!

    Maybe someone can comment.

    John

    Private Sub Form_Open(Cancel As Integer)

    ' leave AutoFillNewRecordFields blank to copy all fields or list just the fields to
    ' copy as follows:

    AutoFillNewRecordFields = CustomerID.Name & ";" & _
    CompanyName.Name & ";" & _
    ContactName.Name & ";" & _
    Address.Name & ";" & _
    City.Name & ";" & _
    PostalCode.Name & ";" & _
    Phone.Name & ";" & _
    ContactTitle.Name & ";" & _
    Region.Name & ";" & _
    Country.Name & ";" & _
    Fax.Name
    End Sub

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Access 2002)

    Hi Hans

    Great piece of code.

    What code changes would be required in module to copy from current record in form focus instead of last record?

    dosen't do it! (copies ist record)

    'rst.MoveLast
    rst.MoveNext
    rst.MovePrevious

    Thanks, John

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms (Access 2002)

    Yes, the reason for using a function in a standard module is that you can use it for several forms. If you use it for just one form, there is no advantage in putting the code in a standard module. In that case, you might as well put the code in the module behind the form (it can be simplified a bit in that case, because you can refer to the form as Me instead of passing the form in an argument).

    If a form has no code, it is advisable to set the HasModule property to False. It will reduce the size of the database when you compact it, and it may improve performance a bit.

    I don't think it matters whether you use the DefaultValue or ControlSource property to set the list of fields to be AutoFilled.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms (Access 2002)

    Hello John,

    The module was not written by me - I adapted it from a sample database provided by Microsoft, so credit goes to them.

    The code in the previously posted example fires when the user has already moved to a new record. Access doesn't know what record was "current" before that. So if you want to copy values from the previously current record, you (the programmer) will have to keep track of that.

    I have attached a new example. It assumes that you have a single field that acts as unique key. The value of this key is kept in a public variable SaveKey (it is not typed - i.e. a variant - because the key field might be text, numeric or date).

    As the user moves from record to record, the key value is saved in SaveKey. If the user moves to a new record, this value is used to look up the record that was current before that. SaveKey is also updated in the AfterUpdate event of the key field control on the form.

    Please note that the function AutoFillNewRecord now has three arguments, and that a function UpdateSaveKey has been added.

    <img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2>

    Another approach is to use a command button on the form to duplicate the current record. For very simple forms, the code for this command button could look like this:

    Private Sub cmdDuplicate_Click()
    If NewRecord Then Exit Sub
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend
    End Sub

    But if your form is more complicated, this will probably cause an error message "Some of the fields names for the data you tried to paste don't match field names on the form". In that case, you'll need to write code to duplicate only the desired fields. I don't have a generalized recipe for that.

    Regards,
    Hans
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •