Help!

[ch5150]

Hi guys,
I have a db where the main table/form is OperatorReports. I have sub-forms that allow users to review the reports by WorkOrderNumber/Date. The challenge I now face is allowing the user to open a new Operator Report that has the header fields autopopulated by the information on the report they were looking at so that the can continue the project but add their own information. I tried to run with an unbound form that reproduces the original OpReport form but uses expressions to fill the fields and an append query to add the data to the table. However, I get errors running the apQuery.
My goal is to come up with some code to create a dataset of the header info to put into the original OpReport form.
Not sure of the best way to accomplish this task and am open to any suggestions.
Respects and regards,
Chris

 

[JHB]

.. I tried to run with an unbound form that reproduces the original OpReport form but uses expressions to fill the fields and an append query to add the data to the table. However, I get errors running the apQuery.

What error do you get, (number and description)?
Show the SQL-string for the apQuery.

 

[ch5150]

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 1 record(s) due to validation rule violations.

The first expression is Expr1: [Forms]![frmOpReportContinuing]![CustomerID] and the destination is tblOperatorReports.CustomerID. The rest of the query follows this pattern. (Hope that helps)

 

[JHB]

..
The first expression is Expr1: [Forms]![frmOpReportContinuing]![CustomerID] and the destination is tblOperatorReports.CustomerID. The rest of the query follows this pattern. (Hope that helps)

Sorry - but not really. Open the query in design, choose "SQL View" and post the string here.
As the error from MS-Access tells you, ("and 1 record(s) due to validation rule violations."), there is a problem with the validation rule.
Show the table/fields structure from the table you want to add the data to, and also how the data you want to add looks like.
Else post your database with some sample data, (zip it) + the name of the form in which you have the problem.

 

[ch5150]

Here is the ApQuery-

INSERT INTO tblOperatorReports ( CustomerID, SalesOrder, [Date], WorkOrder, ItemNumber, Rate, Speed, MachineID, OperatorID, [RAW PART NUMBER1], [SUPPLIER ROLL NUMBER1], [BAR CODE1], BarCodeComments1, Variants1, [ROLL NO OFF1], GoodFootage01, GoodRollFootage01, TotalFootage01, TotalRollFootage01, [RAW PART NUMBER2], [SUPPLIER ROLL NUMBER2], [BAR CODE2], BarCodeComments2, Variants2, [ROLL NO OFF2], GoodFootage02, GoodRollFootage02, TotalFootage02, TotalRollFootage02, [RAW PART NUMBER3], [SUPPLIER ROLL NUMBER3], [BAR CODE3], BarCodeComments3, Variants3, [ROLL NO OFF3], GoodFootage03, GoodRollFootage03, TotalFootage03, TotalRollFootage03, [RAW PART NUMBER4], [SUPPLIER ROLL NUMBER4], [BAR CODE4], BarCodeComments4, Variants4, [ROLL NO OFF4], GoodFootage04, GoodRollFootage04, TotalFootage04, TotalRollFootage04, [RAW PART NUMBER5], [SUPPLIER ROLL NUMBER5], [BAR CODE5], BarCodeComments5, Variants5, [ROLL NO OFF5], GoodFootage05, GoodRollFootage05, TotalFootage05, TotalRollFootage05, [RAW PART NUMBER6], [SUPPLIER ROLL NUMBER6], [BAR CODE6], BarCodeComments6, Variants6, [ROLL NO OFF6], GoodFootage06, GoodRollFootage06, TotalFootage06, TotalRollFootage06, [RAW PART NUMBER7], [SUPPLIER ROLL NUMBER7], [BAR CODE7], BarCodeComments7, Variants7, [ROLL NO OFF7], GoodFootage07, GoodRollFootage07, TotalFootage07, TotalRollFootage07, [RAW PART NUMBER8], [SUPPLIER ROLL NUMBER8], [BAR CODE8], BarCodeComments8, Variants8, [ROLL NO OFF8], GoodFootage08, GoodRollFootage08, TotalFootage08, TotalRollFootage08, [RAW PART NUMBER9], [SUPPLIER ROLL NUMBER9], [BAR CODE9], BarCodeComments9, Variants9, [ROLL NO OFF9], GoodFootage09, GoodRollFootage09, TotalFootage09, TotalRollFootage09, [RAW PART NUMBER10], [SUPPLIER ROLL NUMBER10], [BAR CODE10], BarCodeComments10, Variants10, [ROLL NO OFF10], GoodFootage10, GoodRollFootage10, TotalFootage10, TotalRollFootage10, [RAW PART NUMBER11], [SUPPLIER ROLL NUMBER11], [BAR CODE11], BarCodeComments11, Variants11, [ROLL NO OFF11], GoodFootage11, GoodRollFootage11, TotalFootage11, TotalRollFootage11, [RAW PART NUMBER12], [SUPPLIER ROLL NUMBER12], [BAR CODE12], BarCodeComments12, Variants12, [ROLL NO OFF12], GoodFootage12, GoodRollFootage12, TotalFootage12, TotalRollFootage12, [RAW PART NUMBER13], [SUPPLIER ROLL NUMBER13], [BAR CODE13], BarCodeComments13, Variants13, [ROLL NO OFF13], GoodFootage13, GoodRollFootage13, TotalFootage13, TotalRollFootage13, [RAW PART NUMBER14], [SUPPLIER ROLL NUMBER14], [BAR CODE14], BarCodeComments14, Variants14, [ROLL NO OFF14], GoodFootage14, GoodRollFootage14, TotalFootage14, TotalRollFootage14 )
SELECT [Forms]![frmOperatorReports]![CustomerID] AS Expr1, [Forms]![frmOpReportContinuing]![SalesOrder] AS Expr2, [Forms]![frmOpReportContinuing]![Date] AS Expr3, [Forms]![frmOpReportContinuing]!WorkOrder AS Expr4, [Forms]![frmOpReportContinuing]!ItemNumber AS Expr5, [Forms]![frmOpReportContinuing]![Rate] AS Expr6, [Forms]![frmOpReportContinuing]![Speed] AS Expr7, [Forms]![frmOpReportContinuing]![MachineID] AS Expr8, [Forms]![frmOpReportContinuing]![OperatorID] AS Expr9, [Forms]![frmOpReportContinuing]![RAW PART NUMBER1] AS Expr10, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER1] AS Expr11, [Forms]![frmOpReportContinuing]![BAR CODE1] AS Expr12, [Forms]![frmOpReportContinuing]![START TIME1] AS Expr13, [Forms]![frmOpReportContinuing]![SU COMPLETE1] AS Expr14, [Forms]![frmOpReportContinuing]![ROLL NO OFF1] AS Expr15, [Forms]![frmOpReportContinuing]![GoodFootage01] AS Expr16, [Forms]![frmOpReportContinuing]![GoodRollFootage01] AS Expr17, [Forms]![frmOpReportContinuing]![TotalFootage01] AS Expr18, [Forms]![frmOpReportContinuing]![TotalRollFootage01] AS Expr19, [Forms]![frmOpReportContinuing]![RAW PART NUMBER2] AS Expr20, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER2] AS Expr21, [Forms]![frmOpReportContinuing]![BAR CODE2] AS Expr22, [Forms]![frmOpReportContinuing]![START TIME2] AS Expr23, [Forms]![frmOpReportContinuing]![SU COMPLETE2] AS Expr24, [Forms]![frmOpReportContinuing]![ROLL NO OFF2] AS Expr25, [Forms]![frmOpReportContinuing]![GoodFootage02] AS Expr26, [Forms]![frmOpReportContinuing]![GoodRollFootage02] AS Expr27, [Forms]![frmOpReportContinuing]![TotalFootage02] AS Expr28, [Forms]![frmOpReportContinuing]![TotalRollFootage02] AS Expr29, [Forms]![frmOpReportContinuing]![RAW PART NUMBER3] AS Expr30, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER3] AS Expr31, [Forms]![frmOpReportContinuing]![BAR CODE3] AS Expr32, [Forms]![frmOpReportContinuing]![START TIME3] AS Expr33, [Forms]![frmOpReportContinuing]![SU COMPLETE3] AS Expr34, [Forms]![frmOpReportContinuing]![ROLL NO OFF3] AS Expr35, [Forms]![frmOpReportContinuing]![GoodFootage03] AS Expr36, [Forms]![frmOpReportContinuing]![GoodRollFootage03] AS Expr37, [Forms]![frmOpReportContinuing]![TotalFootage03] AS Expr38, [Forms]![frmOpReportContinuing]![TotalRollFootage03] AS Expr39, [Forms]![frmOpReportContinuing]![RAW PART NUMBER4] AS Expr40, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER4] AS Expr41, [Forms]![frmOpReportContinuing]![BAR CODE4] AS Expr42, [Forms]![frmOpReportContinuing]![START TIME4] AS Expr43, [Forms]![frmOpReportContinuing]![SU COMPLETE4] AS Expr44, [Forms]![frmOpReportContinuing]![ROLL NO OFF4] AS Expr45, [Forms]![frmOpReportContinuing]![GoodFootage04] AS Expr46, [Forms]![frmOpReportContinuing]![GoodRollFootage04] AS Expr47, [Forms]![frmOpReportContinuing]![TotalFootage04] AS Expr48, [Forms]![frmOpReportContinuing]![TotalRollFootage04] AS Expr49, [Forms]![frmOpReportContinuing]![RAW PART NUMBER5] AS Expr50, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER5] AS Expr51, [Forms]![frmOpReportContinuing]![BAR CODE5] AS Expr52, [Forms]![frmOpReportContinuing]![START TIME5] AS Expr53, [Forms]![frmOpReportContinuing]![SU COMPLETE5] AS Expr54, [Forms]![frmOpReportContinuing]![ROLL NO OFF5] AS Expr55, [Forms]![frmOpReportContinuing]![GoodFootage05] AS Expr56, [Forms]![frmOpReportContinuing]![GoodRollFootage05] AS Expr57, [Forms]![frmOpReportContinuing]![TotalFootage05] AS Expr58, [Forms]![frmOpReportContinuing]![TotalRollFootage05] AS Expr59, [Forms]![frmOpReportContinuing]![RAW PART NUMBER6] AS Expr60, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER6] AS Expr61, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER6] AS Expr62, [Forms]![frmOpReportContinuing]![START TIME6] AS Expr63, [Forms]![frmOpReportContinuing]![SU COMPLETE6] AS Expr64, [Forms]![frmOpReportContinuing]![ROLL NO OFF6] AS Expr65, [Forms]![frmOpReportContinuing]![GoodFootage06] AS Expr66, [Forms]![frmOpReportContinuing]![GoodRollFootage06] AS Expr67, [Forms]![frmOpReportContinuing]![TotalFootage06] AS Expr68, [Forms]![frmOpReportContinuing]![TotalRollFootage06] AS Expr69, [Forms]![frmOpReportContinuing]![RAW PART NUMBER7] AS Expr70, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER7] AS Expr71, [Forms]![frmOpReportContinuing]![BAR CODE7] AS Expr72, [Forms]![frmOpReportContinuing]![START TIME7] AS Expr73, [Forms]![frmOpReportContinuing]![START TIME7] AS Expr74, [Forms]![frmOpReportContinuing]![ROLL NO OFF7] AS Expr75, [Forms]![frmOpReportContinuing]![GoodFootage07] AS Expr76, [Forms]![frmOpReportContinuing]![GoodRollFootage07] AS Expr77, [Forms]![frmOpReportContinuing]![TotalFootage07] AS Expr78, [Forms]![frmOpReportContinuing]![TotalRollFootage07] AS Expr79, [Forms]![frmOpReportContinuing]![RAW PART NUMBER8] AS Expr80, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER8] AS Expr81, [Forms]![frmOpReportContinuing]![BAR CODE8] AS Expr82, [Forms]![frmOpReportContinuing]![START TIME8] AS Expr83, [Forms]![frmOpReportContinuing]![SU COMPLETE8] AS Expr84, [Forms]![frmOpReportContinuing]![ROLL NO OFF8] AS Expr85, [Forms]![frmOpReportContinuing]![GoodFootage08] AS Expr86, [Forms]![frmOpReportContinuing]![GoodRollFootage08] AS Expr87, [Forms]![frmOpReportContinuing]![TotalFootage08] AS Expr88, [Forms]![frmOpReportContinuing]![TotalRollFootage08] AS Expr89, [Forms]![frmOpReportContinuing]![RAW PART NUMBER9] AS Expr90, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER9] AS Expr91, [Forms]![frmOpReportContinuing]![BAR CODE9] AS Expr92, [Forms]![frmOpReportContinuing]![START TIME9] AS Expr93, [Forms]![frmOpReportContinuing]![SU COMPLETE9] AS Expr94, [Forms]![frmOpReportContinuing]![ROLL NO OFF9] AS Expr95, [Forms]![frmOpReportContinuing]![GoodFootage09] AS Expr96, [Forms]![frmOpReportContinuing]![GoodRollFootage09] AS Expr97, [Forms]![frmOpReportContinuing]![TotalFootage09] AS Expr98, [Forms]![frmOpReportContinuing]![TotalRollFootage09] AS Expr99, [Forms]![frmOpReportContinuing]![RAW PART NUMBER10] AS Expr100, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER10] AS Expr101, [Forms]![frmOpReportContinuing]![BAR CODE10] AS Expr102, [Forms]![frmOpReportContinuing]![START TIME10] AS Expr103, [Forms]![frmOpReportContinuing]![SU COMPLETE10] AS Expr104, [Forms]![frmOpReportContinuing]![ROLL NO OFF10] AS Expr105, [Forms]![frmOpReportContinuing]![GoodFootage10] AS Expr106, [Forms]![frmOpReportContinuing]![GoodRollFootage10] AS Expr107, [Forms]![frmOpReportContinuing]![TotalFootage10] AS Expr108, [Forms]![frmOpReportContinuing]![TotalRollFootage10] AS Expr109, [Forms]![frmOpReportContinuing]![RAW PART NUMBER11] AS Expr110, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER11] AS Expr111, [Forms]![frmOpReportContinuing]![BAR CODE11] AS Expr112, [Forms]![frmOpReportContinuing]![START TIME11] AS Expr113, [Forms]![frmOpReportContinuing]![SU COMPLETE11] AS Expr114, [Forms]![frmOpReportContinuing]![ROLL NO OFF11] AS Expr115, [Forms]![frmOpReportContinuing]![GoodFootage11] AS Expr116, [Forms]![frmOpReportContinuing]![GoodRollFootage11] AS Expr117, [Forms]![frmOpReportContinuing]![TotalFootage11] AS Expr118, [Forms]![frmOpReportContinuing]![TotalRollFootage11] AS Expr119, [Forms]![frmOpReportContinuing]![RAW PART NUMBER12] AS Expr120, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER12] AS Expr121, [Forms]![frmOpReportContinuing]![BAR CODE12] AS Expr122, [Forms]![frmOpReportContinuing]![START TIME12] AS Expr123, [Forms]![frmOpReportContinuing]![SU COMPLETE12] AS Expr124, [Forms]![frmOpReportContinuing]![ROLL NO OFF12] AS Expr125, [Forms]![frmOpReportContinuing]![GoodFootage12] AS Expr126, [Forms]![frmOpReportContinuing]![GoodRollFootage12] AS Expr127, [Forms]![frmOpReportContinuing]![TotalFootage12] AS Expr128, [Forms]![frmOpReportContinuing]![TotalRollFootage12] AS Expr129, [Forms]![frmOpReportContinuing]![RAW PART NUMBER13] AS Expr130, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER13] AS Expr131, [Forms]![frmOpReportContinuing]![BAR CODE13] AS Expr132, [Forms]![frmOpReportContinuing]![START TIME13] AS Expr133, [Forms]![frmOpReportContinuing]![SU COMPLETE13] AS Expr134, [Forms]![frmOpReportContinuing]![ROLL NO OFF13] AS Expr135, [Forms]![frmOpReportContinuing]![GoodFootage13] AS Expr136, [Forms]![frmOpReportContinuing]![GoodRollFootage13] AS Expr137, [Forms]![frmOpReportContinuing]![TotalFootage13] AS Expr138, [Forms]![frmOpReportContinuing]![TotalRollFootage13] AS Expr139, [Forms]![frmOpReportContinuing]![RAW PART NUMBER14] AS Expr140, [Forms]![frmOpReportContinuing]![SUPPLIER ROLL NUMBER14] AS Expr141, [Forms]![frmOpReportContinuing]![BAR CODE14] AS Expr142, [Forms]![frmOpReportContinuing]![START TIME14] AS Expr143, [Forms]![frmOpReportContinuing]![SU COMPLETE14] AS Expr144, [Forms]![frmOpReportContinuing]![ROLL NO OFF14] AS Expr145, [Forms]![frmOpReportContinuing]![GoodFootage14] AS Expr146, [Forms]![frmOpReportContinuing]![GoodRollFootage14] AS Expr147, [Forms]![frmOpReportContinuing]![TotalFootage14] AS Expr148, [Forms]![frmOpReportContinuing]![TotalRollFootage14] AS Expr149;

 

[Old Man Devin]

150 fields... will take a long time to work out which one had the validation violation!

So basically the problem is that one of the fields has data that isn't allowed into the field you are inserting to, so it just skips the entire row. Maybe (hopefully) you have an idea of which field this is likely to be. Problem could be data type violation, number too big for a number type (e.g. 300 won't fit in a byte type field), or too many characters in a text field.

 

[ch5150]

Quite an unwieldy ApQuery to be sure... However, the data is coming from the cloned form from the original table. My thought is that it is one or all of the combo boxes, and the ApQuery is trying to insert text into a number field from the drop down box, however my attempts to work around that issue have failed. There are only 3 of those. CustomerID, MachineID and OperatorID.
I was given the original program and I have been trying to do my best... I've attached a pared down version of the db. The form I'm having trouble with is called frmOpReportContinuing. The goal is to allow operators to review the previous op report and then click a button to open a new op report that has the header data from the previous worker's report, because he is continuing that persons project.
The procedure is to open the "Sub Form Reports" from the switchboard, and select a work order number, then open the "By Work Order" read-only form. From there select "Continuing Work Order" to open a new record with header info already populated except "Shift" and "Operator".
Respects,
Chris

P.S. I corrected the [Forms]![frmOperatorReports]![CustomerID] to read [frmOpReportContinuing]...

 

[ch5150]

Whew! I was able to correct the issue by building the ApQuery from scratch... Not sure what the difference was, but it works now... The only issue I have remaining is some calculations... Is there an expression or operator that will prevent the calculation from executing if the field value is zero?

 

[Old Man Devin]

You could make the calculation something like Iif(FieldValue=0,0,{Rest of calculation}), so that the meat of the work is only done when needed.