checking record exist

[ahc59always]

Hello forummembers, Glad to be a new member. Hoping for many good answers.

I think i've got a simple problem.
I have a subform in my form, and in that subform is a field called "LezerID" no Primary Key Field, no autonumberfield, just a numbering field. I want to check If I change the LezerID into another number, there will be a messagbox apears to me and say "Number already exist, choose antoher number." And if I then must choose
another number it must be de next free availble number, or the next number that is free +1 or +2.
please keep it as simple as possible. I am just a pupil with access 2007.
thx in advance,
gr.ahc59 from the Netherlands

 

[SydneyGeek]

Hi, and welcome to the Board.
First option is to go to the table design and make that field unique (in the Index, Yes - No Duplicates).
That way you will be unable to create a duplicate number. By setting the Required property to Yes you effectively make the field a Primary Key.

You'll need some code to run the update of the number field.
In Design view on the subform, make sure you select the section at the top left of the form (same as where you select a whole worksheet in Excel) -- you will see a black square. If the Properties aren't showing right-click this square and select Properties.
Go to the Events tab and double-click the blank line next to Before Update. You will see [Event Procedure]. Now click the ... button on the right and you will go to the code window.
Edit or paste, so the code looks like this (note -- edit the table name in the DMax line to match your table name)

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngNextVal As Long
    
    If IsNull(LezerID) Then
        'change the table name to match your table
        lngNextVal = Nz(DMax("[LezerID]", "tblDummy"), 0) + 1
        Me.LezerID = lngNextVal
    End If
End Sub

Now, each time you add a record it will get the next available number.

Denis

 

[ahc59always]

Hi Denis,
thx for the quick reply, and welcome greeting. Ill think, I need several weeks to understand your code, but if I may have further questions, do you allow me to contact you again. If it works Ill send you asap a message.
gr.ahc59

 

[SydneyGeek]

The code that I gave you uses 3 functions.
IsNull -- tests to see if a field is null (ie, Blank). This is not the same as "" (an empty string); it truly is Blank.
NZ -- Null to Zero. Does a test to see if a field or calculation is Null, and converts it to 0. Necessary because Access doesn't give a numeric result if it encounters a Null. You get a Null back instead.
DMax -- finds the maximum value for a field ("[LezerID]" in the above calculation), in a table or query ("tblDummy" in the sample). For your database you can keep the LezerID name, but you will need to change tblDummy to the real name of your table. And if there are spaces in the name, you will need to use square brackets.

If you get stuck, post back and you'll get help.

Denis

 

[ahc59always]

Hi Denis,
Ill promised to send you word, If I solved my problem. The code you send was to difficult for me, I'am sorry. What I did, I've made a checkbox in my subform.
I also made a query with LezerID and the checkbox en set the criteria simpel at -1. So if I selected record in my subform and I marked the record as true. the record will then be deleted when I press a button on my main form. It just working perfect...
But I have truly have to say that your help, tips and advice gave me confidence to go on.
Again many thanks denis.
greetings, ahc59
PS
Ill think if I need you in the future I will call you "Master Denis"

 

[SydneyGeek]

Glad to hear that you have a solution, and thanks for the feedback

Denis

 

[richardberry134]

I an Richard Berry here. I like it. Thanks for sharing it.