Results 1 to 14 of 14
  1. #1
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7

    Command Button VBA Code

    I have created a Command Button that is designed to give the user the choice of populating fields with some information obtained above it in the current record of a form.



    Private Sub Command96_Click()





    Me.[FTPUADDR] = Forms![Connector_Form]![FTPUGOOG]
    Me.[RTDSADDR] = Forms![Connector_Form]![FTPUGOOG]





    End Sub


    This successfully updates the fields I want, but then when I open a new record, that information is still in those fields and if I delete or change them in the new record, the previous one changes also. I can not seperate the record entries. I want this command button to only work on the current record within the form.

    Thank you for your help

    -Chris

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You could add some code to the on current event of the form to set the controls to null if the current record is a new record:

    IF me.NewRecord THEN
    Me.[FTPUADDR] = Null
    Me.[RTDSADDR] = Null
    END IF

  3. #3
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    You know what, I apologize. It appears as though it is not working exactly as I thought. So it will, indeed, erase the fields in the new record, however, it also erases previous records in those fields. Any additional thoughts
    Last edited by nyymattingly23; 01-03-2013 at 03:04 PM. Reason: I was mistaken that it was fixed.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome.

  5. #5
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    You know what, I apologize. It appears as though it is not working exactly as I thought. So it will, indeed, erase the fields in the new record, however, it also erases previous records in those fields. Any additional thoughts

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    It should not be doing that since the code should only fire if the record is new. Is the data changing in the underlying table or just the form?

    Is the form bound to a table or to query?

  7. #7
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Thanks for the reply,

    The form is supposed to be populating a table called "CoreForm_FlexTripBookingWorksheet"

    Now that you ask about bounding to a form, I see that although that button is writing the value in the form, it is not writing to the table, even if I save the form without opening a new record. Is it possible to write it to the table with the rest of the data. When I write data directly into the form it is populating the table fine. It is just these fields that are associated with the command button that are not populating the table.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Something does not sound right. If you complete the entry of the record via the form, the table should be updated. Could you zip and post a copy of your database (with any sensitive data removed)? We can then try to figure out what is going on.

  9. #9
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7

    Attached Access DB

    In the "Connector_Form" you will see the PASSENGER CONTACT INFORMATION section. From here, when the user places the street address into the field, they then have the option to click on "the "Home Address" button in the FIRST TRIP PICK UP INFORMATION section which will populate the PICK-UP ADDRESS field in the FIRST TRIP PICK UP INFORMATION section and the RETURN TRIP DROP-OFF DESTINATION INFORMATION section. 9 out of 10 times the user will be replicating this address with the button, but passengers may sometimes give a home address and not be getting on at their house, in which case these fields also need to have the option of having the user put in their own information.

    If the passenger is getting on at a regular fixed-route stop and not a deviation with in 3/4 of a mile of the regular route line (as it is a flex route) then those two pick-up and drop-off fields will remain empty and the user will choose a FIXED RTE STOP from the drop down list.

    I will be in the field working this morning but will return around lunch. You will probably appreciate the Google Earth button, I was excited to get that working. Basically after the user puts in the PASSENGER CONTACT INFORMATION, they click on the Google Earth button and it opens the desktop version of Gogole Earth and by default the cursor starts at the "search" field in the application so all the user has to do is hit CTRL-V and the address from the access form pastes into it and it flies to the address. I had pout 3/4 mile boundaries from a GIS shapefile into the Google Earth application so the user can see if the passenger falls within the Flex service boundary.

    Thanks again for all your help on this jzwp11!

    FLEX TRIP BOOKING WORKSHEET v2013_1.zip

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In looking at you connector form (in design view), the first time pickup address control (FTPUADDR) is unbound as well as the return trip drop-off address control (RTDSADDR). If these are unbound, any data entered into them will not be stored in the table. These have to be bound controls. I also noticed that the control source for the Return trip pickup address control is set to an expression, this too will not allow data to be stored in the table. You will need to bind the control to the applicable field and use code (like we discussed previously) to populate the data (or enter it manually).

    With that aside, you have some structural issues with your tables that need to be fixed before you worry about forms. If multiple addresses can be associated with a person then you have a one-to-many relationship which requires more than just the coreForm_FlexTripBookingworksheet table. Can an address be associated with more than one person? i.e. can 2 people have the same drop off or pickup address?

  11. #11
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    When you say unbound, you refer it having a "Control Source" linking to the specific field in the coreForm_FlexTripBookingworksheet table?

    This is an interim database before a new proprietary one becomes deployed this summer, so I would prefer to keep it rather simple. 1 record per instance, no need to link the names to common addresses or anything. Ultimately, I use the information to geocode addresses and populate a GIS and map where calls are being made from. We want to see whether they lie within the flex route boundary or not, to identify potential future markets if we are getting a lot of calls outside of our 3/4 area boundary, we can identify where to adjust the routes in the future to accomodate that additional revenue for the transit authority.

    The simpler we can can make the data entry for the reservationist, (i.e., auto populate additional fields), the more confident we can feel that the person is not cutting corners in the data entry.

    I will attempt to bind those fields to the coredata table and see how that works. I understand now why the formula doesn't populate that one field. I would have to do an additional "Update" query thing I believe. I will just leave that be.

    -Chris

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    When you say unbound, you refer it having a "Control Source" linking to the specific field in the coreForm_FlexTripBookingworksheet table?
    Exactly. If a form control is not bound, the data entered into that control will never be stored in the table.

    I would have to do an additional "Update" query thing I believe. I will just leave that be.
    If the form is setup properly, there would be no need to run an update query.


    This is an interim database before a new proprietary one becomes deployed this summer, so I would prefer to keep it rather simple. 1 record per instance, no need to link the names to common addresses or anything.
    Your structure violates normalization rules which would cause problems in the long run (and possibly in the short term), but since you say that this is only a temporary solution, I won't pester you about that. Of course, if the situation changes, we are here to help you out.

  13. #13
    nyymattingly23 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    I really appreciate your time and help on this. I'll let you know how it turns out. Glad I found this forum.

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-16-2012, 10:20 AM
  2. Replies: 12
    Last Post: 03-14-2012, 10:54 AM
  3. Command button code to clear form
    By windwardmi in forum Forms
    Replies: 15
    Last Post: 11-21-2010, 03:21 PM
  4. Form/report command button code
    By max3 in forum Forms
    Replies: 1
    Last Post: 08-26-2009, 02:18 AM
  5. Command button code
    By lfolger in forum Forms
    Replies: 3
    Last Post: 03-25-2008, 04:26 PM

Tags for this Thread

Posting Permissions

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