Compare two table for duplicat data with a query...and set the query results to a varriable 1

[puforee]

In my data base I have my normal data table and an archive data table. On a form I have an archive button. When the archive button is clicked I want to check to see if the data I am about to Archive has already been archived. So, I have a simple query that looks at three fields in both tabls...It has a calculation that produces a 1 if the data is already in the second table. The query is linked to the form where the Archive button was clicked. That form shows the three fieds with specific informaiton. So the query will return...
Form 1 2 2, query based on form...Main table...1 2 3. Archive Table 1 2 3..calculation equals 1. Dup. It would return 0 if the Archive table did not have the information that the Form/Main Table had selected to look at. This all works.

Problem: In VB I want to store the results of the query in a variable. I can then check to see if the results were a 1 ot 0. If it is a 1 I would send a message to the user that the data had already been archived and stop any further action.

Here is what I have done in VB attached to the Archive button:
Dim Duplicate
Duplicate = queryname (no brackets or quotes..no spaces in query name.
MsgBox Duplicate
(more code to come later)

The code runs but the message box shows nothing. I can't be sure Duplicate variable has been set to anything.

Two questions:
1. How can I make what I outlined work?
2. Is there a better way to do this whole thing.

 

[puforee]

See change one bold below

In my data base I have my normal data table and an archive data table. On a form I have an archive button. When the archive button is clicked I want to check to see if the data I am about to Archive has already been archived. So, I have a simple query that looks at three fields in both tabls...It has a calculation that produces a 1 if the data is already in the second table. The query is linked to the form where the Archive button was clicked. That form shows the three fieds with specific informaiton. So the query will return...
Form 1 2 2, query based on form...Main table...1 2 3. Archive Table 1 2 3..calculation equals 1. Dup. It would return 0 if the Archive table did not have the information that the Form/Main Table had selected to look at. This all works.

Problem: In VB I want to store the results of the query in a variable. I can then check to see if the results were a 1 or 0. If it is a 1 I would send a message to the user that the data had already been archived and stop any further action.

Here is what I have done in VB attached to the Archive button:
Dim Duplicate As Integer
Duplicate = queryname (no brackets or quotes..no spaces in query name.
MsgBox Duplicate
(more code to come later)

The code runs but the MSG box shows a 0 when the query actually returns a 1.Two questions:
1. What can I do to make what is described above work?
2. Is there a better way to do all of this?

 

[trevil620]

There are several ways to do this... why not show us the query syntax?

 

[trevil620]

If you only need to know if the record is archived, do you really need to return a variable? (and I assume the FORM shows only the CURRENT record you want to check - and not a list box, etc.)
For example, if your query was something like "select flda from Archive_Table where (flda = '1' and fldb = 17 and fld3 = 'john') (supplying the valuse from form fields), then if the recordset returned zero records, you know it is NOT archived.

 

[puforee]

trevil620 both entries.

Here is the query sql: (Not VB SQL)
SELECT IIf([A_Main Admin Setup]!Revision=[Main Admin Setup]!Revision,1,0) AS Duplicate
FROM [A_Main Admin Setup] INNER JOIN [Main Admin Setup] ON ([A_Main Admin Setup].Revision = [Main Admin Setup].Revision) AND ([A_Main Admin Setup].Course = [Main Admin Setup].Course) AND ([A_Main Admin Setup].Model = [Main Admin Setup].Model)
WHERE ((([Main Admin Setup].Revision)=[Forms]![Main Admin Setup Form]![Revision]) AND (([A_Main Admin Setup].Model)=[Forms]![Main Admin Setup Form]![Model]) AND (([A_Main Admin Setup].Course)=[Forms]![Main Admin Setup Form]![Course]));

I need to make decisions on the results of the query. I sat the result as a variable so I could do If Then Else statement. It the variable is 1...exit code.

If it is 0...do severl more queries to archive 4 talbles worth of CURRENT record identified in the form.
Then more code to update the revision number on the form. The tables are set up so the records are exclusive and cannot be repeated..so, when I roll the revision it becomes a new record based on the rev number...and the old record only shows in the Archive tables.

Hope this helps.

 

[trevil620]

Someone else may correct me if I am wrong, but I don't think trying to return a value is going to work if a record has NOT been archived.
1. I created your table structure (placed 3 DUPLICATE records in each table) and a simple form.
2. Using the following SQL, you can get two results - depending on if the record exists in both tables:
SELECT IIf([A_Main Admin Setup]![Revision]=[Main Admin Setup]![Revision],1,0) AS Duplicate
FROM [A_Main Admin Setup] INNER JOIN [Main Admin Setup] ON ([A_Main Admin Setup].Model = [Main Admin Setup].Model) AND ([A_Main Admin Setup].Course = [Main Admin Setup].Course) AND ([A_Main Admin Setup].Revision = [Main Admin Setup].Revision)
WHERE ((([Main Admin Setup].Revision)=[Forms]![Main Admin Setup]![Revision]) AND (([A_Main Admin Setup].Model)=[Forms]![Main Admin Setup]![Model]) AND (([A_Main Admin Setup].Course)=[Forms]![Main Admin Setup]![Course]));

(a) If there is a match between both tables, you get one record returned that has a value of '1' in field 'Duplicate'
(b) If there is NO MATCH, then you get an empty recordset -- which means uyou get NO VALUE.

3. As long as you are just trying to determine MATCH versus NOMATCH, then why not just open the recordset using the join query and check for EOF?
i.e.
If rs.eof then
msgbox "No Archive Record found"
else
msgbox "Already Archived"
endif

 

[trevil620]

Below I have pasted the code that will allow you to test for 'Archived' versus 'Non-Archived' then do whatever else you want.
Also, it is a good practice to not have spaces in names -- either tables, forms, fields, etc. It is messy. If you 'must' seperate words, don't use a dash - use the underscore.

Good Luck,
Wayne

Option Compare Database
Option Explicit

Private Sub cmdArchive_Click()
If Is_It_Archived = True Then
MsgBox "This record has already been archived"
Else
MsgBox "This record has not been archived"
End If
End Sub

Private Function Is_It_Archived() As Boolean
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT [Revision], [Model], [Course] " & _
"FROM [A_Main Admin Setup] " & _
"WHERE (([Revision]= '" & [Forms]![frm_MainAdminSetup]![Revision] & _
"' AND [Model]= '" & [Forms]![frm_MainAdminSetup]![Model] & _
"' AND [Course]= '" & [Forms]![frm_MainAdminSetup]![Course] & "'));"
Debug.Print vbTab & strSQL
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rsSQL.EOF Then
Is_It_Archived = False
Else
Is_It_Archived = True
End If
rsSQL.Close
Set rsSQL = Nothing
Set dbs = Nothing
End Function

 

[puforee]

trevil620

I have not been ignoring your response. My schedule has been full and I am just now getting back to this project.

I assume a copy paste of the code above into the Click Event of my Archive button is want you intend for me to do....Yess? I will do that now.

More later.

 

[trevil620]

No problem...
Since you already have the form, and I assume you already have a 'Click_Event' for your button, do the following:
1. Copy and paste all the code in 'Private Function Is_It_Archived()' at the end of your code page.
2. In your existing 'Click' event, add just the following; then add whatever code you want to do if found or not found.
If Is_It_Archived = True Then
MsgBox "This record has already been archived"
Else
MsgBox "This record has not been archived"
End If

 

[puforee]

OK, I am not sure I put the Function in the right page. When I open my Main Admin Setup Form....this has the archive button on it....before the form opens I get. Ambiguous name detected: Is_It_Archived.

Then the form opens...when I select a record I get the same error.

When I click on the Archive Buttion the same error shows and....

The expression On Click you enterred as the event property setting produced the following error: (same error) The expression may not result in the name of a macro, th name of a user-defined function, or Event procedure. There may have been an error evaluation the function, event or macro.

So...when I was setting this up...I opened the click event and copied the Private sub into it. I then scrolled to the bottom of that code page and copied the Private Function. Did I put the function in the correct place?

 

[trevil620]

Whoa!
1. You can't embed the 'Private Function Is_It_Archived()' WITHIN ANY EXISTING Subroutine or Function.
2. It sounds like you have added the subroutine TWICE? A simple search of your code should show if it is found more than once.
3. If there is not much code in that form, then AFTER you confirm the above, if you still have an error, paste the code here.