Copy Status Of Checkbox From One Table1 To Table2 2

[cmdwst]

I have a drawing database. Table1 contents:
DWGID Number, Primary Key
Ex Text, 3 char
DwgNo Text, 9 char
Rev Text, 2 char
Title Text, 255 char
Format Text, 3 char
Deleted Yes/No
Comments Memo

Table 2 contents:
DWGID Number, Link to tblDwg
UOID Number, Index
Used Text, 9 char
UDeleted Yes/No


Main Form has Table1 fields. The issue I have is that when the CheckBox Deleted is Checked (Change State), I need to have the program search through Table2 and Change UDelete to the same state (either Checked or Not Checked).


UDelete is used in a subreport to change the text color of the Used field if a specific drawing is deleted. This flags engineers that a change to a drawing is needed.

My code is as follows:
frmDWg
-----*-----

Option Explicit

Private Sub Deleted_AfterUpdate()
'When this Box checked, uncheck ReqUpdt, Updated and NoChange
    If Deleted = "-1" Then
        Ex.ForeColor = "255"
        DwgNo.ForeColor = "255"
        Rev.ForeColor = "255"
        Title.ForeColor = "255"
        Format.ForeColor = "255"
    ElseIf Deleted = "0" Then
        If Not IsNull(Format) Then
            Ex.ForeColor = "16711680"
            DwgNo.ForeColor = "16711680"
            Rev.ForeColor = "16711680"
            Title.ForeColor = "16711680"
            Format.ForeColor = "16711680"
        Else
            Ex.ForeColor = "0"
            DwgNo.ForeColor = "0"
            Rev.ForeColor = "0"
            Title.ForeColor = "0"
            Format.ForeColor = "0"
        End If
    End If
    
    DwgTemp = DwgNo
    DelTemp = Deleted

    CheckDeleted
    
    DwgTemp = ""
    DelTemp = ""

End Sub

-----*-----
Module - Public

Option Explicit
-----
Public DwgTemp As String
Public DelTemp As Variant
-----
Public Function CheckDeleted()
'When frmDwg.Deleted is checked, go to tblUsed and set UDeleted to same state as Deleted for same Dwg

    Dim dbsDoc As Object
    Dim rsUsed As Variant
    Set dbsDoc = CurrentDb

    Set rsUsed = dbsDoc.OpenRecordset("tblUsedOn")
    
    rsUsed.Index = "UOID"
    rsUsed.Seek "=", DwgTemp
    If Not rsUsed.NoMatch Then
        rsUsed.UDeleted = DelTemp
    End If
   
    rsUsed.Close
    Set rsUsed = Nothing

End Function

-----*-----
When I actually click on Deleted, I get the following error:
Run-Time Error '3251': Operation is not supported for this type of object.

When I click debug, the line of code highlighted is:
rsUsed.Index = "UOID"

UOID is the primary index (No Duplicates) in tblUsedOn.

I do not understand what the issue is....

 

[fneily]

Could be a library reference problem. See:
ACC2000: How to Use the Seek Method on Linked Tables

http://support.microsoft.com/kb/210266

Make sure Microsoft DAO 3.6 Object Library check box is selected.
And change Dim rsUsed As Variant to
Dim rsUsed As DAO.Recordset

Don't understand your table2 structure. What is the primary key? DWGID/UOID multi-field PK?

 

[TheAceMan1]

How are ya cmdwst . . .

An [blue]Update[/blue] query should do!. In the AfterUpdate event of [blue]Deleted[/blue], try the following (be sure to disable or remove any other code to handle this to [blue]prevent interaction[/blue]):

[blue]   Dim db As DAO.Database, SQL As String
   
   Set db = CurrentDb
   
   SQL = "UPDATE [purple][B][I]Table2Name[/I][/B][/purple] " & _
         "SET [[purple][B][I]Table2Name[/I][/B][/purple]].[UDeleted] = " & Me!Deleted & " " & _
         "WHERE [[purple][B][I]Table2Name[/I][/B][/purple]].[DWGID] = " & Me!DWGID & ";"
   db.Execute SQL, dbFailOnError
   
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cmdwst]

fneily and TheAceMan1

Thanks to both of you.

fneily - your suggestion made the code work. However, TheAceMan1's suggestion was much simpler and straightforward. I decided to change my code over to his suggestion, while adding a search for the drawing number rather than the DwgId.

fneily - don't dispare, I am going to use the recordset code and tie it to an update button that will search the tblDwg and update the tblUsed for all drawings. This will ensure I have current data before I run my reports.

Thanks again!

 

[fneily]

How can I dispare? I rejoice. You learned two useful lessons. That's what Tek-Tips is about.

 

[TheAceMan1]

Roger That [blue]fneily[/blue]!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]