i have created a function to input data from four textboxes into a table. how do i prevent duplicate records of the same mth/Yr into the table? editing of the data in the table with reference to the mt/Yr is allowed, but how to prevent duplicate records? -
Public Sub PutInMonthlyRecords()
-
-
Dim sql As String
-
Dim Db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim f As Form
-
Dim MthYr As String
-
-
Set f = Forms!frmQpi
-
-
MthYr = f("txtMthYr")
-
-
sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
-
-
Set Db = CurrentDb()
-
Set rs = Db.OpenRecordset(sql)
-
-
-
If rs.RecordCount = 0 Then
-
If (f!txtTotalPF) = 0 Then
-
Exit Sub
-
End If
-
-
rs.AddNew
-
rs![Input MthYr] = f("txtMthYr")
-
rs![Monthly TotalPF] = f("txtTotalPF")
-
rs![Monthly Rejection] = f("txtTotalAvoid")
-
rs![Monthly TotalAvoid] = f("txtRejection")
-
-
rs.Update
-
-
Else
-
If (f!txtTotalPF) = 0 Then
-
rs.Delete
-
-
Exit Sub
-
End If
-
-
-
rs.Delete
-
rs.AddNew
-
rs![Input MthYr] = f("txtMthYr")
-
rs![Monthly TotalPF] = f("txtTotalPF")
-
rs![Monthly Rejection] = f("txtTotalAvoid")
-
rs![Monthly TotalAvoid] = f("txtRejection")
-
-
rs.Update
-
-
End If
-
-
-
Db.Close
-
-
11 5328 ADezii 8,834
Recognized Expert Expert
i have created a function to input data from four textboxes into a table. how do i prevent duplicate records of the same mth/Yr into the table? editing of the data in the table with reference to the mt/Yr is allowed, but how to prevent duplicate records? -
Public Sub PutInMonthlyRecords()
-
-
Dim sql As String
-
Dim Db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim f As Form
-
Dim MthYr As String
-
-
Set f = Forms!frmQpi
-
-
MthYr = f("txtMthYr")
-
-
sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
-
-
Set Db = CurrentDb()
-
Set rs = Db.OpenRecordset(sql)
-
-
-
If rs.RecordCount = 0 Then
-
If (f!txtTotalPF) = 0 Then
-
Exit Sub
-
End If
-
-
rs.AddNew
-
rs![Input MthYr] = f("txtMthYr")
-
rs![Monthly TotalPF] = f("txtTotalPF")
-
rs![Monthly Rejection] = f("txtTotalAvoid")
-
rs![Monthly TotalAvoid] = f("txtRejection")
-
-
rs.Update
-
-
Else
-
If (f!txtTotalPF) = 0 Then
-
rs.Delete
-
-
Exit Sub
-
End If
-
-
-
rs.Delete
-
rs.AddNew
-
rs![Input MthYr] = f("txtMthYr")
-
rs![Monthly TotalPF] = f("txtTotalPF")
-
rs![Monthly Rejection] = f("txtTotalAvoid")
-
rs![Monthly TotalAvoid] = f("txtRejection")
-
-
rs.Update
-
-
End If
-
-
-
Db.Close
-
-
Since you are allowing editing at the Table level, I feel as though your best option may be : in tblOptMonthly, set the Indexed property of the [Input MthY] Field to Indexed = Yes(No Duplicates). In the PutInMonthlyRec ords() Routine, trap the specific Duplicate Record Error that will now be generated if you try to add a Duplicate Record on the [Input MthY] Field via VCBA code, and Exit the Sub-Routine. The Record should not be Appended since rs.Update will not be successful in this scenario.
You are now covered in both situations:
1) Editing a Record and creating a Duplicate Record based on the [Input MthY] Field.
2) Adding a Record via the Sub-Routinre and creating a Duplicate Record on the [Input MthY] Field.
NeoPa 32,563
Recognized Expert Moderator MVP
Nice answer ADezii.
Ariel,
If you feel that any part of this resolution is too complicated for you to implement - don't worry, just post back explaining which parts you find complicated and ADezii (or someone else even if he's too busy) will be able to help you through it.
Error handling is a very important part of VBA code but some people avoid using it. Don't let that frighten you off this solution.
Best of luck.
MODERATOR.
Nice answer ADezii.
Ariel,
If you feel that any part of this resolution is too complicated for you to implement - don't worry, just post back explaining which parts you find complicated and ADezii (or someone else even if he's too busy) will be able to help you through it.
Error handling is a very important part of VBA code but some people avoid using it. Don't let that frighten you off this solution.
Best of luck.
MODERATOR.
thank you. i am new to programming...i don't understand a single thing. any similiar sample programs where i can get ideas from?
NeoPa 32,563
Recognized Expert Moderator MVP
Ariel,
Please reread my earlier post. The wording was not random.
Unless I/we have a particular point to help with, we are looking at saying that the answer is not good enaough and starting again.
That is certainly not the case here, and you have a choice to use it or not. We can help you but I am certainly not prepared to look elsewhere until I'm sure you've given the answer a fair try. As I say, we can help with that. I am certainly not offering to do work for you, simply to help you do the work yourself.
MODERATOR.
From the original post... - Set f = Forms!frmQpi
-
MthYr = f("txtMthYr")
-
sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
-
Can someone please explain this to me? Some of the syntax used in this routine is unfamiliar to me, so don't think that I'm telling you anything is wrong - I just didn't quite follow it.
The main problem I have is with the underlined sections. The way I read it, this is supposed to be matching on a month and year value found in textbox txtMthYr, but is in fact searching for the literal string "MthYr".
Am I misreading this?
It took me a while to realise something. In hindsight, if the WHERE clause is wrong, this might never find a match, and hence give rise to the whole issue of creating duplicates.
here is the function which i re-edited. the codes that were not affecting the program have been deleted. -
Public Sub PutInMonthlyRecords()
-
-
Dim sql As String
-
Dim Db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim f As Form
-
-
-
Set f = Forms!frmQpi
-
Set Db = CurrentDb()
-
-
-
sql = "SELECT * FROM [tblQpiMonthly];"
-
-
Set rs = Db.OpenRecordset(sql)
-
-
If (f!txtTotalPF) = 0 Then
-
Exit Sub
-
End If
-
-
If (f!txtTotalPF) = 0 Then
-
Exit Sub
-
End If
-
-
If (f!txtTotalPF) = 0 Then
-
Exit Sub
-
End If
-
-
rs.AddNew
-
-
rs![Input MthYr] = f("txtMthYr")
-
rs![Monthly TotalPF] = f("txtTotalPF")
-
rs![Monthly Rejection] = f("txtTotalAvoid")
-
rs![Monthly TotalAvoid] = f("txtRejection")
-
-
rs.Update
-
-
-
Db.Close
-
-
End Sub
-
Basically, when the record is not found in the table, it will add the new record. if the record is found in the table, it should edit the record instead of adding the same record into the table.
the form looks this way (see attachment0
the form looks this way (see attachment)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Catherine Jo Morgan |
last post by:
Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?
Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database?...
|
by: ms |
last post by:
Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and
BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since
records to be...
|
by: Carroll |
last post by:
I'm looking for a way in SQL to find duplicate records in a single
table, that are the same based on 3 columns, regardless of what is in
the other columns in the duplicate records. I would like to keep both
records (or it could be more than 2 as well) where duplicate records
are found. Also, I am interested in selecting all columns from the...
|
by: B.N.Prabhu |
last post by:
Hi,
I have a DataTable with several rows. Its having 20 Columns. when i click
the Insert button then i have to check the Database Rows. Whether these new
rows are already available in the Database. If its there, then i need to
seperate the Duplicate Records
Based upon 4 columns(EmployeeID, ProjectName, ProjectType, StartTime --
should be...
|
by: nethravathy |
last post by:
Hi,
The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not.
I tried with following query
1)SELECT elcbtripselect.ELCBTRIP_voltsMIN, elcbtripselect.ELCBTRIP_voltsMAX, elcbtripselect.ELCBTrip_is_partwinding, elcbtripselect.ELCBTrip_is_ydelta,...
| |
by: teser3 |
last post by:
I have my PHP inserting into Oracle 9i.
But how do I prevent duplicate record entries?
I only have 3 fields in the insert in the action page:
CODE
<?php
$c=OCILogon("scott", "tiger", "orcl");
if ( ! $c ) {
echo "Unable to connect: " . var_dump( OCIError() );
|
by: jbrumbau |
last post by:
Hello,
I have been successfully using a database I've created for several months to populate an equipment list for a project we've been working on. However, the form has recently stopped working for some unknown reason. I've been having a problem recently where one table (Project Info) keeps auto-incrementing when I try to create a new record...
|
by: nomvula |
last post by:
hi guys
i need some help to duplicate records on my form datasheet:
here's the example of my form results:
ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual
UJ 18-Apr-08 01-Mar-08 Fees: Asset 1 R 31,200.00 R 31,200.00
NMBM 22-Apr-08 23-Mar-08 P-MI (E) 07/2006 3 R 47,485 R 38,849
i have 200 records deplayed...
|
by: xraive |
last post by:
I have a problem with this. Currently I am trying Allen's code and i am not successful.
Current Design
Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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...
| |