Access VBA Putting name of a Field as a Record

Question

I am with a little bit of a coding problem that I don't know how to solve. I want to put in the table as a record a field name of another table in the same Database.

I will give you an example:

Table1 What I have

Table 2 What I want

The table 1 is update weekly from an external source so I need to record the field name as a record in the second table using VBA language. Does anybody knows if it's possible?

Thank You in advance

GavinP · Accepted Answer · 2016-12-05 14:55:57Z

1

You could do something like this

Dim db As Database
Dim fld As Field
Dim sql As String
Set db = CurrentDb

For Each fld In db.TableDefs("YourTable").Fields

    sql = "Insert into YourSummaryTable([Date], Hours) select '" & fld.Name & "', sum([" & fld.Name & "]) as s from YourTable"
    DoCmd.RunSQL sql


Next fld

Note that you've used a reserved word Date as a fieldname, which isn't best practice and requires the use of the square brackets in the query.

answered Dec 5, 2016 at 14:55

GavinP

6871 gold badge6 silver badges19 bronze badges

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

4 Comments

Tiago Faria Over a year ago

Hey thks you're code works very well. However when I run it, appears a window with "Enter Parameter Value" for each name of the field. Is there a way I do not need to enter nothing by end and the record stays with the name of the field?

GavinP Over a year ago

What data type is your Date field ?

Tiago Faria Over a year ago

It can be whatever I want. But I would prefer Date\Time or Short Text

GavinP Over a year ago

The above sample was based on a text field. If you want dates you will likely have to replace the ' with # for Access. The code sample I gave worked without Enter Parameter Value error for me, so either you have not copied the code correctly or there's something different in your schema that I didn't have.

Collectives™ on Stack Overflow

Access VBA Putting name of a Field as a Record

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related