Basic SQL in VBA help

[bbatson]

Hello,

I am trying to write a command to upload data inputted on a form into a table (I am aware that this is counterintuitiv e to the table/form relationship).

Here is my code:

CurrentDb.Execute "INSERT INTO Table1 ( Salary, Employee ) VALUES '" & [Forms]![Form1]![EmpSalary] & "', '" & [Forms]![Form1]![EmployeeName] & "';"

Suppose I had an additional field in the table, "Hire Date" (format: date/time) and a text box on my form, "Date". How would I include that in the code? I am not familiar with SQL or VBA - trying to teach myself and the apostrophes and quotes are throwing me off - any help you can provide is very much appreciated!

[ADezii]

Hello,

I am trying to write a command to upload data inputted on a form into a table (I am aware that this is counterintuitiv e to the table/form relationship).

Here is my code:

CurrentDb.Execute "INSERT INTO Table1 ( Salary, Employee ) VALUES '" & [Forms]![Form1]![EmpSalary] & "', '" & [Forms]![Form1]![EmployeeName] & "';"

Suppose I had an additional field in the table, "Hire Date" (format: date/time) and a text box on my form, "Date". How would I include that in the code? I am not familiar with SQL or VBA - trying to teach myself and the apostrophes and quotes are throwing me off - any help you can provide is very much appreciated!

Try:
[CODE=sql]
CurrentDb.Execu te "INSERT INTO Table1 (Salary, Employee, [Hire Date]) VALUES('" & [Forms]![Form1]![EmpSalary] & "', '" & [Forms]![Form1]![EmployeeName] & "', #" & [Forms]![Form1]![Date] & "#)"[/CODE]

[PianoMan64]

Hey since you're new to VBA and SQL Statements I'm going to give you a small nugget of information that you'll be able to use in just about anything that you want to do in DAO (Data Access Objects).

If you want to open a form that is bound to a table that you want to be able to set in code and be able to update the table yourself with VBA here is what you're going to need to do.

'Make sure that you have referenced the Microsoft DAO 3.x Library otherwise this example will not work.
' To do this, you need to be in the VBA Code editor, click on Tools > References and select Microsoft DAO 3.6 or Micorosoft DAO 3.x of whatever version of MS access you have installed.

Private Sub Form_Open(Cancel As Integer)
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    
    Set MyDB = CurrentDb()
    Set MyRS = MyDB.OpenRecordset("SELECT * FROM IMG_TLY_BATCHCOUNTER", dbOpenDynaset)
    
    Set Me.Recordset = MyRS

End Sub

'By do the above, this will still allow you to move between Items in the recordset and make and changes to the recordset and even add records to the record set. 
'If you're wanting to do it totally without even binding the form to a datasource at all, then that when it really starts to get interesting with all of the code that you're going to need to write for every little things that you need to do in order to update, delete, insert, and change records as you're going through each record in the table.

'To edit a record within code the follow DAO example would be valid.

Private Sub EditRecord_click()
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    
    Set MyDB = CurrentDb()
    Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name]", dbOpenDynaset)
    'If you want to edit a particular record then you would have to put a Conditional
    'statement in the SQL Statement that goes in the OpenRecordset Method. Example
    ' ("SELECT * FROM [TABLE NAME] WHERE [FieldName] = " & Numeric_Value )or
    ' ("SELECT * FROM [TABLE NAME] WHERE [FieldName] = '" & String_Value & "'")
    ' You don't need the () on the string. just for the method itself is all as I've shown above.
    'Please note that if you want to use values from the form, just simply refer to them by using
    'the Me.obejctname.value to get the value of the object on the current form.
    
    With MyRS
        If Not .EOF Then
            .Edit
            !FieldName = StringOrNumeric_Value 'You can put whatever value go into that field
            !FieldName2 = StringOrNumeric_Value2
            .Update 'This update the current Record
        End If
        .MoveNext 'this allows you to move to the next field
    End With
End Sub

Private Sub AddRecord()
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    
    Set MyDB = CurrentDb()
    Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name]", dbOpenDynaset)
    
    With MyRS
        .AddNew
        !FieldName = VariableOrValue 'Example !FullName = me.FirstName.Value & " " & me.LastName.Value
        .Update
    End With
    
    MyRS.Close
    MyDB.Close
    
    Set MyRS = Nothing
    Set MyDB = Nothing
    
End Sub

Private Sub DeleteRecord(PrimaryKeyValue As Double)

    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    
    Set MyDB = CurrentDb()
    Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
                                  "WHERE [FieldNameofPrimaryKey] = " & PrimaryKeyValue, dbOpenDynaset)
    'Please make sure that you pass the primary key value to the routine,
    'otherwise it will not find anything to delete and generate an error message.
    
    With MyRS
        If Not .EOF Then
            .Delete
        End If
    End With
    
    MyRS.Close
    MyDB.Close
    
    Set MyRS = Nothing
    Set MyDB = Nothing
                                  
End Sub

[bbatson]

Thank you guys very much - the posted code works and the information provided is very helpful. Again, many thanks.

[ADezii]

One critical point that you may also need to mention is that you cannot assign an Open Recordset to a Form Object in earlier versions of Access.

[PianoMan64]

that is very true. Sorry I didn't mention that.

[ADezii]

that is very true. Sorry I didn't mention that.

Many notable points in Post #3, many members are not aware of the benefits of assigning a Recordset to a Form. Nice job, PianoMan64.