Checking imported Table for values in Field

[kweitzel]

Hi Folks,

after extensive searching this helpful DB here I have to post a question:

I have a Customer Details Tabel which i import from excel. The file selection for the import is done via a module called "fgetfilename". Then I add 2 fields to the table ("timestamp" and "AE Actual Complete Date&quot to prepare the data for appending it to a resident table and giving it a timestamp. I do this via queries and it works fine so far.

Now I should check the imported table for the value of 1 field (se_country) and compare it to a predefined value, in this case "NETHERLANDS". Normally just the first or one record needs to be checked, since the data comes sorted by countries out of a SIEBEL system. This validyation is needed, so that a user can not accidently choose a wrong Excel file for import (and corrupt the data).

The code I have so far (as Command Button code) is pasted below. Any help or helpful hints would be nice.

regards

Klaus

Dim Name As String
Dim dbs As Database
Dim tdf As TableDef
Dim fld1 As Field
Dim fld2 As Field
    
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "tbl_Eingang_NL_temp", "" + fGetFileName, True
    Set fld1 = tdf.CreateField("timestamp", dbDate)
    Set fld2 = tdf.CreateField("AE Actual Complete Date", dbDate)
    tdf.Fields.Append fld2
    tdf.Fields.Append fld1
    tdf.Fields.Refresh
    Set dbs = Nothing
    DoCmd.OpenQuery "qry_Timestamp_Eingang_NL_temp"
    DoCmd.OpenQuery "qry_append_Eingang_NL_temp"
    DoCmd.SetWarnings True
    DoCmd.DeleteObject acTable, "tbl_Eingang_NL_temp"
    MsgBox "Import der Daten für NL fertig", vbInformation, "Import NL Daten"

 

[kweitzel]

I managed now, but I am not really happy with the result, since I open 2 hidden forms based on 2 tables where I compare the fields.

Is there anything else I can do to achieve comparing 2 fields directly?

Here ist the code:

Private Sub bttn_Import_NL_Click()
bttn_Import_NL_Click:
On Error GoTo err_bttn_Import_NL_Click:
Dim Name As String
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Variant
Dim fld0 As Variant
Dim fld1 As Field
Dim fld2 As Field
    
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "tbl_Eingang_NL_temp", "" + fGetFileName, True
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!tbl_Eingang_NL_temp
    Set fld1 = tdf.CreateField("timestamp", dbDate)
    Set fld2 = tdf.CreateField("AE Actual Complete Date", dbDate)
        
        DoCmd.OpenForm "frm_Eingang_NL_temp", , , , , acHidden
        DoCmd.OpenForm "frm_Eingang_NL", , , , , acHidden
        Set fld = Forms!frm_Eingang_NL!se_country
        Set fld0 = Forms!frm_Eingang_NL_temp!se_country
            If fld = fld0 Then
                MsgBox "Die Importierten Daten sind für die Niederlande", vbOKOnly, "Daten Import OK"
            Else: MsgBox "Daten sind nicht für Niederlande! Bitte andere Datei auswählen!", vbCritical
                GoTo bttn_Import_NL_Click
            End If
        DoCmd.Close acForm, "frm_Eingang_NL_temp", acSaveNo
        DoCmd.Close acForm, "frm_Eingang_NL", acSaveNo
    
    tdf.Fields.Append fld2
    tdf.Fields.Append fld1
    tdf.Fields.Refresh
    Set dbs = Nothing
    DoCmd.OpenQuery "qry_Timestamp_Eingang_NL_temp"
    DoCmd.OpenQuery "qry_append_Eingang_NL_temp"
    DoCmd.SetWarnings True
    DoCmd.DeleteObject acTable, "tbl_Eingang_NL_temp"
    MsgBox "Import der Daten für NL fertig", vbInformation, "Import NL Daten"
       
Exit_bttn_Import_NL_Click:
    
    Exit Sub

err_bttn_Import_NL_Click:
    MsgBox Err.Description
    'MsgBox "Der Import ist fehlgeschlagen"
    DoCmd.DeleteObject acTable, "tbl_Eingang_NL_temp"
    
    Resume Exit_bttn_Import_NL_Click

End Sub

Thanks for any help ...

Klaus