CODEWITHSUNDEEP
ms-accesscopyvbaappend
Stumf
Stumf

Reputation: 941

Copy Selected Value and TextBox Value and Append to Table - MS Access VBA

I'm doing an internship over the summer and have ended up doing some work on an access database. I have been searching for a way to use VBA code to append a location number which the user has selected from a query in a subform (shown in the picture) and a number which they entered into a text box on a previous form, to an existing table. This table has autonumber as the primary key.

To summarise when the user clicks "Select Site" I would like access to copy a code from a text box on another form and copy whichever location they have selected/highlighted (in this case 9.01) and use the two of them to form a new record in an existing table. I only need to copy the 9.01 - not the whole record.

Any help would be much appreciated!

Form Screenshot

Upvotes: 0

Views: 2572

Answers (1)

VBlades
VBlades

Reputation: 2251

You can use code like this to accomplish what you want to do. insert this code to the Click event on your button and change the form, table, column, and parameter names to what you need:

On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Set db = CurrentDb()

    Set qdf = db.CreateQueryDef("", "INSERT INTO [MyTable] (MyCol1, MyCol2) VALUES ([MyVal1], [MyVal2])")
    qdf.Parameters("[MyVal1]") = Forms![MyForm1]![MyControl1]
    qdf.Parameters("[MyVal2]") = Forms![MyForm2]![MyControl2]

    qdf.ReturnsRecords = False
    qdf.Execute

ExitMe:
    Set qdf = Nothing
    Set db = Nothing

    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    GoTo ExitMe

Upvotes: 1

Related Questions