Add data into table/query from text box and button [Access]

Question

I am looking for help on trying to add data into a table / query through text boxes and a button. Currently, there are two buttons that will be hooked up to the text boxes; Search & Add.

Search I have finished already, where it searches a query attached to a table for the input you entered into the text boxes. Simple.

Though now I'd also like to make an add button, where once you put information into the text boxes and click add instead of search, it directly adds that information onto the table and saves it so you can view it at later points in time.

This is code I found online somewhere, but I don't know how to make it pick up the data from the text boxes using it:

Private Sub Command344_Click()

INSERT INTO OrderT (CustomerName,OrderName,OrderDesc,DateOfPurchase,ProjectDueDate,EngineerDueDate,ProjectComplete,CutplanDueDate,MaterialSpecs,CutplanCode,HardwareSpecs,HardwareDueDate,HardwareComplete,PurchaseOrder,PurchaseSupplier);
VALUES (CustomerName,OrderName,OrderDesc,DateOfPurchase,ProjectDueDate,EngineerDueDate,ProjectComplete,CutplanDueDate,MaterialSpecs,CutplanCode,HardwareSpecs,HardwareDueDate,HardwareComplete,PurchaseOrder,PurchaseSupplier);

End Sub

Button Name: Command344

TextBox Names: CustomerName OrderName OrderDesc DateOfPurchase ProjectDueDate EngineerDueDate ProjectComplete CutplanDueDate MaterialSpecs CutplanCode HardwareSpecs HardwareDueDate HardwareComplete PurchaseOrder PurchaseSupplier

The fields in the table have the same names in the exact same order from top -> bottom, left -> right.

The table name is OrderT.

Form name is SearchF

Johnny Bones · Accepted Answer · 2013-10-24 13:03:18Z

5

You can do this either through a query or by picking up the data from your form directly.

To do this in a query, you would put something like this (untested) code behind your button:

    Dim sSQL as String

    Set sSQL = "INSERT INTO OrderT (CustomerName,OrderName,OrderDesc,DateOfPurchase,ProjectDueDate,EngineerDueDate,ProjectComplete,CutplanDueDate,MaterialSpecs,CutplanCode,HardwareSpecs,HardwareDueDate,HardwareComplete,PurchaseOrder,PurchaseSupplier)
    VALUES (" & Me.CustomerName & "," & Me.OrderName & "," & Me.OrderDesc & "," & Me.DateOfPurchase & "," & Me.ProjectDueDate & "," & Me.EngineerDueDate & "," & Me.ProjectComplete & "," & Me.CutplanDueDate & "," & Me.MaterialSpecs & "," & Me.CutplanCode & "," & Me.HardwareSpecs & "," & Me.HardwareDueDate & "," & 
Me.HardwareComplete & "," & PurchaseOrder & "," & Me.PurchaseSupplier & ");"

    DoCmd.RunSQL sSQL

Picking up the data from the data form (my preferred method) would look like this (untested) code:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
set rec = db.OpenRecordset ("Select * from OrderT")

rec.AddNew
rec("CustomerName") = Me.CustomerName
rec("OrderName") = Me.OrderName
etc...
rec.Update

Set rec = Nothing
Set db = Nothing

answered Oct 24, 2013 at 13:03

Johnny Bones

8,4927 gold badges61 silver badges127 bronze badges

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

2 Comments

4dmonster Over a year ago

Second method is better because don't require type manipulations. And first method would not work unless quote strings with ' and reformat dates with # values in VALUES part.

D347HxD Over a year ago

Thank you! It works. I used the code to input it straight to the table; that it releases another problem. I do use a query to show the results of the search onto the form; though for the query to recognize that row in a table every field needs to be filled out; if it doesn't the query acts like it doesn't exists and moves on. How would I make it so it can take all data from the table and input it onto the subform, and if a field is blank it just leaves it as null on the subform?

Collectives™ on Stack Overflow

Add data into table/query from text box and button [Access]

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related