Replies

I think it is the “$” in the table name that is throwing things off. I tried the statement with the $ and without it (had to rename the table) and it worked without but I got a similar error that you did with it. So, try renaming the table and editing the code and try it that way and see if it works.

I believe the problem lies in your statement:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFilename, “c:HistoryArchivesstrFilename.xls”, True

The argument strFileName should be the name of the table you are exporting as it exists in the database without any date concatenation. The next argument is where you want the date to concatenate with the table name. Something like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “tblNameOfTableToExport”, “c:HistoryArchivestblNameOfTableToExport” & format(Date(),”mddyy”) & “.xls”, True

You can do the concatenation on separate lines but don’t confuse the name of the table to export with the destination file name. They should be two separate objects or variables.

BTW, I tried exporting a table using the same method and intentionally supplying an invalid table name and got a similar error message so that’s why I’m focusing on the transferspreadshet method.

The samples are on my copy of the Office97 Pro CD.

One idea that popped into my head was using the tag property to identify each unbound control. This way you could loop through the forms’ controls collection and test the tag property for a certain value – “UB”? – and then do your writes. I’m sure there are other ways to approach this as others may suggest. It might be helpful to know the bigger picture, for instance why the need exists to write these values to another table?

[indent]

---

What seems so obvious at the time becomes more and more obscure as time passes, and it makes it easier for someone else to pick up and use.

---

[/indent]

How many times have I written something only to come back a month later and scratch my head.

I particularly like where you comment the end ifs, end selects and even end function with the head line. I’ve never seen that done before and while some may consider it overkill I can see where it would come in handy in some of the more complicated logical routines.

Hard to tell from here what exactly is causing the breakdown. I’d be curious to hear what the actual flow looks like if you put in a break point and went through the code line by line. That might help give us a clue where to focus our attention.

I like that even better!

I would hesitate storing this sort of thing in a table as it can usually be calculated in a query. Is there a date associated with each record? If so you could use the year function or right function to return the 2 digit year portion of the date along with a combination of padded zeroes and an autonumber field. Post back if you need help with the expression.

Is there a name property for that object…?

Set f = fs.GetFile(fs.GetFileName(.FoundFiles(i).Name))

Glad to help, Roberta! And I must say it’s nice to connect a face with the name after seeing just your name on the other forum (ElementK).

What’s the error…maybe I can help with that too.

Try this:

strFileName = “PH_Exp$” & Format(strDate, “mddyy”)

Instead of:

strFileName = “PH_Exp$” & strDate

Don’t give up yet. I was able to create a field in a table related to an employee table which displays the name in datasheet view from the first column but stores the employee id in the table from the second column. What may be tripping you up is that even though the id is being stored in the table you still see the name displayed in datasheet view. You can test if the id is being stored by opening a new report in design view and base it on the table where the name is displayed but the id is stored. Add a textbox – not a combobox – to the report and set it’s control source property to the name field. Then preview the report and you should see the id displayed on the report and not the name.

Glad it worked for you. For future reference you can export a query as well as a table which should give you the ability to sort the table data how you want.

Try setting the control source properties for the report controls to your form controls. Use the expression builder if you’re not sure about the correct syntax. It should be something like this:

=[Forms]![frmTest01]![txtUPCODE]

This should work with your code in the format event.

I would have to agree with Charlotte and ask why are you putting so much emphasis on sorting the table?

Having said that try creating the autonumber field in the new table before appending the sorted records to the new table.