UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run-time Error 3075, Access 2016    
 
   
Slicemahn
post May 9 2019, 09:39 AM
Post#1



Posts: 45
Joined: 31-August 05
From: Toronto, CANADA


Hi UA!

I have a form to input data into a SharePoint table. I keep coming across this 3075 error and I am hopeful that a fresh set of eyes and determine what I am doing wrong.

I have the screen print with the error and the string portion that is causing the error. Also, can someone please confirm for me that the Frame object that has the option boxes (Active, Cancelled, TBC) is written correctly? It appears that when none of the boxes are checked, the value "Active" appears. When a button is selected then the value 1, 2, or 3 is captured.


Here is my code below:

CODE
Private Sub cmdAddRecord_Click()

Dim SQL As String

      
   SQL = "INSERT INTO [CBD Facilitation Calendar] (ID,Title, Location,[Start Time],[End Time],Description,[All Day Event],Recurrence,Workspace,[Free/Busy],[Check Double Booking], CoE,City,Region,"
   SQL = SQL & "Facility,Room,Facilitator,[Facilitators Required],[Team Name],[Core Activity Type],Language,Status,[Non-Core Activity Type],Destination,[Program Name],Duration)"
   SQL = SQL & " Values ( "",'" & Me.cboTitle & "', "", #" & Me.txtStartDate & "#, #" & Me.txtEndTime & "#, '" & Me.txtDescription & "', " & Me.chkAllDayEvent & ", " & Me.Recurrence & ", "", "", "", "
   SQL = SQL & "'" & Me.txtCoE & "', '" & Me.cboLocation & "', '" & Me.txtRegion & "', '" & Me.txtFacility & "', '" & Me.Room & "', '" & Me.cboFacilitator & "', '" & Me.cboFacilitatorsReq & "',"
   SQL = SQL & " '" & Me.cboTeamName & "', '" & Me.cboCoreActivity & "', '" & Me.cboLanguage & "', '" & Me.fmeStatus & "', '" & Me.cboNonCoreActivities & "', '" & Me.txtDestination & "', "
   SQL = SQL & "'" & Me.txtProgramName & "', " & Me.txtDuration & ")"
  
          
   DoCmd.RunSQL SQL
        
  

End Sub


Thank you Everyone for your help in advance!

Slice
This post has been edited by Slicemahn: May 9 2019, 09:40 AM
Attached File(s)
Attached File  VBA_SQL_error2.jpg ( 23.15K )Number of downloads: 1
 

--------------------
Let me ask you a question: What do you think of a person who does the bare minimum? ..Office Space 1999
Go to the top of the page
 
Minty
post May 9 2019, 09:43 AM
Post#2



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


Add a debug.print SQL to see your string value.

But it looks like you are missing the opening ( in the field list.
Go to the top of the page
 
Slicemahn
post May 9 2019, 11:41 AM
Post#3



Posts: 45
Joined: 31-August 05
From: Toronto, CANADA


Hi Minty,

I have done a debug.print and it shows the same information. The opening bracket is captured but in the error message given, in that particular portion of the string there is a problem.


Slice

--------------------
Let me ask you a question: What do you think of a person who does the bare minimum? ..Office Space 1999
Go to the top of the page
 
cheekybuddha
post May 9 2019, 02:36 PM
Post#4


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


CODE
SQL = SQL & " Values ( "",'" &  ...

probably ought to be:
CODE
SQL = SQL & " Values ( '','" & ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 9 2019, 02:38 PM
Post#5


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Why even bother inserting to fields with empty data?

If ID is a numeric you may have to insert NULL for that field rather than an empty string, though if it's an autonumber even that might not work in Access.

--------------------


Regards,

David Marten
Go to the top of the page
 
Minty
post May 10 2019, 03:53 AM
Post#6



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


Good spot David on the "" vs '' , and you are correct, the OP should completely remove the reference to the ID assuming it's an autonumber.
Access will keel over if you try and insert any value to that type of data field.
Go to the top of the page
 
gemmathehusky
post May 10 2019, 06:21 AM
Post#7


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


It won't fail if you specify an autonumber.

you shouldn't normally need to do it, but you can insert a value into an autonumber field. It will most likely fail if the autonumber has already been used.

If the number inserted is a new highest autonumberID, it will re-set the next number seed to be your new number.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
cheekybuddha
post May 10 2019, 09:01 AM
Post#8


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


@Dave, can you insert NULL or an empty string in to an autonumber?

In Access, IIRC, it fails (but I haven't double-checked).

In other RDBMS's you can insert NULL to get the next autonumber.

--------------------


Regards,

David Marten
Go to the top of the page
 
Minty
post May 10 2019, 09:56 AM
Post#9



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


Actually, I'm so used to using SQL Server as the BE, that I've wrongly assumed Access is the same and it's not.

I'm off to stand in the corner with the big D hat on...
Go to the top of the page
 
cheekybuddha
post May 10 2019, 11:05 AM
Post#10


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


The take-away in this specific instance is to remove the ID field from the INSERT statement altogether.

If Slice is really meaning to insert an empty string into the ID field of a new record, then it would be better to set that as the default value for the field in the table.

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post May 10 2019, 01:03 PM
Post#11


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


Hi.

If you don't include the field at all, Access will insert the next autonumber.
You would generally only want to insert an autnoumberID if you were manually repairing a table, or needed to reseed the table because of some corruption.


eg In Access, I often use an autonumber of 0 to signify a default value. In this case, I have to manually insert the 0 record into the table for the RI to work.
Unfortunately, I since found that you can't insert a 0 value autonumber into a SQL Server table, which means you can't upsize a table to SQL Server, and get the same functionality.


The append autonumber process is actually documented - (although it's not so clear - it used to be documented differently, I believe)

https://support.microsoft.com/en-gb/help/81...value-in-access


anyway, given a table with an autonumber ID field

INSERT INTO Table1 ( ID, Description )
SELECT 10 AS Expr1, "Sample" AS Expr2;

pokes autonumber 10 into the ID field, and the next autonumber is now 11.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 05:10 PM