Table update order

[fjbrad]

I write a new record into a table then update it in code then start another process which queries the aforementioned table. However the table has not been updated and only seems to do so after the entire procedure has been completed. How can I force the table to be updated?

[June7]

How do you "write a new record into a table"?
Show your code so we can see what is happening.
If you want to provide db for analysis, follow instructions at bottom of my post.

[fjbrad]

How do you "write a new record into a table"?
Show your code so we can see what is happening.
If you want to provide db for analysis, follow instructions at bottom of my post.

The offending code segment is:
foo = DLookup("[filename]", "Publish Names", "[recipeid] = " & recRecipe![recipeid])
Set recPublish = curDb.OpenRecordset("Publish Names")
If IsNull(foo) Then
recPublish.AddNew
recPublish![recipeid] = recRecipe![recipeid]
Else
recPublish.Edit
End If
recPublish![recipename] = recRecipe![recipename]
recPublish![filename] = strPublishName
On Error GoTo 0
recPublish.Update
recPublish.Close

I'm wondering if I ought to just put it in a separate function and call it from this location. Following this code is some housekeeping and calls to two other procedures to write other HTML pages which currently do not reflect the addition made here.

Set recPreferences = curDb.OpenRecordset("preferences")
recPreferences.Edit
recPreferences!LastPublish = intLastDate
recPreferences.Update
recPreferences.Close
Set curDb = Nothing
If Me!PublishToWeb = "Web" Then
foo = WriteMenu()
foo = WriteRecipeList()
MsgBox strPublishName & vbCrLf & "Ready to upload", vbOKOnly, "Recipe Publish"
Call Shell("C:\Program Files\FileZilla FTP Client\filezilla.exe", 1)
Else
MsgBox strPublishName & vbCrLf & "available for local viewing", vbOKOnly, "Recipe Publish"
End If

[davegri]

Don't see how your code got beyond the first line, not to mention the second.
The record source (table or query) contains a space. It must be "[Publish Names]"

[jojowhite]

opening a recordset on table with space does not need to put the
table name in bracket.

you only put bracket on Query or using Domain function.

[jojowhite]

if the OP is working on the form, then after Closing recPublish,
he needs to Requery the Form's recordset:

..
..
recPublish.Update
recPublish.Close
[Forms]![YourFormName].Recordset.Requery 

'if the code is on the same form, use
'Me.Recordset.Requery

[June7]

Weird fact: brackets are not required for table/query name in domain aggregate function, even if there is space or punctuation/symbol. For field name, yes need brackets. So, OP's code is legit in that regard.

Also, if the OpenRecordset were using SQL statement, would need brackets.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

fjbrad, note for future - please put code between CODE tags to retain indentation and readability. Click # icon on edit menu. Might be able to still edit your post.

Why are you using code to create/edit record? Are you not using BOUND form?

[CJ_London]

I note there is a ‘on error GoTo 0’ line which implies error checking has been turned off in code not shown. OP needs to comment out ‘on error resume next’ or step through the code checking values line by line to ensure they are what is expected until the issue is resolved

[fjbrad]

I note there is a ‘on error GoTo 0’ line which implies error checking has been turned off in code not shown. OP needs to comment out ‘on error resume next’ or step through the code checking values line by line to ensure they are what is expected until the issue is resolved

The presence of error handlers are an indication of sloppy housekeeping. The code was checked long ago but this is a round toit project and the annoyance of having to click the publish routine button twice has only recently become annoying enough to warrant attention

[fjbrad]

This routine doesn't alter any of the 4 tables shown on the form so I don't think this would apply.

[jojowhite]

This routine doesn't alter any of the 4 tables shown on the form so I don't think this would apply.

so the tables in your form are not the one being updated?
then what is the problem if it need to complete the whole process before your table get updated?

[madpiet]

Never mind all the code nonsense. Please explain what you're trying to accomplish in more detail.

How are you "updating the record in code"? Do you do something silly like open a recordset and never issue .Edit and .Update statements? Is there a reason you're using a recordset for this?

You can force the record to be updated using VBA and use .Update method of the recordset. I guess if you're doing one record at a time, it's not terrible. But if you're doing bulk updates, just use queries instead.

[fjbrad]

Never mind all the code nonsense. Please explain what you're trying to accomplish in more detail.

How are you "updating the record in code"? Do you do something silly like open a recordset and never issue .Edit and .Update statements? Is there a reason you're using a recordset for this?

You can force the record to be updated using VBA and use .Update method of the recordset. I guess if you're doing one record at a time, it's not terrible. But if you're doing bulk updates, just use queries instead.

The routine writes a hypertext recipe file from four tables. When that completes it writes recipe name, index number, and publish name into the Publish Names table. It then calls two other routines which write hypertext lookup files but they do not have the current recipe included as it hasn't been written into the Publis Name table. I can just run it twice but that screws up my created/modified date.

[madpiet]

The routine writes a hypertext recipe file from four tables. When that completes it writes recipe name, index number, and publish name into the Publish Names table. It then calls two other routines which write hypertext lookup files but they do not have the current recipe included as it hasn't been written into the Publis Name table. I can just run it twice but that screws up my created/modified date.

"writes a hypertext recipe from 4 tables" - can't you just use q query for that?
Maybe you could land it in a temporary table/tempdb (Except Abscess doesn't have those).

If all but the first writing shouldn't happen, then just check for the existence of that recipe or whatever in the final table before writing it again. (Yikes, I'm starting to sound like Aaron what's-his-face!)

I guess you'd have to use DCOUNT() to search the recipe table for a given recipe (so you use the recipe name or ID in the filter), and if it's zero, then you insert the record. otherwise you don't. Then you won't get multiple inserts for the same recipe on the same day.

[fjbrad]

"writes a hypertext recipe from 4 tables" - can't you just use q query for that?
Maybe you could land it in a temporary table/tempdb (Except Abscess doesn't have those).

If all but the first writing shouldn't happen, then just check for the existence of that recipe or whatever in the final table before writing it again. (Yikes, I'm starting to sound like Aaron what's-his-face!)

I guess you'd have to use DCOUNT() to search the recipe table for a given recipe (so you use the recipe name or ID in the filter), and if it's zero, then you insert the record. otherwise you don't. Then you won't get multiple inserts for the same recipe on the same day.

The process of creating the HTML file begins thusly:
Print #intFileNum, " </head>"
Print #intFileNum, " <body itemscope itemtype=" & Chr(34) & "http://schema.org/Recipe" & Chr(34) & ">"
Print #intFileNum, " <div class=" & Chr(34) & "fullpage" & Chr(34) & ">"
Print #intFileNum, " <p class=" & Chr(34) & "breadcrumb" & Chr(34) & ">YOU ARE HERE: <a href=" & Chr(34) & "index.html" & Chr(34) & " target=" & Chr(34) & "_top" & Chr(34) & ">Home</a> > <a href=" & Chr(34) & "recipe.html" & Chr(34) & " target=" & Chr(34) & "_top" & Chr(34) & ">My Recipes</a> > " & strShowName & " <a href=" & Chr(34) & "" & strFileName & "" & Chr(34) & " target=" & Chr(34) & "_new" & Chr(34) & ">print version</a></p>"
Print #intFileNum, " <div class=" & Chr(34) & "titlebar" & Chr(34) & ">"
Print #intFileNum, " <h2><span itemprop=" & Chr(34) & "Name" & Chr(34) & ">" & strShowName & "</span></h2>"
If Nz(recRecipe![author]) = "" Or Nz(recRecipe![source]) = "" Then
strSource = Nz(recRecipe![author]) & " " & Nz(recRecipe![source])
Else
strSource = Nz(recRecipe![author]) & " @ " & Nz(recRecipe![source])
End If

I'm reasonably sure that a query won't cut it.

At any rate I have resolved the problem by removing the offending code to private function and calling it from the same place in the code followed by calls to the code to write the menus.
Thanks to all for your suggestions.