Hi,
I have been trying to adapt this code for my database. I'm using MS Access 2007, and most front end users are in 2010.
The name of the underlying table is tblStudents, and the record source is qryMainDataEntry (which links two tables).
The Form is called frmMainDataEntry. The name of the field holding the data is called StudentName and it has the same name in the table and the form (I know it probably shouldn't).
The code I have tried to apply in the Form is this: - Private Sub StudentName_BeforeUpdate(Cancel As Integer)
-
-
If DCount("StudentName]", "tblStudents", "[StudentName]= '" & Me![StudentName] & "'") > 0 Then
-
MsgBox "Name Is Already In Database!"
-
-
End If
-
End Sub
I suspect the code is confused between the tables and forms, and have tried to differentiate them with tblStudents_StudentName and frmMainDataEntry_StudentName - but that isn't doing the trick.
The code in this thread seems to apply to data entry in the table, and maybe that is part of the issue?
I would like this code to just return a message that the student's name already exists in the database as soon as the user moves out of that field, not when the whole form has been completed. I need to allow duplicates.
Thank you in anticipation of your help. I hope I have provided enough clear information.
regards,
Marc
(Original hijacked thread - Want Access to notify me if a record already exists.)
6 2155 NeoPa 32,558
Recognized Expert Moderator MVP
A very well presented question. These must not be added into existing threads though. I've sorted that for you.
Back to the question, which as I say is well presented and that always makes working on them a lot easier for us.
The code looks fundamentally fine to me. It's missing some logic, but should work assuming all the objects it relates to are correctly named. Can you say what actually happens? Just knowing it doesn't do exactly what you expect is not much to work with. What does it do?
Later, we'll sort out setting the Cancel value depending on the response from the user via the MsgBox() call, but let's get the basics sorted first.
NeoPa 32,558
Recognized Expert Moderator MVP
Right, I had to do something earlier so didn't quite finish looking into this.
It seems you were actually missing a "[" character from line #3 of your code. I have redone this for you, using DLookup() instead of DCount(), and expanded the MsgBox() part to do what you need. I guess you'll find this easy enough to review, but let me know if you have any questions. - Private Sub StudentName_BeforeUpdate(Cancel As Integer)
-
Dim strTmp As String
-
-
With Me.StudentName
-
strTmp = Replace("[StudentName]='%N'", "%N", .Value)
-
'If record not found then all is fine. Nothing further to check.
-
If IsNull(DLookup(Expr:="[StudentName]" _
-
, Domain:="[tblStudents]" _
-
, Criteria:=strTmp)) Then Exit Sub
-
strTmp = "Name (%N) already exists in database%L%LOK to continue anyway?"
-
strTmp = Replace(Replace(strTmp, "%N", .Value), "%L", vbNewLine)
-
'If the operator wants to continue in spite of the clash then exit.
-
If MsgBox(Prompt:=strTmp _
-
, Buttons:=vbYesNo Or vbQuestion _
-
, Title:=.Name) = vbNo Then Exit Sub
-
'Cancel this update.
-
Cancel = True
-
End With
-
End Sub
Hi NeoPa,
Thank you so much for your help. I'm glad my question was clear - I was a bit nervous, it's been a while since I have tried to do any code in my db. Thanks for the tip on starting a new question too.
Thank you for the code - it is picking up if a student name is already entered, so the field names are accurate; and does nothing if a new name is entered.
I would like to change the yes/no buttons to just one "OK" button that acknowledges the message and closes the box. The user then can decide whether to look for the duplicate.
I already have a button on the form labelled 'Check for this student' - which pulls up all records with that name combined with the date of birth so that the user can check for history - but they won't do it every time.
I'm not sure how to modify the code to make this happen?
I think I keep everything up to this point, but when I try to edit the buttons part, I start mucking up your good work :): - strTmp = "Name (%N) already exists in database%L%LOK to continue anyway?"
-
strTmp = Replace(Replace(strTmp, "%N", .Value), "%L", vbNewLine)
-
'If the operator wants to continue in spite of the clash then exit.
-
If MsgBox(Prompt:=strTmp _
-
, Buttons:=vbYesNo Or vbQuestion _
-
, Title:=.Name) = vbNo Then Exit Sub
-
'Cancel this update.
-
Cancel = True
-
End With
Thank you again for your time - I am most appreciative of your generosity.
NeoPa 32,558
Recognized Expert Moderator MVP
A couple of small changes then. The return value of the MsgBox() is no longer required and the message doesn't need to prompt the operator to choose. Try this : - Private Sub StudentName_BeforeUpdate(Cancel As Integer)
-
Dim strTmp As String
-
-
With Me.StudentName
-
strTmp = Replace("[StudentName]='%N'", "%N", .Value)
-
'If record not found then all is fine. Nothing further to check.
-
If IsNull(DLookup(Expr:="[StudentName]" _
-
, Domain:="[tblStudents]" _
-
, Criteria:=strTmp)) Then Exit Sub
-
strTmp = Replace("Name (%N) already exists in database", "%N", .Value)
-
'Notify the operator of the clash.
-
Call MsgBox(Prompt:=strTmp _
-
, Buttons:=vbOkOnly Or vbQuestion _
-
, Title:=.Name)
-
End With
-
End Sub
Thanks NeoPa - that is perfect.
Thanks so much for your help and support. I apologise for the delay in getting back to let you know it worked- I got caught up in so many other things!!!!
All the best,
marcella
NeoPa 32,558
Recognized Expert Moderator MVP
No worries :-)
I'm very pleased that was a help.
-Adrian.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: yvan |
last post by:
Approximately once a month, a client of ours sends us a bunch of
comma-delimited text files which I have to clean up and then import
into their MS SQL database. All last week, I was using a Cold Fusion
script to upload the cleaned up files and then import the records they
contained into the database, though obviously, the process took...
|
by: David C. Barber |
last post by:
I'm trying to determine if any matching records exist on a LIKE query
performing a partial match of last names to a remote back-end database in
the most efficient manner possible. LAN Traffic appears to be the
bottleneck. This column is already indexed, and all I'm trying to determine
is whether no records, or at least one record, exists. ...
|
by: kaosyeti |
last post by:
hey.... i have a duplicate record issue i could use some help with. on a
form that has 2 unbound controls, txtboxyear and cboxmonth, together will
automatically fill in an invisible txtboxdate that is bound to a table and
has no duplicates allowed. the problem is that there are 30 or so other
controls here to be filled in and if the user...
|
by: zoro25 |
last post by:
Hi,
I'm pretty new to Access and I'm not too sure how to handle the following.
There is an atual DB in access and I need to add a search tool to it. More exactly, we have a field name and a field description in the main table. I want to create a menu for an user to query this table to find the specific records that contains key words.
...
|
by: NeoPa |
last post by:
I rarely use forms to manage my data so I expect this is easier than I found it (I hate it when OPs say that :D)
I have a simple table (laid out below) with lists of customers for various jobs that have to be run. The PK is a combination of the first two fields ( & ) and I will typically want to maintain the various (logically separate) lists...
| |
by: Lewis Wood |
last post by:
I am self taught and struggling with a particular problem.
I have created a database to record postcard details using Access 2000.
My main screen (POSTCARD_FORM) shows details of the card and allows users to add comments about the postcard eg "the spire on the church is 50 feet high" .
I have two buttons, an ADD_COMMENTS button and a...
|
by: Phil Stanton |
last post by:
I have a form based on a complex query (Lots of tables)
If I delete a record, everything appears to be OK. Get the message "Youa are
about to delete 1 record ....". I say yes. The record count goes down
correctly. Then if I do Records-->Remove Filter/Sort there are the deleted
records back as if they had never been deleted. Same thin exactly...
|
by: Coll |
last post by:
I'm working on a database that someone else created. I have a form -
frm_main that is based on a query that is based on two tables. The
join is such that the first table tbl_JCN has all records included in
the query, but the second table tbl_SOC only has matching records
displayed. On the form (frm_main), there is a combo box - you can
make...
|
by: csolomon |
last post by:
Hello:
I have a form that allows me to add new records. In the onCurrent event, I have:
Private Sub Form_Current()
'Requery Job Number
Me.cbojobNumber.Requery
'Requery DM_Mix Combo
Me.DM_Mix_cbo.Requery
'Requery SF_MixBatch.DM_materialNo
|
by: slenish |
last post by:
Hello,
what i am trying to do is I have a form set up where you enter in 3 types of information a name, errcode and a number. What I want to do is is when you enter in the record and then hit the save button it looks at the table that its saving to and finds out if there is already a matching record, if there is a matching record prompt the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |