how to prompt user if he already has a record with the same selection in listbox?

[xwnoob]

I would like to prompt the user whenever he adds a new record in my form.

This form has a listbox which users must make a selection.It also has other fields that the user must enter. However, i would like to implement a prompt in the form whereby in the next Add record if the user makes the same selection in the listbox again, he will get a msgbox.

 

[vbaInet]

Why don't you remove the already used item from the listbox instead?

 

[missinglinq]

Replacing ListBoxName with the actual name of your Listbox, and doing the same for "TableFieldName" (using the actual Field Name that holds the ListBox Value) and, of course, "TableName" with the actual Table Name that stores the Data:

Private Sub ListBoxName_BeforeUpdate(Cancel As Integer)

Dim Resp As Integer
 
 If DCount("TableFieldName", "TableName", "TableFieldName = '" & Me.ListBoxName & "'") > 0 Then
  Resp = MsgBox("This Option Has Already Been Selected! Do You Wish to Select It Again?", vbYesNo + vbDefaultButton2, "Duplicate Selection Has Been Made")
   If Resp = vbNo Then
    Cancel = True
   End If
 End If
 
End Sub

Note also that the Multi Select Property for the ListBox has to be set to No.

Why don't you remove the already used item from the listbox instead?

The OP may want the user to be able to make the same selection again, as long as they are made aware that they are making a duplicate selection.

Linq ;0)>

 

[vbaInet]

Makes sense. I would imagine there would be a Date element to this check too.

 

[missinglinq]

I think it was Minolta ads that used to say "The Possibilities are Endless!

 

[vbaInet]

I was expecting to see all 1001 possibilities missinglinq.

 

[xwnoob]

Hi, i have tried the code and i have encountered error '3464'....i have check the expressions and i cant find any error in the spelling...here is your code modified with my tables:

Private Sub ListBorrowerRD_BeforeUpdate(Cancel As Integer)

Dim Resp As Integer

If DCount("BorrowerSerialNoFK", "tblRolesAndDocumentation", "BorrowerSerialNoFK = '" & Me.ListBorrowerRD & "'") > 0 Then
Resp = MsgBox("This Option Has Already Been Selected! Do You Wish to Select It Again?", vbYesNo + vbDefaultButton2, "Duplicate Selection Has Been Made")
If Resp = vbNo Then
Cancel = True
End If
End If

End Sub

Ms access highlighted the line in red...

 

[vbaInet]

xwnoob, we need to know the error description, not just the error code.

 

[missinglinq]

Actually, on my list Error Code 3464 is "No error!"

Is BorrowerSerialNoFK defined as a Number or as Text? The code I gave you is for Text, and if it is a Number the syntax would be slightly different.

Linq ;0)>

 

[vbaInet]

Actually, on my list Error Code 3464 is "No error!"

In case you didn't know (which I'm sure you do) but just fyi, there's a way of checking:

?accesserror(3464)
Data type mismatch in criteria expression.

 

[missinglinq]

I would have expected a Error 13 Datatype Mismatch to be thrown up by that, and in fact actually did suspect that this was the problem, hence my comments in Post # 9.

So, the line

If DCount("BorrowerSerialNoFK", "tblRolesAndDocumentation", "BorrowerSerialNoFK = '" & Me.ListBorrowerRD & "'") > 0

needs to be changed to

If DCount("BorrowerSerialNoFK", "tblRolesAndDocumentation", "BorrowerSerialNoFK = " & Me.ListBorrowerRD) > 0

and all should be right with the world...except the $%^&* Presidential race thing, of course!

Linq ;0)>

 

[vbaInet]

I would have expected a Error 13 Datatype Mismatch to be thrown up by that, and in fact actually did suspect that this was the problem, hence my comments in Post # 9.

That one is a Type Mismatch error, whereas 3464 error is a Data Type mismatch error in criteria. An example of 13 would be when you issue a String argument to a Number argument in a function call. But 3464 in this case is more explicit in the sense that it tells you it couldn't pass the String data type to the Number data type in the recordset within the criteria. So it would appear that none of the domain aggregate functions will ever throw error 13. You were right in your post.

and all should be right with the world...except the $%^&* Presidential race thing, of course!

Linq ;0)>

I'm not following the elections at the moment. It looks like it's not going your way.

 

[missinglinq]

You misunderstand! It's not about the way the "elections are going," it's about the idiocy involved in selecting a candidate! This time, of course, it's the Republicans, but the Democrats have done the same thing in the past!

In one of the "Spenser for Hire" novels Spenser is being interviewed with the idea of hiring him as security consultant for a Congressional campaign. When asked it he has any problems with the Senator's politics he replies, "I have problems with everybody's politics!" That pretty much sums up my feelings!

Linq ;0)>