Thank you both.
Crystal, thank you for providing the code. I’m still in the process of cleaning up the data before proceeding with updating the table. The excel file provided to us contains both the existing providers along with the new providers. I’m trying to determine the best way for this proceed.
1. Filter out new providers and append only those providers
2. Try to bring everything hoping access will not import providers that already exist in the current table.
The Doc-Man,
Attached is a screenshot of the form users get when they create a new case. They will type in the first few letters to see if the provider already exists.
The other thing I noticed when creating a new record beside searching if provider already exist is to check if provider is in the excluded list. There is a exclusion table, but I’ve to find out how this table is updated. There is also a l link one can check if a given provider is in exclusion list “Application.FollowHyperlink "http://......" I wonder this is one of the reasons for slowness.
Following is the code on click event of the search button
TIA
Regards,
Crystal, thank you for providing the code. I’m still in the process of cleaning up the data before proceeding with updating the table. The excel file provided to us contains both the existing providers along with the new providers. I’m trying to determine the best way for this proceed.
1. Filter out new providers and append only those providers
2. Try to bring everything hoping access will not import providers that already exist in the current table.
The Doc-Man,
Attached is a screenshot of the form users get when they create a new case. They will type in the first few letters to see if the provider already exists.
The other thing I noticed when creating a new record beside searching if provider already exist is to check if provider is in the excluded list. There is a exclusion table, but I’ve to find out how this table is updated. There is also a l link one can check if a given provider is in exclusion list “Application.FollowHyperlink "http://......" I wonder this is one of the reasons for slowness.
Following is the code on click event of the search button
Code:
Private Sub cmdProvSrch_Click()
'Open up the Provider Results form
On Error GoTo Err_cmdProvSrch_Click
Me.Visible = False
Forms!frmPICTSMain!txtProvNM2.Value = Me.cboProvider.Column(0)
Forms!frmPICTSMain!txtProvNPI2.Value = Me.txtNPI
Forms!frmPICTSMain!txtProvNO2.Value = Me.txtProvNO
Forms!frmPICTSMain!txtLocNO2.Value = Me.txtLocNo
Forms!frmPICTSMain!txtType2.Value = Me.txtType
'Const strcQryNm As String = "qryCompareLEIE"
Dim db As DAO.Database
Dim con As Object
Dim ProvFlg As String
Dim rstProv As DAO.Recordset
ProvFlg = Me.cboProvider.Column(0)
If IsNull(Me.cboProvider.Column(0)) = False Then
'If Me.cboProvider.Column(0) = [tblLEIE].[BUSNAME] Then
Set db = OpenDatabase("Z:\Name of DB folder\Name of DB_be")
Set rstProv = db.OpenRecordset("tblLEIE")
With rstProv
rstProv.Index = "BUSNM"
rstProv.Seek "=", ProvFlg
If rstProv.NoMatch = False Then
MsgBox "Provider Is (LEIE)", vbCritical + vbOKOnly, "PROVIDER ON LEIE"
Application.FollowHyperlink "http://......"
Cancel = True
Exit Sub
Else
Exit Sub
End If
End With
End If
Exit_cmdProvSrch_Click:
Exit Sub
Err_cmdProvSrch_Click:
If Err.Number = 2105 And Err.Description = "You can't go to the specified record." Then
Resume Next
Else
MsgBox Err.Description
MsgBox Err.Number
Resume Exit_cmdProvSrch_Click
End If
End Sub
TIA
Regards,