Importing From Speadsheet

[Marc]

Hi,

I created an application that manages staffing actions. I created a button
that imports an XLS file from a set directory on C drive using the following
syntax:

DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="Candidate Test", _
FileName:="C:\New Promotion Tracking\Promel Test.xls",
Hasfieldnames:=True, _
Range:="Sheet1!", SpreadsheetType:=5

works great.

[Candidate Test] table is linked to Table main by the field SA (one to
many). In order to show the update to a child form I need to update all empty
SA with the SA from the main table.

I use the SQL statement:

SQL = "UPDATE [Candidate Test] SET SA = " & SA_temp & " WHERE (([Candidate
Test].SA) Is Null)"

Any attempt to udate the table gives me a -503 in the field. i searched the
net and the only thing I could come up with is server unavailble.

Why is this code appears? Is there any other way to update the [Candidate
Test]Table right after the spreadsheet update?

Thanks,

 

[Jeanette Cunningham]

Marc,
I have quickly looked through your question - instead of doing an update
query it may be that you need an append query to put the SA values as new
records in the child table.
That's if the import put new records in the parent table - the matching SA's
would need to be created in the child table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Marc,
I have quickly looked through your question - instead of doing an update
query it may be that you need an append query to put the SA values as new
records in the child table.
That's if the import put new records in the parent table - the matching SA's
would need to be created in the child table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia