Cannot update. Database or object is read-only (re-post)

[Opal]

I posted this about a month ago and didn't get anywhere with it. I
have been struggling with different possible solutions but still
cannot get this form to work as expected.

Initially the form opened based on a sub-query which worked just
fine. However, I could
not get the records to write to a new table, I would get the above
noted error message.
Thinking that this had something to do with the limitations of opening
the form based on a
sub-query, I have changed tactics and instead have the form open bound
to a temporary table,
however, I still cannot write the records to a "permanent" table. I
still get the above
noted message.

The object of the form is for the user to view the records produced
and determine if they
should go forward or be removed - hence why I need a temporary table.

The code is as follows:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If [Status] = "" Then
MsgBox "You must change the status to Open."
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 ConcernCompare;"
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]!AShift_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!AShift_Concern.Value
ConcernRS!AShift_Rank = Forms![ConcernCompareqryfrm]!AShift_Rank.Value
ConcernRS!AShift_IDNumber = Forms![ConcernCompareqryfrm]!
AShift_IDNumber.Value
ConcernRS!AShift_Comments = Forms![ConcernCompareqryfrm]!
AShift_Comments.Value
ConcernRS!BShift_Rank = Forms![ConcernCompareqryfrm]!BShift_Rank.Value
ConcernRS!BShift_IDNumber = Forms![ConcernCompareqryfrm]!
BShift_IDNumber.Value
ConcernRS!BShift_Comments = Forms![ConcernCompareqryfrm]!
BShift_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
'Resume Exit_Submit_Click
End Sub

Any assistance would be appreciated. Thank you.

 

[Bob Quintal]

(e-mail address removed)
m:

The code is as follows:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If [Status] = "" Then
MsgBox "You must change the status to Open."
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 ConcernCompare;"
Set ConcernRS = dbobject.OpenRecordset(strquery)

ConcernRS.AddNew [snip]
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
'Resume Exit_Submit_Click
End Sub

Any assistance would be appreciated. Thank you.

Comment out the line:

On Error GoTo Err_Submit_Click

Then give it a run. Which line of code does it break on?- Hide
quoted text -

- Show quoted text -

It stops on "ConcernRS.AddNew"

Post the SQL for ConcernCompare, it's probably a query featuring a
sub-query or a left join.

 

[Stuart McCall]

I posted this about a month ago and didn't get anywhere with it. I
have been struggling with different possible solutions but still
cannot get this form to work as expected.

Initially the form opened based on a sub-query which worked just
fine. However, I could
not get the records to write to a new table, I would get the above
noted error message.
Thinking that this had something to do with the limitations of opening
the form based on a
sub-query, I have changed tactics and instead have the form open bound
to a temporary table,
however, I still cannot write the records to a "permanent" table. I
still get the above
noted message.

The object of the form is for the user to view the records produced
and determine if they
should go forward or be removed - hence why I need a temporary table.

The code is as follows:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If [Status] = "" Then
MsgBox "You must change the status to Open."
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 ConcernCompare;"
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]!AShift_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!AShift_Concern.Value
ConcernRS!AShift_Rank = Forms![ConcernCompareqryfrm]!AShift_Rank.Value
ConcernRS!AShift_IDNumber = Forms![ConcernCompareqryfrm]!
AShift_IDNumber.Value
ConcernRS!AShift_Comments = Forms![ConcernCompareqryfrm]!
AShift_Comments.Value
ConcernRS!BShift_Rank = Forms![ConcernCompareqryfrm]!BShift_Rank.Value
ConcernRS!BShift_IDNumber = Forms![ConcernCompareqryfrm]!
BShift_IDNumber.Value
ConcernRS!BShift_Comments = Forms![ConcernCompareqryfrm]!
BShift_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
'Resume Exit_Submit_Click
End Sub

Any assistance would be appreciated. Thank you.

Comment out the line:

On Error GoTo Err_Submit_Click

Then give it a run. Which line of code does it break on?

 

[Opal]

I posted this about a month ago and didn't get anywhere with it. I
have been struggling with different possible solutions but still
cannot get this form to work as expected.

Initially the form opened based on a sub-query which worked just
fine. However, I could
not get the records to write to a new table, I would get the above
noted error message.
Thinking that this had something to do with the limitations of opening
the form based on a
sub-query, I have changed tactics and instead have the form open bound
to a temporary table,
however, I still cannot write the records to a "permanent" table. I
still get the above
noted message.

The object of the form is for the user to view the records produced
and determine if they
should go forward or be removed - hence why I need a temporary table.

The code is as follows:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If [Status] = "" Then
MsgBox "You must change the status to Open."
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 ConcernCompare;"
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]!AShift_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!AShift_Concern.Value
ConcernRS!AShift_Rank = Forms![ConcernCompareqryfrm]!AShift_Rank.Value
ConcernRS!AShift_IDNumber = Forms![ConcernCompareqryfrm]!
AShift_IDNumber.Value
ConcernRS!AShift_Comments = Forms![ConcernCompareqryfrm]!
AShift_Comments.Value
ConcernRS!BShift_Rank = Forms![ConcernCompareqryfrm]!BShift_Rank.Value
ConcernRS!BShift_IDNumber = Forms![ConcernCompareqryfrm]!
BShift_IDNumber.Value
ConcernRS!BShift_Comments = Forms![ConcernCompareqryfrm]!
BShift_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
'Resume Exit_Submit_Click
End Sub

Any assistance would be appreciated. Thank you.

Comment out the line:

On Error GoTo Err_Submit_Click

Then give it a run. Which line of code does it break on?- Hide quoted text -

- Show quoted text -

It stops on "ConcernRS.AddNew"

 

[Stuart McCall]

It stops on "ConcernRS.AddNew"

Well if ConcernCompare is indeed a table and not a union query or something,
your code ought to work fine.

One thing you could check is your references. Make sure you have a reference
to DAO 3.x

If you also have a reference set for ADO, and if you aren't using ADO code,
remove it.

 

[AccessVandal via AccessMonster.com]

Hi Opal,

You said that the Form is bound to a Table or Query. I don't see how this
part of the code works.

If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If

Since it is bound, when you "insert" i.e. input data into the fields of the
Form, the Form's property for "Dirty" will be always "True". When you use the
event for the button, you set the Form's "Dirty" to "False", this will insert
a new record into the Table.

Since the Form is bound, you don't need the subroutine "SubmitConcern2"
anymore.

Unless, you're intending to use unbound form?

I posted this about a month ago and didn't get anywhere with it. I
have been struggling with different possible solutions but still
cannot get this form to work as expected.

Initially the form opened based on a sub-query which worked just
fine. However, I could
not get the records to write to a new table, I would get the above
noted error message.
Thinking that this had something to do with the limitations of opening
the form based on a
sub-query, I have changed tactics and instead have the form open bound
to a temporary table,
however, I still cannot write the records to a "permanent" table. I
still get the above
noted message.

The object of the form is for the user to view the records produced
and determine if they
should go forward or be removed - hence why I need a temporary table.

The code is as follows:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If [Status] = "" Then
MsgBox "You must change the status to Open."
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 ConcernCompare;"
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]!AShift_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!AShift_Concern.Value
ConcernRS!AShift_Rank = Forms![ConcernCompareqryfrm]!AShift_Rank.Value
ConcernRS!AShift_IDNumber = Forms![ConcernCompareqryfrm]!
AShift_IDNumber.Value
ConcernRS!AShift_Comments = Forms![ConcernCompareqryfrm]!
AShift_Comments.Value
ConcernRS!BShift_Rank = Forms![ConcernCompareqryfrm]!BShift_Rank.Value
ConcernRS!BShift_IDNumber = Forms![ConcernCompareqryfrm]!
BShift_IDNumber.Value
ConcernRS!BShift_Comments = Forms![ConcernCompareqryfrm]!
BShift_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
'Resume Exit_Submit_Click
End Sub

Any assistance would be appreciated. Thank you.

 

[Opal]

Hi Opal,

You said that the Form is bound to a Table or Query. I don't see how this
part of the code works.

If Me.Dirty Then
Me.Dirty = False
Exit Sub
End If

Since it is bound, when you "insert" i.e. input data into the fields of the
Form, the Form's property for "Dirty" will be always "True". When you use the
event for the button, you set the Form's "Dirty" to "False", this will insert
a new record into the Table.

Since the Form is bound, you don't need the subroutine "SubmitConcern2"
anymore.

Unless, you're intending to use unbound form?

I posted this about a month ago and didn't get anywhere with it. I
have been struggling with different possible solutions but still
cannot get this form to work as expected.

Initially the form opened based on a sub-query which worked just
fine. However, I could
not get the records to write to a new table, I would get the above
noted error message.
Thinking that this had something to do with the limitations of opening
the form based on a
sub-query, I have changed tactics and instead have the form open bound
to a temporary table,
however, I still cannot write the records to a "permanent" table. I
still get the above
noted message.

The object of the form is for the user to view the records produced
and determine if they
should go forward or be removed - hence why I need a temporary table.

The code is as follows:

Private Sub SubmitCMRequest_Click()
If Len([EntryDatetxt] & "") = 0 Then
MsgBox "You must select a Date."
Exit Sub
End If
If [Status] = "" Then
MsgBox "You must change the status to Open."
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 ConcernCompare;"
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]!AShift_Panel.Value
ConcernRS!Concern = Forms![ConcernCompareqryfrm]!AShift_Concern.Value
ConcernRS!AShift_Rank = Forms![ConcernCompareqryfrm]!AShift_Rank.Value
ConcernRS!AShift_IDNumber = Forms![ConcernCompareqryfrm]!
AShift_IDNumber.Value
ConcernRS!AShift_Comments = Forms![ConcernCompareqryfrm]!
AShift_Comments.Value
ConcernRS!BShift_Rank = Forms![ConcernCompareqryfrm]!BShift_Rank.Value
ConcernRS!BShift_IDNumber = Forms![ConcernCompareqryfrm]!
BShift_IDNumber.Value
ConcernRS!BShift_Comments = Forms![ConcernCompareqryfrm]!
BShift_Comments.Value
ConcernRS.Update

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
'Resume Exit_Submit_Click
End Sub

Any assistance would be appreciated. Thank you.

--
Please Rate the posting if helps you

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

Bob, originally, it was a form bound to a sub-query. My research told
me that
I should not handle this form in that way so I created a temporary
table instead.

Access, the form is bound to a temporary table, I need to write the
records to
a permanent table once viewed by the user in the form. I have the
"Dirty" there
from when the form was bound to a query and I forgot to
remove....sorry about
the confusion.

Stuart, my references are fine. No ADO and the DAO is 3.6

 

[AccessVandal via AccessMonster.com]

Hi Opal,

Like I said, you don't need any code to insert a record since the Form is
bound to a Table or Query.
Just simply set the "Dirty" to "True" in the button event or create a new
"Save" button from Access Wizard.

 

[AccessVandal via AccessMonster.com]

Please note error here:

Just simply set the "Dirty" to "False" in the button event or create a new
"Save" button from Access Wizard.

Set to "False"

 

[Opal]

Please note error here:

Just simply set the "Dirty" to "False" in the button event or create a new
"Save" button from Access Wizard.

Set to "False"

Hi Access,

I have bound the form to an SQL invoked query and can now get past the
"ConcernRS.AddNew"
statement without the original error.

Now, on the line that reads:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value

I get: "Object doesn't support this property or method"

I need to be able to write from a temporary table to a permanent table
in order
to accomplish what the user wants with this form. I need the code to
insert the
record as I need to move the desired data the from the one table to
the other.
If there is a "better" way, please advise.

 

[Steve Sanford]

Your original post had the line as:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value

You just posted it as this:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value


the difference being one is "EntryDatetxt" (has txt in it) and the other is
"EntryDate" (doesn't have txt in the name)


It might just be a spelling error. I would check the names.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Please note error here:

Just simply set the "Dirty" to "False" in the button event or create a new
"Save" button from Access Wizard.

Set to "False"

Hi Access,

I have bound the form to an SQL invoked query and can now get past the
"ConcernRS.AddNew"
statement without the original error.

Now, on the line that reads:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value

I get: "Object doesn't support this property or method"

I need to be able to write from a temporary table to a permanent table
in order
to accomplish what the user wants with this form. I need the code to
insert the
record as I need to move the desired data the from the one table to
the other.
If there is a "better" way, please advise.

 

[Opal]

Your original post had the line as:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDatetxt.Value

You just posted it as this:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value

the difference being one is "EntryDatetxt" (has txt in it) and the other is
"EntryDate" (doesn't have txt in the name)

It might just be a spelling error. I would check the names.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Access,

I have bound the form to an SQL invoked query and can now get past the
"ConcernRS.AddNew"
statement without the original error.

Now, on the line that reads:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value

I get: "Object doesn't support this property or method"

I need to be able to write from a temporary table to a permanent table
in order
to accomplish what the user wants with this form. I need the code to
insert the
record as I need to move the desired data the from the one table to
the other.
If there is a "better" way, please advise.- Hide quoted text -

- Show quoted text -

Nope, that's not it....I changed the name on the form from
EntryDatetxt to EntryDate to match the table.

 

[Opal]

Please note error here:

Just simply set the "Dirty" to "False" in the button event or create a new
"Save" button from Access Wizard.

Set to "False"

Message posted viahttp://www.accessmonster.com

Hi Access,

I have bound the form to an SQL invoked query and can now get past the
"ConcernRS.AddNew"
statement without the original error.

Now, on the line that reads:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value

I get: "Object doesn't support this property or method"

I need to be able to write from a temporary table to a permanent table
in order
to accomplish what the user wants with this form. I need the code to
insert the
record as I need to move the desired data the from the one table to
the other.
If there is a "better" way, please advise.

There is something really "buggy" here....I closed the database
and am now back into it 11 hours later and I am back to the same error
code: "Cannot update. Database or object is read-only."

No one has touched it since I closed it....how can I get to
a certain point at one time and then get hung up at another?

 

[Opal]

On Dec 2, 10:18 pm, "AccessVandal via AccessMonster.com" <u18947@uwe>
wrote:

Hi Access,

I have bound the form to an SQL invoked query and can now get past the
"ConcernRS.AddNew"
statement without the original error.

Now, on the line that reads:

ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value

I get: "Object doesn't support this property or method"

I need to be able to write from a temporary table to a permanent table
in order
to accomplish what the user wants with this form. I need the code to
insert the
record as I need to move the desired data the from the one table to
the other.
If there is a "better" way, please advise.

There is something really "buggy" here....I closed the database
and am now back into it 11 hours later and I am back to the same error
code: "Cannot update. Database or object is read-only."

No one has touched it since I closed it....how can I get to
a certain point at one time and then get hung up at another?- Hide quoted text -

- Show quoted text -

OH MAN! I figured it out.....now I know my problem...but now I need
another workaround,
if possible....

I had three text boxes on the form linked to 3 hidden subforms. Each
subform had
a calculated field showing a "count" of the number of "open", "Closed-
NG" and
"closed-OK" records in the ConcernCompare table. The user, upon
opening this form would
like to see how many of each of these there are in the permanent
table....but by having
queries bound to the table to which I am writing to, that was what was
causing the error.
I did this in this manner based on what someone else "taught"
me....now I think there must
be a better way....any suggestions?

 

[Opal]

On Dec 2, 10:18 pm, "AccessVandal via AccessMonster.com" <u18947@uwe>
wrote:
Please note error here:
Just simply set the "Dirty" to "False" in the button event or create a new
"Save" button from Access Wizard.
Set to "False"
--
Please Rate the posting if helps you
Message posted viahttp://www.accessmonster.com
Hi Access,
I have bound the form to an SQL invoked query and can now get past the
"ConcernRS.AddNew"
statement without the original error.
Now, on the line that reads:
ConcernRS!EntryDate = Forms![ConcernCompareqryfrm]!EntryDate.Value
I get: "Object doesn't support this property or method"
I need to be able to write from a temporary table to a permanent table
in order
to accomplish what the user wants with this form. I need the code to
insert the
record as I need to move the desired data the from the one table to
the other.
If there is a "better" way, please advise.

There is something really "buggy" here....I closed the database
and am now back into it 11 hours later and I am back to the same error
code: "Cannot update. Database or object is read-only."

No one has touched it since I closed it....how can I get to
a certain point at one time and then get hung up at another?- Hide quoted text -

- Show quoted text -

OH MAN! I figured it out.....now I know my problem...but now I need
another workaround,
if possible....

I had three text boxes on the form linked to 3 hidden subforms. Each
subform had
a calculated field showing a "count" of the number of "open", "Closed-
NG" and
"closed-OK" records in the ConcernCompare table. The user, upon
opening this form would
like to see how many of each of these there are in the permanent
table....but by having
queries bound to the table to which I am writing to, that was what was
causing the error.
I did this in this manner based on what someone else "taught"
me....now I think there must
be a better way....any suggestions?- Hide quoted text -

- Show quoted text -

Okay...I will never listen to this "guy" again... DCount did the
job for me

 

[Steve Sanford]

Great!!

That was going to be my next suggestion..



OK, I can think of two ways.

1) Use Dcount() function. Set the control sources for each of the three text
boxes to:

=DCount("[txtStatus]","ConcernCompare","[txtStatus]= 'open' ")

=DCount("[txtStatus]","ConcernCompare","[txtStatus]= 'Closed-NG' ")

=DCount("[txtStatus]","ConcernCompare","[txtStatus]= 'Closed-OK' ")


NOTE: I changed the name of the "STATUS" field. "STATUS" is a reserved word.
It confuses Access when you use reserved words as object names.



2) Run code that opens a recordset for each of the three options and pushes
the number into the respective control. (code in the Form Open event)

'------------beg code sample----------------
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSelect As String
Dim Kount As Integer

strSelect = "SELECT txtStatus FROM ConcernCompare WHERE [txtStatus] ='"
Kount = 0

Set db = CurrentDb

'-------get count of OPEN
strSQL = strSelect & "Open" & "'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
Kount = rs.RecordCount
End If
rs.Close

'my made up control name
Me.tbConcernOpen = Kount

Kount = 0
'-----get count of CLOSED NG
strSQL = strSelect & "Closed-NG" & "'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
Kount = rs.RecordCount
End If
rs.Close

'my made up control name
Me.tbConcernNG = Kount

Kount = 0
'-----get count of CLOSED OK
strSQL = strSelect & "Closed-OK" & "'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
Kount = rs.RecordCount
End If
rs.Close

'my made up control name
Me.tbConcernOK = Kount

Set rs = Nothing
Set db = Nothing

End Sub
'------------end code----------------

HTH

 

[AccessVandal via AccessMonster.com]

Hi Opal,

It is better to read up on tutorials. Here's one for you.

http://home.att.net/~california.db/tips.html#aTip1

I suspect that your tables are not normalized, it's impossible to guess
correctly.