Adding/Editing MS Access records in VB

[Med]

I have a Access database that I created for work to keep track of our inventory. All the forms access and edit data from one table. I need to create a way to keep track of when data in that table is changed, and how it is changed. It's been about 10 years since I took an Access class, so I'm rusty to say the least.

I created a new table with fields for the date, time, item name, number used, and number received. My idea was to add a new record to that table every time the inventory table was modified through the forms. I just can't figure out how to access the activity table through the VBasic code I've scratched together.

Any ideas would be appreciated.

[faulkner132]

Are you using VB to interface with your Access DB or are you using VB forms built into Access? It sounds to me like the entire thing should be done in Access (VB code and forms), this way you can easily bind data fields and add record navigors to forms. Access has a multitude of wizards which will do this for you.

[Med]

I'm using the VB in Access, to code the events when you click on the save button, etc.

For instance, the Use Item form draws it's data from the Inventory table. They select an item from the combo box, enter in how many they've used, and click save. The code then adjusts the values to reflect the current inventory levels, and saves the record.

Select Case UsedFrom.Value Case Is = 1 QST.Value = QST.Value - NumUsed.Value Case Is = 2 QBB.Value = QBB.Value - NumUsed.Value Case Else MsgBox ("unknown error: save_click usedfrom.value") End Select TotalQOH.Value = QBB.Value + QST.Value

What I want is to take that TotalQOH, and add that information, plus the date, time, and the Item Name to another table, so that I can run a report later on to see when and how the database was changed. I can't seem to find a wizard to do what I need it to do.

[faulkner132]

In your save event button, simply add the Access code to run an update SQL statement:

DoCmd.RunSQL "INSERT INTO other_table(field1, field2, field3) VALUES(field1value, field2value, field3value);"

[Med]

When I did take my access class, I think there was about a half a paragraph on SQL code, so I'm probably butchering this up pretty badly. I keep getting syntax errors.

I've tried all of these...

Dim SQL As String Dim Today Dim Now Dim Item Dim Use Today = Date Now = Time Item = ItemName.Value Use = NumUsed.Value 'SQL = "INSERT INTO Activity(Date, Time, ItemName, Used) VALUES(" & Today & ", " & Now & ", " & Item & ", " & Use & ");" 'SQL = "INSERT INTO Activity(Date, Time, ItemName, Used) VALUES(Today, Now, Item, Use);" 'DoCmd.RunSQL SQL DoCmd.RunSQL "INSERT INTO Activity(Date, Time, ItemName, Used) VALUES(Today, Now, Item, Use);" 'DoCmd.RunSQL "INSERT INTO Activity(Date, Time, ItemName, Used) VALUES(" & Today & ", " & Now & ", " & Item & ", " & Use & ");"

and none are working. I searched through the help file, which is what gave me the idea for the SQL string variable, but I can't find any in depth explanation of the syntax required for the INSERT INTO command.

[faulkner132]

Originally Posted by Med When I did take my access class, I think there was about a half a paragraph on SQL code, so I'm probably butchering this up pretty badly. I keep getting syntax errors. I've tried all of these... and none are working. I searched through the help file, which is what gave me the idea for the SQL string variable, but I can't find any in depth explanation of the syntax required for the INSERT INTO command.

Strings have to be withing single quotesand dates within pound signs.

Here is an example:
"INSERT INTO table (string_value, date_value, string_value_with_appostrophe) VALUES ('new value', #1/1/2005#, 'What''s up?');

[Med]

Thank you Faulkner! I had to mess around with it, to get it to work, but it's working.

It's messy, but this is the only way I could get it to work.

Dim SQL As String Dim FullDate As String Dim Item Dim Use FullDate = Date & " " & Time Item = ItemName Use = NumUsed SQL = "INSERT INTO Activity VALUES('" & FullDate & "', '" & Item & "', " & Use & ", 0)" DoCmd.RunSQL SQL

I couldn't get it to work with telling it which fields to put the information in, so I just redesigned the table to have 4 fields, with the date and time being the primary key.