Serching for record number and tale out an Error msg

[webcrab]

Hi!
This is the thing: I have a form with bound controls to a table.
One of the textboxes allow the user to put invitation number.

I want to assure that the user doesn't submit a number that is already in the table.

What can I write in vba that would look for the user input in the specific column in the table and if the number appear in the column there will be a messege box that would say that this number already exsists in the

table?

thanx a lot!

 

[branston]

Making the number a primary key in the table would mean that a duplicate could not be entered. Either that or you could try doing an after update, dcount with a where criteria of that number - which would could how many records have that number. If you add an nz function to the start and set that to 0 (so if there are no records, it stores 0) then say if dcount = 0 then carry on, else delete the record and output the error box. Sorry, i know i havent described that very well, let me know if you need more info!

 

[webcrab]

Hi!
Actualy, I just don't want the user to put the text in and when he click the submit button there is the error (you can't go to that record)
I want it to happend after upadate the specific textbox

 

[branston]

Hum... Even on an after update you have to click outside the box for it to happen, other wise there is no way of telling the user has finished inputting.
Does the count way sound like it might work? If so ill try and explain it better!

 

[webcrab]

yes please..

 

[branston]

Ok, i would put in something like:

If nz(dcount("FieldA","TblA","[InvitationNo] ='" & Me.InvitationNo & "'"),0) = 0 then
Else
Me.InvitationNo = ""
MsgBox "Number already in use"
End if

Where FieldA is any field whish is NEVER empty (ID/InvitationName/whatever), TableA is the table where FieldA and InvitationNo is stored.
This is assuming InvitationNo is stored as Text. If its a number replace
"[InvitationNo] ='" & Me.InvitationNo & "'" with
"[InvitationNo] =" & Me.InvitationNo

Give it a go, see what happens! Not 100% sure it will work how we want it to, but worth a go, hey!

 

[Scooterbug]

On the After Update of the text field, you can to do a dcount to see if that inventation number already exsists.

dim InviteNumCount as String
 
InviteNumCount = dcount("*","TableName","[InviteNumber]=" & Me.InviteTextBox)
 
If InviteNumCount <> 0 Then
    Msgbox "Invite Number " & Me.InviteTextBox & " already exsist. Please enter a new one"
    Exit Sub
end if