CurrentDb.Execute looking in documents instead of currentdb

[Vita]

Hi! Thank you for taking the time to help.

My code is below and it runs fine up to the CurrentDb.Execute.
The goal of this code is that its being added to a save function so that the user will be prompted to type their initials which will then get saved with the record (it goes to a history file so we can track changed).


The database is split into frontends so the "Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)" is to copy the name of the frontend incase they hit okay or cancel by accident.

I am trying to have it update the field "Initials" inside of the table "HeatHistory" on the record the form("FrmHeat") is editing.
The only problem I seem to be having is that it is trying to look in the documents folder for "HeatHistory.mdb" which doesn't exist because HeatHistory is a linked table from the backend.


Private Sub SaveHeat(lId As Long)
On Error GoTo HANDLE_ERROR

If (lId <= 0) Then Exit Sub

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Heat")

With rs
.FindFirst "Id = " & CLng(lId)
If (.NoMatch) Then Exit Sub

.Edit
UnloadControls rs
.Update

AddToHistory rs

.CLOSE
End With



Dim InputPrompt As String
InputPrompt = InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))
Dim Result As String
'


' If Len(InputPrompt) > 0 Then _
' Right(Application.CurrentDb.Name, 12)
' DoCmd.RunSQL "UPDATE HeatHistory.Initials " _
' & "(Initials) VALUES" _
' & "('" & InputPrompt & "');"






Dim InpRs As DAO.Recordset
Set InpRs = CurrentDb.OpenRecordset("HeatHistory")

With InpRs
CurrentDb.Execute "UPDATE HeatHistory " _
& "SET Initials = InputPrompt"
End With





ShowHeatSaved txtHeatName
bIsDirty = False

CLEAN_UP:


GoTo EXIT_PROCEDURE


CLEAN_UP_ON_ERROR:
GoTo EXIT_PROCEDURE


EXIT_PROCEDURE:
Exit Sub


HANDLE_ERROR:
MsgBox "Save Heat Error: (" & Err.Number & ") " & Err.Description, vbCritical
GoTo CLEAN_UP_ON_ERROR


End Sub

[CJ_London]

for the future, please use code tags to preserve indentation. Without it,code is difficult to follow.

With regards your issue, looks like

CurrentDb.Execute "UPDATE HeatHistory " _
& "SET Initials = InputPrompt"

should be

CurrentDb.Execute "UPDATE HeatHistory " _
& "SET Initials = '" & InputPrompt & "'"

much as you have in the commented out code just above

tip: small change but when splitting your string - put the space at the beginning of the following line rather than end of the previous line - easier to see if you've missed one

CurrentDb.Execute "UPDATE HeatHistory" _
& " SET Initials = '" & InputPrompt & "'"

[Micron]

One issue ought to be improper concatenation (or more accurately, lack of it). Everything in "quotes" is taken as a literal, so you're trying to set Initials to the word "InputPrompt".
Another issue is that you a)cannot update a recordset with sql; b)are mixing Update method of a recordset with running an action query/sql. They are separate things.
You probably will continue on this path, fixing such issues, but I would simplify the whole method. You can easily get details of the user without their input; one way could be Environ variables.

Please post code, properly indented, within code tags (use # button on posting toolbar).

[Vita]

for the future, please use code tags to preserve indentation. Without it,code is difficult to follow.

With regards your issue, looks like

CurrentDb.Execute "UPDATE HeatHistory " _
& "SET Initials = InputPrompt"

should be

CurrentDb.Execute "UPDATE HeatHistory " _
& "SET Initials = '" & InputPrompt & "'"

much as you have in the commented out code just above

tip: small change but when splitting your string - put the space at the beginning of the following line rather than end of the previous line - easier to see if you've missed one

CurrentDb.Execute "UPDATE HeatHistory" _
& " SET Initials = '" & InputPrompt & "'"

This worked! It doesn't give me that error but it doesn't seem to write the Application.CurrentDb.Name to the Initials field. Is my order of operations wrong?

Thank you and I will try to next time.

[Welshgasman]

This worked! It doesn't give me that error but it doesn't seem to write the Application.CurrentDb.Name to the Initials field. Is my order of operations wrong?

Thank you and I will try to next time.

So show your amended code (within code tags #)

I am trying to have it update the field "Initials" inside of the table "HeatHistory" on the record the form("FrmHeat") is editing.

So why not just update the field for the record you are on with Me.Initials = InputPrompt

[Vita]

So show your amended code (within code tags #)


So why not just update the field for the record you are on with Me.Initials = InputPrompt

Well because [FrmHeat] modifies the data in [Heat] table. One of the other subs saves the changes as a new record inside of a table called [HeatHistory] so that we can see changes with a continous form that filters based on the ID.
Only the changes need an ID because it logs the entry and all changes to a Heat record.

Private Sub SaveHeat(lId As Long)
    On Error GoTo HANDLE_ERROR
        
    If (lId <= 0) Then Exit Sub
        
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Heat")
    
    With rs
        .FindFirst "Id = " & CLng(lId)
        If (.NoMatch) Then Exit Sub
    
        .Edit
        UnloadControls rs
        .Update
    
        AddToHistory rs
    
        .CLOSE
    End With
    


    Dim InputPrompt As String
    InputPrompt = InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))


    Dim InpRs As DAO.Recordset
    Set InpRs = CurrentDb.OpenRecordset("HeatHistory")
    With InpRs
                CurrentDb.Execute "UPDATE HeatHistory" _
                        & " SET Initials = '" & InputPrompt & "'"
    End With
    




    ShowHeatSaved txtHeatName
    bIsDirty = False
    
CLEAN_UP:


    GoTo EXIT_PROCEDURE


CLEAN_UP_ON_ERROR:
    GoTo EXIT_PROCEDURE


EXIT_PROCEDURE:
    Exit Sub


HANDLE_ERROR:
    MsgBox "Save Heat Error: (" & Err.Number & ") " & Err.Description, vbCritical
    GoTo CLEAN_UP_ON_ERROR


End Sub

[Vita]

Well because [FrmHeat] modifies the data in [Heat] table. One of the other subs saves the changes as a new record inside of a table called [HeatHistory] so that we can see changes with a continous form that filters based on the ID.
Only the changes need an ID because it logs the entry and all changes to a Heat record.

Private Sub SaveHeat(lId As Long)
    On Error GoTo HANDLE_ERROR
        
    If (lId <= 0) Then Exit Sub
        
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Heat")
    
    With rs
        .FindFirst "Id = " & CLng(lId)
        If (.NoMatch) Then Exit Sub
    
        .Edit
        UnloadControls rs
        .Update
    
        AddToHistory rs
    
        .CLOSE
    End With
    


    Dim InputPrompt As String
    InputPrompt = InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))


    Dim InpRs As DAO.Recordset
    Set InpRs = CurrentDb.OpenRecordset("HeatHistory")
    With InpRs
                CurrentDb.Execute "UPDATE HeatHistory" _
                        & " SET Initials = '" & InputPrompt & "'"
    End With
    




    ShowHeatSaved txtHeatName
    bIsDirty = False
    
CLEAN_UP:


    GoTo EXIT_PROCEDURE


CLEAN_UP_ON_ERROR:
    GoTo EXIT_PROCEDURE


EXIT_PROCEDURE:
    Exit Sub


HANDLE_ERROR:
    MsgBox "Save Heat Error: (" & Err.Number & ") " & Err.Description, vbCritical
    GoTo CLEAN_UP_ON_ERROR


End Sub

So I figured I would give that recommendation a go anyways but I forgot there is no Me.Initials due to it being a separate table. Should I make a separate post for this issue?

[Welshgasman]

Have you actually tested your code, line by line?

Sub TestInput()
Dim InputPrompt As String
InputPrompt = InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))


Debug.Print InputPrompt
End Sub

One gets something in InputPrompt if you enter something, else nothing if nothing is entered.?

[Vita]

Have you actually tested your code, line by line?

Sub TestInput()
Dim InputPrompt As String
InputPrompt = InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))


Debug.Print InputPrompt
End Sub

One gets something in InputPrompt if you enter something, else nothing if nothing is entered.?

Is that why? I was trying to have a blank box in the inputbox so the users wont get confused and if they type something it will use that but if its empty or they hit cancel I want it to take the name of the front end they are using. (which is the purpose of [Right(Application.CurrentDb.Name, 12)]

[Welshgasman]

So try entering something to start with. As if that does not work, setting it to the last12 characters by other means is not going to.
TBH I have no idea whatsoever what you are doing with InpRs?

I do not believe it can have any bearing on the sql code.

You need to test your code bit by bit, not just create a mass of code and expect it all to work.
See if it works with something entered, then we can work on a default value if nothing entered.

[Vita]

So try entering something to start with. As if that does not work, setting it to the last12 characters by other means is not going to.
TBH I have no idea whatsoever what you are doing with InpRs?

I do not believe it can have any bearing on the sql code.

Allow me to explain a bit more.
You need to test your code bit by bit, not just create a mass of code and expect it all to work.
See if it works with something entered, then we can work on a default value if nothing entered.

Thats the whole problem is that it does not write there.
I have 2 tables and 2 forms. Heat.table, HeatHistory.table, FrmHeat.form, FrmHeatHistory.form

The way things are supposed to work is the users can open FrmHeat and they will be able to enter some information and have it save to the Heat.table. Which works fine.
We wanted a way to track changes though so we made HeatHistory.table and FrmHeatHistory and the plan was that anytime a change was made it would create a new record with that change. That works fine.
My boss however wants a way to track who made the change so we can see what happened if need be.

I could have added a required field for initials but we have existing data and only really need initials for changes.
So I opted to instead add it to the saving feature so that everytime they save a inputbox will come up and ask for initials.

The saving feature already has a Dim rs so I took the Inp from input and made InpRs. Im not familiar with record source. someone else made the saving feature and I tried to reuse it to have it write the initials to HeatHistory.table only.
When I made the inputbox it shows cancel and if someone hit that it would have a null value so I used Nz to check if itself is null and if it is then it should replace it with the frontend name as most of the users have their initials in their frontends but they only have it on the end of the name. Ex: Heatmaster9000_FD.mdb or Heatmaster9000_RD4_FD.mdb. So I just wanted the last 12 so it would say who hit cancel since it still saves if cancelled.

So I tried to use Dao.Recordset to try and have it execute an update query on HeatHistory.table.

I hope that explanation helps and I apologize for being confusing.

[Micron]

Not sure why I'm still following this given what seems to be mucho discussion about the default value of an input box and such and having no comment on my prior post.
I agree, with OP - a value should be returned by the input box if the user does not enter anything into it. At least that's what the M$ documentation says should happen. However,at this point I'll say that approach makes no sense to me since it appears from earlier posts that if there is no user input, the default value is the same thing every time - 12 characters of the app path. How does that identify any one individual??
Unless I'm missing something I see no sense in the approach. Time to get the credentials of the user making the change/addition - either TempVars or fosUserName function?

[Welshgasman]

InputPrompt is not Null though, just a ZLS ?
Plus the default is something to be displayed instead of an empty control.?

https://support.microsoft.com/en-us/...1-4786575314d9

Or am I missing something here?

Re using Me.Initials. If you can link the table with that contains that field, then you can bring that field into the form source. It does not need to be a control on the form, and you can update it in the Form BeforeUpdate event.

[Micron]

What am I missing? Looks like the Nz function should be returning the default. From the link you provided:

InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))

InputBox (prompt [,title ] [,default] [,xpos] [,ypos] [,helpfile] [,context] )

[Welshgasman]

What am I missing? Looks like the Nz function should be returning the default. From the link you provided:

InputBox("Please Initial", "Initials", Nz(InputPrompt, Right(Application.CurrentDb.Name, 12)))

InputBox (prompt [,title ] [,default] [,xpos] [,ypos] [,helpfile] [,context] )

Yes, and as the Docs mentioned, that places a default value in the input box. The InputPrompt is a ZLS not Null though, so NZ will not work, and so no default is displayed.
I tested the code myself, and that is what happend, hence post #8.

Now if the O/P was to change InputPrompt to variant, it works, a default is displayed, but then if the user presses Cancel, a ZLS string is still returned