I have a table that stores jobs. The primary key field is JOBID which will correspond to company invoice #'s. Because the Invoice #'s are generated by a different system, I don't not have this as an autonumber field, I want to control it programatically when the user enters a value into the JobID textbox.
At first, I was using DCOUNT on the BeforeUpdate event of the form to check if there was already a matching JobID in the table, and if so, cancelling the update.
If intInvoiceCheck > 0 Then
Response = MsgBox("You have entered an Invoice # that is already used." & _
Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
"If this is a secondary invoice considering adding 'a' or 's' to make it unique." & _
Chr(10) & Chr(13) & _
"For example, " & Me.JobID & "a, " & Me.JobID & "s...", vbOKOnly, "Duplicate Invoice")
Me.JobID.SetFocus
Cancel = True
Exit Sub
End If
However, if the user is changing say a jobsite address, they can't, because it checks and sees that intInvoiceCheck = 1 and won't continue.
I tried moving my routine to the BeforeUpate event of the JobID textbox like this (this is not the final code, only what I testing with)
'Check that the entered Invoice Number value is unique
Dim intInvoiceCheck As Integer
Dim Response
However, I getthis long error message saying I have changed the validation rule of the field without first checking to see if it's valid....
I can change the to Me.Undo, which works fine, but I don't want to undo the whole entry, only the JobID and set focus to it, and make the user enter a new one.
I have a Listbox on a fprm which has a button attached to it, the button allows you select one of the items in the listbox and append it to a table. the problem is that it currently allows you to add the item as many times as you like. How can I check for duplicates?
Code: Private Sub CmdAdd_Click() 'Add new record to tblShootingTasks strSQL = "INSERT INTO tblShootingTasks ( ShootID, ContactName, Task ) " _ & "SELECT [Forms]![frmTasks]![ShootDateiD] AS ShootID, [Forms]![frmTasks]![Combo15] AS ContactName, [Forms]![frmTasks]![Frame17] AS Task;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL End Sub
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.
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
It should be exceedingly simple, but I got mashed potatoes between the ears today.
I make a query looking up for Name+Firstname+Ref number which I display in a tabular form .
Now, I want to use check boxes to select the ones I wants to print. But the thing is, if I add a check box in the main part of the form, it'll be the same box for all records. Not what I want.
So, how to create (add) a column so I can check individual records in the form?.
Hi guys, a company in my city is giving out prizes, and for each buy you get a sheet with 25 numbers ( kind of bingo thing). Well I have like 300 sheets . So i did an Access where I enter the SheetID and the 25 numbers. Now I have to do another table where I can insert the number that will be draw and whenever one of the sheet is complete, it let me know that I win!!! and the SheetID. Any ideas!!! please please
Hi, i want to set values (=numbers) in fields of a table by selecting check-boxes in a form (based on the same table). as checkbox1 =1 checkbox2= 2 etc If the box is checked the value should be entered, if unchecked the value should be removed. Afterwards i want to show the sum of these values in another field in the same form. I suppose this requires VBA. Could anybody help me plz ?
I have a form where I enter student names, and it often happends that I enter the same student a couple times, Is there any way to let the user know that there is allready someone in the system with the same name, like a pop up message.:confused:
hello all, I have no idea how to run error checking I get this error can't append all the records in the append query. i want a custom error to come up instead of that. PLEASE HELP.
Basically what im trying to do is a database which checks to see if something is available.
The database is a booking system for services. I need the database to check a "booking" table to see if there is a booking on a specific date. If there is, then you cannot make a booking. If there isn't then you can.
In my "booking table" there is a list of dates of bookings. Each booking lasts a week. So if someone wants to make a booking, it cant just check to see if the first day is available, it needs to check for 6 additional days. (i.e. a booking on 01 Jan 2006). If someone wants to make a booking, the database needs to account for a whole week, so the next available booking would be on 07 Jan 2006).
the problem is that sometimes the table doesn't exist... Instead of trapping the error code, is there a way I can check to see if the table exists? If it doesn't I'll simply skip this step of my code and move on.
I have tried the attached code from a previous post http://www.access-programmers.co.uk/forums/showthread.php?t=98727&highlight=table+exists but some of the code doesn't work on Microsoft Access Project. Does anyone have any ideas how I would amend this code so that it works?
I had a little problem with delete query. I would like to delete the records by checking two fields if both fields are empty. I mean if a records had both these fields are empty only. If one of the field has data , the record should stay there.. Is there any way I can do this?
I would have thought this was easy, but I keep getting an error. I have a form with a save button. When the save button is clicked, I want it to check if user has attached an OLE object to a bound object frame. If they have, I'd like a checkbox to be true. Here's the code I tried: If Attachment.LpOleObject = 0 Then Attach.Value = False Else Attach.Value = True End If This seems so incredibly straight forward and it does work if there is an attachment. If there isn't I get the message that my database can't retrieve the value of this property.
Hi, I'm trying to do up a database for a furniture company. I used Northwinds as a guide and made a similar Orders form.
However, I need another functionality which Northwinds do not seem to have. I want to check whether the currect Order would cause the customer to exceed his credit limit.
WHAT I DID For this, I made a query to find the amount any single customer owes to date and the amount that he can still borrow(creditlimit-amountowed+amountpaid). I then made a button which launchs this query.
PROBLEM The problem is I want when i click this button, the query prompts me for CustomerID, which i have to key in again. Then, it launchs the datasheet showing me the credit he would have remaining if this order went through.
It would be better if I do not need to key in his ID again, since it is already on the form when i keyed in the Order. Further, it would be much better if I could get the button to display APPROVED/REJECT(postive/negative balance) and also display the amount of the remaining credit.
Sorry for the long description. Thanks for reading and please let me know if I missed out any crucial details.
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?
I have a form in which new equipment is added. I want to be able to check when the ID number is added (first field on the form) that there is not a duplicate ID number. If I wait till the end of the form and save, then Access tells me that there is a duplicate present but by then I have spent time putting in all the rest of the data.
If have added this code in the before update section for the relevent field, but it doesn't seem to work. Can someone please help clarify or suggest an easier way
I'm new to using VBA for MS Access. May I enlist your help, Please.
I'm working on a human resources database for a group of employees. I'm trying to automate their login process after they have opened the database so that each employee's privacy is respected. Using an initial form, employees are separated into "New User" or "Returning User". My problem concerns setting up the "New User" in the database.
The New User is directed to form "frm_NU". The form has several unbound fields which are later carried to the table "tbl_Employees". Two of the fields on the form are used to create the EmployeeID. Those are Initials [INI] and Phone Extension [EXT]. The EmployeeID will be used in a filter so that only that employee's information is made available to the user (simulated Privacy).
Because the employees are unfamiliar with MS Access and its cryptic error messages, I need to create a message box which will give a plain English warning when a user attempts to use an existing ID to create a new record. [EmployeeID] is a field in the table. it is set as a key and does not allow duplicates.
When a new user enters his/her initials and phone extension I want the new ID to be compared to the table and if found, a message should appear. This is a preemptive strike to prevent the user from getting to the more cryptic MS Access error message.
I've tried using Dlookup and Dcount to try to pass some meaningful data to the If statement variable VarX to trigger the message box; assuming that Varx should be something other than Null or Empty
If VarX <Is Not Empty> Then X = MsgBox("Another record already exists by that name", vbOKOnly) Else Y = MsgBox("Passed", vbOKOnly) End If
I keep getting an error at the DLookup formula or DCount formula saying: Run-time error; 2001 You canceled the previous operation. It also appears that the DLookup never looks up anything nor does DCount count anything even though I'm purposely entering duplicate information which exists in "qry_Employees". I was expecting VarX to be either the duplicate ID or a record number or record count or something.
I currently setup a form for a survey which works fine and all the information that is gathered is stored in an access 2000 database.
The thing i want to do is to only let the person fill out the form using only 1 email address. So if they try to come back and fill out the form again using the same email address, i want there to be a check on the database to see if the email is there. If it is the form wont submit and it will let the person know. If it's not in the database things will work as planned.
this is what i have so far, but when i submit the form it continues on, but it doesnt check and doesnt email or add to the database like it used too...
Code:<%Dim DSNtemp, Conn, SQLDSNtemp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DATABASE LOCATION"Set Conn = server.createobject("adodb.connection")Conn.Open DSNtempemail = Request.Form("email")SQL = "Select * From users Where email = '" & email & "'"Set RS = Conn.Execute(SQL)'HERE WE CHECK TO MAKE SURE THE USERS EMAIL EXISTS, IF IT DOES, WE STOP HEREIf NOT RS.EOF Thenx = "Sorry The Email Address You Entered Already Exist!"Set RS = NothingConn.CloseSet Conn = NOTHINGElse'CONTIUES ON WITH DATBASE INSERTION AND EMAIL TO USER.End If%>
Im trying to check if a field is empty (i.e hasnt got anything in it)- but how do i do this. Ive called the field in my sql statement and then Ive put it in a variable- i, I am then running a if statement
I am making an application in Access where a number of image files are copied to a folder in the server. I need a way to find out whether the folder in which the images are being copied to exist so that a new directory can be created if it does not exist. Please help with a code to check for the validity of the existing path.
Hi I am trying to read a large file and to check if the phone number field is valid. If there are more than 6 "1"s, "9"s or "0"s in the field, then I want to replace it with a null otherwise, I will treat it as a valid number. Can you help? Noel
