CODEWITHSUNDEEP
sqlvbams-access
tomovam
tomovam

Reputation: 51

Populating Access table with information from a Form

I have a Form in Access the information from which I want to transfer to a table. I cannot use the Form, because I need to create a chart with the information and as it turned out you can't create a chart in Access with source - Form. I tried to bound the field in the form to the table, but that transfers the value to the first row in the table and I need every new value to go to the next row, so the bound option does not work for me.

I am trying to take the value from each control in the form and transfer it to the table, but I get an error: SYNTAX ERROR in INSERT INTO statement

Sub PopulateTable()

Dim sSQL As String

sSQL = "Insert INTO [Levels_Shipped_POF] (Level,POF_Pluquo,Actual_Shipped,Buffer) VALUES (5,Forms![Main_Input_Form].[POF_LVL5].Value,Forms![Main_Input_Form].[Shipped_5].Value,Forms![Main_Input_Form].[Buffer_5].value)"
DoCmd.RunSQL (sSQL)
DoCmd.GoToRecord , , acNewRec
                                                                                                

End Sub

The name of the Form is - Main_Input_Form Controls name - POF_LVL5, POF_LVL6, POF_LVL7...… Shipped_5, Shipped_6, Shipped_7...… Buffer_5, Buffer_6, Buffer_7

Upvotes: 1

Views: 87

Answers (1)

June7
June7

Reputation: 21379

LEVEL is a reserved in JET and ODBC. Unexpected results can occur when using reserved words as object names. Also, use of spaces and punctuation/special characters causes issues. If these features are in naming convention, enclose name in brackets to force it to be accepted: [Level]. Advice is to not use in naming convention.

However, correct use of bound form will negate need for any code. Move to New Record row before entering new data and/or set form DataEntry property to Yes. That will always open form to New Record row - existing records will not be displayed.

Upvotes: 1

Related Questions