Append query - avoid duplicate records (1 Viewer)

[Jen_RT]

I have a table (Equipment_tbl) where I have several fields, with the primary key being (Equipment ID). I have designed a query to find the last equipment ID added (newly added equipment ID).

I then want to append this newly added equipment ID to a compatibility matrix table which compares each piece of equipment against a station. The user can then fill in other details such as can it be used there, etc. There are ten stations held in the (stations_tbl) table and the compatibility table has an autonumber as its primary key.

The compatibility table consists of the following fields: Autonumber, Equipment ID, Station ID.

I have the append query working correctly, but before I run it (using a macro), I want to check that the newly added equipment ID does not already exist in the compatibility table.

I'm looking for something like an IF statement.

For example:

If (equipment ID field in the matrix table) = (the newly added equipment ID value) then do not run append query, else run append query.

Any help would be greatly appreciated. As I said, I have the append query working fine, I just need help to avoid it being run more than once and creating duplicate compatibilities.

 

[JamesMcS]

Could you use something like this in the on click of a button on a form:

if dmax([matrixtable].[equipmentid]) >= dmax([equipment_table].[equipmentid]) then
 
msgbox "Exists Already"
 
Else
 
docmd.openquery "Append Query Name"
 
Endif

Or something like that. DMax is basically the VBA version of max in a query - but check the syntax, the above is very much pseudocode.

 

[Jen_RT]

Thanks.

Do you know if there is there any way to do this other than using vba? I'm not familiar with coding at all and have been using macros and queries.

 

[JamesMcS]

Well, my first thought was to just have primary keys in the matrix table. That way, if someone tried to append an already existing piece of equipment, it would come up with an error (which you can trap, but only in VBA I think). Try it out and see if it gives you what you need - if not we can go through setting up forms, buttons etc no worries - it's pretty quick.

 

[Jen_RT]

I already launch my macro from the on click event procedure of the form.

It does the following:

1. displays a message box telling the user they should only perform this action once.

2. opens query that checks for the last equipment ID added.

3. opens query that appends the equipment ID to the compatibility matrix table

4. close first query

5. close second query

6. refresh equipment form.

Can you recomend somewhere else to put the check? Sorry, but my knowledge on this is rather limited.

 

[JamesMcS]

You'd need to do the whole thing in VBA - and it's not really that different. You'd need to do something like

Private Sub Runbutton_Click()
 
Msgbox "Only do this once"
 
if dmax("EquipmentID","Matrix_Table")>= dmax("EquipmentID","Equipment_Table") Then
 
msgbox "Exists Already"
 
Else
 
docmd.openquery "Append Query Name"
 
Endif
 
End Sub

You need to change the on click event to a VBA event, it should automatically open the VBA editor and you can stick the code in there. The help file is really good and it'll take you through the syntax for each command.

If you're not comfortable with VBA yet, I'd take some time out to learn how it works, and in the meantime just set up some primary keys in your matrix table so it's impossible to put a duplicate piece of equipment in there.

 

[Jen_RT]

That makes more sense to me now. I will research vba and make the fields primary keys.

Thanks for your help so far, I will let you know how I progress.

 

[JamesMcS]

Good luck! PM me if you get stuck, I'm not sure if I'll still be subscribed to this thread....

 

[Jen_RT]

When I try to make the equipment ID and station ID primary keys it wont let me because this would create dupliates.

My table essentailly looks like this at the moment:

Stat_vs_Equip_Compatibility_ID, Equipment ID, Station ID, (other fields...)
1, 0001, A
2, 0001, B
3, 0001, C
4, 0001, D
5, 0001, E
6, 0001, F
7, 0001, G
8, 0001, H
9, 0001, I
10, 0001, J
11, 0002, A
12, 0002, B
13, 0002, C
14, 0002, D
15, 0002, E
16, 0002, F
17, 0002, G
18, 0002, H
19, 0002, I
20, 0002, J
21, 0003, A
...and so on.

So as you can see, the equipment ID can be duplicated, and so can the station, but I want to make sure the combined combination of each cannot be duplicated. E.G you could not have:
1, 0001, A
2, 0001, A

Hope that makes sense.

 

[JamesMcS]

That's strange. I've just made a test table in a database, setting EquipmentID and StationID as primary keys, and it works fine - I can have duplicate equipment, duplicate stations, but not both in one record. Have you got dupes in there already? Try doing it in a new table like I did and see if it works from scratch - as you can see from the attached JPEG, I can enter a number of duplicate equipments and stations but in row 13, when I try and duplicate row 1, it comes up with an error.

 

[Jen_RT]

Thank you for your quick response. I just did a quick check and I have one duplicate record set in there. Sorry. I will fix this and carry on with what you suggested. Cheers

 

[JamesMcS]

No worries! Have fun.....

 

[Jen_RT]

It worked an absolute treat. Here is the code I used if anyone is intereted:

Private Sub Command77_Click()
MsgBox "You must only establish compatibility relationships once"
If DMax("Equipment_ID", "Station_vz_Equip_Compatability_TBL") >= DMax("Equipment_ID", "Equipment_TBL") Then
MsgBox "The compatibility relationships have already been established for this equipment item"
Else
DoCmd.OpenQuery "Append equipment to s vs e compatibility query"
End If

End Sub

Just one more thing, is there a quick way to change the code to run my macro instead of the single query. The problem being that I have more than one compatibility table. The other compatibility table compares the newly added piece of equipment with every other piece of equipment in the database?

Cheers - I dont think I could have done this on my own!

 

[JamesMcS]

Yep - I think it's docmd.runmacro or something like that. I think if you look up docmd in VBA help it'll give you all the possible options and a description of how they're used.

But if it was me I'd keep it all in VBA. As an example you can use docmd.runsql to run an sql statement, but you can put variables in the place of object names - so if you need to look in a different matrix table, you would put the matrix table name in a variable and use that in the code:

[I]Private Sub Command77_Click()
MsgBox "You must only establish compatibility relationships once"[/I]
[I]If DMax("Equipment_ID", " & Matrix_Table_Name & ") >= DMax("Equipment_ID", " & Equipment_Table_Name & ") Then[/I]
[I]MsgBox "The compatibility relationships have already been established for this equipment item"[/I]
[I]Else[/I]
[I]DoCmd.OpenQuery "Append equipment to s vs e compatibility query"[/I]
[I]End If

End Sub

[/I]Pseudo code again but you see what I mean

 

[Jen_RT]

Ok. I have created it on two separate on click command buttons.

The first establishes station vs equipment, with the following code:

Private Sub Command77_Click()
MsgBox "You must only establish compatibility relationships once"
If DMax("Equipment_ID", "Station_vz_Equip_Compatability_TBL") >= DMax("Equipment_ID", "Equipment_TBL") Then
MsgBox "The compatibility relationships have already been established for this equipment item"
Else
DoCmd.OpenQuery "Append equipment to s vs e compatibility query"
End If

End Sub

The second establishes equipment vs equipment, with the following code:

Private Sub Command100_Click()
MsgBox "You must only establish compatibility relationships once"
If DMax("Primary_Equipment_ID", "Equipment_vs_Equipment_Compatibility") >= DMax("Equipment_ID", "Equipment_TBL") Then
MsgBox "The compatibility relationships have already been established for this equipment item"
Else
DoCmd.OpenQuery "Append equipment to e vs e compatibility query"
End If

End Sub

If you have time, could you show me how to combine these two separate button on click procedures into a single button e.g. establish compatibility relationships.

The message text is the same, however the DMax check is different for each, so if this is too complicated, it is not an issue for the user to do this twice, it would just be more user friendly if it could be achieved using a single command button.

Thanks

 

[JamesMcS]

Definitely would be better for the user. Both subs are running the same query though, is that right? So if the if conditions in both bits of code are satisfied, the query is run twice? Would that not create a load of dupes?

 

[Jen_RT]

No. The queries are different. One is e vs e and the other s vs e. Sorry, my naming of them doesnt make that very obvious, but they do append to different tables.

 

[JamesMcS]

Ah right then. In that case you could probably just put both if statements in the one command button, it'll run one after the other then.

 

[Jen_RT]

Worked perfectly. Thank you very much for all your help.

 

[JamesMcS]

No probs. Have a good one!