Surname Search Problem

May 26, 2005

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

stDocName = "Patient Details/Visit History"

stLinkCriteria = "[Surname]=" & "'" & Me![Surname] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Form_Click:
Exit Sub

Err_Open_Form_Click:
MsgBox Err.Description
Resume Exit_Open_Form_Click
End Sub

View Replies


ADVERTISEMENT

Surname Name

Aug 22, 2006

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.

Is the sql wrong or do I fix it in the report?

Thanks.

View 1 Replies View Related

Validating A Surname

Apr 2, 2008

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

View 3 Replies View Related

Extracting First Name And Surname

Jan 12, 2006

Hi,

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?

Many thanks

Clay

View 2 Replies View Related

New Idea Duplicate Surname And DOB

Jul 5, 2005

hi ,

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

Set rsc = Me.RecordsetClone

SID = Me.Surename.Value
stLinkCriteria = "[Surename]=" & "'" & SID & "'"

'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

Set rsc = Nothing
End Sub

View 1 Replies View Related

Input Mask For Surname With '

Aug 24, 2005

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.

View 1 Replies View Related

Sample Data With FirstName And Surname

May 3, 2005

Hi everyone,

Can someone please help me out here?

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?

Thanks in advance,

Jean

View 3 Replies View Related

Letter + Auto Number From Surname

Oct 16, 2004

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 only know basic Access so please help.

Thanks a lot

Nick

View 2 Replies View Related

Forms :: Prevent Duplicate First Name And Surname?

Apr 25, 2013

I just need a simple method to prevent a member being imput twice on a form.

I have two fields firstname and surname ( this is necessary).

I want to stop duplication of the same person being entered when staff use the form.

How do I stop this?

View 2 Replies View Related

General :: Only Last Word In Uppercase (Surname)

Sep 11, 2013

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.

View 1 Replies View Related

Combine Columns.. Forename Surname To Produce..

Nov 27, 2006

Hi

im trying to combine the

Forename Column and Surname column so i can produce another column which is initial surname eg

Matthew | Williamson | M Williams
Kevin | smith | K Smith


how do i do this ive completely gone blank!!!!!! :(((

View 3 Replies View Related

General :: Concentrate First Name And Surname Fields In Form?

Aug 8, 2013

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.

View 2 Replies View Related

Reports :: Grouping Multiple Fields (Firstname / Surname)

Jan 21, 2015

I have a report with the fields

Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.

On the database people can be entered multiple times so I may have

Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes

Now I now with the group function I could group by the firstname_ field but the results would group like the following

Bob Smith
Bob Smith
Bob Holmes

Jackson Smith
Jackson Smith

Jimmy Carter

Tim Jones

And if I do it by surname, well I am sure you get the drift.

So how can I sort so I would get the following

Bob Smith
Bob Smith

Bob Holmes

etc etc.....

View 2 Replies View Related

Queries :: Possible To Extract First Letter Of A Surname In Access Query?

Jun 11, 2015

is it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.

Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir

What I want the query to return:

Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A

View 6 Replies View Related

Forms :: Possible To Have Combo Box On Form With Fields Joined With Alphabetical Order On Surname

Aug 1, 2015

I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression

View 11 Replies View Related

VBA - Search Button To Search Entire Records / Runtime Error 3345

Jun 18, 2014

why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.

The dysfunctional code:

Code:
Private Sub btnFind_Click()
If (TxtFind & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Acronym] = " & TxtFind

[Code] .....

The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?

View 5 Replies View Related

Modules & VBA :: Search Code Deactivates Navigation Buttons When Search Results Are Longer Than A Page

Jun 24, 2015

I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.

The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:

Code:

Private Sub cmdAssetSearch_Click()
Dim rs As Object
If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
MsgBox "Please type in an asset number to search for.", vbOKOnly
Me.TextAsset.SetFocus

[code]....

I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.

View 6 Replies View Related

Queries :: Add A Box On Search Forms To Search 3 Cells Of Record For A Keyword

Jun 10, 2013

I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.

View 3 Replies View Related

General :: Search On Google And Return First Link From Search Page

Sep 21, 2012

I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...

View 1 Replies View Related

Queries :: Search Form That Uses A Query To Show Results Of A Search

Aug 5, 2014

I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.

View 2 Replies View Related

Using Subform For Search Criteria And Relating One Search Field To Several Columns

Apr 21, 2015

1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?

2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.

View 6 Replies View Related

How To Create Search Button Using Access 2007 That Can Search From Form

Jul 10, 2012

i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.

i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.

i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.

View 5 Replies View Related

First Name Surname Table To Table

Mar 4, 2007

Hello - new user here. I need help in setting up my database to be used in a non profit sports club.

I have one table containing FirstName and LastName (this will build into my membership information file with lots more detail eventually) and another table which is to be used as a class register.

I want to extract information from the Membership table and use it to produce Class Register table.

I wish to do this by entering a first name or first letter of a name and have all the available portential matching records come up in a drop down box to be selected from.

I have tried every way I can using relationships, codes etc and cannot make this work. I cannot move forward to build the database until this is solved.

Can you help??

Many thanks

Brian

View 4 Replies View Related

Forms :: Search Text Box With Option Box As Criteria For Search

Mar 4, 2014

I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.

View 3 Replies View Related

Forms :: Search Combo Box To Search For A Field On Form

May 7, 2013

I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.

View 5 Replies View Related

Using One Search Button To Search 4 Tables In A Database?

Mar 10, 2008

I have a database that uses four forms and each form has it's own table and each form has it's own search button to find a specific record within that table. I would like to use one search button that will look at all four tables and bring up the correct record when the request number is entered. Can I link all the tables to perform this or is code required? Can someone send me in the right direction for how to do this? Give me an example of code if it's required?
Thanks for any help I can get.

Laura Edmark

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved