Data Error - Help Please
Jun 20, 2007
I have had little reponse to this on the Queries forum so am trying here in a hope that someone can advise me. You can tell Im only a dabbler and not a programmer from my language....
Query "Q_Pot_Spec_PR_entry" shows two joined tables
T_Pot_Spec_PR and T_Pot_FSN_PR
with all records from T_Pot_Spec_PR, and those that match from the T_Pot_FSN_PR.
Clicking in the "Featured Sherd" Yes/No field of T_Pot_FSN_PR will create a record in the second table with 1. a copy of the "Pot_Record" from the first table and 2. a new Autonumber PK
Within the query or a form view of the query I find that I can create up to 4 new records, and then I get "You cannot add or change a record because a related record is required in Table T_Pot_Spec_PR" message. Closing the Dbase down and reopening seems to sort it for a few more records.
I have the same set up on a sister set of data and it works fine. However if this is a known problem I should not be setting up my tables this way. How should I then do it to create related records in separate tables that automatically enter the common field?
Help PLease?
Attached Files A2-2003.zip (205.2 KB, 0 views)
View Replies
ADVERTISEMENT
Apr 3, 2007
I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:
DoCmd.TransferSpreadsheet acImport, , "tblMPSDATA", _
"" & stFilePath & "", True, ""
Everything works fine except the column which contains 'Material' holds both numeric and alpha-numeric values. For example these are both material numbers: 156952 and 1238707-202. The data in this column is formatted as General. The data type for Material in tblMPSDATA is Text, 18 character length. The alpha-numeric materials are all at the end of the file. When I import, an error table is created listing the alpha-numeric materials with the error 'Type Conversion Failure'. But if I have an alpha-numeric material in the first row of data then everything is imported just fine.
I have set up a nice little popup form with a file path and command button for controlling the process of bringing in this data. I really do not want to have to add special instructions about making sure the Excel data is sorted in a certain manner prior to importing. Any thoughts on why Access is not treating everything in this column as text?
Thanks,
JAB
View 5 Replies
View Related
Aug 7, 2006
I just linked a table to an Access Database from SQL Server. Now, whenever I open the linked table, I receive the error "Scaling of decimal value resulted in data truncation."
After a quick Google search (and search of these forums), I found some solutions:
what this means is that your number / data had a whole load of numbers after the decimal point, the length (width) of the field was not enough to display them all so some of the end was got lost
Increase the field size, or, limit the size of the number via the table, in design view, FIELD SIZE
It would appear that somewhere along the way you are
implicitly converting from a decimal value that has high
precision to an integer or other type value that has low
precision. During the conversion their is some data loss
in the form of lost precision.
So, I guess I found the problem, right? I just don't know what I'm suppose to do to correct the issue. There is not a single field in my SQL database that has a precision beyond 10 and a scale beyond 2. So...erm...Why can't Access just deal? What am I doing wrong?
~Andrew
View 1 Replies
View Related
Nov 3, 2005
Okay, I figured out that I can use the instrrev function to look at data from right to left.
I used this function:
Right([mydata], InStrRev(1, [Mydata], "-"))
in a query in an attempt to get all data right of the "-".
When I run my query, I get "error" instead of the data I was expecting.
What is wrong with the function? BTW i am using Access 2002.
TX
Al
View 4 Replies
View Related
Jan 26, 2006
Hi
I am getting the following error when I try to present some information from a database:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/tribute2.asp, line 168
The code relating to this is:
The error line it is talking about is:
Code:rsGuestbook.Open SQLstr, adocon
Code: <%Dim adoCon 'Holds the Database Connection ObjectDim rsGuestbook'Holds the recordset for the records in the databaseDim SQLstr'Holds the SQL query for the databaseSet adoCon = Server.CreateObject("ADODB.Connection")adoCon.Open = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:inetpubftprootlocalusericlay
emember ing.co.nzdb
emembering.mdb;"strcustomerID = Request.QueryString("remID")Set rsGuestbook = Server.CreateObject("ADODB.Recordset") sqlstr = "select * from guestbook where guestbook.mid =" & strcustomeridrsGuestbook.Open SQLstr, adoconDo While not rsGuestbook.EOF'Write the HTML to display the current record in the recordsetResponse.Write ("<font face='Arial' size='2' color='#000080'><i>A tribute provided by ")Response.Write (rsGuestbook("confirstname"))Response.Write (" ")Response.Write (rsGuestbook("conlastname"))Response.Write ("</i></font><font face='Arial' size='1' color='#000080'><i> ")Response.Write (rsGuestbook("conrelationship"))Response.Write ("</i></font><br><br>")Response.Write (rsGuestbook("conmessage"))Response.Write ("<br>")'Move to the next record in the recordsetrsGuestbook.MoveNextLoop'Reset server objectsrsGuestbook.CloseSet rsGuestbook = NothingSet adoCon = Nothing%>
Can anyone see where I have gone wrong ... could it be something to do with the fact that 'remid' relates to another table in the database. Though when I did a reponse write on the sqlstr it is presenting the remid.
Hope this doesn't sound to confusing.
View 8 Replies
View Related
Sep 4, 2007
Hi,
I've join a small company who uses access to store a route card system. Unforunatly it's partially broken and nobody seems to know how to fix it. Instead of bodging it forever i'm hoping to fix it.
My access knowledge is fairly limited so please keep it simple
The route cards are fairly standard, the primary key beening the route card number, the are various fields material, order date, etc.
The problem is when you enter the data into 3 of these fields access repeats the data to all the forms. At the moment we're having to leave them blank and fill them in by hand, which means we don't have the data on computer.
Anyone know any possible causes for this?
View 1 Replies
View Related
Mar 5, 2014
I am trying to aput a stock number into a form called (products) but it tells me there is no matching number in another table ( stocklist). yet that number is in another table (stocklist.
View 2 Replies
View Related
Jun 6, 2013
I have a SQL database with an Access front end that keeps giving me an error "The data has been changed... another user has edited the record.I'm the only user on it, and I'm trying to edit it directly from the table. I already checked my indexes and changed all the bits to ints.
View 6 Replies
View Related
Aug 9, 2012
I have fronend and backend database. I have all data stored in another database (backend) and that database is password protected. I need to export some selected data into Excel which is created in the same process. Below is the code:
Code:
Private Sub ExportLeaversList(strWorkbook As String)
On Error GoTo ERR_HANDLER
Dim objApp As Object
Dim strExcelFileName As String
Dim varStatus As String
Dim strTempQueryName As String
Dim strSelectSQL As String
Dim strPnPDatabaseName As String
Dim strPnPDatabasePassword As String
[code].....
I'm getting error at below line
.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, strWorkbook, True
Run-time error '3275':Unexpected error from external database driver (1309).
View 2 Replies
View Related
Sep 3, 2015
now when I try the section performing data entry with SQL, I am at the point where I have typed the SQL statement:
INSERT INTO Employees(EmployeeNumber, [Last Name], Gender, HourlySalary)
VALUES ('227947','Jameson','M',18.85);
It chokes on the "unknown variable" HourlySalary, I could find nothing wrong with it.I then deleted my HourlySalary and copied theirs directly from the tutorial. It still chokes on HourlySalary.Now I am not sure what to do since I have taken their script and copied it to the SQL window.
View 4 Replies
View Related
Jun 21, 2005
Hi all
I am putting together a quick and dirty db to hold just a few thousand bits of data for some tests I’m about to start.
For various reasons it is better to hold some of these in Excel, and to link Access to Excel.
The problem I have is that when I use Access to edit a “record” in Excel, it will work for the first one or two records, but then Access crashes and I get the very familiar “Sorry for the inconvenience, please send this error file to uSoft blah blah blah” (which I always do, but wonder if anyone ever takes any notice of them!).
The problem occurs whether the spreadsheet is open in the background, or is just sitting closed in its directory with Excel not running.
At the moment, I only have a tiny amount of data as I’m still putting it together, so it’s not a “size” issue; I first noticed the problem with only about ten records!
I expect I’ll move everything to Access (not ideal but hopefully it wont crash quite so often), but I’m curious to know if any of you have actually made a successful link between Access and Excel (and used it for editing data, not just looking, which is fine).
My system: Office 2003 pro/developer, Win XP pro.
Thanks for any thoughts
Skeletal
View 4 Replies
View Related
Feb 20, 2008
Attempting to change a queries calculated field properties I got the message
The Microsoft Jet Database engine stopped the process because you and another user are attempting to change the same data at the same time
Clever since I'm the only user on a stand alone PC.:confused:
I closed the database and tried again but with no joy.
Anybody know what the cause/solution is.
Brian
View 4 Replies
View Related
Apr 28, 2008
Hello All,
I changed one of my queries to generate the period off a date in a field. The simple expression is:
Period: Month([Act Del])
It give me the number of the month...however now I am getting this error. I assume that the number of the month is not matching with the other data types....but I need to know who to fix it.
any ideas?
Thanks
View 4 Replies
View Related
Jan 4, 2005
Hello all - One of my 3 users' computers keeps coming up with an error when I try to enter the following command in the "Validation Rule" field of the table design view:
>=#1/1/1998# And <=Date()
Some investigative work showed that it's erroring on this clause: "<=Date()". Any idea why it would error on her pc and not the other 2 pc's? Her pc is old, but we're all using Access 2002. The error says: "The function you entered can't be used in this expression." Any thoughts would be helpful. Thanks much.
Dan
View 3 Replies
View Related
Mar 8, 2008
This is going to be extremely frustrating for all parties involved, because there's no error message - or any hint as to what's going on.
I have a form, frmSub, that has comboboxs linked to fields in two different tables, Products and PurchaseDetails. All the comboboxes linked to Products are working fine, but when I try and enter anything into the ones connected to PurchaseDetails all I get is a 'beep' and nothing happens.
I've definitely included all the appropriate fields in the record source of the form. I have no idea what else to try.
Here's the code for the record source query of the form. PurchaseDetail.Price and PurchaseDetail.Quantity are the fields not working:
Code:SELECT Products.Product, Products.Size, Products.Brand, Products.UnitOfMeasure, Products.ProductID, PurchaseDetail.Price, PurchaseDetail.QuantityFROM Products INNER JOIN PurchaseDetail ON Products.ProductID = PurchaseDetail.ProductID;
View 3 Replies
View Related
Mar 19, 2014
I am using below code to import various Excel spreadsheets into Access. However every now and again below code goes into error, usually because there is no "toimport" range in the Excel file.
So I would like to use On Resume Next, so it can import all other files. But in order to do it, I need also to be able to somehow capture information about all files that went into error, so I can fix them.
I would like to create like an "error log" and I would like Access to update it with information about all the files that were not uploaded + error message + date/time.
Ideally I would like it to be in Excel file. I tried to use DoCmd.TransferSpreadsheet but it does not do what I need
Code:
Function import()
Dim strFile As String
DoCmd.SetWarnings False
' Set file directory for files to be imported
strPath = "C: est"
[Code] .....
View 5 Replies
View Related
Jun 21, 2015
PHP Code:
Dim strSQL As String    'Add Absence Data to tblHour.
strSQL = "INSERT INTO tblHour (WorkDate,EmployeeID,Hours) "
strSQL = strSQL & "VALUES (#" & Me.AbsenceDteTo & "#, '" & Me.EmployeeID & "', '" & Me.txtAbsHrs & "')"
        CurrentDb.Execute strSQL, dbFailOnError
[Code] ......
View 3 Replies
View Related
Apr 16, 2014
I have a small form with a combobox that gets its data from a query.
I added code to this database to forward to me info about forms and controls in case they generate errors. Occasionally I get an "error = 0" on this combobox.
Since there are no events attached to this combobox then what could be causing this error?
View 8 Replies
View Related
Mar 31, 2015
i recieved error on the following connection string in the combo box change event code as follows,
Private Sub LotNo_Change()
Dim conn As New ADODB.Connection
Dim sConnString As String
[Code]....
View 6 Replies
View Related
Feb 15, 2015
I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:
Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).
Or there are column name spellings and in such cases, I get no error and the simply code hangs.
Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.
'Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
On Error GoTo Macro1_Err
DoCmd.SetWarnings False
' RunSQL executes a SQL string
[Code] .....
View 7 Replies
View Related
Apr 26, 2013
How to create custom error message for duplicate data? I want this custom message at text box before_update event not at form_error .....
View 1 Replies
View Related
Jun 6, 2012
I have a 2003 mdb which I have opened in 2010 from my C drive. If I hover my mouse over a table name it shows the original UNC path. How do I change this so I can view the data without an error message appearing?
View 1 Replies
View Related
Sep 27, 2013
I'm a newbie trying to upgrade a 2003 database to 2010. When I compile this database an error pops up saying "Method or data member not found". This is the code below:
Private Sub CBPrintReq_Click()
On Error GoTo SendPrintError
Dim NewID As String, Notice As String, Temp
Dim CRLF As String * 3
CRLF = Chr$(10) + Chr$(13)
Notice = ""
[Code] ....
View 1 Replies
View Related
Mar 23, 2006
Hello,
I have written an extensive Access DB Application for my company and have placed it on a cd for distribution. When we try to copy it from the CD to a folder on another machine the above mentioned error comes up. Any Suggestions. Jeff
View 3 Replies
View Related
Nov 5, 2005
I am using the funtion:
Clng([text field]) to convert a text field into a number so that I can link in a query. Because it is an informix database, I cannot change the underlying table format.
When I run the query, the data is showing "Error".
I tried changing the other table to text and linking but it doesn't seem to link properly because the query doesn't work.
Your advice appreciated.
View 5 Replies
View Related
May 18, 2006
Hi all, I’m trying to filter the records a subform shows based on the employee number (in a combo box) that the user selects in the main form. At the moment im getting the following message when I select an employee number in the combo box: “Compile error, method or data member not found”:confused:
Ive searched the forums on here and tried to make use of the information other people have posted but im still having trouble getting this to work
Below is the code I have on the combo box of the main form:
Private Sub Cbo_Emp_Filter_AfterUpdate()
Dim strSQl As String
strSQl = " Select * from DATA-EMPLOYEE_MASTER where DATA-EMPLOYEE_MASTER.EMPLOYEE_NUMBER=" & Form![SCREEN-ABSENCE_TRACKING_MAIN]![EMPLOYEE_NUMBER]
Me.SUBFORM_ABSENCE_TRACKING.RecordSource = strSQl
Me.SUBFORM_ABSENCE_TRACKING.Requery
End Sub
Ive also posted the database if any one is willing to have a look for me (ive removed irrelevant tables/forms/reports). Im using access 97
Thanks
Jim
View 3 Replies
View Related
