By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Unable to run Update Query...Please Help

P: 48
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Me![TxtTitle] = (Created_Submitted.Title) Then
  3.     Dim stUpdate As String
  4.  
  5.     stUpdate = "Created_Submitted_Update
  6. DoCmd.OpenQuery stUpdate, acNormal, asEdit
  7. End If
  8.  
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
May 22 '08 #1
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
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"
May 22 '08 #2

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

Expand|Select|Wrap|Line Numbers
  1.       If Me![TxtTitle] = [Created_Submitted.Title] Then
  2.             Dim stUpdate As String
  3.  
  4.             stUpdate = "Created_Submitted_Update"
  5.             DoCmd.OpenQuery stUpdate, acNormal, asEdit
  6.       End If
  7.  
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
May 22 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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
May 22 '08 #4

P: 48
This is the entire active code for that event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CMD_AddRecord2_Click()
  2. On Error GoTo Err_CMD_Add_Record2_Click
  3.  
  4. If [Forms]![Poetry]![TxtTitle] = [Created_Submitted].[Title] Then
  5. Dim stUpdate As String
  6.  
  7. stUpdate = "Created_Submitted_Update"
  8. DoCmd.OpenQuery stUpdate, acNormal, asEdit
  9. End If
  10.  
  11. Exit_CMD_Add_Record2_Click:
  12. Exit Sub
  13.  
  14. Err_CMD_Add_Record2_Click:
  15. MsgBox Err.Description
  16. Resume Exit_CMD_Add_Record2_Click
  17.  
  18. End Sub
  19.  
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.
May 22 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
This is the entire active code for that event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CMD_AddRecord2_Click()
  2. On Error GoTo Err_CMD_Add_Record2_Click
  3.  
  4. If [Forms]![Poetry]![TxtTitle] = [Created_Submitted].[Title] Then
  5. Dim stUpdate As String
  6.  
  7. stUpdate = "Created_Submitted_Update"
  8. DoCmd.OpenQuery stUpdate, acNormal, asEdit
  9. End If
  10.  
  11. Exit_CMD_Add_Record2_Click:
  12. Exit Sub
  13.  
  14. Err_CMD_Add_Record2_Click:
  15. MsgBox Err.Description
  16. Resume Exit_CMD_Add_Record2_Click
  17.  
  18. End Sub
  19.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CMD_AddRecord2_Click()
  2. On Error GoTo Err_CMD_Add_Record2_Click
  3.  
  4.  
  5. Dim stUpdate As String
  6.  
  7. stUpdate = "Created_Submitted_Update"
  8. DoCmd.OpenQuery stUpdate, acNormal, asEdit
  9.  
  10.  
  11. Exit_CMD_Add_Record2_Click:
  12. Exit Sub
  13.  
  14. Err_CMD_Add_Record2_Click:
  15. MsgBox Err.Description
  16. Resume Exit_CMD_Add_Record2_Click
  17.  
  18. End Sub
  19.  
May 22 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.