see below for the current code to find a duplicate surname (this works Fine)
now that ive properly thought it through what i need to do is insert a surename and if (by Chance) when i insert the Date of Birth if this is the same highlight it as a duplicate record. However if these people are twins then i need the option to insert the duplicate record.
any ideas on how to go about this, see code below.
Private Sub Text24_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
'Check StudentDetails table for duplicate StudentNumber
If DCount("Surename", "tblData", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Duplicate Name " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
I have a query with the following sql: SELECT QY_ALL.TB_STAFF_STAFF_ID, QY_ALL.LNAME, QY_ALL.FNAME, QY_ALL.JOB_TITLE, QY_ALL.BASE, QY_ALL.DEPT_WARD, QY_ALL.STATUS, QY_ALL.COURSES_DONE_ID, QY_ALL.COURSE, QY_ALL.START_DATE, QY_ALL.END_DATE FROM QY_ALL WHERE (((QY_ALL.DEPT_WARD)=[Enter Dept/Ward]) AND ((QY_ALL.STATUS)="CURRENT") AND ((QY_ALL.COURSES_DONE_ID)=[SELECT COURSE ID]) AND ((QY_ALL.START_DATE) Between [Enter start date] And [Enter end date])) ORDER BY QY_ALL.LNAME, QY_ALL.FNAME;When I run a report off it it returns correct data except it treats same surnames as one. E.g. if I have Jon, Jo and Fred Smith I get Jon listed but Jo and Fred's records attributed to him.
I need to validate a surname field which obviously can only consist of letters and occasionally spaces and/or hyphens. (i.e. van Driel or Johnson-Crooks)I managed to create a validation rule which would allow me to have space in the surname field:Is Null Or Not Like "*[!((a-z) or ( ))]*"but when I tried to allow hyphens into the field - I used these codes - but none of them worked:Is Null Or Not Like "*[!((a-z) or ( ) or (-))]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like (" ") Or Not Like ("-")Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[!( )]*" Or Not Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Like "*[!( )]*" Or Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[( )]*" Or Not Like "*[(-)]*"Is Null or Not Like "*[!(a-z)]*" or (in ("-"," "))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*"))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*")) And (In ("-"," "))Is Null Or Not Like "*[!(a-z)]*" Or Like ( ) Or (-)Please can smeone help me.James
I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.
Any ideas on how I achieve this through an Access query?
Is it possible to run a query when a check box is pressed? If it is possible how would i then go about comparing the result. I would like it so that a count query is run when the check box is pressed and then if the result is 20 or greater then a command button is disabled. Any help?
I have a button that when clicked is used to switch to a form displaying a record details based on a search by surname using a combo box. The record chosen by using the combo box is also displayed lower down on the initial form. My problem is with multiple surname entries, it keeps switching to the first surname record; even when another surname record is selected.
Can I combine the use of surname and first name in my criteria? or Is there a better way to switch to the correct record once it has been selected. I am a bit of a novice with coding. My code is below:
Private Sub Open_Form_Click() On Error GoTo Err_Open_Form_Click
Dim stDocName As String Dim stLinkCriteria As String
Is it possible to set up an input mask for a form for a surname field so that names will show first letter of name as a capital (easy), as in Smith - but for names like O'Connor, show the first and second letters as capitals? (and, of course, allow the ' to be entered). Thanks.
I have a crazy idea that I think would be very cool if I got it working, but have no idea where to start. I have a database that is broken down into a front end and a back end, and there are about 10 users who use it off and on throughout the day. This database has an entry form to enter new records. I am wanting to put a text box or a label on that main form that would turn green if the database was in use by another user, besides the one that was currently looking at the form. It would be red if there are no other users accessing the database. Is this even possible? Any suggestions or input would be greatly appreciated.
Hi . I need to have a single form , or nested subforms for data entry. The proble is the main form requires information from 2 other forms, and these forms in turn require information from other primary key fields. I know its basic form design, but i just cant seem to get my head around it. Any ideas?
tblVehicles VehicleID Autonumber PK MakeID FK to tblMakes ModelYear Number 'any other fields that describe ONLY the vehicle
tblMakes MakeID Autonumber PK MakeName Text
tblTires TireID Autonumber PK MakeID FK to tblMake TypeID FK to tblTypes DatePurchased
tblTypes TypeID Autonumber PK TypeName Text
tblTireLocation TireLocID Autonumber PK TireLocAbbr Text TireLocation Text (Front Passenger Side, Front Driver Side, etc.)
tblVehicleTires VehicleTireID Autonumber PK VehicleID FK to tblVehicles TireID FK to tblTires TireLocationID FK to tblTireLocations DateAdded Date/Time (Short Date) DateRemoved Date/Removed (Short Date)
tblVehicleChecks VehicleCheckID Autonumber PK VehicleID FK to tblVehicles CheckDate Date/Time (Short Date) Mileage Number
tblTireChecks TireCheckID Autonumber PK VehicleCheckID FK to tblVehicleChecks VehicleTireID FK to tblVehicleTires TireMM Number TirePressure Number
Looking for some advise and help. I think an array will be recommended and if so, please help with code on how to do this.
I am trying to track prescription (Rx) dates (RxOutDate) going out to medical professionals on a particular order. There can be mulitple Rx going out so there will be multiple dates of when those Rx went out. What I would like to do is have the earlies date posted. ie one Rx went out on 10/13/04 and another goes out on 10/14/04. I would like for the 10/13/04 date to show. I'm using a continuous form to display all Rx's per order, on frmRx. I'm trying to display the date out on the form containing the order (frmOrder).
Any thoughts would be greatly appreciated. If an array is suggested. Please help with code on how to it. I've never written one so I would definitely be in the dark.
I am finishing a project at my client, and they said that I could take a copy of the database I created home (i.e. so that I will support them ;) )
I have a table tblPeople, however, the only condition is that I have to change the First- and LastName columns in this table, since it contains personal information. I need to change the names for about 600 people (400 men, 200 women)
I was thinking of adding the First- and Last Names of movie stars, to keep the flair in my DB :) Does anyone maybe have a sample database with (movie star) names that I can use to populate my DB?
Hi i have to design a member database in access, As the membership number i need to use the first letter of the surname, then have an auto number. e.g Smith, if the last member was S47 then Smith would need to be S48, This will be used on a form for a video hire shop, so, the customer details will be entered, is it possible for this auto number to work just by putting the name in the surname field?
I have a textbox called "name", what i am trying to do is have lowercase on first/second names in the textbox except the last word (surname)this i want in uppercase.
My primary key for my database a WorkOrder Number. The issue is that sometimes problems span a selection of WorkOrders. For example, if we get a lot of bad parts, 3 different WorkOrders might be affected, and each may have different resolution to the effect. To speed up data entry, I've had a request to make a button that copies the all the information from a current record into a new one.
If much of this information repeats, it this a poor way to handle this data? Currently it is pretty straight forward with ostly one->many relationships, and this would make things pretty complicated with a bunch of many<->many relationships.
I’m working to ever-improve a Help Desk-like database and I got this idea for “templates.” That is, a quick way to fill out the as much of the ticket form as possible when common issues come in.
So I’ve created a table (Templates)that mimics the structure of my ticket table (Incidents). I’ve gone into Templates and put the values I want automatically entered in each field and I’ve added a combo box and a button on my ticket screen. The combo box is populated, from Templates, with the name of each Template.
My problem is that I have no idea how to make each field on the Incident form populate with the values from the Template table.
I need help with table structure, They keep way too much info. Now that I have said that here is what I need help with. I have been asked to make a small "Ha ha" Database this is what small became. Most of the data for this is in a spreadsheet now I know I can import it where I want it. The main concerbn is that the main piece of information would be the Job Address. Because of all this other information I was not really sure how to setup the tables here is what I have started .
Buyers Information (tbl) Fname Lname Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Buyers Realtor Info (tbl) Fname Lname Company Name Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Sellers Information (tbl) Fname Lname Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Buyers Realtor Info (tbl) Fname Lname Company Name Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Job Information (tbl) Address City (Filled in by Zip) State (Filled in by Zip) County (Filled in by Zip) Zip (with a look to qry)"cool zipcode code" RoofType (With a lookup to A list tbl) RoofCondition (With a lookup to A list tbl) Job Notes (memo)
Appointment Information Time (now real sure how these time and date will work) Date ( " ) Appointment Type (With a lookup to A list tbl) Contact Name Salesman (With a lookup to Salesmen Table) Appointment Results (memo) Referral
As I mentioned this data is in a spreadsheet and most every row has all of the above information ("ha ha, small") but I wanted to get some other Ideas and see if anybody had a better one before I tryed to make it all work.
I may be oversimplifying my idea here, but I'm trying to have Form A, which has a source of Table A have fields automatically filled out with values from Table B when I select a template name and click a button that's on Form A. I simply have no idea how to make something this simple happen. Any ideas?
I've got 20 checkboxes which i need to do the same thing but individually.
If IsNull(DLookup("[question 1]", "qryQuestions")) Then chkQuestion1.Visible = False Else chkQuestion1.Visible = True End If
I tried this:
Dim loopy As Integer Dim tmpquest As String
For loopy = 1 To 20 tmpquest = "question " & loopy tmpCheck = "chkQuestion" & loopy If IsNull(DLookup(tmpquest, "qryQuestions")) Then tmpCheck.Visible = False Else tmpCheck.Visible = True End If Next loopy
tmpquest works. tmpcheck doesn't.
I tried dimming tmpcheck as lots of things but no luck.
I am thinking of adding a Request form to my database so that if the user wants to add a code to the system, they could fill out the form click send and i would then be able to retrieve all the requests via a report.
I have designed the form to something like what i am after...
i have a few issue's here:
A)
i have set up an option wizard so that user can select if this is a new code or a failure mode request
if the user selects "New Event Code" i want the the text box and label for Eventcode Description to become visible and all others to become inactive.
i will explain what happens if the user chooses "Failure Modes" in a minute.
B) I have no idea what is the best method to sort the following section, if you look at my form at the bottom (Highlighted in red) i need this to be a continuous option as the user may wish to add more than one Failure mode to an Event Code.
C) going back to my options if the user select "Failure Modes" i need the bottom section to become active.
D) If that was not bad enough i have no idea on how to store this in a table.
Definitions:
Event Code ---> Top Line Code
Failure Mode --> Is the Event code description but broken down further
I am currently using Access 2007, where I used to be able to concentrate First Name and Surname Fields by simply using:
= [First Name] &" "& [Surname]
However I have downloaded Access Run Time 2010 on the other employees computers so they will be able to access the Database, however since i have done this the First Name and Surname do not appear, it simply shows:
#Name?
I am guessing this is because it is a newer version, however even when I open the Database with Access 2007 it still does not work, even if I delete it and add it again.
I am looking to build and Overtime tracking form and have been pondering on the design of it. Here is my idea and not sure how I should go about doing it (straight forms and tables or queries). I would like to select my employees in a combobox and have their information autofill the remaining fields and/or add new ones. Additionally have fields that I can input any overtime occurred which would be stored in a seperate table but displayed in a sub-form in datasheet view. These fields that were just filled out with the overtime information would clear everytime the add record button is hit.
I know that sounds like alot and sure I can muddle my way through that part of it, the big question I have is do I need a seperate table for every employees overtime? Or is there code/query that can select the records of the selected employee and display the information that pertains just to them?
Has anyone come across or posess a sample database similar to this? If not can someone point me in the right direction for example code and tutorials?
