Unable to run Update Query...Please Help

[dougmeece]

Hello experts,

I have an update query that I want to modify records meeting a certain criteria in a table based on information on a form.

I am having trouble recognizing the table for a comparison to text box on the form.

Table Name: Created_Submitted
Update Query: Created_Submitted_Update
Text Box: TxtTitle

 
If Me![TxtTitle] = (Created_Submitted.Title) Then
    Dim stUpdate As String
 
    stUpdate = "Created_Submitted_Update
DoCmd.OpenQuery stUpdate, acNormal, asEdit
End If
 

I am getting the message “Object Required” when I try to run this. I have been away from working on this for a week or so but I have still not been able to get this to work.

As always, any help would be greatly appreciated.

Thanks,
Doug

[puppydogbuddy]

Doug,
Try changing the following:

this:
If Me![TxtTitle] = (Created_Submitted.Title) Then
to this:
If Me![TxtTitle] = [Created_Submitted.Title] Then

and this:
stUpdate = "Created_Submitted_Update
to this:
stUpdate = "Created_Submitted_Update"

[dougmeece]

I did that but I am now getting the message: Microsoft Access can't find the field "|" referred to in your expression.

      If Me![TxtTitle] = [Created_Submitted.Title] Then
            Dim stUpdate As String
 
            stUpdate = "Created_Submitted_Update"
            DoCmd.OpenQuery stUpdate, acNormal, asEdit
      End If
 

I have also created a query to verify what has not been submitted and then tried comparing the form's text field to the corresponding field in the verify query results. That gave me the same thing.

Ultimately what I need this to do is compare the text field on the form to the Title field in the table and also to determine if the [Submitted To] field in the table is equal to "Not Applicable". If that all matches then I wnat to run the update query to modify that particular record in the table. If not then it will run an append query. The append query is working fine but there are no comparisons between the form and the table for that one.

Thanks,
Doug

[puppydogbuddy]

Can't tell where error msg is coming from. Can you pinpoint the error line?

Try changing this:
If Me![TxtTitle] = [Created_Submitted.Title] Then
to this:
If Me![TxtTitle] = [Created_Submitted].[Title] Then


or this:
If Me![TxtTitle] = [Title] Then

[dougmeece]

This is the entire active code for that event:

Private Sub CMD_AddRecord2_Click()
On Error GoTo Err_CMD_Add_Record2_Click
 
If [Forms]![Poetry]![TxtTitle] = [Created_Submitted].[Title] Then
Dim stUpdate As String
 
stUpdate = "Created_Submitted_Update"
DoCmd.OpenQuery stUpdate, acNormal, asEdit
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
 

I can't seem to step through it to see where it is failing. I tried your suggestions and also repolaced Me![TxtTitle] with [Forms]![Poetry]![TxtTitle]. None of them worked.

Someone pointed me to some CheckFilter code but whan I tried that it didn't work either. I'm pretty sure I had it all setup wrong though.

[puppydogbuddy]

This is the entire active code for that event:

Private Sub CMD_AddRecord2_Click()
On Error GoTo Err_CMD_Add_Record2_Click
 
If [Forms]![Poetry]![TxtTitle] = [Created_Submitted].[Title] Then
Dim stUpdate As String
 
stUpdate = "Created_Submitted_Update"
DoCmd.OpenQuery stUpdate, acNormal, asEdit
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
 

I can't seem to step through it to see where it is failing. I tried your suggestions and also repolaced Me![TxtTitle] with [Forms]![Poetry]![TxtTitle]. None of them worked.

Someone pointed me to some CheckFilter code but whan I tried that it didn't work either. I'm pretty sure I had it all setup wrong though.

Doug,
Your code above looks ok, but try placing brackets around "[Created_Submitted_Update]" and see what happens. If that does not work, try this:

Instead of doing the check of the form entry against the table in the above code, do it in the Update query itself.......that is, go to your query [Created_Submitted_Update] and drag TxtTitle again from table area to the query grid and in the criteria row of TxtTitle put....... = [Forms]![Poetry]![TxtTitle]

Then change your code to look like this:

Private Sub CMD_AddRecord2_Click()
On Error GoTo Err_CMD_Add_Record2_Click
 
 
Dim stUpdate As String
 
stUpdate = "Created_Submitted_Update"
DoCmd.OpenQuery stUpdate, acNormal, asEdit
 
 
Exit_CMD_Add_Record2_Click:
Exit Sub
 
Err_CMD_Add_Record2_Click:
MsgBox Err.Description
Resume Exit_CMD_Add_Record2_Click
 
End Sub