Hi

pedie · Apr 16, 2011

Hi, I need VBA to add fields to table [taking each rec available in table1,field "Names" and adding it to table "Team_US"].

Sorry for the title...
All this time..I've been trying to post and it error and couldnt post...so at last i just tried with one hi and it went through...

Regards
Pedie

SydneyGeek · Apr 16, 2011

Why are you creating a field for each name? I see a problem when those names change (as they will); any queries, forms, and reports will need to be redesigned.

If you need to see data by each person you could use a crosstab query. In fact, if you want to create a table with all the names you could (1) create a crosstab and (2) create a make-table query that uses the crosstab as the data source.

Denis

Norie · Apr 16, 2011

pedie
Why?

pedie · Apr 16, 2011

Hi Denis and Norie....

. my apologies...way away for sometime from forum..
'm trying to build a database which will store data [like an attendence for example] for each day for each user....
I have list of user in excel which is being copied manually to access table and i want to run a code to create field for each user...

If i create field for each user manually it takes time so i want to know if there is a shortcut/rather a code to finish this in one go....

alansidman · Apr 16, 2011

In a normalized database, you would not have a field for each name, but a field that contains all the names. Each name would be an individual record in the field. Take a look at the white paper on data normalization
http://forums.aspfree.com/microsoft-access-help-18/relational-database-principles-208217.html

Alan

xenou · Apr 16, 2011

The design implementation you are described makes querying the database very difficult, maintenance even more so. It should be avoided. That said, a quick way to create fields in Access would be to import a row of data from Excel (with perhaps one dummy value below the "names" you want as field names). But such a setup is probably better used in Excel, not Access, anyway ...

ξ

pedie · Apr 16, 2011

Hi Alan, hi Xen..
Alan, i could not get most of what it is about..[limited knowledge

]...
Xen, hmmm....i tried copying from excel but instead of names coming in fields it is appearing in first records of each field...

I also tried using this code but it can add only 1 col at a time so...

I thought writing this code should be easy....but thanks a lot for the advice...because if you guys dont say then....i wont know how it is ....

I'd love to have the code though

Code:

[/FONT]
[FONT=Courier New]Option Compare Database
Option Explicit
Public Sub addFieldToTbl()
   On Error GoTo ErrHandler
   Dim db As Database
   Dim tbl As TableDef
   Dim fld As DAO.Field
   Set db = CurrentDb()
   Set tbl = db.TableDefs("Team_US")
   Set fld = tbl.CreateField("Organization122", dbText, 30)
   
   tbl.Fields.Append fld
   fld.Properties("Required").Value = True
   fld.Properties("AllowZeroLength").Value = False
CleanUp:
   Set fld = Nothing
   Set tbl = Nothing
   Set db = Nothing
   Exit Sub
ErrHandler:
   MsgBox "Error in addFieldToTbl( )." & vbCrLf & vbCrLf & _
       "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
   Err.Clear
   GoTo CleanUp
End Sub

alansidman · Apr 16, 2011

Pedie;
Data Normalization is key to maintaining databases. Perhaps this tutorial will make it clearer.

http://www.phlonx.com/resources/nf3/

If your database is not normalized, you will eventually have difficulty in extracting data in your queries as Xenou has expressed earlier.

Alan

pedie · Apr 16, 2011

Alan...thanks again for the quick response...
'm reading it through...i got that same page from the earlier link...inside the pdf...

I also need another help. Please help in getting this done...
Form1 has textbox with username, combobox with dropdown YES and NO. and textbox1 with the date.
I want a code to find username field then date and then on the field and record intersection record/cell mark yes/no as per combobox value.

Thanks again.

<TABLE style="WIDTH: 258pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=344><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" span=7 width=40><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #948b54; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=64>

DATES

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User1

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User2

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User3

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User4

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User5

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User6

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=40>

User7

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #948b54; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>

4/17/2011