Add Field & then populate with value from InputBox

[PWD]

Good morning. I'm new to Access VBA (OK with XL!).

Basically I want to add a new field to the end of a table so I can save the records with a unique reference, e.g. "24062011", append these new records to a 'Master' table each time. Good news, I appear to be able to create a new field:-

Dim CDbTD As TableDef
Set CDbTD = CurrentDb.TableDefs

CDbTD("Combined_Table").Fields.Append CDbTD("Combined_Table").CreateField("TableDate", dbText, 8)

I appear to be able to prompt & get a value for the date:-

'Prompt for date
DateString = InputBox("Enter data - Format: ddmmyyyy")
If Len(DateString) > 0 Then

THIS IS WHERE I AM STUCK

End If

I have tried various combinations (having previously declared & Set:-

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Combined_Table")

)

rs.AddNew
rs("TableDate").DefaultValue = DateString
 [Or this:-] rs("TableDate").Value = DateString

("Invalid operation, error 3219")

rs.Update

Do I need to do some sort loop to add this value to all the records in the new field instead?

This doesn't work:-

For Each x In rs("TableDate")
x = DateString
Next x

Many thanks,

Des.

 

[PWD]

OK this works, but it's REAL slow:-

Dim db   As DAO.Database
Dim rs As DAO.Recordset

Set db = Access.Application.CurrentDb
Set rs = db.OpenRecordset("Combined_Table", dbOpenDynaset)

DateString = InputBox("Enter data - Format: ddmmyyyy")

    With rs
    .MoveLast
    .MoveFirst

        Do While .EOF = False
            .Edit
            !TableDate = DateString
            .Update
          .MoveNext
        Loop
        
    End With

  Set rs = Nothing
  Set db = Nothing

And there are only 190 records! Is there a more efficient way of doing this?

Many thanks,

Des.

 

[Andrzejek]

Are talking about the situation where you have a table called "Combined_Table" and you want to add a Date field "CDbTD" at the end of this table in code?
[tt]
Combined_Table

FName LName SSN [red]CDbTD[/red]
Bob Brown 123 [red]11/11/2011[/red]
Susie White 987 [red]5/5/2010[/red][/tt]

Have fun.

---- Andy

 

[randy700]

First of all, you are NOT adding a DATE field.
Dates are numbers and you have declared a TEXT field.

If you truly want a date field, you will need to modify your "CreateField" code to use Date/Time rather than text.

Regardless, to update all records in the table, I'd use an UPDATE query.

DateString = InputBox("Enter data - Format: ddmmyyyy")
strSQL = "UPDATE Combined_Table SET TableDate = #" & DateString & "#"
DoCmd.runSQL strSQL

Note:
If you decide to stick with a text field, change the # symbol to a single quote (').


Randy