I have a weird situation and I was wondering if anyone has had an issue with this. I have a record missing from a table. Normal Users are not allowed to delete records from the Form view. There is a blank record where the record should be. The auto number counts from 37, 38, 40. Record 39 is missing and there are blanks where it should be. The autonumber field is my Primary Key and it is blank just like the rest of the fields in the record. I know that the record existed at one time because one of the DB users has a report referencing that record. Any Ideas?
I made a query that calculates the sum of from a table that contains defect types and numbers.
The problem is when after the inspection there's no defect no values will be entered in the mentioned table. And running the query the result is 'NOTHING'.
I tried to deal with this 'nothing' putting this in another query:
I want to set up that if there is missing value of the fields, then we cannot go to next record or exit the form when we click to do next action.
I tried to add the code in the After update event, or Exit event,
Example Code: if (isnull([Zip]) or len([zip]) = 0) then msgbox "missing" [zip].setforce end if
when I click to go to next record, or exit the form, it give out message "missing", it work fines, but, it still go to next record, or exit the form after the message.
i have made a query.. when executed it returns 4 results..when i view the report however (made using the wizard based on that query) only 3 results are displayed..i then add a record to the database... the query returns 5 results.. and again the report only displays 4 results...i believe that the first record entered into the database is missing from the report.. but it is present in the query.. could i have accidentally deleted the first record from the report when i was altering the layout in design view??anyone come across this before?is there a general rule with reports based on queries that only display results with certain criteria or something??
I got a problem regarding query work week in database. The database contain data of year 2006 and 2007. When i query about work week, some record is missing.
i wrote the sql statement as SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);
There is one record missing... which is 31/12/2006 record.
I have a database which has one main form linked to a table which has 325 records in it. The problem is when you open the form it says there are only 324 records :confused: I have gone through the table comparing the records with the form records, I found what I thought was the 'missing' record. But when I did a find on the form I managed to retrieve the record. Can anyone help as I'm a bit baffled.
I have a fairly simple query to weed out all the records in our database that are missing vital pieces of infomation :
Code: SELECT Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code FROM Contacts WHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));
Is there anyway to count how many fields are missing for each record ?
I am having an issue with DLookup, which, for some reason which I do not understand appears to missing the first record in the table.
What I am trying to do is to extract the e-mail address of all users who have 'Admin' status to the database. In the table ('LogIn') there are 3 users who have 'Admin' status and this is confirmed by the DCount function in the following code. There respective ID values in the table are 1, 49 and 51.
Yet when I run the Loop to get all e-mail addresses it misses ID 1 and just returns records for ID 49 and 51.
I have tried changing other users status to 'Admin' and it finds them but always misses the first record.
I have a report that is fixed at one page for one record. However, there is a subreport with many transactions ("CanGrow"=No). If this goes to a new page I have to close the report and start a new one. This all works fine, except for the last transaction - if the second-to-last transaction record is at the bottom of a page then the last one does not print.
Access seems to format the data, then it realizes that it won't fit on the page so doesn't print it. I am using record id number to know which transaction record to start the next report on, so when it gets to the end my logic thinks it has reached the end as the id number is the last one in the set.
How can I know if the last record has printed or not?
I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:
I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.
What I thought would work was do a Left Join For Diversity and Employee such as:
SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];
Then, do a Left Join for Gender and Employee such as:
SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];
And then do a Union. But that doesn't work.
Any thoughts or comments would be much appreciated!
I am working on a database where the records are numbered sequentially. On a regularly basis, records will be deleted leaving a gap in the record number sequence. I would like to have my db recognize that a number(s) is number in the sequence and use the missing number (filling in the gaps) when a new record(s) is added until all spaces are filling and then to continue from the largest number sequentially.
I have a form and was wondering if it was possible to set it so that a user is not able to click the submit [Submitcmd] unless certain text boxes are filled (however SampleOuttxt needs to be able to remain blank).
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this ID Username tblDailyLog TimeIn MorningBreakOut MorningBreakOut LunchOut LunchIn AfternoonOut AfternoonIn TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button 3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once) 3. The table is updated with the new time (using some dynamic SQL) 4. The table is repeatadly checked using a DO loop to make sure the the correct time went in. 5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens). 6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other) 7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs. I've built plenty of Databases in my time and have never come across this. __________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue) Dim TempSQL As String TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _ "#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _ "#" & Now & "#," & _ "'" & FieldUpdating & "'," & _ "'" & NewFieldValue & "'," & _ "'" & GetNTUser & "'," & _ "'" & TimesheetNumber & "'," & _ "'" & fOSMachineName & "')" ' MsgBox TempSQL DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False 'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table DoCmd.RunSQL TempSQL, False End Sub
Public Function CleanData(ByVal DataToClean As String) Dim TempData As String Dim i As Integer TempData = "" For i = 1 To Len(DataToClean) Select Case Mid(DataToClean, i, 1) Case "'" TempData = TempData & "`" Case """" TempData = TempData & "`" Case Else TempData = TempData & Mid(DataToClean, i, 1) End Select Next i CleanData = TempData End Function
When I run this Query without the WHERE statement/Clause- it returns the accurate 985 records. However, when I include the criteria with the WHERE clause (it takes a range of +/- 10% of the Square Footage size into consideration), I lose 15 records, and only get 970 results. When I increase the range to +/- 15% in that field, it gives me 7 more records back.
Obviously it is dropping out records that do not meet the criteria for that field- how do I prevent that? I guess I would like it to display a ZERO or something, but still display the entire 985 records. What do I need to include to do that? Here's the SQL-
SELECT tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, Avg(tbSold.SoldPrice) AS AvgOfSoldPrice, Count(tbSold.SoldPrice) AS CountOfSoldPrice FROM tbListing AS tbAct, tbListing AS tbSold WHERE (((tbSold.SqFt) Between ([tbAct].[SqFt]+([tbAct].[SqFt]/100*10)) And ([tbAct].[SqFt]-([tbAct].[SqFt]/100*10)))) GROUP BY tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, tbSold.Status, tbSold.Area, tbSold.Level HAVING (((tbAct.Status)="ACT") AND ((tbAct.City)="TEMPE") AND ((tbSold.Status)="CLOSD") AND ((tbSold.Area)=[tbAct].[Area]) AND ((tbSold.Level)=[tbAct].[Level]));
I hope someone can help me with this. I figure I'm missing one line of code but can't figure it out. I have 3 tables - tblGeneral (which is my main table), tblMDLink, and tblMD. The primary key in tbl General is FileNo. The primary key in tblMDLink is an autonumber and the primary key in tblMD is also an autonumber called MDID. tblMDLink is just a linking table containing the fields Fileno and MDID and uses an autonumber as its primary key. It contains some other fields that have nothing to do with this. I have a query called qryMDLink which contains all the fields of tblMDLink together with a calculated field calle full name which just combines the last name and first name fields of tblMD. Now I come to my form (which is actually a subfrom) called subfrmIMEDoctor. The purpose of the fomr is to allow users to add the names of various doctors to various files. The record source of the form is qryMDLink. The form also contains a FileNo field which is added programatically. The field has a drop down combo box with its record souce set to MDID. The drop down box has two columns with the first (not visible to the user) set to MDID and the second set to Full Name. Here's the problem - it won't let me enter data. Whenver I click on the box I get the message - "You cannot add or change a record because a related record is required in table tblIMEDoctor". By the way, I did set up one to many relationships beteen tblGeneral & tblIMELink and between tblIMEDoctor and tblIMELink. Any help would be greatly appresciated. Thanks, Tom Gorton
I have a relatively large database 65M, 42K records.
For some reason I seemed to have lost most (not all) of the queries I have written. However, if I go to file>database properties>contents, I can see all my queries there.
I certainly did not delete them. Any I idea of what’s going on ? I'm, using Acess 2000 on win2K. Might this be a resources / memory related issue?
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Hi, when designing reports or forms, when I pressing the toolbox icon, it would depress, but no toolbox was visible. I tried resetting the toolbox (through view/toolbars) and I set all of the settings back to default. I even uninstalled and reinstalled MS Access several times but no luck. Any help please.
Recently I lost the the functionality of the toolbox then shortly after I lost the toolbox icon completely. The only way to use the features was to create an new toolbar. System Restore finally fixed the problem. This has happened twice on me with the 200 and 2003 versions. Is it me? and is there an easier way to sort this. Reinstalling Access never cured the problem either.
I have a database that has just been split. In the back end, I don't see the tables. I have gone to Tools>Options>View and found that Show Hidden Objects is checked. When I go to the front end, the links to the tables show up and I can open them and see the data. When I check the Linked Table Manager, I see that the path for the links is leading to the back end database.
I also noticed that on the bottom of the screen, the buttons for the front end show the name of the front end database and have blue page-like icons, but the button for the back end has only the pink key icon. Also, when I try to link another mdb to these tables, the window for selecting the table for linking is empty. I also notice that suddenly, any time I close an Access database, it goes through a compact and repair cycle. This just started happening today. I'm fairly sure that yesterday I could see the tables in the back end.
Oh, yes. Just remembered one other thing. Not everyone here has that location on the network mapped to the same drive. In order to accomodate everyone, the linked table manager was set up with the complete path to the folder:
\companyname.comfolderlevel1folderlevel2folder level3...ackend.mdb instead of J:.....
Using Windows XP and Office 2003
There is no verticle scroll bar as was mentioned in another post I found when doing a search on "Hidden Objects".
Hi, i've developed a program in access 2003, but now i'm on access 2000 and seems that one / more library is/are missing..
i've this part of code
Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim strOrd As String, strSql As String If Button = 1 Then If Y <= 225 Then If Right$(List0.RowSource, 5) = " Asc;" Then strOrd = " Desc;" Else strOrd = " Asc;" End If strSql = "SELECT cliente.CLI_ID, cliente.CLI_TITOLO, cliente.Cognome, cliente.Nome, cliente.Indirizzo, cliente.Cap, cliente.[Citta'], cliente.Regione, cliente.Nazione, cliente.CLI_SOCIO FROM cliente WHERE (((cliente.CLI_SOCIO)=Yes)) ORDER BY " Select Case X
and this is the screen with the libraries currently installed ..
Alot of my databases entries are missing. today i went on MS access 2007 and it said "security risk" so i went to options and enabled marcros. but when i get on to my database there is only one entry! but i know the information is there. the file is 1700 mb. what should i do?
There are two tables in my database. A and B. Each table has the same criteria: Name, Number, and Date.
I have created a formula in my query which will add the Number fields together when they both have the same name. My problem is that when there is not a row for a particular name on table B, the calculation does not list that row in the results.
I would like for my calculation to insert a zero in the space and add Number.
I have tried the Nz function, but this results in a missing name and date and just the numbers are shown.
How do I get the results to show a row where the name is only on one field and still make sure that it places a zero in its spot and add the zero and the other number together based on the name.
