checking tables (1 Viewer)

[johnnyguy]

hi, i have a code being created for lecturers using the initials of lecturer names inputted. for example john smith = JS, i have also added a variable called codenumber which i have used to add a number to the initials. JS1, as more than one lecturer may have the same initials. I do not know how i can check if JS1 already exists in my table as its a primary key of the lecturer table. so in vba what code do i need to place in an IF statement to check the code that is about to be produced does not exist or if it does it adds 1 to the codenumber variable making it JS2 for instance.

 

[Treason]

hi, i have a code being created for lecturers using the initials of lecturer names inputted. for example john smith = JS, i have also added a variable called codenumber which i have used to add a number to the initials. JS1, as more than one lecturer may have the same initials. I do not know how i can check if JS1 already exists in my table as its a primary key of the lecturer table. so in vba what code do i need to place in an IF statement to check the code that is about to be produced does not exist or if it does it adds 1 to the codenumber variable making it JS2 for instance.

this should be fairly easy to do...



'This opens the table in question
Dim rst as Recordset
Set rst = CurrentDB.OpenRecordset("tblLecturer", dbopendynaset)

'Attempts to find the Initials from the codenumber in
'the field that I called Field1
rst.FindFirst "[Field1] = '" & codenumber & "'"

If rst.Nomatch = true then
'If it doesnt find it then you can use it
Else
'If it finds it now you can add 1 to the end of it
End If



Thats more or less what you want to do. You may want to rearrange this to include a loop. So it will check JS1 then JS2 etc.. until it finds an open spot. To be honest though I think you need to rethink this whole project. It sounds like it may be faulty.