Cannot update. Database or object is read-only

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub

Sub SubmitConcern2()
On Error GoTo Err_Submit_Click

Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)

ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub


When I tab through the code, it jumps from

ConcenRS. AddNew

Right to:

MsgBox Err.Description

But I can't figure out why it does this. Can anyone help?
 
Opal said:
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub

Sub SubmitConcern2()
On Error GoTo Err_Submit_Click

Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)

ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub


When I tab through the code, it jumps from

ConcenRS. AddNew

Right to:

MsgBox Err.Description

But I can't figure out why it does this. Can anyone help?
Click to expand...

Is ConcernComparetbl linked from another database? If you open the table in
datasheet view will it allow you to enter records?

Have you made any changes to the database immediately prior to this problem
occurring?

Ed Metcalfe.
 
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Click to expand...
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Click to expand...
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Click to expand...
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Click to expand...
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
Click to expand...
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Click to expand...
Exit_Submit_Click:
Exit Sub
Click to expand...
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
Click to expand...
When I tab through the code, it jumps from
Click to expand...
ConcenRS. AddNew
Click to expand...
Right to:
Click to expand...
MsgBox Err.Description
Click to expand...
But I can't figure out why it does this. Can anyone help?
Click to expand...

Is ConcernComparetbl linked from another database? If you open the table in
datasheet view will it allow you to enter records?

Have you made any changes to the database immediately prior to this problem
occurring?

Ed Metcalfe.- Hide quoted text -

- Show quoted text -
Click to expand...


Hi Ed

ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.

I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?
 
news:[email protected]...
Click to expand...
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Exit_Submit_Click:
Exit Sub
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
When I tab through the code, it jumps from
ConcenRS. AddNew
Right to:
MsgBox Err.Description
But I can't figure out why it does this. Can anyone help?
Click to expand...
Click to expand...
Is ConcernComparetbl linked from another database? If you open the table in
datasheet view will it allow you to enter records?
Click to expand...
Have you made any changes to the database immediately prior to this problem
occurring?
Click to expand...
Ed Metcalfe.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Hi Ed

ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.

I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....

I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:

Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1

to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:

http://www.lebans.com/rownumber.htm

and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.

I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?
 
Opal said:
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Click to expand...
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Click to expand...
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Click to expand...
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Click to expand...
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
Click to expand...
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Click to expand...
Exit_Submit_Click:
Exit Sub
Click to expand...
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
Click to expand...
When I tab through the code, it jumps from
Click to expand...
ConcenRS. AddNew
Click to expand...
Right to:
Click to expand...
MsgBox Err.Description
Click to expand...
But I can't figure out why it does this. Can anyone help?
Click to expand...
Is ConcernComparetbl linked from another database? If you open the
table in
datasheet view will it allow you to enter records?
Click to expand...
Have you made any changes to the database immediately prior to this
problem
occurring?
Click to expand...
Ed Metcalfe.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Hi Ed

ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.

I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....

I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:

Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1

to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:

http://www.lebans.com/rownumber.htm

and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.

I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?
Click to expand...

Opal,

An AutoNumber field is probably the simplest solution.

Why aren't you using one in your main table? Do you have a reason to save to
a temp table first?

Ed Metcalfe.
 
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Exit_Submit_Click:
Exit Sub
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
When I tab through the code, it jumps from
ConcenRS. AddNew
Right to:
MsgBox Err.Description
But I can't figure out why it does this. Can anyone help?
Is ConcernComparetbl linked from another database? If you open the
table in
datasheet view will it allow you to enter records?
Have you made any changes to the database immediately prior to this
problem
occurring?
Ed Metcalfe.- Hide quoted text -
- Show quoted text -
Hi Ed
ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.
I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....
Click to expand...
I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:
Click to expand...
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
Click to expand...
to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:

and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.
Click to expand...
I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?
Click to expand...

Opal,

An AutoNumber field is probably the simplest solution.

Why aren't you using one in your main table? Do you have a reason to save to
a temp table first?

Ed Metcalfe.- Hide quoted text -

- Show quoted text
Click to expand...

When the form is loaded, there is actually three "auto-number"
fields. One for each
shift and one for the 'concern' generated by the form. The users want
to be able to track
their inputs in this manner. Since I cannot have more than one "auto-
number" field in a
query I was using the:

Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1

to generate auto numbers in the field. But this will not work as was
noted in my last
post.

I will work on a temp table and see how that pans out.
 
news:[email protected]...
Click to expand...
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel = Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Exit_Submit_Click:
Exit Sub
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
When I tab through the code, it jumps from
ConcenRS. AddNew
Right to:
MsgBox Err.Description
But I can't figure out why it does this. Can anyone help?
Is ConcernComparetbl linked from another database? If you open the
table in
datasheet view will it allow you to enter records?
Have you made any changes to the database immediately prior to this
problem
occurring?
Ed Metcalfe.- Hide quoted text -
- Show quoted text -
Hi Ed
ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.
I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -
- Show quoted text -
Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....
I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:
http://www.lebans.com/rownumber.htm
and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.
I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?
Click to expand...
Opal,
Click to expand...

An AutoNumber field is probably the simplest solution.
Click to expand...
Why aren't you using one in your main table? Do you have a reason to save to
a temp table first?
Click to expand...
Ed Metcalfe.- Hide quoted text -
Click to expand...
- Show quoted text
Click to expand...

When the form is loaded, there is actually three "auto-number"
fields. One for each
shift and one for the 'concern' generated by the form. The users want
to be able to track
their inputs in this manner. Since I cannot have more than one "auto-
number" field in a
query I was using the:

Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1

to generate auto numbers in the field. But this will not work as was
noted in my last
post.

I will work on a temp table and see how that pans out.- Hide quoted text -

- Show quoted text -
Click to expand...

Okay....its not panning out....I am getting the same error.

Help?
 
Opal said:
On Nov 3, 9:06 pm, "Ed Metcalfe" <[email protected]> wrote:
Click to expand...
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Click to expand...
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Click to expand...
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Click to expand...
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Click to expand...
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
Click to expand...
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate =
Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel =
Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Click to expand...
Exit_Submit_Click:
Exit Sub
Click to expand...
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
Click to expand...
When I tab through the code, it jumps from
Click to expand...
ConcenRS. AddNew
Click to expand...
Right to:
Click to expand...
MsgBox Err.Description
Click to expand...
But I can't figure out why it does this. Can anyone help?
Click to expand...
Is ConcernComparetbl linked from another database? If you open the
table in
datasheet view will it allow you to enter records?
Click to expand...
Have you made any changes to the database immediately prior to
this
problem
occurring?
Click to expand...
Ed Metcalfe.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hi Ed
Click to expand...
ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.
Click to expand...
I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....
Click to expand...
I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:
Click to expand...
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
Click to expand...
to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:

and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.
Click to expand...
I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?

An AutoNumber field is probably the simplest solution.
Click to expand...
Why aren't you using one in your main table? Do you have a reason to
save to
a temp table first?
Click to expand...
Ed Metcalfe.- Hide quoted text -
Click to expand...
- Show quoted text
Click to expand...

When the form is loaded, there is actually three "auto-number"
fields. One for each
shift and one for the 'concern' generated by the form. The users want
to be able to track
their inputs in this manner. Since I cannot have more than one "auto-
number" field in a
query I was using the:

Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1

to generate auto numbers in the field. But this will not work as was
noted in my last
post.

I will work on a temp table and see how that pans out.- Hide quoted
text -

- Show quoted text -
Click to expand...

Okay....its not panning out....I am getting the same error.

Help?
Click to expand...

Opal,

I'm a bit stumped without actually being able to see the database. I've seen
this error message before, but not in this situation so I'm unsure exactly
what is causing it.

Is it something you would be able to upload to an FTP server (or even email
to me)? Or does it contain confidential data?

Ed Metcalfe.
 
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate =
Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel =
Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Exit_Submit_Click:
Exit Sub
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
When I tab through the code, it jumps from
ConcenRS. AddNew
Right to:
MsgBox Err.Description
But I can't figure out why it does this. Can anyone help?
Is ConcernComparetbl linked from another database? If you open the
table in
datasheet view will it allow you to enter records?
Have you made any changes to the database immediately prior to
this
problem
occurring?
Ed Metcalfe.- Hide quoted text -
- Show quoted text -
Hi Ed
ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.
I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -
- Show quoted text -
Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....
I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:
http://www.lebans.com/rownumber.htm
and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.
I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?
Opal,
An AutoNumber field is probably the simplest solution.
Why aren't you using one in your main table? Do you have a reason to
save to
a temp table first?
Ed Metcalfe.- Hide quoted text -
- Show quoted text
When the form is loaded, there is actually three "auto-number"
fields. One for each
shift and one for the 'concern' generated by the form. The users want
to be able to track
their inputs in this manner. Since I cannot have more than one "auto-
number" field in a
query I was using the:
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
to generate auto numbers in the field. But this will not work as was
noted in my last
post.
I will work on a temp table and see how that pans out.- Hide quoted
text -
- Show quoted text -
Click to expand...
Click to expand...
Okay....its not panning out....I am getting the same error.
Click to expand...
Help?
Click to expand...

Opal,

I'm a bit stumped without actually being able to see the database. I've seen
this error message before, but not in this situation so I'm unsure exactly
what is causing it.

Is it something you would be able to upload to an FTP server (or even email
to me)? Or does it contain confidential data?

Ed Metcalfe.- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Ed,

Not confidential, but proprietory...sorry. I walked away from it as I
was about
ready to through my laptop.....When I came back the message was
gone....
very strange....
 
I'm lost with this one. I have a form that opens with the
results of a select query between two tables. Actually
a sub-query - but that is not the problem, that works
fine. The object of the form is for the user to select
records from the query that need to be elevated to another
team of users to take action on. The form was working,
but now I am getting the above error message when
I select the "Submit" button on the form. Here is the
code:
Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If
SubmitConcern2
End Sub
Sub SubmitConcern2()
On Error GoTo Err_Submit_Click
Dim dbobject As DAO.Database
Dim ConcernRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM ConcernComparetbl;"
Set ConcernRS = dbobject.OpenRecordset(strquery)
ConcernRS.AddNew
ConcernRS!IDNumber = Forms![ConcernCompareqryfrm]!IDNumber.Value
ConcernRS!EntryDate =
Forms![ConcernCompareqryfrm]!EntryDatetxt.Value
ConcernRS!Status = Forms![ConcernCompareqryfrm]!Status.Value
ConcernRS!Panel =
Forms![ConcernCompareqryfrm]!AShifttbl_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!
AShifttbl_Concern.Value
ConcernRS!AShifttbl_Rank = Forms![ConcernCompareqryfrm]!
AShifttbl_Rank.Value
ConcernRS!AShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
AShifttbl_IDNumber.Value
ConcernRS!AShifttbl_Comments = Forms![ConcernCompareqryfrm]!
AShifttbl_Comments.Value
ConcernRS!BShifttbl_Rank = Forms![ConcernCompareqryfrm]!
BShifttbl_Rank.Value
ConcernRS!BShifttbl_IDNumber = Forms![ConcernCompareqryfrm]!
BShifttbl_IDNumber.Value
ConcernRS!BShifttbl_Comments = Forms![ConcernCompareqryfrm]!
BShifttbl_Comments.Value
ConcernRS.Update
Exit_Submit_Click:
Exit Sub
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub
When I tab through the code, it jumps from
ConcenRS. AddNew
Right to:
MsgBox Err.Description
But I can't figure out why it does this. Can anyone help?
Is ConcernComparetbl linked from another database? If you open the
table in
datasheet view will it allow you to enter records?
Have you made any changes to the database immediately prior to
this
problem
occurring?
Ed Metcalfe.- Hide quoted text -
- Show quoted text -
Hi Ed
ConcernComparetbl is in this database, not in another. When I open
the table in
datasheet view, yes I can enter records.
I made some changes to a separate form prior to this problem....what
should
I be looking for to resolve the problem?- Hide quoted text -
- Show quoted text -
Sorry to trouble you, I found the problem, but I am not sure how
to correct it so that the form functions the way I want it to....
I have a field on the form that generated an auto number
to be submitted to the table. This is an "ID" field for the
Concern that is generated from the form. Initially, on the
"On Open" event for the form I had the following:
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
to generate the autonumber. However, what happened was that if
there was more than one record generated by the query, the ID number
was the same for each record because they had not been written to the
table. I needed the ID numbers to increment because I cannot have
duplicate ID numbers in the table. I found a database on
Steve Lebans website:
http://www.lebans.com/rownumber.htm
and thought the solution presented would resolve my issue. But
it doesn't. After submitted records to the table (i.e. ID # 1 and 2)
and closing the form and then re-opening, the next ID numbers
should be 3 and 4 and so on....but it resets to # 1 again, which
is a problem.
I believe this is why I am getting the error. Should I, instead
send the query to a temporary table with an autonumber field and
then have the form open off that table?
Opal,
An AutoNumber field is probably the simplest solution.
Why aren't you using one in your main table? Do you have a reason to
save to
a temp table first?
Ed Metcalfe.- Hide quoted text -
- Show quoted text
When the form is loaded, there is actually three "auto-number"
fields. One for each
shift and one for the 'concern' generated by the form. The users want
to be able to track
their inputs in this manner. Since I cannot have more than one "auto-
number" field in a
query I was using the:
Me.IDNumber = Nz(DMax("IDNumber", "ConcernComparetbl"), 0) + 1
to generate auto numbers in the field. But this will not work as was
noted in my last
post.
I will work on a temp table and see how that pans out.- Hide quoted
text -
- Show quoted text -
Click to expand...
Click to expand...
Okay....its not panning out....I am getting the same error.
Click to expand...
Help?
Click to expand...

Opal,

I'm a bit stumped without actually being able to see the database. I've seen
this error message before, but not in this situation so I'm unsure exactly
what is causing it.

Is it something you would be able to upload to an FTP server (or even email
to me)? Or does it contain confidential data?

Ed Metcalfe.- Hide quoted text -

- Show quoted text -
Click to expand...


.....and now its back again...... something is not right. I am
running the
form off of a table. The table is fed by an append query. In my
research
as to why this is happening, I found that query results that have a
subquery
within them are read only. I thought this may have been causing the
error so I made a temporary table from the sub-query and am running
the
form off of the temporary table. It worked a few moments ago, and now
its
not....something is not right here and I'm ready to throw my laptop
again...
 
Back
Top