CptPicard
asked on
Only 1 ContactID can be added to each table
Hi - I use Access 2010.
I have a form which I use to add data to a table. And I have another table which has all my Contacts Details including a field unique to them called ContactID.
Basically, when a user starts to fill in the form they have to enter their ContactID. If they have already filled in the form before and try entering their ContactID again, it will display a message saying that their ContactID has already been used and so they can't carry on which is great and this is the code I use.
-------------------------- -
if dcount("*","tbl1", "ContactID=" & me.cblContactId) > 0 then
msgbox "Contact ID already exists! I'm sorry but you have already completed this form before"
cancel=true
end if
-------------------------- -
However, I have since added a search form which allows a user to click on a button within the main form which opens up another form called "frmContactsSearch". This form basically has a list box inside which displays all Contacts details (Contact ID, Title, First Name, Last Name, DOB, Telephone). All the user has to do is click on their name, and click a button called 'input to form' which basically puts all their details into the original form. this is the code that sits within the 'on click' event.
-------------------------- ---------- ---------- ----
Private Sub btnInput_Click()
On Error GoTo Err_btnInput_DblClick
Forms("frm1")![cboContactI D] = ListBox.Column(0)
Forms("frm1")![cboTitle] = ListBox.Column(1)
Forms("frm1")![cboFirstNam e] = ListBox.Column(2)
Forms("frm1")![cboLastName ] = ListBox.Column(3)
Forms("frm1")![cboDOB] = ListBox.Column(4)
Forms("frm1")![cboTelephon e] = ListBox.Column(5)
Me.Refresh
DoCmd.Close
If IsNull(ListBox.Value) Then
DoCmd.Close
DoCmd.OpenForm ("frm1")
End If
Exit_btnInput_DblClick:
Exit Sub
Err_btnInput_DblClick:
Resume Exit_btnInput_DblClick
End Sub
-------------------------- ---------- ------
I basically need it to do the same thing as the code at the top that when a user selects their name from this searchform, if when they click the button 'input to form' that ContactID has already been used in the form, it will display a message box informing them that that ContactID has already been used and will not allow them to add it.
I have a form which I use to add data to a table. And I have another table which has all my Contacts Details including a field unique to them called ContactID.
Basically, when a user starts to fill in the form they have to enter their ContactID. If they have already filled in the form before and try entering their ContactID again, it will display a message saying that their ContactID has already been used and so they can't carry on which is great and this is the code I use.
--------------------------
if dcount("*","tbl1", "ContactID=" & me.cblContactId) > 0 then
msgbox "Contact ID already exists! I'm sorry but you have already completed this form before"
cancel=true
end if
--------------------------
However, I have since added a search form which allows a user to click on a button within the main form which opens up another form called "frmContactsSearch". This form basically has a list box inside which displays all Contacts details (Contact ID, Title, First Name, Last Name, DOB, Telephone). All the user has to do is click on their name, and click a button called 'input to form' which basically puts all their details into the original form. this is the code that sits within the 'on click' event.
--------------------------
Private Sub btnInput_Click()
On Error GoTo Err_btnInput_DblClick
Forms("frm1")![cboContactI
Forms("frm1")![cboTitle] = ListBox.Column(1)
Forms("frm1")![cboFirstNam
Forms("frm1")![cboLastName
Forms("frm1")![cboDOB] = ListBox.Column(4)
Forms("frm1")![cboTelephon
Me.Refresh
DoCmd.Close
If IsNull(ListBox.Value) Then
DoCmd.Close
DoCmd.OpenForm ("frm1")
End If
Exit_btnInput_DblClick:
Exit Sub
Err_btnInput_DblClick:
Resume Exit_btnInput_DblClick
End Sub
--------------------------
I basically need it to do the same thing as the code at the top that when a user selects their name from this searchform, if when they click the button 'input to form' that ContactID has already been used in the form, it will display a message box informing them that that ContactID has already been used and will not allow them to add it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"I need to tidy this up"
Just housekeeping, but important!
Wait a second, you have backed up to your original code again, not the listbox code. Are you talking about clearing the combo in the original form?
me.cboContactID = Null
should work. Or are you talking about the listbox?
Just housekeeping, but important!
Wait a second, you have backed up to your original code again, not the listbox code. Are you talking about clearing the combo in the original form?
me.cboContactID = Null
should work. Or are you talking about the listbox?
ASKER
Yes the combobox not the listbox. For some reason when I put
me.cboContactID = Null
within the code, it doesn't clear the value for cboContactID and instead gives me a run-time error '-s147352567 (800200009)':
Also, very annoying it keeps creating blank records. :(
me.cboContactID = Null
within the code, it doesn't clear the value for cboContactID and instead gives me a run-time error '-s147352567 (800200009)':
Also, very annoying it keeps creating blank records. :(
ASKER
Is it because I have this in my afterupdate?
Private Sub cboContactID_AfterUpdate()
Me![cboTitle] = Me![cboContactID].Column(1 )
Me![cboFirstName] = Me![cboContactID].Column(2 )
Me![cboLastName] = Me![cboContactId].Column(3 )
Me![cboDOB] = Me![cboContactID].Column(4 )
Me![cboTelephone] = Me![cboContactID].Column(5 )
Me.Refresh
End Sub
Private Sub cboContactID_AfterUpdate()
Me![cboTitle] = Me![cboContactID].Column(1
Me![cboFirstName] = Me![cboContactID].Column(2
Me![cboLastName] = Me![cboContactId].Column(3
Me![cboDOB] = Me![cboContactID].Column(4
Me![cboTelephone] = Me![cboContactID].Column(5
Me.Refresh
End Sub
Ok, see the problem. You are trying to reset the value of that control in its BeforeUpdate event, which you cannot do.
I generally don't like to use the control BeforeUpdate event for this type of stuff. Instead, move that to the Control AfterUpdate event. Assuming that the form is in Add record (data entry) mode and on a New record then this should work.
Private Sub cboContactID_AfterUpdate
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
MsgBox "Contact ID already exists in that form!"
me.undo 'this would undo the entire record
'me.cboContactID = Null 'This would only set the combo back to NULL
End If
End Sub
I generally don't like to use the control BeforeUpdate event for this type of stuff. Instead, move that to the Control AfterUpdate event. Assuming that the form is in Add record (data entry) mode and on a New record then this should work.
Private Sub cboContactID_AfterUpdate
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
MsgBox "Contact ID already exists in that form!"
me.undo 'this would undo the entire record
'me.cboContactID = Null 'This would only set the combo back to NULL
End If
End Sub
With the other stuff in the AfterUpdate I would simply add an ELSE before the EndIf in my previous code and insert the code you just posted between the ELSE and the End IF
Hopefully, all of those other controls you are filling in are not bound controls. You should not be storing that contact info in multiple locations, just the ContactID should be stored in the recordsource that populates that form.
If you want to display all of the contact info associate with the combo, I'd put a subform on the control, bound to the ContactID, and display the data that is in your Contacts table in that subform.
If you want to display all of the contact info associate with the combo, I'd put a subform on the control, bound to the ContactID, and display the data that is in your Contacts table in that subform.
ASKER
Works fantastically!! Thanks for your time. Will look into adding a subform at a later stage.
ASKER
I've just noticed that after putting this code in the after update for the combo box and when I enter a ContactID and then try removing the ContactID from the box I get an error message saying
Run-time error '3075':
Syntax error (missing operator) in query expression 'ContactID=".
This is the code below.
---------
Private Sub cboContactID_AfterUpdate()
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
MsgBox "This ContactID has already been added!"
Me.Undo
Else
Me![cboTitle] = Me![cboContactID].Column(1 )
Me![cboFirstName] = Me![cboContactID].Column(2 )
Me![cboLastName] = Me![cboContactID].Column(3 )
Me![cboDOB] = Me![cboContactID].Column(4 )
Me![cboTelephone] = Me![cboContactID].Column(5 )
Me.Refresh
End If
End Sub
Run-time error '3075':
Syntax error (missing operator) in query expression 'ContactID=".
This is the code below.
---------
Private Sub cboContactID_AfterUpdate()
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
MsgBox "This ContactID has already been added!"
Me.Undo
Else
Me![cboTitle] = Me![cboContactID].Column(1
Me![cboFirstName] = Me![cboContactID].Column(2
Me![cboLastName] = Me![cboContactID].Column(3
Me![cboDOB] = Me![cboContactID].Column(4
Me![cboTelephone] = Me![cboContactID].Column(5
Me.Refresh
End If
End Sub
What line gets highlighted when that error occurs?
Not sure why you are including the Me.Refresh line in your code?
You only need that to refresh the data that was in the underlying recordsource of your form when it was opened.
Not sure why you are including the Me.Refresh line in your code?
You only need that to refresh the data that was in the underlying recordsource of your form when it was opened.
ASKER
On this line
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
That would normally indicate that the combo is empty. Try changing:
Private Sub cboContactID_AfterUpdate()
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
to
Private Sub cboContactID_AfterUpdate()
if me.cboContactID & "" = "" then Exit Sub
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
this would check to see whether the combo is empty and if so, would exit the AfterUpdate event. Did you get rid of the me.refresh line?
Private Sub cboContactID_AfterUpdate()
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
to
Private Sub cboContactID_AfterUpdate()
if me.cboContactID & "" = "" then Exit Sub
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
this would check to see whether the combo is empty and if so, would exit the AfterUpdate event. Did you get rid of the me.refresh line?
ASKER
Yep I got rid of the me.refresh line. Works now thx.
ASKER
But with first my bit of code (below) I want it to remove the ContactID after you click OK on the message box that pops up so I thought adding 'me.cboContactID.value = Null' would work, but it doesn't. Any ideas? Otherwise the user will have to click on the ESC button on the keyboard each time to clear the data in that field.
---
Private Sub cboContactID_BeforeUpdate(
If DCount("*", "tbl1", "ContactID=" & Me.cboContactID) > 0 Then
MsgBox "Contact ID already exists in that form!"
me.cboContactID.Value = Null
Cancel = True
End If
End Sub
---