Why won't this work? 
Author Message
 Why won't this work?

Okay, gurus, I'm stumped on this one.  In the code below, the lines starting
with "Dim strDefVal as String..." and going through the last
dbsToolkit.Execute line work fine.

But the "Set tdfOptAmv3=dbsToolkit.TableDefs!tblTEMPCLSOptAMV3" (2nd from
the bottom) gives me the error "3265 -- Item not found in this collection".
tdfOptAMV3 is set earlier as a Public variable (along with tdfOptAMV2.
dbsToolkit is set earlier to CurrentDB (but the error message doesn't change
when I set it again in this particular sub).

And that line looks exactly the same as the "Set tdfOptAMV2..." one (except
for obvious differences).  So why doesn't it work?

Thanks in advance.  Carol.
---------------------------------------------
Dim strDefVal As String
strDefVal = "=Left$(CStr((Now()*10000000000)),15) &
Left$(CStr(Int(Rnd()*10000000000)),7) &
Left$(CStr(Int(Rnd()*10000000000)),7)"

'creating TEMP tables and setting unique ID for key fields.

dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
PK_Main Text(20), AMV2Field1 Text(255), " & _
    " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
CLSTableName Text(20))"

Set tdfOptAMV2 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV2
tdfOptAMV2.Fields![PK_AMV2].DefaultValue = strDefVal

dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV3 (PK_AMV3 Text(20),
PK_Main Text(29), AMV3Field1 Text(255), " & _
    " AMV3Field2 Text(255), AMV3Field3 Text(255), AMV3FieldNum Text(20),
BCDKey Text(20), CLSTableName Text(20))"

Set tdfOptAMV3 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV3
'tdfOptAMV3.Fields![PK_AMV3].DefaultValue = strDefVal



Sat, 05 Feb 2005 23:42:59 GMT  
 Why won't this work?
Carol,

I'd consider checking whether the TableDefs collection is refreshing in time
for you to use the new table.

So before the:
Set tdfOptAMV2 = or Set tdfOptAMV 3 =

I'd try a:
dbsToolkit.TableDefs.Refresh

In addition, with the .Execute method I always use the dbFailOnError option
as in:

dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
PK_Main Text(20), AMV2Field1 Text(255), " & _
 " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
CLSTableName Text(20))", dbFailOnError

The .Execute method will fail "silently" if there is an error but by using
the option you will have a trappable error.

Steve


Quote:
> Okay, gurus, I'm stumped on this one.  In the code below, the lines
starting
> with "Dim strDefVal as String..." and going through the last
> dbsToolkit.Execute line work fine.

> But the "Set tdfOptAmv3=dbsToolkit.TableDefs!tblTEMPCLSOptAMV3" (2nd from
> the bottom) gives me the error "3265 -- Item not found in this
collection".
> tdfOptAMV3 is set earlier as a Public variable (along with tdfOptAMV2.
> dbsToolkit is set earlier to CurrentDB (but the error message doesn't
change
> when I set it again in this particular sub).

> And that line looks exactly the same as the "Set tdfOptAMV2..." one
(except
> for obvious differences).  So why doesn't it work?

> Thanks in advance.  Carol.
> ---------------------------------------------
> Dim strDefVal As String
> strDefVal = "=Left$(CStr((Now()*10000000000)),15) &
> Left$(CStr(Int(Rnd()*10000000000)),7) &
> Left$(CStr(Int(Rnd()*10000000000)),7)"

> 'creating TEMP tables and setting unique ID for key fields.

> dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
> PK_Main Text(20), AMV2Field1 Text(255), " & _
>     " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
> CLSTableName Text(20))"

> Set tdfOptAMV2 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV2
> tdfOptAMV2.Fields![PK_AMV2].DefaultValue = strDefVal

> dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV3 (PK_AMV3 Text(20),
> PK_Main Text(29), AMV3Field1 Text(255), " & _
>     " AMV3Field2 Text(255), AMV3Field3 Text(255), AMV3FieldNum Text(20),
> BCDKey Text(20), CLSTableName Text(20))"

> Set tdfOptAMV3 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV3
> 'tdfOptAMV3.Fields![PK_AMV3].DefaultValue = strDefVal



Sun, 06 Feb 2005 00:28:34 GMT  
 Why won't this work?
Worked great, Steve.  Thanks again.

Carol.

Quote:

> Carol,

> I'd consider checking whether the TableDefs collection is refreshing in
time
> for you to use the new table.

> So before the:
> Set tdfOptAMV2 = or Set tdfOptAMV 3 =

> I'd try a:
> dbsToolkit.TableDefs.Refresh

> In addition, with the .Execute method I always use the dbFailOnError
option
> as in:

> dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
> PK_Main Text(20), AMV2Field1 Text(255), " & _
>  " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
> CLSTableName Text(20))", dbFailOnError

> The .Execute method will fail "silently" if there is an error but by using
> the option you will have a trappable error.

> Steve



> > Okay, gurus, I'm stumped on this one.  In the code below, the lines
> starting
> > with "Dim strDefVal as String..." and going through the last
> > dbsToolkit.Execute line work fine.

> > But the "Set tdfOptAmv3=dbsToolkit.TableDefs!tblTEMPCLSOptAMV3" (2nd
from
> > the bottom) gives me the error "3265 -- Item not found in this
> collection".
> > tdfOptAMV3 is set earlier as a Public variable (along with tdfOptAMV2.
> > dbsToolkit is set earlier to CurrentDB (but the error message doesn't
> change
> > when I set it again in this particular sub).

> > And that line looks exactly the same as the "Set tdfOptAMV2..." one
> (except
> > for obvious differences).  So why doesn't it work?

> > Thanks in advance.  Carol.
> > ---------------------------------------------
> > Dim strDefVal As String
> > strDefVal = "=Left$(CStr((Now()*10000000000)),15) &
> > Left$(CStr(Int(Rnd()*10000000000)),7) &
> > Left$(CStr(Int(Rnd()*10000000000)),7)"

> > 'creating TEMP tables and setting unique ID for key fields.

> > dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
> > PK_Main Text(20), AMV2Field1 Text(255), " & _
> >     " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
> > CLSTableName Text(20))"

> > Set tdfOptAMV2 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV2
> > tdfOptAMV2.Fields![PK_AMV2].DefaultValue = strDefVal

> > dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV3 (PK_AMV3 Text(20),
> > PK_Main Text(29), AMV3Field1 Text(255), " & _
> >     " AMV3Field2 Text(255), AMV3Field3 Text(255), AMV3FieldNum Text(20),
> > BCDKey Text(20), CLSTableName Text(20))"

> > Set tdfOptAMV3 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV3
> > 'tdfOptAMV3.Fields![PK_AMV3].DefaultValue = strDefVal



Sun, 06 Feb 2005 01:59:24 GMT  
 Why won't this work?
Thanks for the suggestions, Steve.  I'll give them a try.

Carol.

Quote:

> Carol,

> I'd consider checking whether the TableDefs collection is refreshing in
time
> for you to use the new table.

> So before the:
> Set tdfOptAMV2 = or Set tdfOptAMV 3 =

> I'd try a:
> dbsToolkit.TableDefs.Refresh

> In addition, with the .Execute method I always use the dbFailOnError
option
> as in:

> dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
> PK_Main Text(20), AMV2Field1 Text(255), " & _
>  " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
> CLSTableName Text(20))", dbFailOnError

> The .Execute method will fail "silently" if there is an error but by using
> the option you will have a trappable error.

> Steve



> > Okay, gurus, I'm stumped on this one.  In the code below, the lines
> starting
> > with "Dim strDefVal as String..." and going through the last
> > dbsToolkit.Execute line work fine.

> > But the "Set tdfOptAmv3=dbsToolkit.TableDefs!tblTEMPCLSOptAMV3" (2nd
from
> > the bottom) gives me the error "3265 -- Item not found in this
> collection".
> > tdfOptAMV3 is set earlier as a Public variable (along with tdfOptAMV2.
> > dbsToolkit is set earlier to CurrentDB (but the error message doesn't
> change
> > when I set it again in this particular sub).

> > And that line looks exactly the same as the "Set tdfOptAMV2..." one
> (except
> > for obvious differences).  So why doesn't it work?

> > Thanks in advance.  Carol.
> > ---------------------------------------------
> > Dim strDefVal As String
> > strDefVal = "=Left$(CStr((Now()*10000000000)),15) &
> > Left$(CStr(Int(Rnd()*10000000000)),7) &
> > Left$(CStr(Int(Rnd()*10000000000)),7)"

> > 'creating TEMP tables and setting unique ID for key fields.

> > dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV2 (PK_AMV2 Text(20),
> > PK_Main Text(20), AMV2Field1 Text(255), " & _
> >     " AMV2Field2 Text(255), AMV2FieldNum Text(20), BCDKey Text(20),
> > CLSTableName Text(20))"

> > Set tdfOptAMV2 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV2
> > tdfOptAMV2.Fields![PK_AMV2].DefaultValue = strDefVal

> > dbsToolkit.Execute "Create Table tblTEMPCLSOptAMV3 (PK_AMV3 Text(20),
> > PK_Main Text(29), AMV3Field1 Text(255), " & _
> >     " AMV3Field2 Text(255), AMV3Field3 Text(255), AMV3FieldNum Text(20),
> > BCDKey Text(20), CLSTableName Text(20))"

> > Set tdfOptAMV3 = dbsToolkit.TableDefs!tblTEMPCLSOptAMV3
> > 'tdfOptAMV3.Fields![PK_AMV3].DefaultValue = strDefVal



Sun, 06 Feb 2005 01:04:21 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Why won't AllowZeroLength work?

2. Why won't this work right

3. Why won't this work?

4. Q: Why won't this work

5. Why won't INSTR() work!!???

6. Why won't this work?

7. Why won't this work

8. Why won't this work?

9. Why won't this work right

10. Why won't this work?

11. Why won't this work?

12. Why won't this work?

 

 
Powered by phpBB® Forum Software