"No New Data Added" on form

[Niko]

Hello everyone, I have an issue with my 'Importing excel" button. I am able to import my excel in and store duplication of data in a temp file. However, when i added in new data in my excel file, it shows no new data added when i have new data. How can i fix that?

Below is my code:

Private Sub cmdImport_Click()
Dim filepath As String
filepath = "C:\Users\user\Desktop\FabricPO.xlsx"
If FileExist(filepath) Then
DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
If IsNull(DLookup("[Date]", "NewFabricPO")) Then
MsgBox "No new data to add"
Else
DoCmd.OpenQuery "qryappend", acViewNormal
End If
Else
MsgBox "File not found. Please check filename or file location."
End If
Dim SQLDelete As String
SQLDelete = "delete * from TempFromExcel"
DoCmd.RunSQL SQLDelete
End Sub




Function FileExist(sTestFile As String) As Boolean


'this function does not use DIR since it is possible that uou might have
'been in the middle of running DIR against another directory in
'an attempt to match one directory against another
'it does not handle wildcard characters


Dim lSize As Long
On Error Resume Next
'Preset length to -1 because files can be zero bytes in length
lSize = -1
'get the length of the file
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist = True
Else
FileExist = False
End If


End Function

[ssanfu]

Let me see if I understand.

You import an Excel spreadsheet into a table named "TempFromExcel".
Then you do a DLookup on a field named "Date" (very bad idea - DATE is a reserved word) in the domain (table) "NewFabricPO" without any criteria.

If IsNull(DLookup("[Date]", "NewFabricPO")) Then

(Note: The DLookup function returns a single field value from a single random record)
If the dlookup returns a value, then you run an append query? Otherwise, you say "No new records".
Last step is to delete all records from table "TempFromExcel".



Hmmmmm, I would expect you to use table "TempFromExcel" to see if there were new records....... Why are you looking in table "NewFabricPO"???


What is the SQL of the query "qryappend"?

[Niko]

Let me see if I understand.

You import an Excel spreadsheet into a table named "TempFromExcel".
Then you do a DLookup on a field named "Date" (very bad idea - DATE is a reserved word) in the domain (table) "NewFabricPO" without any criteria.

If IsNull(DLookup("[Date]", "NewFabricPO")) Then

(Note: The DLookup function returns a single field value from a single random record)
If the dlookup returns a value, then you run an append query? Otherwise, you say "No new records".
Last step is to delete all records from table "TempFromExcel".



Hmmmmm, I would expect you to use table "TempFromExcel" to see if there were new records....... Why are you looking in table "NewFabricPO"???


What is the SQL of the query "qryappend"?

Hi, I use table "TempFromExcel to store duplicate data.
NewFabricPO is actually a unmatched query, and based on this table i would be able to add data that are not found in NewFabricPO table.
What is the SQL of the query "qryappend"? - tis is to append al my data from NewFabricPO to my main table.

I uses data becuase the data will be updated every night, and user will have to import data in every morning and with that it should be able to add the new data in based on the date

[ssanfu]

OK, what is the SQL of the query "NewFabricPO"?

Again, What is the SQL of the query "qryappend"?

Or post your dB for analysis......

[Niko]

OK, what is the SQL of the query "NewFabricPO"?

Again, What is the SQL of the query "qryappend"?

Or post your dB for analysis......

SQL for query "NewFabricPO"
SELECT TempFromExcel.[PO Type], TempFromExcel.Season, TempFromExcel.[Style NO], TempFromExcel.[GL Lot], TempFromExcel.Name, TempFromExcel.[Supplier ID], TempFromExcel.PO, TempFromExcel.[User ID], TempFromExcel.Name1, TempFromExcel.Line, TempFromExcel.Release, TempFromExcel.Date, TempFromExcel.Classification, TempFromExcel.Content, TempFromExcel.[Material Name], TempFromExcel.[Yarn Size and Type], TempFromExcel.[Fabric Weight], TempFromExcel.[Fabric Cuttable Width], TempFromExcel.Reference, TempFromExcel.Fabrication, TempFromExcel.Color, TempFromExcel.[Our Qty], TempFromExcel.UOM, TempFromExcel.[Supplier Qty], TempFromExcel.UOM1, TempFromExcel.[Unit Price], TempFromExcel.[Currency Code], TempFromExcel.Company, TempFromExcel.Approve, TempFromExcel.Canceled, TempFromExcel.[PO Date], TempFromExcel.[Garment Del Date], TempFromExcel.FOB
FROM TempFromExcel LEFT JOIN FabricPO ON TempFromExcel.[Date] = FabricPO.[Date]
WHERE (((FabricPO.Date) Is Null));

SQL for query "qryappend"
INSERT INTO FabricPO ( ID, [Date], PO, [Style NO], [GL Lot], Fabrication, [Fabric Cuttable Width], Color, [Our Qty], [Supplier Qty], Approve )
SELECT FabricPOQuery.ID, FabricPOQuery.Date, FabricPOQuery.PO, FabricPOQuery.[Style NO], FabricPOQuery.[GL Lot], FabricPOQuery.Fabrication, FabricPOQuery.[Fabric Cuttable Width], FabricPOQuery.Color, FabricPOQuery.[Our Qty], FabricPOQuery.[Supplier Qty], FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;

[ssanfu]

So now there is a query "FabricPOQuery".

If you execute this query, are records returned?

SELECT 
FabricPOQuery.ID, 
FabricPOQuery.Date, 
FabricPOQuery.PO, 
FabricPOQuery.[Style NO], 
FabricPOQuery.[GL Lot], 
FabricPOQuery.Fabrication, 
FabricPOQuery.[Fabric Cuttable Width], 
FabricPOQuery.Color, 
FabricPOQuery.[Our Qty], 
FabricPOQuery.[Supplier Qty], 
FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;

[Niko]

So now there is a query "FabricPOQuery".

If you execute this query, are records returned?

SELECT 
FabricPOQuery.ID, 
FabricPOQuery.Date, 
FabricPOQuery.PO, 
FabricPOQuery.[Style NO], 
FabricPOQuery.[GL Lot], 
FabricPOQuery.Fabrication, 
FabricPOQuery.[Fabric Cuttable Width], 
FabricPOQuery.Color, 
FabricPOQuery.[Our Qty], 
FabricPOQuery.[Supplier Qty], 
FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;

Yes, there are records returned

[ssanfu]

OK, that is good...

Try changing the code to (changes in BLUE)

Private Sub cmdImport_Click()
    Dim filepath As String
    
    filepath = "C:\Users\user\Desktop\FabricPO.xlsx"
    If FileExist(filepath) Then
        DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
        If IsNull(DLookup("[Date]", "NewFabricPO")) Then
            MsgBox "No new data to add"
        Else
            '  DoCmd.OpenQuery "qryappend", acViewNormal
            CurrentDb.Execute "qryappend", dbFailOnError
        End If
    Else
        MsgBox "File not found. Please check filename or file location."
    End If
    
    Dim SQLDelete As String
    SQLDelete = "DELETE * FROM TempFromExcel"
    '   DoCmd.RunSQL SQLDelete
    CurrentDb.Execute SQLDelete, dbFailOnError
End Sub

If no errors, then try changing "qryappend" to

INSERT INTO FabricPO ([Date], PO, [Style NO], [GL Lot], Fabrication, [Fabric Cuttable Width], Color, [Our Qty], [Supplier Qty], Approve )
SELECT  FabricPOQuery.Date, FabricPOQuery.PO, FabricPOQuery.[Style NO], FabricPOQuery.[GL Lot], FabricPOQuery.Fabrication, FabricPOQuery.[Fabric Cuttable Width], FabricPOQuery.Color, FabricPOQuery.[Our Qty], FabricPOQuery.[Supplier Qty], FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;

The "ID" field has been removed from the INSERT and the SELECT parts.



Again, the field name "DATE" should be changed and the spaces in all object name should be removed.

[Niko]

Fabric.accdb

OK, that is good...

Try changing the code to (changes in BLUE)

Private Sub cmdImport_Click()
    Dim filepath As String
    
    filepath = "C:\Users\user\Desktop\FabricPO.xlsx"
    If FileExist(filepath) Then
        DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
        If IsNull(DLookup("[Date]", "NewFabricPO")) Then
            MsgBox "No new data to add"
        Else
            '  DoCmd.OpenQuery "qryappend", acViewNormal
            CurrentDb.Execute "qryappend", dbFailOnError
        End If
    Else
        MsgBox "File not found. Please check filename or file location."
    End If
    
    Dim SQLDelete As String
    SQLDelete = "DELETE * FROM TempFromExcel"
    '   DoCmd.RunSQL SQLDelete
    CurrentDb.Execute SQLDelete, dbFailOnError
End Sub

If no errors, then try changing "qryappend" to

INSERT INTO FabricPO ([Date], PO, [Style NO], [GL Lot], Fabrication, [Fabric Cuttable Width], Color, [Our Qty], [Supplier Qty], Approve )
SELECT  FabricPOQuery.Date, FabricPOQuery.PO, FabricPOQuery.[Style NO], FabricPOQuery.[GL Lot], FabricPOQuery.Fabrication, FabricPOQuery.[Fabric Cuttable Width], FabricPOQuery.Color, FabricPOQuery.[Our Qty], FabricPOQuery.[Supplier Qty], FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;

The "ID" field has been removed from the INSERT and the SELECT parts.



Again, the field name "DATE" should be changed and the spaces in all object name should be removed.

My database still cannot detect new data. I attached my database.

[ssanfu]

Would you also post the Excel file to import, "FabricPO.xlsx" so I can walk through the process? You will probably have to zip it before posting......


EDIT: I tried to create an excel worksheet, but there are fields in the query "qryappend" that are not in table "TempFabricTable". And apparently the import table has changed because the query uses "TempFromExcel", not "TempFabricTable".

[Niko]

Fabric.zip

Would you also post the Excel file to import, "FabricPO.xlsx" so I can walk through the process? You will probably have to zip it before posting......


EDIT: I tried to create an excel worksheet, but there are fields in the query "qryappend" that are not in table "TempFabricTable". And apparently the import table has changed because the query uses "TempFromExcel", not "TempFabricTable".

I have attach my database and the excel

[ssanfu]

I don't believe this is your real dB because there are missing objects (queries/tables).

"NewFabricPO" - missing
"TempFromExcel"- missing
"qryappend"- missing



I am not willing to waste my time trying to get the code functioning on a dB that will be thrown away.

Good luck with your project..........

[Niko]

I don't believe this is your real dB because there are missing objects (queries/tables).

"NewFabricPO" - missing
"TempFromExcel"- missing
"qryappend"- missing



I am not willing to waste my time trying to get the code functioning on a dB that will be thrown away.

Good luck with your project..........

because my real database contain sensitive information, thus not able to post here. therefore, i make a similar database

"NewFabricPO" is replace by NewFabric
"TempFromExcel" is replace by TempFabricTable
"qryappend" is replace by qryAppendToMainTable

I just change the name of the query and the tables

[Minty]

I don't believe this is your real dB because there are missing objects (queries/tables).

"NewFabricPO" - missing
"TempFromExcel"- missing
"qryappend"- missing



I am not willing to waste my time trying to get the code functioning on a dB that will be thrown away.

Good luck with your project..........

You may want to see a remarkably similar post here https://www.access-programmers.co.uk...d.php?t=296799

[ssanfu]

because my real database contain sensitive information, thus not able to post here. therefore, i make a similar database

"NewFabricPO" is replace by NewFabric
"TempFromExcel" is replace by TempFabricTable
"qryappend" is replace by qryAppendToMainTable

I just change the name of the query and the tables

Didn't need the actual data - did need that actual dB and some sample (not real) data in the spreadsheet.

But it looks like the issue is solved in your posts at www.access-programmers.co.uk/forums