Results 1 to 14 of 14
  1. #1
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7

    Auto-Populating Fields using Combo Box and Data from Linked Table Occasionally Not Working


    Hello All,

    I have a MS Access forum (front-end) with two linked tables, one is the backend database while the other is a linked ODBC database (SQL Server). I'm using a combo box to auto-populate customer information in the form based on the value (work order) entered in the combo box (see below).

    Code:
    Private Sub workOrder_AfterUpdate()
    
    
    Me.toolNumber.Value = Me.workOrder.Column(1)
    Me.customer.Value = Me.workOrder.Column(2)
    Me.partNumber.Value = Me.workOrder.Column(3)
    Me.revision.Value = Me.workOrder.Column(4)
    
    
    End Sub

    I'm pulling the customer data from the linked table on the SQL Server (see below).

    Code:
    SELECT DISTINCTROW dbo_vw_plating.WONUM, dbo_vw_plating.TOOLNUM, dbo_vw_plating.CUSTNAME, dbo_vw_plating.PARTNUM, dbo_vw_plating.REV
    FROM dbo_vw_plating
    ORDER BY dbo_vw_plating.WONUM DESC;
    This works a majority of the time but occasionally some work orders will not auto-populate the customer information. If I close the MS Access file and re-open it, it auto-populates just fine. I've been working on this issue for a while now and have searched online but haven't found anything that fixes the issue. Does anyone have any ideas what could be happening? The only pattern I have found in the work orders that don't auto-populate customer information is that they end with -101 or -202 for example, where-as work orders that end with -000 seem to never have an issue. I thought it may be an issue with the linked SQL table not updating, but even if I refresh the link using the linked table manager it still doesn't auto-populate. It's not until I close then re-open the front-end ms access file that it will work. Thanks for reading and I appreciate any help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why duplicate this data into local table? Why not just save record ID? Why do you need DISTINCTROW?
    It would be very odd that issue is data dependent.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I guess you're saying the form textboxes don't populate sometimes when you've made a combo selection? When you say you close db and open the operation will now work, or does that mean the results you expected are there all of a sudden? Makes a difference, I think.
    Perhaps try Me.Repaint and if that doesn't solve it, Me.Refresh - after the last setting is made (you don't need both). FWIW, you don't need .Value as it is the default property.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    I guess you're saying the form textboxes don't populate sometimes when you've made a combo selection? When you say you close db and open the operation will now work, or does that mean the results you expected are there all of a sudden? Makes a difference, I think.
    Perhaps try Me.Repaint and if that doesn't solve it, Me.Refresh - after the last setting is made (you don't need both). FWIW, you don't need .Value as it is the default property.
    Correct. Its very difficult to replicate the issue as well. Occasionally the customer information does not auto-populate the other fields, yet when I close the ms access file then re-open, it populates all the customer information no problem when I scan the work order. I'll try me.repaint and me.refresh. By "after the last setting is made" do you mean after the last field is entered? Should I use me.repaint or me.refresh under the On-Click event for the form save button?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    it populates all the customer information no problem when I scan the work order.
    Still don't know what that means or how scanning fits in with choosing column values from a combo. Anyway, I'll wait for your update.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    Still don't know what that means or how scanning fits in with choosing column values from a combo. Anyway, I'll wait for your update.
    I am utilizing a barcode scanner to input data (work order) into the combo box. I added me.repaint under the on-click event for the save button and will let you know if the issue persists. Thanks for the help.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Probably not your issue, but you realize that the combobox column property is 0 based? When you ask for what's in column 1, that is the 2nd column.
    Hope repaint works for you, but it should be in the afterUpdate of the combo, not on some button click.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7
    @JUNE7 The table is a log for a manufacturing process. This is one of my first MS Access forms so for means of simplicity I did not make it a relational database. I added DISTINCTROW after watching a tutorial online. Its very odd as it works 90% of the time, so I was trying to find some type of pattern in the work orders that didn't auto-populate.

  9. #9
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    Probably not your issue, but you realize that the combobox column property is 0 based? When you ask for what's in column 1, that is the 2nd column.
    Hope repaint works for you, but it should be in the afterUpdate of the combo, not on some button click.
    Yes I did realize that. It auto-populates 90% of the time, just occasionally does not autofill for a combo box selection. If I close then re-open the file, then select the same combo box value that wasn't working, it works. I will keep you updated.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you're getting values for a combo (e.g. CustomerName) from a table that isn't a lookup table (not lookup field) it's not unusual to use DISTINCT or DISTINCT ROW. Nor do I think it's unusual to get such values from a non-local table with the intention of using those values when creating new records in your local table. Maybe not best practice, but aside from potentially losing the ability to get those values because of connection issues, no real harm either. I would probably just link to this foreign table or have a lookup table if practical.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    Still don't know what that means or how scanning fits in with choosing column values from a combo. Anyway, I'll wait for your update.
    I tried me.repaint in the afterupdate event for the combo box and the issue is still occurring. The combo box is pulling data from the linked table and it appears when a value is added to the linked table it's not updating in the combo box until the file is closed then re-opened. Is there a work around for this? I will try me.refresh.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    it appears when a value is added to the linked table it's not updating in the combo box until the file is closed then re-opened.
    That sounds like you're adding a value to the back end table (not the ODBC one, correct?) after the form has already been opened? Then you'd need to requery the combo. That is not the same problem as the combo not populating your form fields in the combo afterUpdate event. If you drop the combo down and the value you're looking for isn't there, you can't select it, so when you close the combo, it won't update, hence the event won't run. However, I don't see the connection between this and the last 3 digits of the work order - unless those problem digits have something to do with an added record that isn't included in the combo's current list. I was going to suggest that you look at the data in ALL the fields of the table that is involved in the combo rowsource because you're using DISTINCTROW and not DISTINCT, but now **it sounds like you're adding data at some point after the form has been loaded**.

    EDIT ** Refresh or Repaint won't help if that is the case. You could also requery the form, but that might have undesirable effects, such as moving off the current record and the first record of the form's recordset becomes current.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    ryanlovejoy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    That sounds like you're adding a value to the back end table (not the ODBC one, correct?) after the form has already been opened? Then you'd need to requery the combo. That is not the same problem as the combo not populating your form fields in the combo afterUpdate event. If you drop the combo down and the value you're looking for isn't there, you can't select it, so when you close the combo, it won't update, hence the event won't run. However, I don't see the connection between this and the last 3 digits of the work order - unless those problem digits have something to do with an added record that isn't included in the combo's current list. I was going to suggest that you look at the data in ALL the fields of the table that is involved in the combo rowsource because you're using DISTINCTROW and not DISTINCT, but now **it sounds like you're adding data at some point after the form has been loaded**.

    EDIT ** Refresh or Repaint won't help if that is the case. You could also requery the form, but that might have undesirable effects, such as moving off the current record and the first record of the form's recordset becomes current.
    I was able to solve the issue using Me.ComboBox.Requery on the On Click Event for my "New Entry" button. Thanks for all the help!

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome and thanks for reporting back. Good luck with your project!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 09-26-2016, 08:53 AM
  2. Replies: 3
    Last Post: 03-17-2015, 10:50 PM
  3. Replies: 2
    Last Post: 04-15-2014, 10:03 PM
  4. Auto populating fields from MS access
    By fawazeto in forum Forms
    Replies: 0
    Last Post: 04-04-2011, 08:25 PM
  5. Replies: 4
    Last Post: 06-10-2009, 12:43 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