Prevent data being entered in twice (1 Viewer)

[COMP]

Hi, i am trying to find a method where if a authortiy has been chosen from a lookup box and users try to entert it in again in the same form a message will come up saying that "the authortiy has allready been selected".

thanks

 

[neileg]

A bit vague?

Can you explain what you are trying to do? It's certainly possible to prevent duplicates in the same field. It's also possible to test for duplicates in different fields. Depends what you are trying to do.

 

[COMP]

In my database i have a authorities table. Now i have linked that to a form called projects and what i am trying to do is if a user for example chooses an authority from a dropdown list box, and wants to choose another authority aswell that may be involved in that particular project how can i prevent them from choosing the same one or how can i show a message if they acccidently choose the same autortityyto say "The authority chosen has allready been selected".

 

[Access Hero]

Put a unique key on the columns in question and your DBMS will do that for you.

 

[COMP]

the different authorities do have unique keys the primary key but it still lets is select the same authority.

Thanks

 

[Access Hero]

the different authorities do have unique keys the primary key but it still lets is select the same authority.

Thanks

That means that you haven't yet put a unique key on ALL the columns that would prevent a duplicate (aka "same") authority.

 

[The_Doc_Man]

If you have the same authorities twice with different keys, you have a bad situation called denormalization.

Normally, this means that you've got mixed-up fields in the table that don't belong there. But there is another viewpoint that says if a key points to any record as a selector (prime key), that key should uniquely do so. If two PKs refer to the same authority from the same table, your structure is instantly wrong. About half of the nice things Access would do for you behind the scenes become useless if you don't resolve uniqueness issues.

WHY are two authority records with different keys pointing to the same authority? This must not happen. If there is a difference between the records, what is its significance? (These are rhetorical questions.)

 

[HAMMAMABUARQOUB]

Can just put a picture or a screen cap of your form! let us just ave a look at the situation...
if you have 2 combo box for choosing different authorities you need to re-fill the second so it includes no more the selected authority in the first one,,,
if you have only one combo for on project this can be done by the PKs
thanks

 

[COMP]

Right guys scrap that idea it seems difficult to do. What i have done is delete the authority table and created a new one with a tick box next to them. I have created a form on this table and have added it to the project form. Now when i add with a relationship to the project ID none of the authorities come up in the boxes for me to tick. However when i dont have a link they all appear and i can tick the relevant ones. However when i go to another project it doesnt reset the tick boxes. Is their a way of doing this??

 

[neileg]

COMP you are never going to get the best out of this forum by asking questions this way. You need to tell us what you are trying to do in terms of the business process. So far, all you have done is give a very vague idea of what you think the solution is and asked for help to make it work.

From the very limited amount of information you have given, I have a feeling that your second approach is not as good as the first.

 

[COMP]

Ok, right basically what i am trying to find the solution for is: For every project there can be one or more authority involved with that project. i have created a project form which contains a subform with inputting the authorities in from a drop down list. I am trying to find a way where once the user has chosen "authority1" and wants to add another authority from the dropdown list but acccidently selects "authority 1" how can i display a message or something like this to tell the user that authority 1 has allready been selected. Now the authorities are in a table where they have a unique key and have Yes(no duplicates) however when i try to enter in authorities that are different from one another for the same project it will not let me. Is this any more clear??

 

[COMP]

Ok, right what i am trying to find the solution for is: For every project there can be one or more authority involved with that project. i have created a project form which contains a subform with inputting the authorities in from a drop down list. I am trying to find a way where once the user has chosen "authority1" and wants to add another authority from the dropdown list but acccidently selects "authority 1" how can i display a message or something like this to tell the user that authority 1 has allready been selected. Now the authorities are in a table where they have a unique key and have Yes(no duplicates) however when i try to enter in authorities that are different from one another for the same project it will not let me. Is this any more clearer??

 

[DCrake]

I know what you are trying to acheive. So heres my soluition.

When you start with the full list of authorities your rowsource for the combobox will be something along the lines of

Select AuthId, AuthName From TblAuthorities Order By AuthName

So the user selects an authority. Now on the after update event of the combobox have a hidden textbox where you store the AuthId in. So as soon as they select one you refresh the row source with

"Select AuthId, AuthName From TblAuthorities Where AuthID Not In(" & Me.TextBox & ") Order By AuthName"

Your only problem is going to be is when the user select Auth1, Auth3 and then decides it should be Auth2 not Auth3.

What you really need is 2 listboxes one for selected and one for unselected and drag the ones you want into the selected listbox upon which they are removed from the unselected listbox.

There is a demo of exactly that in the sample databases section.

See link

David

 

[COMP]

can i not enter in any visual basic code in the authority field?? something like this:

Dim row As String
row = form_Local_Authorities.Local_Authority_Name.Value
DoCmd.GoToRecord , , acNewRec
If form_Local_Authorities.Local_Authority_Name.Value = row Then
messagebox.show ("Authority allready chosen")
End If

 

[HAMMAMABUARQOUB]

an idea,,,
Add a field to the Authority table call it 'Selected" and its Yes/No
in the after update event of the combo ... make an update SQL to set the selected field to yes where the authory is the combo choosen..
now re fill the combo box with an SQL select statement such that it excludes the Selected authorities,,,
when adding a new record,, reset all the vaues of selected to no in the Authority table!!

but if you insist on the msg thing... just dont do the refel select statemnt, rather with the afterupdate event make an If statemne to ask about the value of the Selected field, if its yes then display the msg, elese do what's next .. but note you need to refresh the combo to display the lates updates on the values...
that's all

 

[neileg]

You're storing the authorities linked to your project record by a junction table, I presume since you have a many to many relationship between projects and authorities. In that case, you can add a compound index to the projectID and the authorityID and set it to No Duplicates. That's all you need.

 

[COMP]

Hi Neileg thanks for that, how do i go about adding a compound index to the project ID and Authority ID??

 

[Niroth]

Ok, right basically what i am trying to find the solution for is: For every project there can be one or more authority involved with that project. i have created a project form which contains a subform with inputting the authorities in from a drop down list. I am trying to find a way where once the user has chosen "authority1" and wants to add another authority from the dropdown list but acccidently selects "authority 1" how can i display a message or something like this to tell the user that authority 1 has allready been selected. Now the authorities are in a table where they have a unique key and have Yes(no duplicates) however when i try to enter in authorities that are different from one another for the same project it will not let me. Is this any more clear??

This is what I would do but I don't know if it's good practice or not:

count the record in the table where you store the user and their assigned authority, using these criterias user=me.user (or userID) and authority = me.authority (or authorityID).

with beforeupdate event

if count>0 then
me.authority = null
msgbox "This user has chosen this authority, please select a different authority."
end if

Something like that.

 

[neileg]

To set a compound index:
Open the table in design view
Click the Index button (looks like a ruler and a lightning flash)
Enter a name on the first line/first colum (Fred or anything you like)
In the second column select the first field
On the second row/second column select the second field. Leave the second row first column blank
click back on the first row and set the property Unique to Yes in the box at the bottom