how to output calculated field to excel (1 Viewer)

[RoyinForest]

I've written a complete ( I hope ) description of the problem and am attaching a document with screen captures. Basically I have a form with a subform that works right to let me enter data. It does not let me extract some of the data using a query.2448 "You can't assign a value to this object"

 

[sneuberg]

If TotalParts has something assigned to its control source, e.g., =Sum(...) or =SubformName!SomeTextBoxName you can get this "You can't assign a value to this object" kind of error.

Also if this sort of thing is done to obtain the total then it seems unlikely that it's in the query. You may need to redo the query or even make a separate query for the output.

 

[Ranman256]

If you mean,export a string to excel that excel will understand as a formula calculation,
Like: =[a1]*[b1]
Then no.

Access doesn't use Col A,Col B,etc. and can't translate formula. (2 different animals)
Excel could run a macro to produce the formula After the export.

 

[RoyinForest]

I'm not trying to invoke Excel or to have it run a formula or anything. I just need to create a string of actual values for the fields in the query. Once I output the string it is all integers and text. (problem is I have blanks for some fields instead of integers)

 

[JHB]

You show the table "PDP-C-xls" but in the query you insert the data in ""PDP-W-xls"
Only an advice, don't use special signs in table/field - name like $, % etc. And you don't need the [Somefield].Value
Could you post your database with some sample data + a description how to reproduce the error + the Excel file?

 

[RoyinForest]

Hi Jon
I will post a zip file in the forum but since it is for a family run business I'd appreciate it not going public.
For this purpose I have deleted the data from the "field invoice 2" table ( the main table for all data entry ) and then added two test records. The files that are created to feed into Excel are both in the zip file, names start
with - Export. The access code looks at two fields to decide which set of files to create (Call Source and East/West/central).
The "Parts-2017-03-09.xls" file is an excel file of parts prices used to populate the PART USED sub-form but this is not necessary
for our purpose. Or you can update the "linked tables" and it should jump in.
As you can see from the main form there are 4 types of reports for each of 3 locations giving 12 different sets of tables and queries.
(so we only need to solve 1 )

To recreate the error just enter some data into the "Data Entry" panel. Make "Call Source = PDP" and 'East/west/central = west" and
use tab key to navigate left-right and down. Fields with drop down boxes are required but (apart from the two already mentioned) values
are not important. The subcase box is mandatory, numeric - I usually use 8 or 9 digits all the same 11111111 22222222 etc.
the Parts Used subform can be populated using copy / paste from the spreadsheet below it. ( or just manually type into the fields )
the total Parts box below the subform is filled in by vba code . (Pink boxes are auto-filled, white boxes are completed manually)
There error pops up when you click on the "RECALCULATE" button to make sure all totals are complete. That drives the SUB Command159_Click code. If I comment out the flagged statement, then the record is created as shown bu the two examples. But without the totals I need). Here's the error: (box didn't copy but message is "Run-time error '244': You can't assign a value to this object.)

Hope this helps explain the process enough so you can figure out how to run it. It is easier to do than to describe I think).
Thanks again for you assistance.

Roy

 

[JHB]

Sorry, but I can't see any attachment!
And I think you also try to show a picture, (can't see it)!

 

[RoyinForest]

I couldn't even connect to this website earlier today until I switched my DNS server away from my ISP. (tried 3 PC's). Anyway, I'm obviously on now. the picture was the error message and I retyped the error in text in my reply last night. The zip file is about 2.5 MB and shows on my SENT mail. I've successfully retrieved it on a test mail account at Netscape. I just noticed there IS a size limit of 2 MB so I'll break it up and post again. Oops, even the database alone (with only 2 records) zips down to 2.4 MB. Can you access it in my dropbox if I send you the link?

 

[JHB]

The server was down in a long time yesterday - I couldn't access it either.
Did you do a "Compact & Repair", (it will reduce the size)?

 

[RoyinForest]

Unfortunately yes I did run a compact and repair. It reduced to size to about 5.5 Mb. Zipping it cut the size to about 2.4 MB. The max size is 2 Mb so I still can't email it to you. I did put it into my dropbox and sent you the link so you should be able to get if from www.dropbox.com using the link.

Thanks for still trying for me.

Roy

 

[RoyinForest]

I don't see any place to click on "thumbs up" or mark this as solved. Guess my rookie status is showing.

 

[JHB]

In the top of the thread click "Thread Tools" choose "Mark this thread as solved"
The "Thanks" is located in the bottom right side of a thread.