Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Auto populate a field from another source

  1. #1
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16

    Auto populate a field from another source

    Access 2003

    Hello,

    I need help in having a field automatically populated with a value from another field from the same form and same record.

    I have created an input form for adding a new record and when the first entry is made into an empty text box or selection from a combo box is made, the record is created and the record number shows up. This is working great.
    Form name = FrmAssignMRR1
    Record source (table) = MRR Table
    Field name = text145 (this is the autonumber or record number)

    What I need is the value from text145 to automatically populate into another field on the same form rather than type it in manually.
    Record source (table) = MRR Table
    Field name = MRR_NO

    I'm basically repeating the autonumber in another field in the same table.
    Field names and properties of the MRR Table;
    MRR NO ID (autonumber & Primary Key)
    MRR NO (number)
    Date (date)
    many more

    It's a design flaw but I'm not going to change it at this point. The life of the database will expire in another 5 months.

    Is there a simple way to have my field (MRR NO) automaticly populate with the same value as the autonumber at the same time?

    Thanks in advance for any help,

    George

  2. #2
    NoellaG's Avatar
    NoellaG is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    439
    Hi,
    just put in the afterUpdate event of the field text145:

    me.MRR_NO = me.text145

    gr
    NG

  3. #3
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16
    Digg,

    I just tried it again and nothing happens. No errors or results.
    I put this exact phrase directly in the "after update" properties of field Text145

    me.MRR_NO = me.text145

  4. #4
    NoellaG's Avatar
    NoellaG is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    439
    Hi,

    the property of the field should be set to event procedure (there's a drop down) and then the line I posted is the code that goes into that procedure.

    gr
    NG

  5. #5
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16
    I placed it in the event procedure.

    Private Sub Text145_AfterUpdate()
    Me.MRR_NO = Me.Text145
    End Sub

    Same results. Nothing happens.

    I wonder if the value in text145 isn't really saved or updated yet? That would explain why the after update isn't working. The record is still not saved at that point?

    just thinking out loud of reason why the code isn't working

  6. #6
    NoellaG's Avatar
    NoellaG is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    439
    normally the code should be performed after you changed the value of text145 and then tabbed out of the field, regardless if the record is saved or not. Maybe set a waypoint and watch what happens?

  7. #7
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16
    Text145 never has focus. When the form opens, this field is sitting there with with "(AutoNumber)" in the text box. Then the moment I start to type or make a selection to any of the other fields, then Text145 shows the next sequencial number instead of the word "(Autonumber)". It's really just showing the append next record at the end of the table until the record is started. That is what I meant by the record not being saved yet. The pencil (so to speak) is still showing on the record line at the time I want my other field to auto populate. It may not recognize that Text145 has a value yet.

    Once again, just grasping at straws. i really do not know the answers.

    Also, what do you mean by Waypoint? I'm not familiar with that term.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,478
    You can force a save with

    If Me.Dirty Then Me.Dirty = False

    or

    DoCmd.RunCommand acCmdSaveRecord

    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16
    Would I place either of those in the before update or On dirty as a procedure in Text145?

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,478
    Sorry, now that I read more closely, I'd go a different way. Try your code:

    Me.MRR_NO = Me.Text145

    in the after update event of the form rather than the textbox (that event will never fire).
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  11. #11
    vitols is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Germany
    Posts
    9
    Quote Originally Posted by gbullo View Post
    Access 2003

    Hello,

    I need help in having a field automatically populated with a value from another field from the same form and same record.

    I have created an input form for adding a new record and when the first entry is made into an empty text box or selection from a combo box is made, the record is created and the record number shows up. This is working great.
    Form name = FrmAssignMRR1
    Record source (table) = MRR Table
    Field name = text145 (this is the autonumber or record number)

    What I need is the value from text145 to automatically populate into another field on the same form rather than type it in manually.
    Record source (table) = MRR Table
    Field name = MRR_NO

    I'm basically repeating the autonumber in another field in the same table.
    Field names and properties of the MRR Table;
    MRR NO ID (autonumber & Primary Key)
    MRR NO (number)
    Date (date)
    many more

    It's a design flaw but I'm not going to change it at this point. The life of the database will expire in another 5 months.

    Is there a simple way to have my field (MRR NO) automaticly populate with the same value as the autonumber at the same time?

    Thanks in advance for any help,

    George


    ----------------------------
    Hi George


    Checkout RaiseEvent

    Vitols

  12. #12
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16
    That seemed to do the trick and populated the MRR_NO field but I had to put a requry command on the after update of a different control to force the save but now it has caused another problem.

    When I try to get out of the form, I get a message telling me I cant save the data and if i continue to close, i may lose the data that was just entered.

    I've tested it and no data is lost but every time I try to close the form, I get the message.

    I think by forcing the record to save before it's complete just to get the MRR_NO field to auto populate, it thinks the record has already been saved and there is possibly a conflict.

    Suggestions?

  13. #13
    vitols is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Germany
    Posts
    9
    Hi George

    Try

    Forms("frmAnother").Controls("txtControl_1") = lngNumber

    quicker

    Vitols

  14. #14
    gbullo is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    16
    Vitols,

    I'm not sure I follow you. Where would this code be placed and what exactly does it do? If I am trying to populate a text box named "MRR NO" with the value from the auto number field named "text145" would your code read as follows?

    Forms(text145).controls(MRR NO)=lngNumber

    If this is correct, where would this be placed and what would the result be?

  15. #15
    vitols is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Germany
    Posts
    9
    Quote Originally Posted by gbullo View Post
    Vitols,

    I'm not sure I follow you. Where would this code be placed and what exactly does it do? If I am trying to populate a text box named "MRR NO" with the value from the auto number field named "text145" would your code read as follows?

    Forms(text145).controls(MRR NO)=lngNumber

    If this is correct, where would this be placed and what would the result be?


    Hi George

    Try

    Data flow is like water, it goes from the source to the sink.

    Forms("frmAnother").Controls("txtControl_1") = lngNumber

    Forms & Controls is VBA syntax.
    frmAnother is another form e.g your sink form.
    txtControl_1 is a textbox on the sink form.
    lngNumber ist the value which the textbox on the sink form receives.
    Call this code with a after update on your source form.


    p.s
    "text145" means sweet nothing.
    txtCustomerId means its a textbox containing the CustomerId.

    Vitols

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto Populate
    By co_sportsguy in forum Access
    Replies: 3
    Last Post: 09-01-2010, 12:22 PM
  2. Auto-populate form question
    By revnice in forum Access
    Replies: 19
    Last Post: 08-06-2010, 12:01 PM
  3. auto populate combo field?
    By myboii in forum Access
    Replies: 8
    Last Post: 07-09-2010, 04:46 AM
  4. Auto-Populate Combo box
    By vincenoir in forum Forms
    Replies: 3
    Last Post: 10-14-2009, 06:06 AM
  5. Replies: 3
    Last Post: 10-05-2009, 06:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums