VBA - query reading newly added field as null

[yorkiechris]

Hi there.

I'm running Access 2003.

I'm trying to:
1) run a make table query that creates a table in the back end (no problems)

Table in the backend is called STP_BulkTransactions

2) add an autonumber field to it (no problem)

I call this function:
Function AddIDSTP(TableName As String, db As Database)

strSQL = "ALTER TABLE " & TableName & " ADD TempID Autoincrement"
db.Execute strSQL
db.TableDefs.Refresh

Set strSQL = Nothing

End Function

3) link that table to the front end (no problem)

I do this by calling:

Function AddLinkedTable(TableName As String)

Dim tdfLinked As TableDef

Set tdfLinked = CurrentDb.CreateTableDef(TableName)
tdfLinked.Connect = ";DATABASE=x:\path\to\backend.mdb"
tdfLinked.SourceTableName = TableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh

End Function

4) append the data in that table to another table (after adding the DMax of
the current table (BIG PROBLEM!!)
....
varMaxBulkRef = Nz(DMax("BulkRef", "[BulkTransactions]"), 548000000)
strAppToBulk = "INSERT INTO BulkTransactions ( BulkRef, Description,
EntryType, UnitPriceRef, " & _
"LastUpdated ) SELECT " & CInt(varMaxBulkRef) & " +
STP_BulkTransactions.TempID AS Expr1,

.....

All the other fields

.....

CurrentDb.Execute strAppToBulk, dbFailOnError



This returns an error saying that the field I am trying to append to the
BulkRef field is null (which is not permitted). I've messed around with the
varMaxBulkRef - that's definitely not null. So the only thing left that could
be null is the TempID that was added by the AddIDSTP() function - but this
clearly isn't null as it's an autonumber.

Does anyone have any ideas please?

Love

yorkiechris

 

[Daryl S]

Yorkiechris -

I would put a breakpoint in the code where you assign varMaxBulkRef and then
step through the code. Check the values of varMaxBulkRef and
STP_BulkTransactions.TempID before the SQL is run. I would also do a
debug.print strAppToBulk
after the SQL statement is created but before it is run. You can then
copy/paste it out of the immediate window and look at it and even copy/paste
it into a query (SQL View) and test it out there. There may be something
else wrong with the SQL. If you don't see the issue, post the SQL (the
strAppToBulk from the immediate window from the debug.print statement). Then
we might be able to help more...

 

[yorkiechris]

Hi Daryl, thanks for replying.

varMaxBulkRef is fine - it picks up the value fine. I'm 99% sure the problem
is with the TempID that was added earlier. It's like Access hasn't caught up
with the changed TableDef, despite me refreshing it at every opportunity.

When I write the results to a new table instead of appending it to the
existing table (using identical SELECT statement in the action query), there
isn't a problem. When I append this new table to the existing table, there
isn't a problem.

So in the end I've worked round it by creating a temporary intermediate
table, but it seems very strange. Vagaries of Access 2003/VBA I guess...

Thanks anyway.

Chris.

Yorkiechris -

I would put a breakpoint in the code where you assign varMaxBulkRef and then
step through the code. Check the values of varMaxBulkRef and
STP_BulkTransactions.TempID before the SQL is run. I would also do a
debug.print strAppToBulk
after the SQL statement is created but before it is run. You can then
copy/paste it out of the immediate window and look at it and even copy/paste
it into a query (SQL View) and test it out there. There may be something
else wrong with the SQL. If you don't see the issue, post the SQL (the
strAppToBulk from the immediate window from the debug.print statement). Then
we might be able to help more...

--
Daryl S

Hi there.

I'm running Access 2003.

I'm trying to:
1) run a make table query that creates a table in the back end (no problems)

Table in the backend is called STP_BulkTransactions

2) add an autonumber field to it (no problem)

I call this function:
Function AddIDSTP(TableName As String, db As Database)

strSQL = "ALTER TABLE " & TableName & " ADD TempID Autoincrement"
db.Execute strSQL
db.TableDefs.Refresh

Set strSQL = Nothing

End Function

3) link that table to the front end (no problem)

I do this by calling:

Function AddLinkedTable(TableName As String)

Dim tdfLinked As TableDef

Set tdfLinked = CurrentDb.CreateTableDef(TableName)
tdfLinked.Connect = ";DATABASE=x:\path\to\backend.mdb"
tdfLinked.SourceTableName = TableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh

End Function

4) append the data in that table to another table (after adding the DMax of
the current table (BIG PROBLEM!!)
...
varMaxBulkRef = Nz(DMax("BulkRef", "[BulkTransactions]"), 548000000)
strAppToBulk = "INSERT INTO BulkTransactions ( BulkRef, Description,
EntryType, UnitPriceRef, " & _
"LastUpdated ) SELECT " & CInt(varMaxBulkRef) & " +
STP_BulkTransactions.TempID AS Expr1,

....

All the other fields

....

CurrentDb.Execute strAppToBulk, dbFailOnError



This returns an error saying that the field I am trying to append to the
BulkRef field is null (which is not permitted). I've messed around with the
varMaxBulkRef - that's definitely not null. So the only thing left that could
be null is the TempID that was added by the AddIDSTP() function - but this
clearly isn't null as it's an autonumber.

Does anyone have any ideas please?

Love

yorkiechris