VBA Access - Adding a New Record in Table Based on User Input and Auto Completing the Rest

Question

very rusty on my VBA, but I am trying to add a new record based on user input. There are text boxes where a person enters in the data, but there are fields within the table that they are not entering. I want to add the new record with SQL statements autofilling data from another table matched on the ID they are entering. I am using a recordset to update the table and have tried putting a sql query directly into addnew which just inputs the text of the query into the table. I have also tried setting the statements into variables with no success.

 Set rec = db.OpenRecordset("Select * from Table")
    rec.AddNew
        rec("A") = Me.A
        rec("B") = "SQL1"
        rec("C") = "SQL2"
        rec("D") = "SQL3"
        rec("E") = Me.E
        rec("F") = Me.F
        rec("G") = Me.G
        rec.Update

UPDATE:

  Set rec = db.OpenRecordset("Select * from TableA")
    rec.AddNew
        rec("A") = Me.A
        rec("B") = DLookup("B", "TableB", "A=" & Me.A)
        rec("C") = DLookup("C", "TableB", "A=" & Me.A)
        rec("D") = DLookup("D", "TableB", "D=" & Me.A)
        rec("E") = Me.E
        rec("F") = Me.F
        rec("G") = Me.G
        rec.Update
    rec.Close

Zack · Accepted Answer · 2018-08-03 17:03:53Z

1

I'm not sure what your SQL statements (SQL1, SQL2, SQL3) do, but you might just need a simple DLOOKUP call. Something like this:

Set rec = db.OpenRecordset("Select * from Table")
rec.AddNew
rec("A") = Me.A
rec("B") = DLookup("FieldB", "TableA", "FieldA=" & Me.A)
rec("C") = DLookup("FieldC", "TableA", "FieldA=" & Me.A)
rec("D") = DLookup("FieldD", "TableA", "FieldA=" & Me.A)
rec("E") = Me.E
rec("F") = Me.F
rec("G") = Me.G
rec.Update

You could also use a DAO.Recordset combined with a parameterized query to get the B, C, and D records you need.

answered Aug 3, 2018 at 17:03

Zack

2,3511 gold badge10 silver badges13 bronze badges

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

3 Comments

Santosh Over a year ago

DLookUp comes in handy in these situations. Nicely done!

D. Mellen Over a year ago

I apologize for not having the SQL in there. I like your solution, however now I get the error "Data type mismatch in criteria expression" and I'm not sure why. I updated what my code looks like now.

Zack Over a year ago

Is your Me.A value a string? If so, you'll have to wrap it in (escaped) double quotation marks: DLookup("FieldB", "TableA", "FieldA=""" & Me.A & """")

Jeanine · Accepted Answer · 2018-08-03 17:03:58Z

0

https://msdn.microsoft.com/en-us/library/office/ff845624.aspx

VBA: OpenRecordset .AddNew method runs slow

I think you need a With rec statement. And it looks like a .Close might be a good idea.

 Set rec = db.OpenRecordset("Select * from Table")

  With rec
   .AddNew        
     rec("A") = Me.A
    rec("B") = "SQL1"
    rec("C") = "SQL2"
    rec("D") = "SQL3"
    rec("E") = Me.E
    rec("F") = Me.F
    rec("G") = Me.G
    .Update
    .Close
 End With

answered Aug 3, 2018 at 17:03

Jeanine

12 bronze badges

Collectives™ on Stack Overflow

VBA Access - Adding a New Record in Table Based on User Input and Auto Completing the Rest

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related