how to check if a recored exists

[afnan_88]

hello all



i have a form that contain a field that the user will fill it with an id then he will click a bottun
i want to do folowing on the click event:

check that the id enterd by the user is already exists or no

1- that means i want to make a search for the given id or if you have another way you can tell me

2- if its not exist

a msg box will displayed

3-if exists the user can edit the related recored

i already know how to make 2 & 3 but i want your help in the 1st step

thanx in advance

 

[Uncle Gizmo]

Assuming you have a list of unique ID’s already in your database, it stands to reason you do have because these are the record ID’s you want to look at!

Why not have a combo box to display the list of ID’s?

When your user types an ID in to the combo box, if it’s not in the list, (in the combo box) then it won’t show, indicating to the user that the ID does not exist. This method solves the first problem without actually having to write code to look for ID typed in.

You could also utilise the “not in list” event of the combo box t display your message box.

 

[afnan_88]

uncle gizmo thanks alot
i like your idea but i can't use the combo box because my IDs can't be displayed in this way for privacy reasons

still seeking for other solutions

 

[John Big Booty]

You could use the Dcount() Function, it might look something like;

If Dcount(" ID","TableName","ID =" & Me.FieldName)=0 Then
     MsgBox "The ID you have entered does not exist,........"
     Me.FieldName.SetFocus
     Me.FieldName.SelStart=0
     Me.FieldName.SelLength = Len(Me.FieldName.Text)
     Cancel = True
     Exit Sub
End If

 

[missinglinq]

...my IDs can't be displayed in this way for privacy reasons

This doesn't really make much sense! What is the difference between allowing users to see a list of the ID numbers, which are just assigned numbers, and retrieving the related data or simply entering one number after the other and seeing related data?

Whether a given person has the authority to view the data has to be addressed before retrieving data thru either of methods that have been suggested.

 

[afnan_88]

john big booty
i try your sol.

x = DCount("[104_National_ID]", "INITIAL ASSESSMENT", "[104_National_ID] = " & Me.National_ID.Text)

i get this error :

the expression produced this error [104_National_ID]

 

[boblarson]

Do not use .TEXT! That requires the control have focus. Just use the .VALUE (actually you don't need to include that because it is the default) and you need text delimiters if the ID is text:

x = DCount("[104_National_ID]", "INITIAL ASSESSMENT", "[104_National_ID] = " & [B][COLOR=red]Chr(34) &[/COLOR][/B] [COLOR=blue][B]Me.National_ID[/B][/COLOR] [B][COLOR=red]& Chr(34)[/COLOR][/B])

 

[afnan_88]

it's working now
i wanna cry
thaaaaaaaaaaaanx all

 

[boblarson]

it's working now
i wanna cry
thaaaaaaaaaaaanx all

Glad we could help you out.

 

[Uncle Gizmo]

Do not use .TEXT! That requires the control have focus.

Well I'll be a monkey’s uncle! I didn’t know that!
I do know that I avoid the “TEXT” suffix in my programming, never had much success with it, now I know why!