check for validity of entry

[sades1]

hi,

how can i check if a value entered for a field by the user already exists on a table?
i have a field 'susbcription number' and want to check if the subscription number exists when entered by the user
and then show an error message if it doesn't......

tnx,
SaDeS

 

[Bry12345]

I'd design a small form with an unbound text box into which to enter the subscription number. Pass the value entered to a variable.

Then (probably using a command button), use DCount to count the records using the variable from the text box as criteria. You can then use If, Then Logic to display the appropriate message box.

If you are trying to test for a value already entered on an existing form, you'll have to modify the DCount criteria to use this value.

I have sample code if you need it.

- - - -

Bry

 

[sades1]

tnx,

yes please,.....the sample code would help a lot!!

SaDeS

 

[WildHare]

If you want to verify that the number entered DOES exist, I'd take the possibility of error out of the hands of the user, and give them a combo box of the valid numbers to pick from instead. Eliminates any possibility of error that way....

NEVER tell a user they entered an invalid number. GIVE THEM A LIST of the valid numbers instead. User Interface rule #26A....

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[sades1]

hey im still having trouble figuring out the code to see if the fileds entered exists...please gimme some sample code someone!!

 

[WildHare]

There is no sample code needed. Follow these steps:

1) write a query that retrieves the subscription numbers, sorted anyway you want
2) place a combo box on the form. Use the wizard, and select the Option "Find a record on my form based on the value I pick from the combo box"
3) Set the c/b recordsource to your new query.
4) set C/B properties of LIMIT TO LIST and AUTO EXPAND to YES
5) Sit back and enjoy....

When you drop down the list, as the user enters numbers, the combo will automatically jump to the values, e.g. if the number is "21849", as soon as the user enters "2", the combo will jump down to the first entry beginning with "2", and as they type the "1", it will jump to the first entry in the "2s" beginning with "21" and so on...Easy as cake.


There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Bry12345]

The Hare's way will work great . . .but just in case you want to do it the hard way, here you go. This checks the field 'Barcode' for the presence of the value passed to strProposedBarcode, usually from an unbound field on a form

Private Sub . . . .

Dim intX As Integer
Dim strProposedBarcode As String

On Error . . .etc

intX = DCount("[Barcode]", "tblMain", "[Barcode] = strProposedBarcode&quot

If intX = 0 Then

'Barcode does not exist . . do whatever
Else

'Barcode exists . . do whatever

End If

End Sub


WildHare - 26A?????? - - - -

Bry

 

[sades1]

thanks a lot bry