DLookup Searching

Sep 9, 2005

I have a database that has some tables joined via relationships.
The main table is a customer table, for gathering the first name, last name, address, etc. Then there are others are linked to it.
I have worked up a DLookup command

On Error GoTo ErrorHandler
Dim varFound As Variant
varFound = DLookup("LastName", "tblCustomers", "LastName = '" & Me.txtLastName & "' AND FirstName = '" & Me.txtFirstName & "'")

If Not IsNull(varFound) Then
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "There is a Duplicate Record found. Are you sure you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Duplicate Record Found" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
Screen.PreviousControl.SetFocus
DoCmd.SetWarnings (False)
DoCmd.GoToControl "txtFirstName"
DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
DoCmd.GoToRecord , , acNewRec


End If


End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 3021 Then ' no current record
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

Does anyone know of a good way that if, for example, the search found multiple records that it would display the multiple records in a popup form. You could then select the record that you want in the popup and it would retrieve the record? It would be similar to a search function, I believe.

View Replies


ADVERTISEMENT

Searching

Jun 25, 2007

Hi, I have two questions which i dont think are two far apart, hence same thread.

First is, how do i make a search text box, that when i click a button searches for what is in that text box, rather than bringing up the find box. It is alright but it always compares it to what was last tabbed, or is the selected tab, and i want it always to compare to another textbox called company name.

secondly. I am often entering entries to the database with the same details. but a different name, how can i have it so that i enter say 4 or 5 names which starts 4 or 5 new records, but then places the rest of the details across all the new records.

THANKS
aLEX

View 2 Replies View Related

Searching

Jul 22, 2005

Hi All,

I have a database which has 9,00,000 zip codes.... when i am searching for a particular zip code it's taking some time.... can anyone tell me how can i speed up the process.........

CoolNax

View 2 Replies View Related

Searching

Apr 11, 2006

Hey all.

I have a database but want to create a Search function.

I want the search function to read a drop down box for a table name, then read two text box's for search criteria then search the table and displat results.

My knowledge of Microsoft Access is limited.

I think I have the theory behind it but not the know how for the coding.

So far I have been able to get it too look at the List Box for the table name then when I hit the search button ive set it to just open the table for now.

but cant get any further, any help would be greatly appreciated.

Thanks in advance.

View 7 Replies View Related

F12 For Searching - Help

May 7, 2005

Hi,

I have a command button (SEARCHING). I want to Press F12 as a function key instead of pressing on the command button. Any help pls...???????

Sysop470

View 1 Replies View Related

Searching By Name

Jun 27, 2005

I need some assistance in searching for a recorded by employee name with the click of a button. There could be more than one record for each employee name so i need a way that once the records are found they can be displayed to the user? ANY help is greatly apprieciated!!!!

THANKS

View 6 Replies View Related

Searching Dates

Apr 15, 2006

I want to run a query where I can enter two dates ( On on form ) and have a report show the results.

I know I can write the dates in the criteria field in a query, but I want to be able to enter the dates on a form and do it this way.
Please help, anything will help me, I hope. :confused:

Thanks

View 2 Replies View Related

Help With Searching Database

May 10, 2005

I have built a basic database to hold details on all my computer books and software as i hundereds of them.

I have built a table with text, number and memo fields which i want to be able to search.

What i would like is a form where you can enter details into various fields (eg Title, Author, a field to search all fields, etc) and then display the results in a table in the form below it. Then when the record is double-clicked it opens the record ina new form displaying more details.

Is there a way to do it as i have spent months trying to figure it out and trying to include the sample databases?

I have attached my database so any help will be appreciated.
Thanks,
Aden

View 2 Replies View Related

Error When Searching

Sep 8, 2005

Hi there

In my form every time i click on the search button a box pops out and asks me if i want to continue or accpet the change. the funny thing is i just search and nothing else.

thanks

i attached a screen shot to help

View 1 Replies View Related

Searching An Entry With VBA

May 12, 2006

Hello, I'm trying to search an entry of a recordset with VBA

The entry can only be found by searching 3 field values. In my case the correct entry can only be found if the data in the field Date, No_Employe and No_Project all match the data of the entry I want.

Findnext doesn't work, it only access one field and I need three.
Seek doesn't work since it need to search a key, and neither Date, No_Employe or No_Project are keys.

How can I proceed ?

Should I use multiples Findnext ( how do I do that ? ) ?


EDIT :

Also please note that I'm searching for 400-500 entries in a row, so speed is an issue.

View 1 Replies View Related

Searching Through A TextBox

Dec 11, 2006

Hi All,

I have a small yet important question to ask. I have a text box on a form, that text box accepts a product number that the user wants to modify. I want to be able to do the following:

- If the product number does not exist, prompt with an error message (this part is not a problem).
- If the product number does exist, populate the text boxes on the form with the respective data. ex: Product number 512547. Does exist, hence: (TB = textbox)
Prd. No.TB = 512547
Rec'd Date.TB = 10/10/2006
Comments.TB= blah blah blah, etc.
I am simply trying to pull information from a table into text boxes on a form using a text box.

THANKS A MILLION!

View 1 Replies View Related

Searching With Combo Box

Aug 7, 2007

In my Access DB, I have a field named ‘REFNO’. This contains reference numbers of some communications. I have a Combo Box to search records with this ‘REFNO.’ It so happened that there were two communications with the same REFNO. When I typed the wanted number in the Combo Box, only one record was displayed in the form which was not the one I wanted. I was able to see the other record by opening the table. Is there a way to display the next record with the same number in the form with the Combo Box? Grateful for help.

View 1 Replies View Related

Searching Records

Feb 23, 2005

I have a parent table called patient info. and a child table called fills. The database in the future will become very large, and will be difficult to find a particular patient in the parent table to update fills in the child table. How can I search through the parent table with ease so that I can update fills?
Thanks for those who help!

View 1 Replies View Related

Searching By Using A Query

Oct 1, 2005

I have a database and I made a query to search records. The query worked perfectly, but now the only problem is that if a search has an ID number that is bigger than 239, the results will not be displayed. The query will be empty! :S

What is the problem with my query?

Thanks in advance

Aber^28

View 2 Replies View Related

Searching For Dates

Nov 11, 2005

I have to database where people enter information and some times miss entering in a field value called [Scanned Invoice] the db automatically gives every entry a date.

The first part was find out which jobs where missing a [scanned invoice] 24hrs ago by making a query [scanned invoice] Is Null [Date Entered] =date()-1 which works fine.

I then realised that sometimes people access the system on a saturday and as the system used globally and different countries have weekends on different days and theres the issues of every country having different back holidays. So i think that rules out networking days.

Is there away where i can get the query to check if the previous day had and entry and keep going back until it finds the next entry?

I tried an iif statement but i am new to Access and i could not get it to work.

I have tried iif(date()="0",date()-1) that seems to work but i can not get it to work any futher? HELP????

View 5 Replies View Related

OR And LIKE When Searching With A Keyword

Feb 26, 2006

Hello Access Fans!

I've got this query whereby i would like to find the address information about people by a keyword search on either their name or their quickname which currently looks like this:

SELECT Tbl_Addresses.*
FROM Tbl_Addresses
WHERE ((((Tbl_Addresses.Name)OR(Tbl_Addresses.Quickname) ) Like "*" & [Enter a Name] & "*"));

However this doesn't return any results? it works if i search for just the Tbl_addresses.Name or the Tbl_Addresses.Quickname but not for both? does anyone know how to fix this problem?

Thank you

Jarv

View 3 Replies View Related

String Searching

Mar 20, 2006

I have a problem with an SQL query that is used to create a report, the query itself works fine, however I need to get it to do something extra. Basically we have a column that contains 1-3 letters. The letters are A, B and C. The query searches the database and returns result depending on which of the letters we entered. We do this using the like statment -:

SELECT * FROM TableUser WHERE UserType LIKE B

So the above SQL statement returns all entries with the letter B in the column UserType. Also we my need to search for AB, which returns all entries with AB and ABC. The problem is that we sometimes need to search for AC, which should return the values AC and ABC. The problem is that it only returns the values AC and not ABC. I know why it does this as it is searching for a string like 'AC', I am just not sure how to get round the problem. Is there a way of searching the string for the Value AC so that it returns the entries AC and ABC.
Thanks in advance for any help.
Cheers
Andy

View 1 Replies View Related

Searching From The Switchboard

Sep 9, 2006

i have put a song database together (access 2002) with a swithboard to use when i run karaoke nights.

my question is, i have a search button on the switchboard where i can search for name or artist, which works by entering the entire song name or artist but i cannot figure out how i can search for part of a field.

i know how to do it in a normal query (using the LIKE command) but it doesnt work when i try it in the switchboard

many thanks in advance

View 5 Replies View Related

Help With Searching Records

Oct 16, 2006

hi.. i am currently creating an employee database.. and there is a form on
the database that needs to search on records.. i am planning to create a form
with a single textbox (for the keyword entry) and a search command button
that when clicked, the search results will be shown in a seaparate continuous
form.

now here is my query:


SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName,
tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName,
tblOutlet.OutletName, tblPosition.PositionName
FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN
tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer.
DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition.
OutletIDFK) INNER JOIN tblEmployee ON tblPosition.PositionIDPK = tblEmployee.
PositionIDFK
WHERE (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor])
) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR
(((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.EmployeeIDPK)=[Forms]![frmSearchEmployee]![txtSearchFor]));

but when i click search, the results are empty. what could be wrong with the
query? is it the query? what should be the record source for the search form
and the result form?

if you want to see the actual ms access file, here it is:
http://www.gigafiles.co.uk/files/636/human%20resource%20info%20system_2006-10-11.zip

the name of the form is frmSearchEmployee and frmSearchResultEmployee and the
name of the query is qrySearchResult.. thanks a lot and God bless..

View 1 Replies View Related

Help Searching One Column Against Another

Feb 5, 2007

I am new here. I need a little direction on the right path to take. My background is RPG programming so I am very shakey when it comes to SQL and Access DB.

I have two spreadsheets I am importing into a Access DB. These two spreadsheets have lists of first and last names.

I need to treat one as the "master" listing of names and take the other table and search against the master looking for occurences of the names. To start out, I will just try to match last name against last name. The only catch is the first and last names are all together in one column in each table.

So, what I think I am looking for is a way to take a character string and search another table column for the same character string.

For example:
Let's say the "master" table has a column for name and one of the rows is Duck, Donald. The second table has a name in a row Duck, Buford. I would want to take the string of "Duck" and search the master table for "Duck."

I guess I could do this because the first and last names are seperated by a comma. The reason for not searching for the entire first and last name is because most of the time they do not match exactly. There may be a middle initial or maybe the first name was mispelled. Anyway, I am looking to strip out the last name and search for it in another table.

Where would you suggest I start looking?

Thank you.
Scott

View 4 Replies View Related

Searching With A Like Clause

Jul 12, 2007

hi, is it possible to have a form where you enter a word in a text box and it searchs a table and brings up all records which match it, but it doesnt have to be an exact match.

e.g.
you enter : the

results : the car
the bike
etc.

any help appreciated.

jjames

View 1 Replies View Related

Searching With Froms

Mar 17, 2005

I hope someone can help. I'm new to databases. Can someone tell me how to set up a form so it will filter out records based on what I type in a text box, and filter down as I type?

View 1 Replies View Related

Searching Database

May 27, 2005

hi
does anybody done this project that posted below in little more advanced mode ?!

http://www.access-programmers.co.uk/forums/showthread.php?t=79612

View 1 Replies View Related

Searching A Subform.

Jun 29, 2005

Is there any way to have a query box on one form and have it "filter' persay a list of entries in a form/query in a subform below?

I can't seem to get it to work or find any help on the subject.

View 3 Replies View Related

Searching Problem

Jul 15, 2005

I have implemented the search function in this datebase Search02.zip (found here http://www.access-programmers.co.uk/forums/showthread.php?p=319650#post319650 )

I am having one problem. The Search works fine, but whenever I click in the list box for record to be brought up nothing happens. Although it works fine when I click the add new record button and then click the appropriate record in the list box.

tia
-ryan

View 14 Replies View Related

Searching On Form

Jul 21, 2005

http://lemmikki.uw.hu/promotion.JPG

Okay, this is how my form looks. I have thousands of records, but in a special way. I would like to make a search having the email, when and what together. The table is like this:


email1@xyz.com 20050712 E0001
email2@xyz.com 20050712 E0001
email3@xyz.com 20050712 E0001
email2@xyz.com 20050712 E0002
email3@xyz.com 20050712 E0002
email1@xyz.com 20050720 E0003
email2@xyz.com 20050720 E0003
email3@xyz.com 20050720 E0003
email4@xyz.com 20050720 E0003
email1@xyz.com 20050720 E0004
email4@xyz.com 20050720 E0004

Okay, there's a key for finding the records, but searching by it is quite difficult. Searching by these 3 data would make it easier. But it's quite difficult for me. I would like to have 3 lists (on the picture I have 2 so far) and a button that I can press and the record would come out...


Can someone help me?

View 4 Replies View Related







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