Newbie help please... (1 Viewer)

[jd_boss_hogg]

Hi All - hoping someone can help me with the structure of some coding. I do dbase programming every 6 months, for a few minutes a time, and have never really learned how to structure things correctly.

I have a button on a form which runs a macro. That macro does various tasks. I have some code on an event procedure (lost focus) on another form, and i'd like to use that code in the macro from the click of the button . I thought i could just use "run code" and paste the stuff into a module/function. However, looks like functions work completly differently to event procedures, and that is what i don;t understand.

This is what i want to do, preferably from a line in the macro.

Private Sub print_advice_LostFocus()

Dim strMsg
Dim Strusername

Strusername = Environ("username")


strMsg = Strusername & " , INVOICED" & Chr(13) & Chr(10)
Set db = CurrentDb
[job_history] = strMsg & [job_history]
End Sub

At the moment, the only way i can get it to do the above is to have both an OnClick (macro) AND lost focus (event) on the same button, but that's just messy.

Sorry if ive used the wrong terminology or stuff...

 

[Chrism2]

It sounds like you want to use the same code on different buttons throughout your project. (Sorry if I've misunderstood).

Your Sub is currently Private; and so only available in the form it belongs in.

Try pasting it into a Module and calling it a Public Sub

Public Sub PrintAdvice()

Dim strMsg
Dim Strusername

Strusername = Environ("username")


strMsg = Strusername & " , INVOICED" & Chr(13) & Chr(10)
Set db = CurrentDb
[job_history] = strMsg & [job_history]
End Sub

then call it with

Call PrintAdvice

from anywhere in the project.

Be careful about referencing any controls or values that might differ between forms.

Hope I'm on the right track there.

 

[jd_boss_hogg]

Actually, all i'm lookinng to do is run the code from within a macro. I just can;t work out how to do it, because it will only let me run functions and i don;'t think what i have is a function.

I tried this in a module, but it doesn;t like the last couple of lines...

Function update_history()
Call PrintAdvice
End Function

Public Sub PrintAdvice()
Dim strMsg
Dim Strusername
Strusername = Environ("username")
strMsg = Strusername & " , INVOICED" & Chr(13) & Chr(10)
[job_history] = strMsg & [job_history]
End Sub

 

[stopher]

Also your code refers to [job_history] so presumably this is a field on your form. If you plan to use the code elsewhere then [job_history] must be able to reference something e.g. a field by the same name on your other form.

Also I see you have the following code. But it doesn't appear to serve any purpose.

Set db = CurrentDb

hth
Chris

 

[ghudson]

Actually, all i'm lookinng to do is run the code from within a macro. I just can;t work out how to do it, because it will only let me run functions and i don;'t think what i have is a function.

I tried this in a module, but it doesn;t like the last couple of lines...

You can simplify it to just one line of code and run it or a variation of it when ever you need it.

Function update_history()

    [job_history] = Environ("UserName") & " , INVOICED" & Chr(13) & Chr(10)  

End Function

 

[jd_boss_hogg]

Function update_history()

    [job_history] = Environ("UserName") & " , INVOICED" & Chr(13) & Chr(10)  

End Function

that doesn;t work, because i get "compile error - expected variable or proceedure, not module". It appears to not like field names in a function module ?

 

[jd_boss_hogg]

maybe i've made this sound more complicated than it is.... all i actually want to do, is by using a line within a macro, update a field with the date/time/ environ(username) and a quick one line of text. I can't do it by SETVALUE, because it doesn;t like "environ' in the expression, and i can;t do it by RUNCODE because i can;t seem to use field names in a function. I CAN easily get it to work in a sub , but don;t know how to run a sub from a macro ?? Confused, i am !

 

[DCrake]

You ned to run som sort of table update, such as

DoCmd.SetWarnings False
DoCmd.RunSQL "Update YourTable Set   [job_history] = " & Environ("UserName") & " , INVOICED" & vbNewLine
DoCmd.SetWarnings True

 

[jd_boss_hogg]

Dave, from one Lancashire lad to another - that meant nothing to me. Flew straight over the old noggin.

Told you i was a newbie!

I started off with a SETVALUE in a macro, which did this...
item: [Forms]![Job Entry]![job_history]
expression: Environ("username") & " , ORDER ENTERED "

But access doesn;t like the Environ stuff in an expression. I don;t know what to do from there. I have used it on a different form succesfully, in some vba assigned to an 'after update' event' , but in this case i want to run it in my macro on the click of a button.

 

[boblarson]

Environ I believe does have a problem from within a macro. So you need a wrapper function for it. What you do is this -

1. Into a STANDARD MODULE (not form, report, or class module) you place this:

Public Function GetUser() As String
   GetUser = VBA.Environ("username")
End Function

2. And then in your macro you can reference:

expression: GetUser() & " , ORDER ENTERED "

 

[ghudson]

Your original request above displays VBA coding, not a macro. I use Environ in SQL's and VBA all the time. Here is a short sample...

INSERT INTO tblUserLog ( NetworkID, [DateTime] )
SELECT DISTINCT UCase(Environ('UserName')) AS NetworkID, Now() AS [DateTime]
FROM tblUserLog;

or

DoCmd.RunSQL ("INSERT INTO tblUserLog ( NetworkID, [DateTime] ) SELECT DISTINCT UCase(Environ('UserName')) AS NetworkID, Now() AS [DateTime] FROM tblUserLog;")