Check a Field against a table's field--S/B simple 2

[MossC]

I think this is an easy one but I can't figure it out.

I have a text box where I will enter a value. What I want to do is to check the value against an existing table when a button is clicked. If the value that is entered already exists on the table, I want a message box indicating the value exists and to ask to input a unique value.

I'm sure it's simple but I'm still a novice.

TIA.

 

[Steve101]

Instead of using a text box, consider using a Combo Box, which is one of the other data entry controls available from the Access 'Toolbox' toolbar (use the View, Toolbars, Toolbox menu options, when you're in form design view, to see the toolbox).

When using a combo box, you get to specify the table or query that you wish to be used to control the "pulldown list" that appears in the combo box. In particular, note the RowSourceType, Rowsource, ColumnCount, ColumnWidths, ListWidth and LimitToList properties associated with the combo control.

Hope this helps; much easier than trying to use the text box to validate the entry.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MossC]

Steve,

What I have is a combo box and 2 buttons. One button is "View Existing" and one is "Enter New". If the "Enter New" button is clicked, I want to make sure that it doesn't exist.

The combo box is so that it can be dropped down and then the "View Existing" can be clicked.

I found this code posted by Grumbledook earlier but haven't tried it yet (I changed the field name):

If IsNull([FieldName]) Then
MsgBox "Unique value required!"
DoCmd.CancelEvent
End If

If DCount("FieldName", "Table", "FieldName = '" & Me!FieldName & "'&quot > 0 Then
MsgBox "Duplicate value,insert a unique one"
DoCmd.CancelEvent
End If

I was using the wrong search words earlier and found this now.

Thanks.

 

[MossC]

I changed the code a little to match my field names and here is what I came up with:

If IsNull([LoanNumber]) Then
MsgBox "You must enter a Loan Number"
DoCmd.CancelEvent
End If

If DCount("[LoanNumber]", "tblLoanInfo", "[LoanNumber] = '" & Me![LoanNumber] & "'&quot > 0 Then


MsgBox "Duplicate value,insert a unique one"
DoCmd.CancelEvent
End If

However, the DCount line gives me the following error message:

Data type mismatch in criteria expression.

It's late and I know I'm missing something simple . . .

 

[Steve101]

OK Mossc. I understand. I think I may have given you the wrong advice here re using the combo box. Sorry about that. Stick with the text box, and use the code you specified earlier, as follows:
[tt]
If IsNull([FieldName]) Then
MsgBox "Unique value required!"
DoCmd.CancelEvent
End If
[/tt]
This block of code should be associated with the form's BeforeUpdate event.
[tt]
If DCount("FieldName", "Table", _
"FieldName = '" & Me!FieldName & "'&quot > 0 Then
MsgBox "Duplicate value,insert a unique one"
DoCmd.CancelEvent
End If
[/tt]
This block of code should be associated with the text box's BeforeUpdate

Hope this helps; sorry about the b.. steer,

Regards,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MossC]

Actually, I made a mistake earlier. It is a combo box that has a row source as the LoanNumber field in a table named tblLoanInfo. The "Limit to List" is set to "No" so that I can input a new Loan Number.

I tried the code on the Before Update section but it didn't work. I didn't think it would since I am not updating anything.

I have the code now on the On Click and that is where I am getting the error.

 

[MossC]

This is where I am still getting stuck:

If IsNull([LoanNumber]) Then
MsgBox "You must enter a Loan Number"
DoCmd.CancelEvent
End If

If DCount("[LoanNumber]", "tblLoanInfo", "[LoanNumber] = '" & Me![LoanNumber] & "'&quot > 0 Then

MsgBox "Duplicate value,insert a unique one"
DoCmd.CancelEvent
End If

I get the following error message:

Data type mismatch in criteria expression.

Is it because the name of my combo box and my field name are the same? My problem is that I refer to both of those in several other queries and forms.

 

[MossC]

Is there any way to do this without renaming either my field or my combo box?

Is that even the problem?

I am happy to leave a star for anyone who can help.

Thank you.

 

[Steve101]

Try removing the single quotes from the line:
[tt]
If DCount("[LoanNumber]", "tblLoanInfo", "[LoanNumber] = '" & Me![LoanNumber] & "'&quot > 0 Then
[/tt]
ie. replace this line with:
[tt]
If DCount("[LoanNumber]", "tblLoanInfo", "[LoanNumber] = & Me![LoanNumber]) > 0 Then
[/tt]

It could be that LoanNumber, is defined in the table as a number, not a text field.

the other thing; its not clear from your code in which event(s) you're placing your code. this could be relevant.

Hope this helps,






Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[jamescen]

The single quote is used when you use string types. I think that LoanNumber is of type Integer so the single quotes are not needed. Try this:

If DCount("[LoanNumber]", "tblLoanInfo", "[LoanNumber] = " & Me![loannumber]) > 0 Then

MsgBox "Duplicate value,insert a unique one"
        DoCmd.CancelEvent
End If

 

[WildHare]

While the above thread has its' merits, I am a bit nonplussed at the requirement for the USER to come up with a unique number. Why not let the system GENERATE a number for you? Can you imagine the frustration of trying to come up with a unique number in a database that may have thousands of loan numbers in it?

When the ENTER NEW button is pressed, the system should be able to generate a unique number for you. Doesn't that make more sense?

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

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

 

[Steve101]

Jim,
Quite right; I agree. I guess the "teacher" in me was going along with trying to help the poster achieve what he set out to do; though as you point out, he is doing it hard. Still, at the end of the day, he'll be wiser for the experience, (and for having this pointed out to him),
All the best,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[WildHare]

Indeed. I am of two minds sometimes looking at some of the threads here - torn between answering a question with a technique that may or may not be elucidating (or even correct), or asking the higher level question of "WHY did it get this way in the first place?"

I don't want to appear as a wise-ass (not that I'm NOT, mind you...) but sometimes I think people would be better served if they stepped back and took a different look at their problem. After all, if someone says, "what's the best type of hammer to hit myself in the head with, claw or ball peen?", I'm not so sure that there isn't a more important question to be asked in response.

I teach too (15+ years of comm college) and while it seems standoffish sometimes, I have gradually come to the realization that telling someone "You are asking the wrong question" does more benefit to them in the long run.

See ya!


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

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

 

[MossC]

I've been out all day and haven't had a chance to check all day. I haven't looked at the code yet but I wanted to respond to Jim.

These loan numbers are generated by a Bank and we will be servicing any loans in default. Thus, when the Bank gives us a loan to service, we have to enter it into the database.

 

[MossC]

Steve's code didn't work for me but jamescen's did. Thank you!!

You are a star!