I have tried to do a search on this but i cant seem to find something similiar. And I did a post of what i wanted to do here:
Click Me (http://www.access-programmers.co.uk/forums/showthread.php?t=74845)
This is my table structure:
http://www.geocities.com/gerald20000/Alpha/table.jpg
** I have keep this structure the simple. In actual of what i want to do, its more section under the 3rd lvl. as in more section under tblNAMe and tblRELATIONSHIP.
tblMALE (Question related to Name - Male will be here)
MaleQuestion
MaleAnswer
tblFEMALE (Question related to Name - Female will be here)
FemaleQuestion
FemaleAnswer
tblMOOD (Question related to Mood will be here)
MoodQuestion
MoodAnswer
tblNEAR (Question related to Near Relation will be here)
NearQuestion
NearAnswer
tblFAR (Question related to Far Relation will be here)
FarQuestion
FarAnswer
So what am i trying to do? People would put in a question and an answer into a box. After that, the person can choose which category does the inputed question belongs to which category(male? female? .. ).This is actually a FAQ search engine.
So ppl will have an option to search for keywords and match questions. There should also be an option weather it should search from tblQNA or tblName or tblGOOD. So it has different level of searching. This is why it has such tree. So after searching, it will display the possible matched question (display question only). then the user can click which question to view the answer (together with the question).
Hope to get some advise. Is this how the best way to implement? OR is there a better method? pls advise and thanks in advance. ill be trying to do the access now. ill post as i goes along.
I am trying to create a search facilty that will allow me to search any table /form. I have read some posts and have tried to download some of the examples in the sample database section,however after downloading I can't open as my version of access (97) says it does not recognise the format. Ant suggestions would be appreciated . Thanks Treggy
anyone knows how to make a search engine inside microsoft access or database? like for example, i want to look/find information on a specific record.. how will i start making a search engine? help me coz i dont know how.
I would like to create a query where I can search my selected fields on a particular field. However I do not want to do this in the standard way. I would like the query to appear in a 'search engine' type format (like a form but with a search). Is this possible????
anyone knows how to make a search engine inside microsoft access or database? like for example, i want to look/find information on a specific record.. how will i start making a search engine? help me coz i dont know how. :confused:
Here I am again all lost in the beyond side of Access. So, I would like to create a "search engine" , through a macros if possible, so that the users can find the one thing among the hundreds of specs available to them in this form/subforms assebly.
My form has two subforms inbuilt in it, hence it can be really time consuming to find the one piece of information they are looking for. And I was hoping that there was a way they could just type for example "rated voltage" in the search box and they would jump to the one textbox titled "rated voltage". Just like most of us do when reading a large document online, we just use "find" to get what we are looking for.
I have a form with about 30 fields on it, all connected to a table with file information. I want to create a search form using all 30 fields, so that if a user inputs information in any one of these fields and clicks search, it will find records based on the combination of what he/she inputted in the fields. For all the fields that he/she leaves blank, the search engine will ignore in its search.
I have already created a query that does this somewhat. For each field, I have used this as the criteria:
I have put this in the criteria and they are all linked by an And statement. It works fine, except that the program does not seem to match Null fields together. So, if the user leaves a field blank, the search won't ignore that field, it will only show records with some piece of data in that field. All records that are Null in that field are cut out.
So, I guess my question is: how would I make the program be unbiased towards fields that are Null and let it include records that have null in the field? Am I going about this the wrong way?
Say I have a piece of lumber in inventory that's 20 ft. long. I cut it into 2 pieces, one 13 ft. and the other 7 ft. Now I need to remove the 20 ft piece from inventory and replace it with the 2 pieces I just cut. Is there any way to automate this in Access? I'm have trouble visualizing and approach to this problem.
I've been tasked with modifying an Access97 query. Currently the query has a single contraint on the field titled jobtitle. We are interested in maintaining this constraint but in the event of null values defaulting a new contstraint to field called contacttype. Thus if there are no results returned for positiontitle = 'dtbc' return all rows where contacttype = 'prim'. It would seem an if then is the only option and I'm not sure how to approach this in access.
Thank you,
Josh
:confused:
Currently here is the query with the constraint only on contacttype = 'prim' SELECT [Edition Header File].edition, [Edition Header File].instant, [Edition Header File].[page_#], [Edition Header File].franchise_page, [Edition Header File].subedition, [Edition Header File].subedition_page, [Edition Header File].supplier_name, [Edition Header File].supplier_line, [Edition Header File].sup_page, [Edition Lines File].product, [Edition Lines File].sup_style, [Edition Lines File].imp_style, [Edition Lines File].discount_code, [Edition Lines File].sup_disc_code, dbo_DistSup.Company, dbo_DistSup.LineName, dbo_DistSup.AD1, dbo_DistSup.AD2, RTrim$([CITY])+", "+RTrim$([ST])+" "+RTrim$([ZIP])+IIf(([St]) In ('LB','NT','AB','YK','PQ','BC','MB','NF','NB','PE' ,'ON','NS','SK'),"CANADA",IIf(Len(RTrim([St]))<3," USA")) AS city_to_zip, Trim([emailaddr]) AS tEmail, Trim([webaddr]) AS tWeb, [FName] & " " & [LName] AS contact, dbo_DistSup.Phone, dbo_DistSup.TollFreePhone, dbo_DistSup.Fax, dbo_DistSup.TollFreeFax, dbo_Alternate_ID_1.IDNumber AS asi, dbo_Person.ContactType, dbo_Person.ContactType FROM ((dbo_Person INNER JOIN dbo_Jobs ON (dbo_Jobs.PerNbr = dbo_Person.PerNbr) AND (dbo_Person.EntNbr = dbo_Jobs.EntNbr) AND (dbo_Jobs.OrgNbr = dbo_Person.OrgNbr) AND (dbo_Person.USTOID = dbo_Jobs.USTOID)) INNER JOIN (((([Edition Header File] LEFT JOIN [Edition Lines File] ON ([Edition Header File].[page_#] = [Edition Lines File].page_no) AND ([Edition Header File].instant = [Edition Lines File].instant) AND ([Edition Header File].edition = [Edition Lines File].edition)) LEFT JOIN dbo_DistSup ON [Edition Header File].instant = dbo_DistSup.Instant) INNER JOIN dbo_Alternate_ID ON [Edition Header File].instant = dbo_Alternate_ID.IDNumber) INNER JOIN dbo_Alternate_ID AS dbo_Alternate_ID_1 ON (dbo_Alternate_ID.EntNbr = dbo_Alternate_ID_1.EntNbr) AND (dbo_Alternate_ID.OrgNbr = dbo_Alternate_ID_1.OrgNbr)) ON (dbo_Person.USTOID = dbo_Alternate_ID.USTOID) AND (dbo_Alternate_ID.EntNbr = dbo_Person.EntNbr) AND (dbo_Person.OrgNbr = dbo_Alternate_ID.OrgNbr)) INNER JOIN dbo_PositionTitle ON dbo_Jobs.PositionTitle = dbo_PositionTitle.PositionTitle WHERE (((dbo_Alternate_ID.IDType)="inst") AND ((dbo_Alternate_ID_1.IDType)="asi")) GROUP BY [Edition Header File].edition, [Edition Header File].instant, [Edition Header File].[page_#], [Edition Header File].franchise_page, [Edition Header File].subedition, [Edition Header File].subedition_page, [Edition Header File].supplier_name, [Edition Header File].supplier_line, [Edition Header File].sup_page, [Edition Lines File].product, [Edition Lines File].sup_style, [Edition Lines File].imp_style, [Edition Lines File].discount_code, [Edition Lines File].sup_disc_code, dbo_DistSup.Company, dbo_DistSup.LineName, dbo_DistSup.AD1, dbo_DistSup.AD2, RTrim$([CITY])+", "+RTrim$([ST])+" "+RTrim$([ZIP])+IIf(([St]) In ('LB','NT','AB','YK','PQ','BC','MB','NF','NB','PE' ,'ON','NS','SK'),"CANADA",IIf(Len(RTrim([St]))<3," USA")), Trim([emailaddr]), Trim([webaddr]), [FName] & " " & [LName], dbo_DistSup.Phone, dbo_DistSup.TollFreePhone, dbo_DistSup.Fax, dbo_DistSup.TollFreeFax, dbo_Alternate_ID_1.IDNumber, dbo_Person.ContactType, dbo_Person.ContactType, dbo_Alternate_ID.USTOID HAVING ((([Edition Header File].edition)=[forms]![data entry header file].[form]![edition]) AND (([Edition Header File].instant)=[forms]![data entry header file].[form]![instant]) AND (([Edition Header File].[page_#])=[forms]![data entry header file].[form]![page_#]) AND ((dbo_Person.ContactType)="prim") AND ((dbo_Alternate_ID.USTOID)=1)) ORDER BY [Edition Header File].edition, [Edition Header File].instant, [Edition Header File].[page_#] WITH OWNERACCESS OPTION;
Hi, ive been asked to provide a solution, for an electronic spreadsheet be sent out via email then returned by customers, once filled in for all the data to be collected onto one sheet that looks like the attached sheet. the easiset way i can see is to not use a spreadsheet but to use a datbase instead and just put it in the desired format, how easy is it to import mutliple spreasheets into correct fields on a dbtable thanks for any input or ideas
I'm trying to build a table structure for this database. Heres some background information about the business:
The business is a service business. We visit the customer's location and run tests on whatever water systems they have. Each customer is unique in that they could have any combination of systems at their site. They can also have more than one of the same type of system. The test results are the data that I need to record and store for future access. Each customer is visited on average once or twice a once a month. So there should not be more than 1-2 entries of data for each system for each customer per month.
For example customer RUTGERS might have two systems labeled HWS and CHWS. customer BMSQUIB might have three systems labeled HWS1, HWS2, and CHWS.
What I need to do with this information is go into the records of service visits, and retrieve for example, the last 4 visits of a specific system and prepare that information to be printed in a report along with their contact information.
I have come up with three tables to do this:
Customers Table contains: Customer ID (pk), contact information data.
Systems Table contains: System ID (pk), Customer ID, System Name
Service Records Table contains: Record ID(pk), Date, System ID, Data
My thoughts were to have a table to contain customer information(each customer with a unique ID), a table to contain system information for each customer (each system has a unique ID), and a table to store the results of every service visit for each system(each individual visit has a unique ID)
Please critique this table design. If you think its sufficient, perhaps you could lead me in a direction pertaining to how to retrieve data on the most recent 4 visits (last 4 entries) for a specific SINGLE system from the Service Records table. I would assume that you would need to use a query and then get the data from that and put it into a form.
Edit:I just realised i had accidently writted the title as (to do with importing access data) it should read (to do with importing excel data)This is going to be a trick hard to understand question but I will try my best to explain itI have a database set out in the following wayhttp://img524.imageshack.us/img524/1350/databasetableli1.pngThe way it works is; Let's pretend Access Programmers is a company and working on different forums is a different jobSo on one record it would readJames.90| Access Programmers|Tables Forum| Wed=3= Mon=2Then the record below might readJames.90| Access programmers | Forms Forum| mon=5 tue=6So each record is one unique company,Project and CTR which the person has worked for that week meaning if you only work on one forum you would only write one record out each weekNow the data i am receiving is in an excel file where it's set out in a daily basis Where One Day Date|Name|Company|CTR|etcSo if a person works 5 days a week on 2 companies each day that is 10 records when it should only be 2 recordsSo to sum it up. My database is set out weekly and the excel data is set out dailyMy questionWhat would be the best way to convert this data into the database. Changing the database structure around is not an option and i can't change the format we recieve the excel data in. I can change it once i have the file thorough a converter but i can't change the raw source of the dataWhat would be a way to solve this problem because i am completly stummted and am open to any option of converting or anythingThankyou for your time. Also if you have trouble understanding what i mean Please say so and i will upload a copy of the database and a copy of the excel sheet!
Okay - well, I have figured out how to edit the schema file but now I am getting a different error message when I try to do the export:
The Microsoft Jet Database engine could not find the object "Form888.txt". Make sure the object exists and that you spell its name and path name correctly.
I believe that it is spelled correctly and I have changed the path a few times to see if it was that and it's still not working. Can anyone help me with this please?
My 641 export works fine the other 2 do not. I get the above error messge.
Someone else set this up for me and I know very little about VB. I kinda just do like trial and error, you know?
Heres the code:
Private Sub cmdUpload_Click()
On Error GoTo Err_Upload_Click DoCmd.TransferText acExportDelim, ExportSpec, EDMISfile, txtUpload MsgBox txtUpload & " written." DoCmd.Close (acForm), "frmedmisupload" Exit Sub
Err_Upload_Click: MsgBox Err.Description
End Sub
Private Sub comEDMIS_AfterUpdate() On Error GoTo Err_EDMIS
We have been having intermittent problems with an MS Access 2000 front-end application linked to a SQL Server 2000 database recently.
From the switchboard a user was sometimes getting "There was an error executing the previous command". When she shut the application and opened it again it works fine for a little while - then the error occurs again.
I removed the generic error handling code from the code for the Switchboard form and I got the error message: "The Microsoft Jet database engine cannot find the input table or query 'Switchboard items'. Make sure it exists and that its name is spelled correctly."
I have been searching this forum and the web to find out what the cause of this error message is but I cannot find anything to go on or to try that might help get rid of this problem.
Does anyone know why this error occurs? Any idea how to stop it of happening?
Any ideas or suggestions would be greatly appreciated.
Start with: frm_W-GraphSearch select from combo and ever this for date criteria: 12/1/06 to 12/13/06 Open report - There you will see one out of three graphs showing Go to Report Design mode and open the sql in one of the graphs and try to run it, there you see the error:
"The Mocrosoft Jet engine does not recognize 'Forms![W-GraphSearch].Text0' as a valid field name or expression."
Dear All, I prepared times ago a database that contains important data that will be frequently updated. Since yesterday I cannot load the database anymore. I get a pop-up with following statement: Quote The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time Unquote If I click ok, the loading process will be aborted. I'm the only user of this database, neither the database nor the directory containing the database is sharable. It is located on the harddisk of my computer which nobody from outside can access. What can I do to recover the access to my database?
I use MS Office 2003 but tried to open the database also on MS Office 2000 with the same result. The Help function of MS-Access does not really help. Who knows how to solve the probleem???? With regards Siegfried
I have a form that contains 3 required fields i.e. linked to other tables using an ID. If the user tries to close the form without entering data in the required fields I get the message: "The Microsoft Jet database engine cannot find a record in the table 'table name' with key matching field(s) 'myID'
I have created an If Then Else statement to check if the required fields have been filled in, and if not display a MsgBox. the problem is that Jet database engine message pops up.
I've tried using DoCmd.SetWarnings False on the form but this doesn't stop them.
i get this error the time i enter info into a registration page from where i am getting all the customers info
Microsoft JET Database Engine error '80004005'
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. in line 74 and my corresponding query is
Set Commrs2 = Server.CreateObject("ADODB.Command") Commrs2.ActiveConnection=strconnect line 74: Commrs2.CommandText = "INSERT into registration(username,fname, mname, lname, sex, address, city, state, country, pincode, phone, mobile ,email)VALUES('"&logid&"','"&fname&"','"&mname&"','"&lname&"','"&sex&"','"&add&"','"&city&"','"&state&"','"&country&"','"&pin&"','"&phone&"','"&mobile&"','"&email&"' )" Commrs2.Execute
Set Commrs3 = Server.CreateObject("ADODB.Command") Commrs3.ActiveConnection=strconnect line 79 : Commrs3.CommandText = "INSERT into userpass(username,password) VALUES('"&logid&"','"&pass&"'" Commrs3.Execute
also sometimes i get this on line 79 Microsoft JET Database Engine error '80040e14' Syntax error in INSERT INTO statement.
i did a google search and i found this article http://www.kbalertz.com/kb_884185.aspx but i was not able to find the security warning dialogue box anywhere
I am trying to access a Microsoft Access database located on a server on my network from my web server. The folder containing the access database has been shared on the network with everyone having full access. I created a virtual directory on my web server pointing to the share on the other server and when I try to connect it says:
Microsoft JET Database Engine (0x80004005) The Microsoft Jet database engine cannot open the file '\server1databasejt_test.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
I am trying to make changes to a particular field in Access but whenever I try to do this, I get an error messsage saying that Microsoft Jet Database Engine Stopped the process because you and another user are attempting to change the same data at the same time.
There is no once accessing the database at this time and this error message appears only when I go to that specific field. I've also tried to delete the whole row but it wouldn't allow me to do that saying the program has been locked.
I have a recursive script that seems to timeout after a few dozen requests, the database is returning the typical "Microsoft JET Database Engineerror '80004005' " error ("Unspecified error").
The code is pretty straight forward, it is a function that calls itself to generate a tree structure for a site map. It works great the first few dozen times but seems to 'time out' and return the above error after a few dozen records (building the tree).
The code looks like this :
Function BuildContent(id,depth) ' open the children Set GetKids = Server.CreateObject("ADODB.Recordset") GetKids.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:usersXXXXhtmldb1.mdb" GetKids.Source = "SELECT ID,Name FROM category WHERE parent = " & id & "" GetKids.CursorType = 0 GetKids.CursorLocation = 2 GetKids.LockType = 1 GetKids.Open() GetKids_numRows = 0
if GetKids.EOF <> true then MyLinks = countlinks(id) MyCats = countkids(id) response.write "Number of Listings (" & GetKids("Name") & "): " & Mylinks & "<br>"
maxdepth = 1 BuildContent = "<ol>" while GetKids.EOF <> true
if MyCats > 0 then show = true if KidsCats > 0 then show = true if depth < maxdepth then show = false
if KidsLinks > 0 then show = true if MyLinks > 0 then show = true
if show = true then BuildContent = BuildContent & "<li><a href='" & GetKids("id") & "_e.asp'>" & GetKids("name") & "</a></li>" else BuildContent = BuildContent & "<li>" & GetKids("name") & "</li>" end if if depth < maxdepth then BuildContent = BuildContent & BuildContent(GetKids("id"),depth+1) GetKids.MoveNext wend GetKids.Close() Set GetKids = Nothing
BuildContent = BuildContent & "<br></ol>" end if End Function
there are a couple of support fuctions :
Function countkids(id) Set GetListing = Server.CreateObject("ADODB.Recordset") GetListing.ActiveConnection = MM_Connection_STRING GetListing.Source = "select * FROM category WHERE parent = " & id & "" GetListing.CursorType = 1 GetListing.CursorLocation = 2 GetListing.LockType = 1 GetListing.Open() GetListing_numRows = 0 countkids = GetListing.RecordCount GetListing.Close Set GetListing = Nothing End Function
Function countlinks(id) Set GetListing = Server.CreateObject("ADODB.Recordset") GetListing.ActiveConnection = MM_Connection_STRING GetListing.Source = "select * FROM records WHERE parent = " & id & "" GetListing.CursorType = 1 GetListing.CursorLocation = 2 GetListing.LockType = 1 GetListing.Open() GetListing_numRows = 0 countlinks = GetListing.RecordCount GetListing.Close Set GetListing = Nothing End Function
I think the DB is being called too often, too quickly but I have no way of slowing the script down. If that's the case, is there a way to slow the code execution to give the DB time to catch up?
Otherwise, is it a driver problem (Microsoft JET Database Engineerror '80004005' ) and would switching to a OBDC connection give me better results or should I be using a different db driver?
I'm getting this error Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query _expression '123 street'.
The field in DB is type text. But the data to be written in the field is; number followed by characters. In other words it is an address field on the form, trying to insert new record into DB.
here is 3 different data I entered into field:
1. 'just some text' - works fine 2. 'text and number 123 ' - works fine 3. '123 number and text' - does not work. and gives the above error. Anybody has any idea?
