VBA for input box

[Homegrownandy]

I have created a macro after some days. All it does it create an audit record of specific changes I want to track.



I have an example database which uses VBA to do the same thing. On this database there is some code which forces the user to enter their 'reason for change'.

This information is then kept alongside the new record in the audit table.

All I need is an input box that opens either when a text box is updated OR when a new record is added to a table. The text should then be saved accordingly.

Relevant information:

form [JobF] contains the textbox [Scheduled_Start] < this is what can be changed and needs to b tracked
[AuditT] is the table where the new records will be kept.

all help appreciated.

[Homegrownandy]

Private Sub Scheduled_Start_AfterUpdate()
Dim Note As String
    Note = InputBox("Reason for change:", _
             "Rescheduling Tracker")
    
End Sub

This is what I have so far. As an input box it's working. I just cant figure out how to save the input.

[ItsMe]

First, I would say it is a good idea to use Hungarian Notation with your variable names. One reason why is to avoid reserved words. I do not know if Notes is a reserved word, but strNotes is not a reserved word.

What do you want to do with the user input? You can edit an existing record in a table using SQL.

Dim strSQL As String
 Dim strNote As String
     strNote = InputBox("Reason for change:", _
              "Rescheduling Tracker")
 strSQL = "UPDATE TableName SET TableName.FieldName = '" & strNote & "' WHERE TableName.MyFieldID = 5"
CurrentDb.Execute strSQL

[Homegrownandy]

*Googles Hungarian notation* - As with everything I do; I'm just trying out suggestions or what I find online/in books. I had never even heard of Hungarian notation. I do see your point. Thanks for that it does look a lot better.

Just a question about the code;

WHERE TableName.MyFieldID = 5

Why does it =5? how do I change this value to represent the active record.
I'll have a play and see what I can do.

I have made a macro which creates the audit record, I want the user entered note to be added to the last record added to the audit table. Am I asking too much with this?

[Homegrownandy]

attachment.zip

This is what I'm using to reference, although my method is very different to how this works. This has an input box that comes up and then applies the user entered 'note' to the audit.

Currently my macro will enter [new data] [old data] [when]. I just need to add a note for the reason of change.

[ItsMe]

Why does it =5? how do I change this value to represent the active record.

You would replace 5 with a variable. First declare a variable and then use the variable by concatenating to your WHERE clause.

Dim strSQL As String
 Dim strNote As String
dim lngFieldID as long
     
     lngFieldID = 5
     strNote = InputBox("Reason for change:", _
              "Rescheduling Tracker")

 strSQL = "UPDATE TableName SET TableName.FieldName = '" & strNote & "' WHERE TableName.MyFieldID = " & lngFieldID
CurrentDb.Execute strSQL

I have made a macro which creates the audit record, I want the user entered note to be added to the last record added to the audit table. Am I asking too much with this?

If you use an embedded macro or a saved Macro Object to append a record, it is not likely you will know this record's PK value. So, determining what value to replace 5 with will be difficult. Why not replace the UPDATE statement in my previous example with an append query? This is why I will suggest creating a Query Object that performs the task you desire. You can name and save the Query Object or use the SQL statement generated by the query designer in your VBA.

This is what I'm using to reference, although my method is very different to how this works. This has an input box that comes up and then applies the user entered 'note' to the audit.

I avoid Macros when possible. They may seem easier to implement, but the reality is they have limits. When these limits are realized, the real work begins. Also, the use of input boxes seems like a good idea, but they rarely are. Before assigning a value to a field in a table, you need to do data validation. It is common practice to use Controls within forms to collect user input. I prefer to do data validation on controls.

I downloaded your example. I do not see any Macros. In Access, a Macro is an Object. When you record a macro in Excel, it is saved as VBA. When you record a macro in Access, it is saved as an Object and it has a Name. What you have in your example is not a Named and Saved Macro Object. You have some functions that create SQL statements and execute the statements. These functions are VBA procedures.

I did not look very carefully at the custom functions, but it seems you would have to make edits to the function in order to accept arguments for multiple columns. I would recommend using the approach I suggested earlier. Create an action query using the Query Designer.

[Homegrownandy]

Im actually understanding code/variables now. Not totally, but a bit.... Its a start. Thanks mate.

[ItsMe]

I don't know if I have mentioned them to you before, but I created a few video tutorials that are available for free download. There are a several on VBA. I would recommend watching the VBA videos in order. The one's regarding the Message Box are a good introduction to Functions.
https://www.accessforums.net/tutoria...tml#post277671