Why doesn't this work ? I can I se

[welshone]

Why doesn't this work ?
I can I see the table SEN_Moderation and Moderation_Extra ?

cheers.

Private Sub Command0_Click()

Dim update As String
Dim Insert As String

update = "Update_Moderation_Extra"
Insert = "Insert_into_Mod_Extra"

If [SEN_Moderation]![DfEE] = [Moderation_extra]![DfEE] And [SEN_Moderation]![DOB] = [Moderation_extra]![DOB] And [SEN_Moderation]![Surname] = [Moderation_extra]![Surname] And [SEN_Moderation]![Forename] = [Moderation_extra]![Forename] Then
DoCmd.OpenQuery update, acNormal, acEdit

Else

DoCmd.OpenQuery Insert, acNormal, acEdit

End If


End Sub

 

[mc9901]

How far do you get? Have you tried setting breakpoints?

I would try adding many parentheses to the SQL statement so that the comparisons are absolutely clear to the compiler and optimizer. Also, I would declare the string variables using more unique names, since "insert" and "update" may have special meaning to Access or VBA - something like strInsertQueryName and strUpdateQueryName?

 

[welshone]

Cheers, however

problem is it doesn't get as far as running the queries.

it doesn't recognise
the tables in the If statement :

If [SEN_Moderation]![DfEE] = [Moderation_extra]![DfEE] And [SEN_Moderation]![DOB] = [Moderation_extra]![DOB] And [SEN_Moderation]![Surname] = [Moderation_extra]![Surname] And [SEN_Moderation]![Forename] = [Moderation_extra]![Forename]

 

[PaulF]

the code doesn't deal with accessing the two tables to gather the information needed for the If statement. Just because the two tables exist in the database, doesn't mean you can just reference their names, and Access with know what to do with them. If you create a query that has both tables in them and JOIN them together on a unique key field(s) then you can use this query (using OpenRecordset Method)to do the comparison of the fields. Or if you open both tables in code(using the OpenRecordset Method), and loop through one, and search the other table for the matching record, and if "found", you can compare the values, but with the code you have shown, there is no way for Access to know what these values are for it to do any comparison with.
There are plenty of examples in the Help file to show you how to do this.

PaulF

 

[welshone]

#Cheers Paul.

problem is, there is no unique numberID, thats why I need to link Dfee, DOB, forename and surname together.

do you know how to reference tables in the codE ?

 

[PaulF]

Apparently the four Fields make up the Unique Key, so create a Query that is based on the two Tables, and JOIN the Tables on all four Fields. Then you can do many things, depending on how you JOIN the four Fields.

If you use an "INNER" JOIN where there must be a match in all four Fields, then you can build the Update events right into the Query.

If you use a "RIGHT" or "LEFT" JOIN then you can Display all Records in One Table and only the matching records in the other Table. Using this method you can use a Criteria Row of Is Null for one of the four Fields that make up the Unique Key in the Table you want to Append to, and then you'll get all of the records in the Table that don't have a matching record in that Table and you can do the Append action that you want to do.


Another option (non Query) would be to create an Index consisting of all four Fields in each of the two Tables, then in code open each Table and perform a "SEEK", and store that to a Boolean Variable, and if both Variables are TRUE then you have a matching record.
The code you posted doesn't show what values you are comparing


With this all said, I'm not sure what your Append or Update Querys actually do, and if it might be easier to perform in Code.

PaulF

 

[welshone]

here are my 2 queries.
so if data already exists then run update, if data doesn't exist then run insert....

UPDATE Moderation_Extra INNER JOIN SEN_Moderation ON (Moderation_Extra.DOB = SEN_Moderation.DOB) AND (Moderation_Extra.Forename = SEN_Moderation.Forename) AND (Moderation_Extra.Surname = SEN_Moderation.Surname) AND (Moderation_Extra.DfEE = SEN_Moderation.DfEE) SET Moderation_Extra.DOB = ([SEN_Moderation].[DOB]), Moderation_Extra.Surname = ([SEN_Moderation].[Surname]), Moderation_Extra.Forename = ([SEN_Moderation].[Forename]), Moderation_Extra.DfEE = ([SEN_Moderation].[DfEE]);


INSERT INTO Moderation_extra ( DfEE, Surname, Forename, DOB )
SELECT SEN_Moderation.DfEE, SEN_Moderation.Surname, SEN_Moderation.Forename, SEN_Moderation.DOB
FROM SEN_Moderation INNER JOIN Moderation_extra ON (Moderation_extra.DfEE = SEN_Moderation.DfEE) AND (SEN_Moderation.Forename = Moderation_extra.Forename) AND (SEN_Moderation.Surname = Moderation_extra.Surname) AND (SEN_Moderation.DOB = Moderation_extra.DOB);

 

[PaulF]

so for the Append Query, if you change from an INNER JOIN to a RIGHT JOIN (show all records in SEN_Moderation, and only those records in Moderation_Extra that match), and add Is Null to the Criteria Row for the Field Moderation_Extra.DOB (Uncheck the Show Checkbox). Then all you should have to do is run the two Querys without having to do any testing in Code.

PaulF

 

[welshone]

In one table I have 7000 records and in the mod_extra table I have 7060 records now ?
they should be the same amount ?


but thanks, thats getting closer !!