-2

I have a form that captures call logging. I need to use the form to insert data to/update 2 tables. Basically I want the customerID, date, time, event, notes from the form to insert to a table every time a new call is made. This data comes from text boxes/list boxes on the form. This will always be an insert and this I can do. I also want to update a table with just the 'next call due' data - customerID, event, TimeOfNextCall, DateOfNextCall. If customerID and event exist then I want to update, else insert. I presume I need to check to see if a record exists before I update, else I will need to insert. Please can anyone help me with the vba code to check whether a record exists before I know whether to insert or update. I think I need to select the data and check if it is null then do the relevant process, but not sure of the vba code to do this. Feel free to point me to another post asking the same thing - I did look but couldn't see anything. Cheers in advance. Jules

Pixie007
  • 1
  • Try finding some basic insert/update table examples - there's plenty of those out there - then substitute field and table names to match your situation. Try it out, report back with your code attempts and errors or results. – dbmitch Jul 24 '16 at 19:21
  • Erm- thanks. Not quite what I expected from a help forum, or was it - oh yes - last time I asked a question I was asked to go away and try again. I have to say I'n – Pixie007 Jul 24 '16 at 20:43
  • http://stackoverflow.com/help/on-topic – dbmitch Jul 24 '16 at 20:49
  • "Focus on questions about an actual problem you have faced. Include details about what you have tried and exactly what you are trying to do." – dbmitch Jul 24 '16 at 20:51
  • I'm not really sure what the point is of these forums if the answer is to go away and find out yourself, then come back. I have googled this, and did ages ago before I was ready to use it. Sometimes googling the answer happens quickly, sometimes not. I came on here to ask as I couldn't find a relevant example and don't have the hours to keep googling. I did say in my post 'feel free to point me to another post'. I am not interested in the insert/update part of the code - what I need is the vba code to pull the data to see if a record exists then update or insert accordingly. – Pixie007 Jul 24 '16 at 20:51
  • This is obviously not the forum for me! – Pixie007 Jul 24 '16 at 20:52

2 Answers2

2

You actually don't need VBA code for what you describe; it can be done with an After Insert data macro in the table on which you perform the INSERTs. For example, if that table was named [tblCallLog] and the "upsert" table was named [tblNextCall] then the following macro would INSERT or UPDATE a "next call" for that CustomerID in 30 days:

AfterInsert.png

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

In VBA:

If me.NewRecord then 'insert stuff Else 'update stuff

Rene
  • 1,095
  • 1
  • 8
  • 17