check if a value exists

[jimbeaurama]

I am trying to code a validation step in a procedure whereby Access will check to see if a date exists in the table I am importing to. This is not a unique record. I want to make sure I do not mistakenly hit the import button on the form and pull in duplicate records.

I have already written a query to check if the value exists in both the table and the source document (Excel spreadsheet) Is there a way to run that query and if the result is not null, to abend or exit the procedure? I am afraid that my desire to do things the easy way is going to create more work for me. Thanks.

 

[Andrew Fergus]

Hello and welcome.

Yes you can abend the import provided the import is part of a macro. To do this, add a new line into your macro before the import line, view conditions, enter the condition (see here for some examples from MS technet), on the condition line use the "StopMacro" command. Your revised macro will now abend if the condition is met. You may need to follow your nose a little with the condition, but it can be done. Plus if you open MS Access help (the Alt F11 version), and search for "StopMacro" there are some useful bits in there too.

HTH, Andrew.

 

[jimbeaurama]

Thank you for the prompt reply, Andrew. I am having trouble figuring out how to implement this. In order to check whether the date has been imported already, I have a query that will run and display the date if it exists in the table and in the Excel spreadsheet. There is only one date in the field, a week ending date that all records have.

Can I run the query at the beginning of the sub and if the result is not null, pop up a MsgBox that will indicate the data has already been imported, and then exit the sub? Here is the sub:

Private Sub Command2_Click()

DoCmd.RunMacro "mcrDELETELATEST"
DoCmd.RunMacro "mcrIMPORTDATA"
On Error GoTo Err_Command2_Click

Dim stDocName As String

stDocName = "aqryAPPENDWEEKLY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click
End Sub

I want to end the sub prior to the first macro (mcrDELETELATEST) running. If there is a condition in the macro I can use, great, but I cannot figure this out on my own.

Thanks for your help with this.

Jim

 

[Andrew Fergus]

Hi Jim

I'm not good with VB but one way of doing this would be test for the null condition in the first few lines of the mcrDELETELATEST macro and abend the entire process at that point. {unless someone else can help with the VB code}

If you go into the macro design view (mcrDELETELATEST), Click View -> Conditions, the screen should now have a new column on the left hand side of the macro commands.

Insert three new blank lines at the top of the macro.

Can I run the query at the beginning of the sub and if the result is not null, pop up a MsgBox that will indicate the data has already been imported, and then exit the sub?

To do this bit, you can create the query but can't call on it directly from within the macro. You need to create a form that is based on the query and the macro can look at the value on the form. I discussed a similar problem with another person here - but be aware that this was for a slightly different problem (although the concept still holds true).

After you have created your query and your new form based on that query - the form should only have one item on it and it will either be null or populated depending on the outcome of your query. In the absence of more info you will need to work this bit out yourself.

Back in the macro design,

First Line = OpenForm (your new form, based on the query you want to test), No conditions
Second Line = MsgBox (set the message box up the way you want it informing the user the import will abend), condition = "IsNull([Forms]![Your Form Name]![The Variable Name])" (without the quotes) OR if you change a null to zero back in the query then the condition should be "[Forms]![Your Form Name]![The Variable Name] = 0" (without the quotes)
Third Line = StopAllMacros (this is the abend), condition = "IsNull([Forms]![Your Form Name]![The Variable Name])" (without the quotes, or the alternative = 0 version)

The 4th line in the macro will be back to your normal line, with no conditions. You might also edit the macro so that the completion of the mcrDELETELATEST starts the next macro mcrIMPORTDATA, using the "RunMacro" command at then end of your first macro and remove this bit of code

DoCmd.RunMacro "mcrIMPORTDATA"

from this bit of code:

Private Sub Command2_Click()

DoCmd.RunMacro "mcrDELETELATEST"
DoCmd.RunMacro "mcrIMPORTDATA"
On Error GoTo Err_Command2_Click

and as I mentioned earlier, VB is not my strength so I'm not sure of the impact of leaving in this piece of code :

stDocName = "aqryAPPENDWEEKLY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

HTH, Andrew.