After Update Problem??

[gmatriix]

Hello All,

I have this code below that works just fine however, I don't believe I have it in the correct event. When I have it in this event it does not fire when record has been changed. I have to click to the new record, click back, and edit the record. Then I get a message that the record has been changed.

How do I get this to fire whenever the record has been saved? I thought about a message box that ask if they want to save this record or not. If they click yes then it would fire.

Any Ideas??

Private Sub Form_AfterUpdate()
Dim dd As Date
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim pID As Long
    Dim sHostName As String
    Dim sUserName As String
    Dim cmd As String
    
    pID = [Product ID].Value
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    ' Get Host Name / Get Computer Name
    sHostName = Environ("computername")
    ' Get Current User Name
    sUserName = Environ("username")        'Username is obtained to produce Time and UserStamp
    cmd = "UPDATE Product SET DateStamp=#" & dd & "#, UserStamp='" & sUserName & "' WHERE [product ID]=" & pID & ""
    dbs.Execute cmd, dbSeeChanges
End Sub

 

[pr2-eugin]

Hello gMatrix, I hope in the past I might have asked you to put this piece of code inside the AfterUpdate Property.. Yes it work absolutely fine, until you hit the BRICK WALL.. But I have been encouraged not to do this. I did understand the con's of this practice..

So I am sorry for misguiding you.. This piece of code should be placed inside the BeforeUpdate property.. So that does not require to use the UPDATE aommand at all.. since it is directly linked with the particular record.

Even the code even is less confusing..

Private Sub Form_BeforeUpdate(cancel As Integer)
    Dim dd As Date
    Dim sUserName As String
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    ' Get Current User Name
    sUserName = Environ("username")        'Username is obtained to produce Time and UserStamp
    Me.DateStamp = dd
    Me.UserStamp = sUserName
End Sub

Once again I am so sorry..

 

[pr2-eugin]

Okay can you check if you have done either of the two:
* Have you used UPDATE somewhere else in the Form Methods?
* Have you copied the entier code starting with Private till End Sub?

For time being delete the Form_AfterUpdate method you currently have inplace and place the above code from start till end.. and see what happens..

 

[gmatriix]

Thanks again pr2-eugin,

that works just fine...I don't need the command as you stated. this is much simpler and works great!

Thanks again!

 

[pr2-eugin]

You are most welcome.. Hope this has not caused you much of a hassle..

 

[gmatriix]

No hassle,

BTW,

Do you know a easy way for a browse button. Basically, I need to browse for a file and just the path of the file is returned??

 

[pr2-eugin]

Try this..

    Dim dd As Integer
    Dim selFile, selRoute As String
    Dim fileDump As FileDialog
    
    Set fileDump = Application.FileDialog(msoFileDialogFilePicker)
    dd = fileDump.Show
    If dd = -1 Then
        selRoute = fileDump.SelectedItems(1)
        selFile = StrReverse(selRoute)
        selFile = StrReverse(Mid(selFile, 1, InStr(selFile, "\") - 1))
    Else
        Set fileDump = Nothing
        Exit Sub
    End If

This will allow you to select for a file and selFile will have the File name and selRoute will have the File path. Hope this helps.,

 

[gmatriix]

Thanks,

I get a "user define type -not defined" on filedialog error

Any Ideas?

 

[pr2-eugin]

Check the References under Tools... scroll and find "Microsoft Office 14.0 Library" and check it.. I mean tick it.. That will sort it out..

 

[gmatriix]

Thanks that worked.

Now Im trying to get the results in my field called Blueprint1. So I did this

    If dd = -1 Then
        selRoute = fileDump.SelectedItems(1)
        selFile = StrReverse(selRoute)
        selFile = StrReverse(Mid(selFile, 1, InStr(selFile, "\") - 1))
        selFile = BluePrint1

But Im getting nothing....

 

[pr2-eugin]

That should be the other way around mate.. Always the value on the Right will be assigned to the variable on Left of the Equal sign.. So it is BluePrint1=selFile not the other way around..

 

[gmatriix]

Ooops..thanks for that..

This is the end result

Private Sub Command299_Click()
Dim dd As Integer
    Dim selFile, selRoute As String
    Dim fileDump As FileDialog
    
    
    Set fileDump = Application.FileDialog(msoFileDialogFilePicker)
    dd = fileDump.Show
    If dd = -1 Then
        selRoute = fileDump.SelectedItems(1)
        selFile = StrReverse(selRoute)
        selFile = StrReverse(Mid(selFile, 1, InStr(selFile, "\") + 40))
        BluePrint1 = selFile
        DoCmd.RefreshRecord
    Else
        Set fileDump = Nothing
        
        Exit Sub
    End If
End Sub

This works great! thanks...The field is formatted as a hyperlink..and it shows up that way but when I click on it..it does nothing..

If I type the exact same thing in it does fine or if I put a space in and then delete it ..then it recognizes it as a hyperlink

weird....

 

[pr2-eugin]

Hmmm.. Okay instead of FilePicker use Open.. That will work out..

 

[gmatriix]

which reference should I use in tools...getting the user define error again...

Thanks

 

[pr2-eugin]

Hope you have not done this..

Set fileDump = Application.FileDialog(FileOpen)

It is just,

Set fileDump = Application.FileDialog(msoFileDialogOpen)

You do not have to add any more References.. Microsoft Office 14.0 is all you need for msoFile operations.

 

[gmatriix]

No...I didn't do that....

it still was not launching the hyperlink...

then I added this

selFile = "#" & selFile & "##"

And now it is launching.....see... I am learning alittle bit!

Thanks for all your help....hopefully I will keep learning...


Thanks again!