I have a table in which I must enter informations about companies (Denomination, address, telephone...). I would create a field called Denomination, in which to enter the names of the companies. I need to be able to control and avoid the fact that a name could be entered more than once, for mistake, BUT, I do would like to have the possibility to admit exeptions, this is because it can happen that two different companies have the same name. Does anyone knows if there is the a way to allow to violate the UNIQUE constraint, once imposed, or if there are other ways to implement this idea?
Thx a lot.
p.s. What I write could be not clear, I am not mothertongue, and I am an Access newbie too.
you can't violate the unique constraint, what you will need to do is remove this constraint and control the allowance of duplicate values in VBA - for example in the form before update event you might have something like
Code:
dim rst as recordset
set rst = currentdb.openrecordset("SELECT * FROM myTable WHERE myField=" & myForm.myField)
if not rst.EOF then 'there is a duplicate
if msgbox("this name already exists, do you still want to save it?",vbyesnocancel,"duplicate")<>vbyes then
cancel=true
end if
end if
set rst=nothing
Hi CJ_London thank you, really.
You helped me to understand that I cannot violate a constraint, and that if I knew VBA I would had a powerfull instrument to control the DBMS. Currently I'm studying SQL syntax, but I'd like to learn VBA too, can you tell me which level of knowledge I have to achieve before to be able to understand, and then use the code you sent me? I like to use Access.
Thx.
There is no 'level' to reach as such, just start. You'll need to learn the language requirements but the vba editor is pretty good at providing warnings - I've been using it for many years but still keep learning new things and better ways of achieving the same result.
The thing to be is organised in your approach so I would work in the following manner:
1. think of a task which needs to be done
2. define and write down the task as a series of steps or actions in the order in which they need to be completed - perhaps use a flow diagram
3. research the keywords online for help in learning what the vba keywords/functions are
4. write the code for each step and test each stage before proceding to the next one
the truth is if both Company A and Company B can both have denomination X, then these are different denominations = and you should use a multi-field index consisting of BOTH COMPANY and DENOMINATION, to provide the constraint.
This begs the question whether the same value for the attribute DENOMINATION in both tables actually means the same thing - but that is a different question - although depending on the answer, I would possibly have a lookup table for the valid denominations.
Open your form in design view
display the properties for the form if not already showing (assuming you are using 2007 or later this is an option on the ribbon under the design tab) or right click on the black square here
in the properites window, select events -
about 5 lines down you will see 'before update'
select the line then click on the ... carat to the right and select code and the vba window will open to display this
Then copy and paste the code I provided between these two lines
The code I provided is 'pseudo code' which means I have made up names so you need to change them to what you require.
so in the below code, you need to change the bits highlighted in red to the names you are actually using in your table and the bit in green to the name of the control on your form - so for the form if you type 'me.' the autocomplete will come on and you can then select it from the list
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
dim rst as recordset
set rst = currentdb.openrecordset("SELECT * FROM [COLOR=red]myTable[/COLOR] WHERE [COLOR=red]myField[/COLOR]=" & [COLOR=green]myForm.myField[/COLOR])
if not rst.EOF then 'there is a duplicate
if msgbox("this name already exists, do you still want to save it?",vbyesnocancel,"duplicate")<>vbyes then
cancel=true
end if
end if
set rst=nothing
End Sub
You'll note that although I have provided the example all in lowercase, you now have what is called camel style - e.g. OpenRecordset
This is good because you can type all in lower case and Access will convert to the camel style if there is a match which is a good visual check that the names are correct - so for example if you typed openrcordset, this is not recognised (it's missing an e) so would stay lowercase.
Finally, because this is pseudo code it may not be complete. From your original post, you say the field denomination is a name, which implies it is text rather than a number so we need to make a small change to the code
WHERE myField=" & myForm.myField
works for numbers, but for text you need to change to
WHERE myField='" & myForm.myField & "'"
basically surrounding the target with single quotes - if you are learning SQL you will know this already.
For testing and debugging learn how to use the following
debug.print
use of the immediate window
use of breakpoints
One other thing, which you will only learn from experience is you need to allow for the strange things users will do - for example, not enter a denomination - do you want to save a record without a denomination? If yes then fine, if not, then you need to add more to the code above, or set allows zero length strings to No in your table design.
And one other thing to consider is whether a user can enter a single quotation mark in the denomination e.g. St'Marks
if so, you'll also need to allow for that - which is to replace the single quote with two single quotes which is easy to do
Ok, I'have read as much carefully as I can all your tip, and I have a pair of things to ask you:
- are the red myField and the green myField the same word? (I hope).
- (silly) do I have to search info about debugging on the web? (I guess).
maybe I don't understand what you mean by "the name of the control on your form", so I am not sure about what I want to overwrite there. I confirm that the field Denomination is text.
) The red myfield is the name of the field in your table - probably 'Denomination'. the green myfield is the name of the control on your form - if you have used the form wizard to create your form it will have the same name.
As with all these things - try it and see what happens
- (silly) do I have to search info about debugging on the web? (I guess).
All forms contain controls - there are many - textbox, listbox, combobox among them. If you look at their properties you will see the following
under the 'other' tab, name - this is the name of your control and is referenced as me.nameofcontrol (using a dot)
under the data tab, control source - this is the name of the field from your recordsouce that populates the control - often it is the same name as the name of your control and is referenced as me!nameoffield - using a bang (!)
When access creates a form for you, these names are the same which can be confusing. What some people do to reduce the confusion is to rename the control (under the other tab) by preceding the name with the type of control - e.g. txt, lst, cbo - so you might have txtDenomination for example. But this is a personal preference
you need to ensure you have the quotation marks correct and better not to have spaces in table names and fields (same as SQL) but if you want spaces you must also use square brackets (same as SQL)
OpenRecordset("SELECT * FROM [Anagrafica Aziende] WHERE Denominazione='" & me.[AggiungiAzienda.Denominazione] & "'")
I've done an error when describing you the situation. I try to explain: the field Denominazione is not indexed, but I would to index it, by imposing Yes, and adding duplicates not allowed, unfortunately, when I try to select it the DBMS doesn't allow me to.
. You'll need to learn the language requirements but the vba editor is pretty good at providing warnings - I've been using it for many years but still keep learning new things and better ways of achieving the same result.
