Run-Time Error '424 - Object Required

[dougmeece]

Good Morning Experts,

I spent all weekend working on this and got absolutely no where.

I have a database with an input form that runs an append query to append to a table.

Form Name: Poetry
Append Query Name: Created_Submitt ed_Work
Table Name: Created_Submitt ed

I have also created what I hope is an update query. The purpose of this query is to check whether a record exists that contains the words “Not Applicable” in the table field [Submitted To]. I am trying to test this in stages by verifying I can examine the table field for “Not Applicable” in the (Created_Submit ted.[Submitted To]) field after clicking the Add Record command button.

I am constantly getting the message “Run-Time Error '424 - Object Required”. But I don’t know what it is referring to. I assume it is not able to access the table to verify the field contents. If that is the case I have no idea how to make it happen. If that is not the case, then again, I have no idea what is causing this.

I have commented out everything in the event and am just trying to do s simple if statement to see if it works.

Here is what I used to test that:

Private Sub CMD_AddRecord2_Click()
If (Created_Submitted.[Submitted To]) = "Not Applicable" Then
       MsgBox "This Worked!", vbOK, "Woo Hoo!!!"
    End If
End Sub

Any help would be greatly appreciated.

[nico5038]

Did you try:

Private Sub CMD_AddRecord2_Click()
If mE.[Submitted To] = "Not Applicable" Then
   MsgBox ("This Worked!", vbOK, "Woo Hoo!!!")
End If
End Sub

This assumes that the code is behind a form and the field is named [Submitted To] (Check the name in the Other tab of the properties window).
For referring to the field in a table a Dlookup() statement is needed.

Nic;o)

[dougmeece]

Thank Nico,,

I tried using the code below and received a differnet message.

Private Sub CMD_AddRecord2_Click()
    Dim subTo As Variant
        subTo = DLookup("[Submitted To]", "Created_Submitted", "[Title] = " _
        & Forms!Poetry!TxtTitle)
End Sub

When I ran this just to see if it would error I received the message "Run-time error 3075 - Syntax error (missing operator) in query expression '[Title] = Blue Jean Hoochie Mama'" . In this error Blue Jean Hoochie Mama is the name of a poem that is in the table. I want to look up the values for the Submitted To field in the Created_Submitt ed table where the Title field in the same table is equal to the TxtTitle field on the form Poetry.

I have tried looking up this error and found information referrinfg the apostrophes and such but the value selected does not contain any.

Thoughts???

Thanks,
Doug

[nico5038]

Thank Nico,,

I tried using the code below and received a differnet message.

Private Sub CMD_AddRecord2_Click()
    Dim subTo As Variant
        subTo = DLookup("[Submitted To]", "Created_Submitted", "[Title] = " _
        & Forms!Poetry!TxtTitle)
End Sub

When I ran this just to see if it would error I received the message "Run-time error 3075 - Syntax error (missing operator) in query expression '[Title] = Blue Jean Hoochie Mama'" . In this error Blue Jean Hoochie Mama is the name of a poem that is in the table. I want to look up the values for the Submitted To field in the Created_Submitt ed table where the Title field in the same table is equal to the TxtTitle field on the form Poetry.

I have tried looking up this error and found information referrinfg the apostrophes and such but the value selected does not contain any.

Thoughts???

Thanks,
Doug

Strings need to be embedded in quotes like:

Private Sub CMD_AddRecord2_Click()
    Dim subTo As Variant
        subTo = DLookup("[Submitted To]", "Created_Submitted", "[Title] = '" _
        & Forms!Poetry!TxtTitle) & "'"
End Sub

The single quotes ( ' ) are hard to see, but they need to be in the WHERE parameter for every text field. Dates need surrounding #'s and numbers need nothing.

Nic;o)

[dougmeece]

I got the exact same error message. I moved the right parantheses to the very end of the code you sent and got further. the problem there was that the update query wanted to update all the records whether the title field in the table matched to txttitle filed on the form or not.

I added an If statement to test it and opened up some of the code.

Private Sub CMD_AddRecord2_Click()
On Error GoTo Err_CMD_Add_Record2_Click
    
    Dim subTo As Variant
                
        subTo = DLookup("[Submitted To]", "Created_Submitted", "[Title] = '" _
        & Forms!Poetry!TxtTitle & "'")
        
    If subTo = "Not Applicable" Then
        
        Dim stUpdate As String
        stUpdate = "Created_Submitted_Query"
        DoCmd.OpenQuery stUpdate, acNormal, acEdit
        
        CMD_Cancel.Visible = True
        cmdSpecificSearch.Visible = True
        lblSubmissionDetails.Visible = False
        lblSubmittedTo.Visible = False
        cboSubmitted.Visible = False
        lblWebsite.Visible = False
        TxtWebsite.Visible = False
        lblType.Visible = False
        TxtType.Visible = False
        lblDateSubmitted.Visible = False
        TxtDate.Visible = False
        lblDateAdded.Visible = False
        TxtDate2.Visible = False
        lblAccepted.Visible = False
        cboAccepted.Visible = False
        CMD_Cancel.SetFocus
        CMD_AddRecord2.Visible = False
        CMD_Cancel2.Visible = False
        CMSAllRecords.Visible = True
        Me![cboTitles] = ""
        Me![TxtYearCreated] = ""
        Me![cboSubmittedBox] = ""
        Me![cboSubmitted] = ""
        Me![TxtWebsite] = ""
        Me![TxtType] = ""
        Me![TxtDate] = ""
        Me![TxtDate2] = ""
        Me![cboAccepted] = ""

    End If

Exit_CMD_Add_Record2_Click:
    Exit Sub

Err_CMD_Add_Record2_Click:
    MsgBox Err.Description
    Resume Exit_CMD_Add_Record2_Click
    
End Sub

[nico5038]

The UPDATE query needs to hold the unique key.
Personally I use often:

curentdb.execute ("UPDATE tblX SET Field1 = '" & Me.FieldFromForm & "' WHERE ID=" & Me.ID)

Nic;o)

[dougmeece]

Thanks. I will try that tomororw, but I really have no idea where it goes in my code. I will have to research it in the morning.

[nico5038]

Would be after the IF statement:

If subTo = "Not Applicable" Then

use it instead of running the UPDATE query.

Nic;o)

[dougmeece]

I apologoze but I don't understand all of it. I am really struggling with this one and I don't really know why.

WHAT YOU SENT

curentdb.Execute ("UPDATE tblX SET Field1 = '" & Me.FieldFromForm & "' WHERE ID=" & Me.ID)

WHAT I THINK I NEED (I have bolded what I do not understand)

curentdb.Execute ("UPDATE Created_Submitted [B]SET Field1[/B] = '" & Me.TxtTitle & "' WHERE [B]ID =" & Me.ID[/B])

Do I need to replace Field1 with something else?
Is Where ID supposed to be the field in the table (Title) and Me.ID supposed to be the form field again?

Again, sorry for my ignorance.

Thanks,
Doug

[dougmeece]

I read online that Access 2000 and 2002 will not allow the Currentdb command to work. I am using Access 2003 Sp3 on an XP machine. Do you think this be a problem for me?

Would be after the IF statement:

If subTo = "Not Applicable" Then

use it instead of running the UPDATE query.

Nic;o)

[nico5038]

My line of code is a sample, as I don't know the table and field(s) you use.
Just open the helpfile to see the syntax and to replace my sample names with the names you need.
The ID field should be the unique identifier of the row in the table, else all rows are updated...

Nic;o)

[NeoPa]

I read online that Access 2000 and 2002 will not allow the Currentdb command to work. I am using Access 2003 Sp3 on an XP machine. Do you think this be a problem for me?

No, I doubt this will be a problem.

What you should understand if you're setting up multiple fields is that your UPDATE SQL string will have to include a {[Field]=Value,} for EACH of the fields to be updated.

Are you getting the picture Doug?