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.