Modules & VBA :: Afterupdate To Find And Goto Duplicate Values
Apr 10, 2015
Someone fills in a new patient into the database, and the 'chipsoftnummer' which is the number in another database. That number is unique, so i want to have it where if someone fills in a number that already exists in the database the afterupdate event will open that record in the form.Here's the code i put in the input textbox update field:
private Sub chipsoftnummer_AfterUpdate()
Dim NewCHIP As Integer
Dim stLinkCriteria As Integer
Dim custNo As Integer
[code]...
It doesn't work, simple things like hello world do work so VBA is enabled.
I've attached the corresponding part of the database (took out all non-relevant fields and tabs) .
Below is the code I have created to calculate the values "CalcValue" and "CalcWork". The values for AssignValue, AssignPriority, Complex, Effort, Goal are all assigned by the selection made in the referred to/related combo boxes. However, the caluclated values return the concatonation of the intergers and not the sum of the integers. Additionally, if I am to use the CDec() function how do I specify the number of decimal places?
what am I missing here?
thanks!
Private Sub cboAssignPriority_AfterUpdate()
Dim CalcValue, AssignValue, AssignPriority, Complex, Effort, Goal, CalcWork As Integer
I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.
In addition to this I have some questions:
I have to find the duplicates of values in an array. Here is the code that I use.
Code: Sub FindDuplicates() Dim I As Integer, J As Integer, IEND As Integer, text() As String ReDim text(IEND) For I = 1 To IEND - 1 For J = I + 1 To IEND If text(I) = text(J) Then text(J) = "" Next J Next I End Sub
It works but is not performing well. Are there more efficient ways of doing this?
I have this Sheet1 which is a manual input, I need to copy the values from Sheet 1 to Sheet 3, then I have Sheet 2 where there is data auto generated, How can I vlookup values copied from Sheet 1 and find it in Sheet2 ??? Is it possible using the vba code?
I should find the values copied from Sheet 1 in Sheet 2 and copy the values seen to sheet 3?
I have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT COLUMN in UPDATED HEADCOUNT - EMPID FORM CELL user will input an EMPID - newEMPID FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
Is there a way to search for a term within a form and goto a record that matches that term assuming there was only one matching result (Unique ID, for example).
But NOT filter the results so that user can still navigate as usual after the search has been carried out.
I have an afterupdate event that is supposed to send an email when a box is updated. Most of the times the code works fine except for one case.
The code checks the value of a field in subform in another tab of the same form
Code: 'find the MSM date [Forms]![Project Main]![Marketing Specialist Main]![MSMDate].SetFocus MSMDate = Nz([Forms]![Project Main]![Marketing Specialist Main]![MSMDate].value, "__-___-__")
This field (MSMDate) keeps its value in a separate table.
The problem comes when there is no value in this other table for the given project. If this is the case, the subform does not even appear on the main form (it is all a grey box) and the Setfocus returns an error.
I tried to comment the setfocus line to see what the NZ function would do in the next line and it also returns an error.
It is almost as if the subform was not "initialized" for that particular record...
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
I have a form with (among others) a Y/N field and a multivalued field. The Y/N field indicates whether or not the resident is year-round or not (seasonal/snowbird). The multivalued field contains the months the resident it here. The user wants me to auto-select all months in the multivalued vield when the year-round field is set to yes in order to minimize data entry.
I have been trying to use an AfterUpdate Event using the following syntaxes as a test but Access doesn't like the format at all:
[Master Table].[Months Available].[Value] = "June" (this gives 2465 error) Me ! [Months Available].[Value] = "June" (this gives a 424 error)
I'm not at all fluent with VBA but have been successful in populating form fields using data fields in another table so thought this would be similar.
Any way other than not to use multivalued fields? Because that isn't an option.
I have 33,099 records in a query, that i'm importing into a table. (don't bother witht he semantics, it's from a linked dbf file) The table does not have a primary key. Given Three Fields (out of 74):
Item_ID Title AltTitle
With the table populated with all the records, I highlighted those three fields in Design View of the table, and told told access to make all three of them the Primary key. Upon attempting to save the table, I got an error message saying that data in the table violated the primary key unique fields rule or what not. So I wanted to make a query to determine where the error occurred. I could not off the top of my head figure out how to select only the duplicated records in a table, so instead, i figured if they violated the Primary Key unique field rule, there should be duplicate entries. so I did this:
select distinctrow item_id, title, alttitle from tbl_Table;
I got 33, 099 records returned on the DISTINCTROW. Strange as that was, I deleted all the records from the table, set the primary key as I wanted it, and then repopulated the table via my sql insert into commands. This time the table reports only 33,093 records, meaning 6 records somehow violate the primary key unique index, but don't violate a DISTINCTROW call. How can i find them to determine how they are violating the primary key unique index? thanks Jaeden "Sifo Dyas" al'Raec Ruiner
Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)
Bank Account # "=" Fund #
For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that
Bank Account #.Currency = Fund #.General Ledger #
Sorry, if that's kinda cryptic, but here's an example:
Bank Account - ABCD123 Currencies operated in - CAD, USD Fund # - F30
Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.
Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.
Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).
Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.
There is an issue that I cannot resolve in MS Access:
I have a large files with multiple columns that has 2 columns that randomly contain duplicated data.
I would like to filter out the duplicated entries in these 2 columns without spoiling the data integrity - and so the duplicates appear on the same row.
I think what I want it pretty much like conditional formatting in excel, but unfortunately the file size is really large to be filtered in excel.
I have a many to many relationship, and one of my tables (the junction table) has only 2 fields (BookID & AuthorID). These fields together are set as primary key, in order to avoid duplicate records. Eventually, if i will try to put a duplicate record, i will get Access error's message "The changes you requested to the table were not successful because they would create duplicate values in the index, primarykey or relationship ..."
What is the best approach in order to check whether a duplicate record is going to be created, and if yes, i will get a customised error message rather than the above mentioned default error message ?
I need help creating a report that summarizes duplicated values. I need to write a query that groups payments of equal amounts (duplicate values only) for each vendor together. I need to show a group header containing the vendor number and name. I was using the Having Count(*) > 1 function but that only tells me which values are duplicates It doesn't display them. I need to display the duplicates.
i have a sub form which on the click of a button sets 4 fields
Private Sub Command8_Click() LastPaymentDate.Value = Date NextPaymentDate.Value = Date + 7 AmountPaid.Value = [Form_Frm_Customer]![Payments] [Form_Frm_Customer]![Payments Due By].Value = Date + 7 End Sub
It updates all the fields fine but i keep getting the error 'the changes you requested were not successful because they would create duplicate values'
I have checked the table and all fields are set to allow duplicates.
I have a form in a database for our products (doors). I have a button that duplicates one of these records (all new products are based on standard template records).
When this record is created a new product number is produced by the system which was the number of the original (template) record plus the customer enquiry number.
I want to be able to warn the user if they are trying to save this newly created record with a product number that already exists so that they can alter the number to be unique. It will also warn them if they re-number an existing record to another already existing product number. Since the field is defined as unique, at present the system says absolutely nothing and just doesn't save the newly created record which seems is a touch annoying!
I have tried several things with dlookup, dcount arguments but can't seem to get anything to work.
Hopefully someone here can point me in the right direction?
