Hi folks,
I have this situation: a table with patients information and IDs, and a table with evaluations for those patients (identified by ID).
I am trying to make it so that the user can't add the same evaluation twice. I want to do it by checking if there is already an evaluation for that patient with the same date.
I know how to show evaluations only for a patient, but I don't know how to check if the value entered by the user in the date field is a duplicate (case in which he shouldn't be allowed to enter it). I assume this is done somehow through the "validation rule" property? But how should the expression be?
Thanks a lot!
I would like to check to see if a previous date value has been entered for a specific record and display a message if it has. I have spent some time researching vlookup and vcount but im not quite there in what i want to achieve.
I have a data entry subform, linked to my main form through StairID. The subform is called tblEstateInspection and has a PK called estateinspectionID, stairID is a FK and links it main table.
Like i say i have been investigating what other people have done using dlookup / dcount, what i want to do is check that a specific date has not been entered for another inspection for the same stairID, i guess what i have got below at the moment just looksUp the value and because it is never null the messagebox always shows? How can i ammend it so that if it finds records that have same stairid, checks Inspectiondate field and only shows message if the new date matches old date
Private Sub InspectionDate_AfterUpdate() Dim strFilter As String Dim check As Variant strFilter = Me!StairID
I have two four tables. Invent, trans, NewOrder and reorder tables. The new order table is not joined by a relationship unlike the others which are joined with the Invent ID field.
The new order table has the fields(ID,QTY,NAME,Order Date). I want to use this to order new items that are not in the Invent table. What do I do to check that I don't enter a new item name in the NAME field that is already present in the Invent table?
working through validation rules etc at the minute on my database. Have come across this one is sort of stumping me. I have a tick box in the field "Medication to be repeated" the next field after it is "Date to be repeated on". I am looking a validation rule which makes it that if the checkbox is ticked then date to be repeated must contain a valid date value.
I have a form which allows the user to book rooms.
On this form, there are the following fields:
BookingID: (Autonumber) RoomID: Text box Time:Text Box Date: Text Box Class: Text Box Teacher: Text Box
The form adds this information to the Booking table.
What I'm looking to do is prevent the user from double booking a room,like being able to check if the Room is already booked at that time and date, before the new information is added to the table and the room becomes double booked.
Basically this would be checking the RoomID, Time and Date fields, as everything else is irrelevant. What would be the best way to do this?
I have a form with a couple fields. i want to check to see if i have a duplictae field. the field is batch_ID. so if somoen tries to enter a batch_id thats already in the table a message box will popup saying duplicate record and it will take me to that record ?
i managed to get checking for duplicate record by ONE field ie member_no
code :
If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then MsgBox " This member is already exist!" & vbCrLf Me.c_memberid.SetFocus Else DoCmd.CancelEvent End If
how i want to get checking duplicate record by TWO fields ie member_no and run_no ?
I have a form - see attached image. The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.
I have a database where a user imports data into a table monthly. She saves the Excel file and then clicks a button in Access to import the data. My instructions say to only click that button once, but so far, she has clicked it THREE TIMES, thus importing the data three times. How can I prevent this? Here is a copy of my import code:
Option Compare Database Option Explicit
Public Function ImportTableData() On Error GoTo ImportTableData_Err
Dim strPeriod1 As String Dim strMM1 As String Dim strYY1 As String Dim strYYYY As String Dim strFormName As String Dim strMDPath As String Dim strMDFileName As String Dim strDCPath As String Dim strDCFileName As String
'No Warnings DoCmd.SetWarnings False
'Import Current Month data in a temporary table DoCmd.OpenForm "frmEnterAgingDates", , , , acFormReadOnly, acHidden strFormName = "Forms!frmEnterAgingDates" strPeriod1 = Forms!frmEnterAgingDates.Period1.Value strPeriod1 = Format(strPeriod1, "mmddyyyy") strMM1 = Left(strPeriod1, 2) strYY1 = Right(strPeriod1, 2) strYYYY = Right(strPeriod1, 4) strMDPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300116405.100-AR Due from PAR Plans NASCO" strMDFileName = "MD AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls" strDCPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300117630.300-AR NASCO Refund Due from PAR Plans117630-2007" strDCFileName = "DC AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
I'm pretty new to Access, so please overlook any stupid questions :) I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates. The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted. So, after consulting various posts, I did a BeforeUpdate event with the following code: (the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")
Private Sub LS_Number_BeforeUpdate(Cancel As Integer) On Error GoTo LS_Number_BeforeUpdate_Err
If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"
When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.
I have a problem where in a table called tblPatientLanguage that contains three fields
PatientLanguageID PatientID LanguageID
a patient who is identified by PatientID can speak several languages identified by the LanguageID.
The problem is with my current form and code
For Each varSelected In Me!lstLanguage.ItemsSelected rs.AddNew rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID] rs("LanguageID") = Me!lstLanguage.ItemData(varSelected) rs.Update Next varSelected
when the user click on the command button called cmdOK it will put the data in the table "tblPatientLanguage" and corresponding fields. without checking to see if there is already a duplicate languageID that corresponds to the same patientID in the table
This has lead to duplicates in the table
example PatientID = 9
if I look in the table I will see 7 entries that say
PatientID = 9 and LangaugeID = 7
I want to prevent this from happening with and if statement I could make both the PatientID and LanguageID primary keys this would prevent this from happening. But I would rather do it in the VBA code? This way in will move thorugh the selections without poping an error and the person inputing the date. Any ideas sorry for being long winded. I attached the database.
PS. If looking at the database you have to use form frmPatientDemographics and click on the language link
I have a simple database that I put my execptions in for the day for scanning. Is there a way that my serial field on my forum can check to see if someone already scanned that serial for that day using the "On Lost Focus" event.
I need to create a record of Users. I created a form with fields containing Username,FirstName,LastName & Email Address wherein the UserName is equals the value of the fields FirstName & LastName. I have a save command button and when I click it, I want it to be able to check if the new entry has a similar UserName that exists in the database, if it exists then it will allow me to edit the UserName and then save the data otherwise will allow me to save it.
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day) - contains 'name' 'date' and '# of hours' worked - an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month) - contains the number of contracts worked that is derived from seperate system - this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January) - the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours) - employees dont have access to this system to enter their own # of contracts keyed on a daily basis. - for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
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....
I'm in need a of a check-in/check-out application for my company. We have about 550-600 employees at any given time and our turnover is about 20-30% per year. Our check-in/check-out process requires our employees to personally visit between 30-40 areas in our company (personnel, safety, credentials, parking, insurance, etc) within the first month of employment. This is currently done manually and is a huge drain on labor, especially when check-in sheets are lost, misplaced or, in some cases, forged.
I'm looking to build a database that would be intranet based, password secure (by check-in area) that would allow the new employee to present at a particular check-in, check-out site, complete that portion of the check-in/out process and then allow the person responsible for the check-in/out to enter the status into the database. At any point in the process, I would want to know the status of the person checking in/out (how long they've been checking in/out and what portion of the process have they completed).
Hi I have sendkey "{f7}" on loss focus. this works great as a spell check but then I get the mesage box that spell check is complete. How do I stop this box from occuring?
Is it possible to put a "tag" (probably the wrong term) on individual record entries? So that on a shared database the administrator could see who entered what. That way if there is a problem the administrator would know who to speak to or who needs retraining/firing ha:)
Ive looked around and havent come up with anything.
I'm currently doing a query, when a report is received, we enter a date, i would like a query which would show records where the received date has not been entered. i have tried putting in isnull() but does not work.
Hi all,I'm working on a library database for a small collection.I have two fields, 'Date Out' and 'Date IN'. Is it possible that whatever date is written for the date taken out, I can set the 'Date IN' to be 7 days later than that date?Thanks in advance.
What I need to do is when I enter the data in the form and before I go to the next record I want to save this row in the table and then insert two more rows with the same values (that I just entered and saved in the table) while incrementing the index field. I also want to programatically manipulate a field value in the second and third rows that I will insert before commiting.
For Example:
Tbl-A Reg_Num Number (Index) Name Text (30) Reg_Type Text (1) Category Text (10) Sub_Category Text (10) Short_Desc Text (10) Long_Desc Text (50)
After I enter these values I click on "SAVE" button. When I click on save, I want to insert the same row twice (notice Reg_Num and Reg_Type values) in the table and increment Reg_Num and change the value of Reg_Type.
As you see, I am incrementing the index field value by 30,000 if Reg_Type is "W" and by 50,000 if the Reg_Type is "X". What it means is copy the row that I just entered, insert it in the table, change the Reg_Type value and increment Reg_Num based on the business rule. COMMIT. Do the same for the next row.
I have about 20 Fields in the table. I do not want the user to type 10 times the same values and make mistakes. This is just an example. I have about 8 different Reg_Types that needs to be stored in the table everytime a new Reg_Num is entered with Type "A". User can enter any Reg_Type but to make my life easier I'm restricting to start from Reg_Type "A". For each Reg_Type the Reg_Num needs to be computed because it is extreamly important in our application.
I've got a form with a series of combo boxes which are dependent on each other, however i keep finding that after ive entered some data and left a record, when i then return to the record, some of the information ive entered has been deleted and the combo box is blank.
I think it must be requerying the combo boxes everytime the record opens but i dont know why, i have only put requerys "OnChange" because that is the only time the dependent combo boxes should be requeried.
Once the info is entered and ive moved on to a new record, i need for it to stay that way when i return to the record and only change again when i edit it.
I have a form that the user will enter data, when the user has finished entering the data, i want them to click on a button, with a yes or no option button on it.
if the user selects yes (approved)......i want the record to be emailed to a few peoples email address.
i was thinking that i could store the email address's in a table and email the record to whoever appears in the list.
if the user selects no (Amend Record)..... i want the set focus to go back to the first field of the last record for Amendment.
I have a form with a number of entry fields and after a button is clicked I write to an MS Sql datatable. Everything works fine as long as I put a value in each field. If I do not put a value in one of the fields the sql does not run and the record is not added / updated in the database. I have tried checking the length of the data in the field using
Ent_Length = Len(tb_TagDesc) or Ent_Length = Len(tb_TagDesc.Text) or Ent_Length = Len(tb_TagDesc.value) but nothing shows.
