INSERT INTO - errors, but allows input into table

Question

For reasons I cannot see I get the following error message:

Compile error: Method or data member not found

when I use the following:

Private Sub cmd_Add_Click()

Dim strSQL As String

strSQL = " INSERT INTO BERTHAGE " _
& "(BOAT, LOCATION, BERTH_WEEK, BERTH_YEAR, BERTHED) VALUES " _
    & Me.Add_Boat & "','" _
    & Me.LOCATION & "','" _
    & Me.txt_week & "','" _
    & Me.txt_year & "','" _
    & Me.In_Port & "');"

cmd_Clear_Click

End Sub

Once I click OK and use the refresh button the entry is put into the database, but each time I do an entry I have to go to the same process.

I would like to figure out what method or data is missing?

I should add that there is an outnumber primary key field on this table (Berth_ID), and each time I use the cmd_Add button a new ID number is created for the new record. This includes creating a new ID number for the new record that triggers the error.

Here is all the VBA associated with this form

Private Sub Form_Load()
DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

Private Sub LOCATION_Change()
    Me.txt_Cur_Flo = Me.LOCATION.Column(1)
    Me.txt_Cur_Doc = Me.LOCATION.Column(2)
    Me.txt_Cur_Ori = Me.LOCATION.Column(3)
End Sub

Private Sub cmd_Add_Click()

Dim strSQL As String

strSQL = " INSERT INTO BERTHAGE " _
& "(BOAT, LOCATION, BERTH_WEEK, BERTH_YEAR, BERTHED) VALUES " _
    & Me.Add_Boat & "','" _
    & Me.LOCATION & "','" _
    & Me.txt_week & "','" _
    & Me.txt_year & "','" _
    & Me.In_Port & "');"

cmd_Clear_Click

End Sub

Private Sub cmd_Clear_Click()
Me.Add_Boat = ""
Me.LOCATION = ""
Me.txt_Cur_Flo = ""
Me.txt_Cur_Doc = ""
Me.txt_Cur_Ori = ""  
Me.Add_Boat.SetFocus
End Sub

Private Sub cmd_Close_Click()
DoCmd.Close
End Sub

Are you getting a compile error, or a runtime error? If you compile the project in the VB editor what do you see? — Tim Williams
– Tim Williams, Commented May 26, 2020 at 16:49
What does Access highlight when it gives you the compile error? — HansUp
– HansUp, Commented May 26, 2020 at 16:57
I will say no as LOCATION appears to 'know' the numbered spot I am trying to use, eg, I choose location 6 on the form and when I run cmd_Add_Click I get the following in the immediate window Debug.Print Me.LOCATION 6 — DHPA
– DHPA, Commented May 26, 2020 at 17:38

Parfait · Accepted Answer · 2020-05-27 19:46:27Z

2

Consider the best practice of parameterization and not string concatenation of SQL mixed with VBA variables. Due to missing quotes, the compiler attempts to reference a column name and not its literal value. Instead, consider parameterization with defined types which is supported with Access SQL using QueryDefs. Notice below, SQL and VBA are complete separate.

SQL (save as stored query)

PARAMETERS prmBoat TEXT, prmLoc INT, prmBerthed INT;
INSERT INTO BERTHAGE (BOAT, LOCATION, BERTHED)
VALUES(prmBoat, prmLoc, prmBerthed)

VBA

Dim db As Database
Dim qdef As QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdef = db.QueryDefs("mySavedParamQuery")

' BIND PARAM VALUES
qdef!prmBoat = Me.Add_Boat
qdef!prmLoc = Me.LOCATION
qdef!prmBerthed = Me.In_Port

' EXECUTE ACTION QUERY
qdef.Execute

Set qdef = Nothing
Set db = Nothing

Even better, save your query with form controls intact and simply call OpenQuery:

SQL (save as stored query)

INSERT INTO BERTHAGE(BOAT, LOCATION, BERTHED)
VALUES(Forms!myForm!Add_Boat, Forms!myForm!LOCATION, Forms!myForm!In_Port)

VBA

Private Sub cmd_Add_Click()
   Dim strSQL As String

   DoCmd.SetWarnings False                   ' TURN OFF APPEND PROMPTS
   DoCmd.OpenQuery "mySavedActionQuery"
   DoCmd.SetWarnings True                    ' RESET WARNINGS

   Call cmd_Clear_Click

End Sub

edited May 27, 2020 at 19:46

answered May 26, 2020 at 17:20

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DHPA Over a year ago

I will consider it, but I'm just not there yet.

June7 · Accepted Answer · 2020-05-26 16:55:02Z

1

Missing opening parenthesis after VALUES. Also missing apostrophe in front of Me.Add_Boat. These special characters must always be in pairs, an even number by counting.

If Berth_Week and Berth_Year are number fields (and should be), don't use apostrophe delimiters.

If In_Port is a Yes/No field, don't use apostrophe delimiters.

answered May 26, 2020 at 16:55

June7

21.7k8 gold badges28 silver badges36 bronze badges

7 Comments

DHPA Over a year ago

I believe this is what you are suggesting Private Sub cmd_Add_Click() Dim strSQL As String strSQL = " INSERT INTO BERTHAGE " & "(BOAT, LOCATION, BERTH_WEEK, BERTH_YEAR, BERTHED) VALUES (" ' & Me.Add_Boat & "','" _ & Me.LOCATION & "'," _ & Me.txt_week & "," _ & Me.txt_year & "," _ & Me.In_Port & ");" cmd_Clear_Click End Sub The issue I find is when I use the ' it greens out the rest of the lines behind it?

June7 Over a year ago

Put apostrophe inside quote marks same as others, so VALUES('" &

DHPA Over a year ago

Still same issue - the line Private Sub cmd_Add_Click() highlights yellow and then .LOCATION in the line & Me.LOCATION & "'," _ highlights blue background with white letters, after I hit the stop button and refresh the entry goes into the table correctly.

June7 Over a year ago

Access is not recognizing name LOCATION. Me.LOCATION is a listbox?

DHPA Over a year ago

it is a combo box

|

DHPA · Accepted Answer · 2020-05-27 18:27:37Z

0

The issue appears to be that I was doubling up the inputs into the 'week' and 'year' field. this was happening (I believe) because those text box fields were already accessing the week and year information directly from the default value on the BERTHAGE table. Essentially I went through each input and would run it individually waiting for the error to occur. Once it occurred I took it out of the INSERT INFO statement. With the removal of week and year, everything is working. That was a painful exercise, and still not complete, but I am back to a function form/DB so I'll take the small victories when they occur.

Private Sub cmd_Add_Click()

Dim strSQL As String
CurrentDb.Execute " INSERT INTO BERTHAGE " & "(BOAT, LOCATION, BERTHED) VALUES ('" &     Me.Add_Boat & "'," _
    & Me.New_Loc & "," _
    & Me.In_Port & ");"

cmd_Clear_Click

DoCmd.Requery

End Sub`

answered May 27, 2020 at 18:27

DHPA

558 bronze badges

1 Comment

Parfait Over a year ago

Glad you found your solution. However, I would be remiss not to emphasize again parameterization and avoid messy string concatenation of SQL. This is a programming industry concept for any language beyond VBA that interacts with any database beyond Access using SQL. I know you want the easy answer that works (which arguably is the DoCmd.OpenQuery approach) but you would be thankful in future to heed best practices for readable, maintainable, efficient code. Good luck and happy coding!

Collectives™ on Stack Overflow

INSERT INTO - errors, but allows input into table

3 Answers 3

1 Comment

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related