CODEWITHSUNDEEP
vbasql-server-2008ms-access-2002
user3608243
user3608243

Reputation: 63

Access 2002 VBA - Getting an Append Query error when trying to enter a value into a table

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]

Upvotes: 0

Views: 73

Answers (1)

3-14159265358979323846264
3-14159265358979323846264

Reputation: 3290

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!

Upvotes: 2

Related Questions