How to add a new record in a table by code or macro? (1 Viewer)

[WilliamQian]

I have a simple table of Customer names. It's as simple as two fields involved:

CustomerID
CustomerName

How can I use VBA code or macro to add a new record to this table? I'm a newbie, so if possible, please provide a few lines of example codes. Thanks.

 

[CraigDolphin]

dim strSQL as string

strSQL = "Insert into tblCustomer ([CustomerName]) Values ('" & Inputbox("Please tell me a name to add to the table") & "');"
docmd.runSQL strSQL

or you could define a variable that holds the name and use that in place of the input box...

or use a value currently in the text control instead of the input box. eg ... & [Forms]![FormNameHere].[txtboxNameHere] & ...

whatever floats your boat

 

[WilliamQian]

This is great! It works. Thanks, Dolphin.

 

[CraigDolphin]

You're welcome.

 

[WilliamQian]

I also learned a lot from your other posts. They solved my problems on ComboBox. Here's my code. When an user input a customer name that is not in my customer table, a message box will pop up and ask if the user wish to add it to the list. If yes, a new record will be added to the customer table. Done!

It works just perfectly. Thanks again.

Private Sub CUSTOMER_NotInList(NewData As String, Response As Integer)
    
    Dim ctl As Control
    Dim strSQL As String
    ' Return Control object that points to combo box.
    Set ctl = Me.CUSTOMER
    
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Customer '" & ctl.Text & "' is not in the list. Add it?", _
         vbOKCancel) = vbOK Then
            strSQL = "Insert into Customer ([CompanyName]) Values ('" & ctl.Text & "');"
            DoCmd.RunSQL strSQL
            Response = acDataErrAdded
    End If

End Sub

 

[CraigDolphin]

Again, you're welcome. And thanks for using the search function to find what you need before asking.

 

[boblarson]

Again, you're welcome. And thanks for using the search function to find what you need before asking.

A Big AMEN to that! That is a trait that is seems to be seldom seen here, but is greatly appreciated.