Table not found by code

P

Pele

I had posted this before but didn't get help.

Somebody had helped me put together a VB code to add some textboxes from a
form to a table. Anyway, I get an error message when the code reaches the
a point refering to OpenRecordset; a message pops up and says "Item not
found".
I don't know why the code can't find the table since it the table thus exist
and it is named correctly. Can somebody hep me with this code (see below); I
have to find a way to let the code recognize the table name; changing the
table name is not an option.

Below is the full code I am working with:

Pele

Private Sub CmdaddHeadCountRecord_Click()
On Error GoTo Err_CmdaddHeadCounTRecord_Click
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim totalrecords As Integer
Dim i As Integer
Dim varHDCT As Long

'check that data entry is complete and correct
If [Forms]![frm_add Head Count record]![test Entry] = 1 Then
DoCmd.RunMacro "Mac_check add Head count record entry"
Exit Sub
End If

'saves the record in table "HEAD COUNT TBL"
DoCmd.RunMacro "Mac_set CC Code in Head Count Table" 'to include CC
code in table
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.hc_id ' HC_ID is a field
totalrecords = SW + EW + 1

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEAD COUNT EXTRA TIME TBL"
Set rst = CurrentDb.OpenRecordset("Head count extra time tbl")
With rst
For i = SW To EW
' Add new record.
.AddNew
!hc_id = varHDCT
!Week = i
![Meetings and training st] = Me.ST
'ub = unbound
![Meetings and training ot] = Me.OT
![Unscheduled OT] = Me.Unsched_OT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table Head count extra time tbl"

Stop

Exit_CmdaddHeadCounTRecord_Click:
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_CmdaddHeadCounTRecord_Click:
MsgBox Err.Description
Resume Exit_CmdaddHeadCounTRecord_Click


End Sub
 
F

FSt1

hi,
i think you have a naming problem.
see this site.
http://support.microsoft.com/?id=826763
characters not to use with access.
Space in as the top of the list and your table name is full of spaces.
you might get it to go if you inclose your table name in brackets [ name ]
but i would follow microsoft's advice and get rid of the spaces.

Post back if neither works.

Regards

FSt1
 
P

Pele

FST1,

I don't have the option regarding the changing the name; I need to keep the
name as is and find a way around it. I tried the second proposal you made and
put the name in brackets. The problematic line now looks like this:

Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",
)

Unfortunately, a window pops up and says "Microsoft Access can't find the
field 'forms' referred to in your expression". When I press okay, another
window pops up saying ""Object variable or with block variable not set". This
window seems to be locked in some kind of loop since it won't disappear after
i press OKay. Anyway, I can only get out by closing the application by using
the task manager.

Please let me know what other things I can try.

Toks




FSt1 said:
hi,
i think you have a naming problem.
see this site.
http://support.microsoft.com/?id=826763
characters not to use with access.
Space in as the top of the list and your table name is full of spaces.
you might get it to go if you inclose your table name in brackets [ name ]
but i would follow microsoft's advice and get rid of the spaces.

Post back if neither works.

Regards

FSt1


Pele said:
I had posted this before but didn't get help.

Somebody had helped me put together a VB code to add some textboxes from a
form to a table. Anyway, I get an error message when the code reaches the
a point refering to OpenRecordset; a message pops up and says "Item not
found".
I don't know why the code can't find the table since it the table thus exist
and it is named correctly. Can somebody hep me with this code (see below); I
have to find a way to let the code recognize the table name; changing the
table name is not an option.

Below is the full code I am working with:

Pele

Private Sub CmdaddHeadCountRecord_Click()
On Error GoTo Err_CmdaddHeadCounTRecord_Click
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim totalrecords As Integer
Dim i As Integer
Dim varHDCT As Long

'check that data entry is complete and correct
If [Forms]![frm_add Head Count record]![test Entry] = 1 Then
DoCmd.RunMacro "Mac_check add Head count record entry"
Exit Sub
End If

'saves the record in table "HEAD COUNT TBL"
DoCmd.RunMacro "Mac_set CC Code in Head Count Table" 'to include CC
code in table
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.hc_id ' HC_ID is a field
totalrecords = SW + EW + 1

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEAD COUNT EXTRA TIME TBL"
Set rst = CurrentDb.OpenRecordset("Head count extra time tbl")
With rst
For i = SW To EW
' Add new record.
.AddNew
!hc_id = varHDCT
!Week = i
![Meetings and training st] = Me.ST
'ub = unbound
![Meetings and training ot] = Me.OT
![Unscheduled OT] = Me.Unsched_OT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table Head count extra time tbl"

Stop

Exit_CmdaddHeadCounTRecord_Click:
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_CmdaddHeadCounTRecord_Click:
MsgBox Err.Description
Resume Exit_CmdaddHeadCounTRecord_Click


End Sub
Click to expand...
Click to expand...
 
F

FSt1

hi,
try this
add a dim
dim db as database
add a line
set db = codeDb()
Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",dbOpenDynaset)

this is the same syntax i use with recordset that work. if this doesn't work
then something else is the problem. are you sure this is the line it is
crashing on?

post back if problems

regards

FSt1
Pele said:
FST1,

I don't have the option regarding the changing the name; I need to keep the
name as is and find a way around it. I tried the second proposal you made and
put the name in brackets. The problematic line now looks like this:

Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",
)

Unfortunately, a window pops up and says "Microsoft Access can't find the
field 'forms' referred to in your expression". When I press okay, another
window pops up saying ""Object variable or with block variable not set". This
window seems to be locked in some kind of loop since it won't disappear after
i press OKay. Anyway, I can only get out by closing the application by using
the task manager.

Please let me know what other things I can try.

Toks




FSt1 said:
hi,
i think you have a naming problem.
see this site.
http://support.microsoft.com/?id=826763
characters not to use with access.
Space in as the top of the list and your table name is full of spaces.
you might get it to go if you inclose your table name in brackets [ name ]
but i would follow microsoft's advice and get rid of the spaces.

Post back if neither works.

Regards

FSt1


Pele said:
I had posted this before but didn't get help.

Somebody had helped me put together a VB code to add some textboxes from a
form to a table. Anyway, I get an error message when the code reaches the
a point refering to OpenRecordset; a message pops up and says "Item not
found".
I don't know why the code can't find the table since it the table thus exist
and it is named correctly. Can somebody hep me with this code (see below); I
have to find a way to let the code recognize the table name; changing the
table name is not an option.

Below is the full code I am working with:

Pele

Private Sub CmdaddHeadCountRecord_Click()
On Error GoTo Err_CmdaddHeadCounTRecord_Click
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim totalrecords As Integer
Dim i As Integer
Dim varHDCT As Long

'check that data entry is complete and correct
If [Forms]![frm_add Head Count record]![test Entry] = 1 Then
DoCmd.RunMacro "Mac_check add Head count record entry"
Exit Sub
End If

'saves the record in table "HEAD COUNT TBL"
DoCmd.RunMacro "Mac_set CC Code in Head Count Table" 'to include CC
code in table
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.hc_id ' HC_ID is a field
totalrecords = SW + EW + 1

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEAD COUNT EXTRA TIME TBL"
Set rst = CurrentDb.OpenRecordset("Head count extra time tbl")
With rst
For i = SW To EW
' Add new record.
.AddNew
!hc_id = varHDCT
!Week = i
![Meetings and training st] = Me.ST
'ub = unbound
![Meetings and training ot] = Me.OT
![Unscheduled OT] = Me.Unsched_OT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table Head count extra time tbl"

Stop

Exit_CmdaddHeadCounTRecord_Click:
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_CmdaddHeadCounTRecord_Click:
MsgBox Err.Description
Resume Exit_CmdaddHeadCounTRecord_Click


End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Pele

Fst1,

I tried the additional code you'd provided and I got the following error
message:

The microsoft Jet database engine cannot find the input table or query
'[Head Count extra time tbl]'. Make sure it exists and that its name is spekt
correctly'.

I then took out the brackets from the name and reran it but it gave me the
eror message "Object variable or with block variable not set" and it then
gets stuck as before.

I know the line that is problematic because I put a msgbox before and after
that line and the msgbox before is the only one that gets displayed i.e. the
code stooped at that line. To make sure I didn't make a mistake in the name,
I copied the name from the table itself and it still didn't work.

Pele

FSt1 said:
hi,
try this
add a dim
dim db as database
add a line
set db = codeDb()
Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",dbOpenDynaset)

this is the same syntax i use with recordset that work. if this doesn't work
then something else is the problem. are you sure this is the line it is
crashing on?

post back if problems

regards

FSt1
Pele said:
FST1,

I don't have the option regarding the changing the name; I need to keep the
name as is and find a way around it. I tried the second proposal you made and
put the name in brackets. The problematic line now looks like this:

Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",
)

Unfortunately, a window pops up and says "Microsoft Access can't find the
field 'forms' referred to in your expression". When I press okay, another
window pops up saying ""Object variable or with block variable not set". This
window seems to be locked in some kind of loop since it won't disappear after
i press OKay. Anyway, I can only get out by closing the application by using
the task manager.

Please let me know what other things I can try.

Toks




FSt1 said:
hi,
i think you have a naming problem.
see this site.
http://support.microsoft.com/?id=826763
characters not to use with access.
Space in as the top of the list and your table name is full of spaces.
you might get it to go if you inclose your table name in brackets [ name ]
but i would follow microsoft's advice and get rid of the spaces.

Post back if neither works.

Regards

FSt1


:

I had posted this before but didn't get help.

Somebody had helped me put together a VB code to add some textboxes from a
form to a table. Anyway, I get an error message when the code reaches the
a point refering to OpenRecordset; a message pops up and says "Item not
found".
I don't know why the code can't find the table since it the table thus exist
and it is named correctly. Can somebody hep me with this code (see below); I
have to find a way to let the code recognize the table name; changing the
table name is not an option.

Below is the full code I am working with:

Pele

Private Sub CmdaddHeadCountRecord_Click()
On Error GoTo Err_CmdaddHeadCounTRecord_Click
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim totalrecords As Integer
Dim i As Integer
Dim varHDCT As Long

'check that data entry is complete and correct
If [Forms]![frm_add Head Count record]![test Entry] = 1 Then
DoCmd.RunMacro "Mac_check add Head count record entry"
Exit Sub
End If

'saves the record in table "HEAD COUNT TBL"
DoCmd.RunMacro "Mac_set CC Code in Head Count Table" 'to include CC
code in table
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.hc_id ' HC_ID is a field
totalrecords = SW + EW + 1

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEAD COUNT EXTRA TIME TBL"
Set rst = CurrentDb.OpenRecordset("Head count extra time tbl")
With rst
For i = SW To EW
' Add new record.
.AddNew
!hc_id = varHDCT
!Week = i
![Meetings and training st] = Me.ST
'ub = unbound
![Meetings and training ot] = Me.OT
![Unscheduled OT] = Me.Unsched_OT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table Head count extra time tbl"

Stop

Exit_CmdaddHeadCounTRecord_Click:
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_CmdaddHeadCounTRecord_Click:
MsgBox Err.Description
Resume Exit_CmdaddHeadCounTRecord_Click


End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Pele

Fst1,

I apologize for this but your code was correct. I had made a mistake in the
name of one of my fields and that was the problem all along.

In the code below, I had been mistakenly using HC-ID instead of [HC ID].
Once I changed it, the code worked fine.

Thanks for your help.

Pele


Set rst = CurrentDb.OpenRecordset("Head count extra time tbl", dbOpenDynaset)
'Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]")
With rst
For i = SW To EW
' Add new record.
.AddNew
![HC ID] = varHDCT
!Week = i



FSt1 said:
hi,
try this
add a dim
dim db as database
add a line
set db = codeDb()
Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",dbOpenDynaset)

this is the same syntax i use with recordset that work. if this doesn't work
then something else is the problem. are you sure this is the line it is
crashing on?

post back if problems

regards

FSt1
Pele said:
FST1,

I don't have the option regarding the changing the name; I need to keep the
name as is and find a way around it. I tried the second proposal you made and
put the name in brackets. The problematic line now looks like this:

Set rst = CurrentDb.OpenRecordset("[Head count extra time tbl]",
)

Unfortunately, a window pops up and says "Microsoft Access can't find the
field 'forms' referred to in your expression". When I press okay, another
window pops up saying ""Object variable or with block variable not set". This
window seems to be locked in some kind of loop since it won't disappear after
i press OKay. Anyway, I can only get out by closing the application by using
the task manager.

Please let me know what other things I can try.

Toks




FSt1 said:
hi,
i think you have a naming problem.
see this site.
http://support.microsoft.com/?id=826763
characters not to use with access.
Space in as the top of the list and your table name is full of spaces.
you might get it to go if you inclose your table name in brackets [ name ]
but i would follow microsoft's advice and get rid of the spaces.

Post back if neither works.

Regards

FSt1


:

I had posted this before but didn't get help.

Somebody had helped me put together a VB code to add some textboxes from a
form to a table. Anyway, I get an error message when the code reaches the
a point refering to OpenRecordset; a message pops up and says "Item not
found".
I don't know why the code can't find the table since it the table thus exist
and it is named correctly. Can somebody hep me with this code (see below); I
have to find a way to let the code recognize the table name; changing the
table name is not an option.

Below is the full code I am working with:

Pele

Private Sub CmdaddHeadCountRecord_Click()
On Error GoTo Err_CmdaddHeadCounTRecord_Click
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim totalrecords As Integer
Dim i As Integer
Dim varHDCT As Long

'check that data entry is complete and correct
If [Forms]![frm_add Head Count record]![test Entry] = 1 Then
DoCmd.RunMacro "Mac_check add Head count record entry"
Exit Sub
End If

'saves the record in table "HEAD COUNT TBL"
DoCmd.RunMacro "Mac_set CC Code in Head Count Table" 'to include CC
code in table
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.hc_id ' HC_ID is a field
totalrecords = SW + EW + 1

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEAD COUNT EXTRA TIME TBL"
Set rst = CurrentDb.OpenRecordset("Head count extra time tbl")
With rst
For i = SW To EW
' Add new record.
.AddNew
!hc_id = varHDCT
!Week = i
![Meetings and training st] = Me.ST
'ub = unbound
![Meetings and training ot] = Me.OT
![Unscheduled OT] = Me.Unsched_OT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table Head count extra time tbl"

Stop

Exit_CmdaddHeadCounTRecord_Click:
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_CmdaddHeadCounTRecord_Click:
MsgBox Err.Description
Resume Exit_CmdaddHeadCounTRecord_Click


End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Item not found -table name issue 2
Code to cut a paragraph that's followed by a table 2
Adding table into existing access database via ADO 0
Runtime Error 3021: 'No Current Record' 2
Please help me edit this code 10
Holiday day tracking 2
Append loop working but causing duplicates - I'm lost 3
record count of table while looping through all tables 2

Top