Small query function don't work on runtime version

[nd0911]

Hi davegri,

First thanks for the great tip's but:

1. I tried to convert my ACCDB file to ACCDR (I changed the suffix) and it works just fine, but in the real Runtime it false.



2. I tried the code you attached but I'm not sure I understand it, why the following line of code is not ״Chained text״ ?

arg = format(IdField,"000000000")

3. I did same update to your code and it works just fine, but again, it works only on the regular Access and not in the run time version

Public Sub SetId_9(tblName As String, IdField As String)
Dim SQL As String


SQL = "UPDATE " & tblName & " SET " & IdField & " = " & "Format([" & IdField & "]," & """" & "000000000" & """)"


CurrentDb.Execute SQL
End Sub

[davegri]

Well, we tried 2 different approaches. If it still doesn't work in the runtime, I'm at a loss. I don't have runtime installed and am not inclined to do so.

[CJ_London]

you are too focussed on your double quotes and what the sql string looks like

SQL = "UPDATE [" & tblName & "] SET [" & IdField & "] = '" & Format(IdField,"000000000") & "'"

[andy49]

  SQL = "UPDATE " & tblName & " SET IdField = " & arg

    Debug.Print SQL

    Currentdb.Execute SQL

I read this morning that Runtime wont let you alter the format of tables.

Isn't what you're doing an example of this?

Perhaps it would work if you did this as part of a query?

[CJ_London]

I read this morning that Runtime wont let you alter the format of tables.

not true. Runtime does not have the ability to edit tables using the usual gui but you can still do it in vba code to execute SQL create table, alter table etc. When you click the 'update table' button, Access creates and runs the required sql in the same way.

[davegri]

Reading through this thread again, I notice that the OP never specified the error thrown, so let's find out with an error handler. Maybe it's not this procedure at all.
Actually, if using the runtime is anticipated, EVERY procedure should have an error handler. Without one, the runtime will terminate Access for any error, and leave the user bewildered.

Public Sub SetId_9(tblName As String, IdField As String)
    On Error GoTo SetId_9_Error
    Dim SQL As String
    Dim arg As String
    arg = Format(IdField, "000000000")
    Debug.Print arg
    SQL = "UPDATE " & tblName & " SET IdField = " & arg
    Debug.Print SQL
SetId_9_EXIT:
    Exit Sub
SetId_9_Error:
    Select Case Err
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume SetId_9_EXIT
End Sub

[CJ_London]

I notice that the OP never specified the error thrown

I've mentioned it a number of times but to no avail