Index or duplicate key violation

[XV1957]

Hello Experts,
what am I not seeing? It must be so obvious that it baffles me.I am trying to add a "T" in front of an article number and write this new number to a table.
Access cannot find the new Article strArt because it does not exist. When I put a msgbox after "if .nomatch then" it correctly shows the message. Yet it refuses to create the new record because of a key/index violation. Apart from an index on the recordkey, it has an index on Artno, both indexes do not (obviously) want duplicate values.

Here is the code:
Private Sub btnCopyT_Click()
Dim db As Database
Dim rstArt As Recordset
Dim strArt As String
Set db = CurrentDb
strArt = "T" & Me.frmNomenclatuurSubfrm.Form.Artno
Set rstArt = db.OpenRecordset("tblArticle", dbOpenDynaset)
With rstArt
.FindFirst "Artno = '" & strArt & "'"
If .NoMatch Then
rstArt.AddNew
!Artno = strArt
rstArt.Update
End If
End With

End Sub

 

[XV1957]

Sorry, did a compact and repair and now it works. This drives me crazy.

 

[RainLover]

Think about putting the T in a separate field.

I realise you fixed it but this may be of benefit.

 

[XV1957]

Thank you Rainlover.
Can you elaborate on your Idea?
I am not seeing the advantage, as I am not a professional programmer.

 

[RainLover]

what am I not seeing? It must be so obvious that it baffles me.I am trying to add a "T" in front of an article number and write this new number to a table.
Access cannot find the new Article strArt because it does not exist. When I put a msgbox after "if .nomatch then" it correctly shows the message. Yet it refuses to create the new record because of a key/index violation. Apart from an index on the recordkey, it has an index on Artno, both indexes do not (obviously) want duplicate values.

For a Start "T" is not a Number. Typo I assume.

If there are two fields then you can sort filter etc. on either should there be a need.

You can Concatenate them when they are displayed. In the interim you have a bit more flexibility with separate tables.

If you can't see a use, then maybe there is none. It was just something I thought you might consider.

About the Compact and Repair. This could be a serious problem down the track so make sure you do heaps of back ups. I don't mean one a day. I am talking every 5 minutes or so when you are designing.

There are some tools in our archives that will help with that.

Best of luck with the project. And don't be afraid to ask if you don't know or are simply unsure.

 

[jdraw]

XV1957,

Just a few general comments/suggestions:

- use code tags when posting your code snippets. You can highlight your code then click the # on the advanced tab, or

you can put [c o d e] and [/ c o d e] tags around the vba/sql involved. (no spaces in the c o d e

- there are 2 free utilities that can be very useful with Access vba coding
=== Smart Indenter which adds some intelligent indentation to your code, and

===MZTools for VBA which has numerous features including header text outline, error handling, unused variable notification...

Good luck with your project.

 

[XV1957]

Rainlover,
thanks for your help.
I should have been more explicit: our Article numbers are, in reality, alphanumerical strings.
I do appreciate your advice on back-up. I did two backups a day already and will now try to have more.
By the way: how do I change that "thanked 0 times in n posts" to a real number reflecting my really thankful activity ?

 

[XV1957]

Hi Jdraw,
Thanks for your advice and your best wishes.
I do indent my code (the copy and paste did it) and I use MZ. Some of you experts pointed this last solution to me. I do appreciate your unrelenting efforts to educate and help the Acces crowd out there.

 

[RainLover]

By the way: how do I change that "thanked 0 times in n posts" to a real number reflecting my really thankful activity

?

This is the count of how many times you have been thanked.
The top one is for when you thank someone else.

I should have been more explicit: our Article numbers are, in reality, alphanumerical strings.

Then both should be text. Also make the length/size the same. I use 50. Long time since I had a problem but I think they should be the same. Maybe someone else could comment.