I need to be able to assign a title to a set of dependents that is not what they are normally called. As in, every dependent is either called "Dependent" or "Step Child", but I need to call them "Child1" "Child2" "Child3" etc, for however many children an employee has.
I have a function that does this, however, I need the oldest child to be Child1 and so on, but every time I add an ORDER BY clause to my sql, it duplicates the records. I confirmed this by adding the child's name to the end, and instead of this:
Child1 Martha (eldest)
Child2 Edward
I got:
Child1 Martha
Child2 Martha
Child3 Edward
Child4 Edward
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
I created 1 query from all 3 tables, then created the form from that 1 query.Now when I enter data into the form and click to save it , it produces this error message:The Microsoft Database Engine cannot find a record in the tblGas with key matching fields 'tblMain_GasID.The tblGas does contain a field GasID, but does it mean that the field should be tblMain_GasID
The expression On Click you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name
Now this is the error message that I am constantly getting from any command button I hit on a certain form. Here is the code of the form.
Option Compare Database Option Explicit Public inputCSV As String, ORG As String
I changed the names of the buttons, reconstructed the code under those names, went to the modules and changed names, made sure that a sub o function name is not duplicated in the project... But helas the error is still there. It used to work and suddenly does not work.
My report (rptBilling_STS_Summary) has three subreports (rptBilling_STS_Summary_Install, rptBilling_STS_Summary_Rental, rptBilling_STS_Summary_LDRate) that return values that are grouped by customer and calculates a total for each customer.
I need the report to export to excel for our client but every attempt has produced a blank XLS file. I have tried every export method I can think of. This is what I have tried:
Export button from external data ribbon Export from print preview Export via macro Export via VBA (DoCmd.OutputTo acOutputReport, "rptBilling_STS_Summary", acFormatXLS, , False, , , acExportQualityPrint)
All this has produced the same blank excel file... Very frustrating...
I have searched and found a lot of information on 2007 and it requiring sp2 but all I can find on 2010 is instructional information.
Update: I copied the database to my local PC and when I export the XLS file it opens in protected view.
I have a two part problem right now. I have a Table Labeled "MAIN" where I have three fields that I would like to pull from, lets call them solution, version and testcasename.
Solution and the application name in which there will be some duplicating and also the version also when it pertains to the testcasename for example:
I created a query(SplashSort) to pull just the information stated above.
Under Solution I am trying to apply the string;
Select (Main.ID), (Main.solution) FROM (Main) GROUP BY (Solution) ORDER BY (Solution) Select (Main.ID), (Main.Version) FROM (Main) Group BY (Version) ORDER BY (Version) Select (Main.ID), (Main.TestCaseName) FROM (Main) Group BY (TestCaseName) ORDER BY (TestCaseName)
My report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
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 am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
I am trying to put in a control measure which won't allow the user to accidentally add a client to areferral more than once. The simplified relationship structure reads:
I am as a far as using a dlookup which correctly gives the error message, but only if it is the first client added to the tblClientReferral table. If the hypothetical situation arises in which the user adds a client, then adds a second client to the referral and accidentally adds the second clientn twice, the Dlookup is not picking this up.
I suppose the solution is using a dlookup which has the criteria saying if the referralID = 'this' AND the clientID = 'this' Then 'do this'. But as far as I am aware you cannt use AND in the criteria?? atleast, it hasn't worked when I tried. A snippet of the vba code used is below:
Dim objClient As Object Set objClient = Forms![frmAddClient]![cboClientID]
If (DLookup("clientID", "tblClientReferral", _ "referralID = Forms![frmReferral]![referralID]")) = objClient Then MsgBox "Client already added" GoTo ExitSub Else
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
If there a way i can remove duplicates from an email list? I pull these emails from a recordset. But i email may appear more than once and it doesn't look good e.g
I'd like to remove the duplicated email, if its possible. Code below.
Code: Set rs = CurrentDb.OpenRecordset("select * from query") With objMailItem If rs.RecordCount > 0 Then rs.MoveFirst Do If Not IsNull(rs![email]) Then vRecipientList = vRecipientList & rs![email] & ";" rs.MoveNext Else rs.MoveNext End If Loop Until rs.EOF .To = vRecipientList
I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.
I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.
Any way to do this by creating a recordset and looping through to check for dupes.
INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism ) SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));
Ref# Rev 97 b 98 c 99 c 99 e 100 c 100 b 101 a 102 b
I need to create a simple report but remove the duplicates (ex. Ref# 99,100). I need to delete the older Rev's (Ex Ref# 99 Rev C, Ref# 100 Rev B).Is this done throughRecordsets? will an SQL query do the trick?
I have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?
code is below.
Code: Function SyncEmployes() Dim lngColumn As Long Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset Dim rsHH As DAO.Recordset Dim rsPhone As DAO.Recordset Dim rsEmail As DAO.Recordset Dim rsAddress As DAO.Recordset Dim rsPerson As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code: Option Compare Database Option Explicit Private Function IsDuplicateRecord() As Boolean On Error Resume Next Dim PreviousRecordID As Long IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB bob 19800201 Sam 19761211 Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
I have a cool little form for the use of the organization's treasurer wherein she enters checks and deposits. I have a query that generates a running sum, so each line in the accounting report based on the table has the account total as of that date. The query behind the report is exactly what I want and is in the correct order. For example, the four transactions for 9/15/2014 are in the order entered, and the line total is correct. The report insists on listing the 4 in the example in some other order with or without OrderBy specified. With code from someone, perhaps on this forum, I added the following to the OnPage event:
Code: Private Sub Report_Page() Const TWIPSPERINCH = 1440 ' Offset from right edge of Control where our ' Vertical Line will start - adjust as you desire. ' Expressed in Twips Dim intLineMargin As Integer
[code]....
The result is a beautiful report (albeit in the wrong order) with vertical lines all the way to the bottom of the page.If I remove the OnPage code, the report is still in the wrong order, so obviously that wasn't the problem. OK, I think it is corrupted. I open a new db, import all into it. Still in wrong order. So next, I begin to recreate the report from scratch. Hooray! Right order! But I still want the vertical lines between the columns. So now, I copy the OnPage code and paste it into the new report's OnPage code. I get error message: "Compile error: User-defined type not defined" with the line "Dim MIPSstr As udtPrtMips_str" highlighted. Ok, I delete all of it and reenter it line for line manually, leaving out the comments. Get the same error message. After trying to research it, the only suggestions were to add Activex reference which can't be the problem, because the other version of the report doesn't throw an error and both are in the same db.
I have attached a clip of the report and a clip of the underlying query. As you can see in the first capture.png, the check numbers are in the wrong order and the amounts in the Total line don't sync with the total above adjusted by the amount on that line.
How do I get VBA to say: Go to the first control in the tab index after the current active control (that is visible atm), except such and such controls (because they will lose visibility in a moment).
I am a VBA beginner and am using Allen Browne's method for filtering a field in a subform: URL....It works well except that I cannot seem to figure out how to order the results when the subform field is filtered. I want to order the results by CompanyName.Here is the code I am using for changing the record source from the main form to the subform:
Code: Dim sSQL As String If Me.cboFilterFavorites = Null Or Me.cboFilterFavorites = "In Business" Or Me.cboFilterFavorites = "Out of Business" Or Me.cboFilterFavorites = "Unsure" Then If Me.RecordSource <> "qryCompanies" Then Me.RecordSource = "qryCompanies" End If
[code]...
I feel as if I have tried everything. I even changed the subform record source from a table to a query (qryCompanyTypes) so that I could add the CompanyName field and sort but to no avail. I have tried adding the CompanyName field to the SQL string but keep getting errors. I have tried simply using Order By in the SQL and still get errors.
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code: Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer) DoCmd.SetWarnings False If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then Cancel = True MsgBox "There were import errors, please open View Import Errors above." End If End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
How to define Order that starts with a dependency on the existing value.
Basically, a function that chooses what Status Validation to run then knows when to stop that displays some very simple predictive Inference.
Each Record has a Status. The Status will generally be correct 85% of the time. Once in a while, various things in the database are updated that triggers the business need to change the status.
-A collection of Rule based validation functions matches each Status. -The objective is not to run Rule 1 through 40 sequentially for each record. -Each Rule Validation function is lengthy and can take 0.1 seconds per rule (per record). -If the current record's rule is still valid 85% of the time, Then run that Status Rule Validation function first. -If the Validation returns True, there is no need to run the rest of the rules. Move to the next record.
The challenge: Statistically, if the Current Status failes its Rule Validation, there is a high probability that one of the Status Validation Function near to the current Status will validate to true.
Like the board game Clue, once a Validation Rule comes out False, there is no need to check it for that single record again.
In an extremely simplistic world, think about Status of : Order in Progress, Order Completed, Completed Order Paid, Shipping in Progress, Item Shipped Ready, Shipped, In Transit, Delivered, Rejected, Payment Recended, ...
So, if the current status was In Transit, there is a higher odd that Delivered would be the next. There would be no need to start from the beginning again. The wonderful world of Metadata.
I have code attached to a command button to fill a Combo Box with data from a music collection. A letter of the alphabet is entered into a Text Box then records beginning with that letter are copied from a table, either by Artist or Title. They are saved to a temporary table at which time they are in no particular order. Those records are copied to a further table and saved in alphabetical order. This table is then used to fill the Combo Box.
I used two temporary tables because the records were not displayed in the correct order. I hoped this might cure it, it did not. The records are in order in the table but not in the Combo Box.
Code: Private Sub Command68_Click() 'SEARCH AND FILL COMBO BOX On Error GoTo errTrap DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM tempList;"
I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:
strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";" Set rst1 = db.OpenRecordset(strSQL)
The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.
