0

I have a form in Access 2002, with its fields being fed by a query. It displays record data row by row. There's a check box at the end titled Acknowledged. The goal of this form is for the user to acknowledge having seen the record. On the "OnClick" event of the check box I have code that auto fills the user name field and the current date field. There are four different record/notification types. All of this works to this point. There's one notification type, type number 2.

When trying to acknowledge this type of record, it has to enter the username and date into another table besides the current query. Here is the code: 

If NotType = 2 Then
        strSQL = "INSERT INTO Maintenance (AckBy, AckDate) VALUES('" & AckName & "', '" & AckDate & "');"
        'DoCmd.SetWarnings False
        'CurrentDb.Execute strSQL, dbSeeChanges
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    End If

The reason I have both DoCmd.RunSQL and CurrentDb.Execute there was for testing purposes. When running CurrentDb.Execute I'd get no error, but the table would not be populated with these two values for that record. When I use DoCmd.RunSQL I get a confirmation window asking me if I want to append the row. When I say yes there's an error stating I cannot append records in the append query. I just want to put these values into this table.

Update: Create table SQL in MS SQL 2008, linked table created in Access 2002: CREATE TABLE [dbo].[Maintenance]( [MaintenanceID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [AssetID] [int] NOT NULL, [MaintenanceDate] [datetime] NULL, [MaintenanceDescription] [nvarchar](255) NULL, [MaintenancePerformedBy] [nvarchar](100) NULL, [MaintenanceCost] [float] NULL, [NextMaintenanceDate] [datetime] NULL, [Acknowledged] [bit] NOT NULL, [AckTimeStamp] [timestamp] NULL, [AckBy] [varchar](50) NULL, [AckDate] [datetime2](7) NULL, CONSTRAINT [PK_Maintenance] PRIMARY KEY CLUSTERED ( [MaintenanceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

user3608243
  • 63
  • 6

1 Answers1

2

If AckDate is indeed a date, then in Access, you should enclose your date in ## rather than '' as follows ...

strSQL = "INSERT INTO Maintenance (AckBy, AckDate) VALUES('" & AckName & "', #" & AckDate & "#);"

From the CREATE TABLE SQL, it can be seen that you have two columns that need mandatory values to allow an INSERT ... AssetID and Acknowledged ... you need to include these in your INSERT statement or you will get the error you are already getting!

3-14159265358979323846264
  • 3,250
  • 2
  • 15
  • 38
  • Tried that, but it's still giving me an error. Says: Limited set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations. – user3608243 Jul 21 '15 at 13:30
  • 1
    What happens if you insert this line ... `MsgBox strSQL` ... before this line ... `DoCmd.RunSQL strSQL` ... do you see the SQL you expect? – 3-14159265358979323846264 Jul 21 '15 at 13:34
  • It contains the information I need. The msgbox shows: INSERT INTO Maintenance (AckBy, AckDate) VALUES ('user1', '7/21/2015 11:06:28 AM'); – user3608243 Jul 21 '15 at 13:37
  • 1
    You should have `INSERT INTO Maintenance (AckBy, AckDate) VALUES ('user1', #7/21/2015 11:06:28 AM#);` – 3-14159265358979323846264 Jul 21 '15 at 13:40
  • Correct, didn't save it last time. Did get the message you have there. Still gives me the same error though. – user3608243 Jul 21 '15 at 13:42
  • 1
    Have you tried constructing the SQL manually? The reason I say that is it might be worth trying different date/time formats (eg `dd/mm/yyy`). Also try inserting into only one column at a time. You need to eliminate some potential causes of the error. – 3-14159265358979323846264 Jul 21 '15 at 13:49
  • I've tried each field separately and I get the same error both times. – user3608243 Jul 21 '15 at 14:03
  • I think it's having a problem with the fact that the form is getting it's values from a query. I'm just trying to pass values to a table, but it keeps referencing an append query. – user3608243 Jul 21 '15 at 14:07
  • 1
    Can you add the `CREATE TABLE` SQL for that table to your question please. – 3-14159265358979323846264 Jul 21 '15 at 14:07
  • Edited my original post. – user3608243 Jul 21 '15 at 14:28
  • Good News! I was able to enter the record! Now I just have to be able to select the current record and insert the user and date values there instead of creating a new record in the Maintenance table. – user3608243 Jul 21 '15 at 14:58
  • 1
    Great! You should be able to do that by extending your SQL query to something like `INSERT INTO Maintenance (AssetID, Acknowledged, AckBy, AckDate) VALUES ('YOURASSETID',1,'user1', #7/21/2015 11:06:28 AM#);` ... I'm assuming it is acknowledged, hence the `1` value for `Acknowledged`. – 3-14159265358979323846264 Jul 21 '15 at 15:01
  • Quick question on that. The record already has the asset ID in it. It's just waiting for an acknowledger and an acknowledge date. Could I use a WHERE statement with my INSERT statement where I stipulate that the AssetID be equal to Me.AssetID? – user3608243 Jul 21 '15 at 16:14
  • 1
    http://www.w3schools.com/sql/sql_update.asp ... You need update instead of insert. – 3-14159265358979323846264 Jul 21 '15 at 16:26
  • Got it! strSQL = "UPDATE Maintenance SET AckBy='" & AckName & "', AckDate = #" & AckDate & "# WHERE AssetID = " & Me.AssetId – user3608243 Jul 21 '15 at 16:55