Solved Ms Access VBS

[Number11]

So i am looking to add a check to a process that copies records from a linked table into another, but i want a pop up to check if the data withing the linked table is the same as now..
something like..

Import data is ready with todays date click yes to continue or no of the date is not yet todays date?
then the import/copy would continue

 

[CJ_London]

Left join your source table to your destination table on a unique value or group of values to identify uniqueness. Then set a criteria on a destination table field to is null. This will exclude records already imported

Convert to an insert query if not already done

No need for a popup form

 

[Number11]

Left join your source table to your destination table on a unique value or group of values to identify uniqueness. Then set a criteria on a destination table field to is null. This will exclude records already imported

Convert to an insert query if not already done

No need for a popup form

Thanks for your thoughts, however i do need this data check as the user would not know that the linked table data has been refreshed so its like a check table data has been refreshed to carry on or check again in an hours or so

 

[arnelgp]

what do your check does?
check number of records? check all field contents of a record are same on the other table?

 

[Number11]

what do your check does?
check number of records? check all field contents of a record are same on the other table?

So the check is just that the refresh date in the linked table is the same date as when its run, to ensure that the user is importing current date data, i already have an index thats stops duplications

 

[RonPaii]

So i am looking to add a check to a process that copies records from a linked table into another, but i want a pop up to check if the data withing the linked table is the same as now..
something like..

Import data is ready with todays date click yes to continue or no of the date is not yet todays date?
then the import/copy would continue

The data copied from the linked table will be current assuming it's not locked by another user editing it. It may not be current a second later, which is a good reason to NOT duplicate data. If the user is viewing the data in a form and the from has a record selector bar; Access will display an icon that the record is locked.

 

[LarryE]

As soon as anyone asks a question related to copying data from one table to another, I immediately suspect the design is wrong from the beginning. Please take a screenshot of your table and relationship design and post that so we can see what the design looks like.

 

[Gasman]

As soon as anyone asks a question related to copying data from one table to another, I immediately suspect the design is wrong from the beginning. Please take a screenshot of your table and relationship design and post that so we can see what the design looks like.

That is generally the case, but it is recommended here many times to import data into a intermediate table, do whatever is needed and then populate the real table. This in my mind is what the O/P is trying to do?

 

[CJ_London]

i do need this data check as the user would not know that the linked table data has been refreshed so its like a check table data has been refreshed to carry on or check again in an hours or so

Sorry, now not at all clear what you are trying to achieve.

Perhaps show some example data (doesn't have to be real, just relevant) and use it to illustrate what you want to achieve.

 

[Number11]

so i just need some code to check a linked table field Importing_Date is todays date, rather than yesterdays which means the linked table hasn't yet been refreshed message box to say date is current or date is not current before the query runs to copy data into access tables?

 

[arnelgp]

you can use DMax() domain function to retrieve the Importing_Date:

If DMax("Importing_Date", "LinkedTableName") = Date() Then
    If Msgbox ("Import data is ready with todays date click yes to continue or no to abort import.",vbInformation+vbYesNo) vbYes Then
        ' code goes here for your import vba code
    Else
         Msgbox "Import aborted."
    End If
Else
     Msgbox "No new record to import."
End If

 

[Number11]

you can use DMax() domain function to retrieve the Importing_Date:

If DMax("Importing_Date", "LinkedTableName") = Date() Then
    If Msgbox ("Import data is ready with todays date click yes to continue or no to abort import.",vbInformation+vbYesNo) vbYes Then
        ' code goes here for your import vba code
    Else
         Msgbox "Import aborted."
    End If
Else
     Msgbox "No new record to import."
End If

getting a complile errort on
If Msgbox ("Import data is ready with todays date click yes to continue or no to abort import.",vbInformation+vbYesNo) vbYes Then

 

[RonPaii]

Add an equal before vbYes

bInformation+vbYesNo) = vbYes Then