Office Software Thread, Microsoft Access Fill All Blanks in Table in Technical; Originally Posted by powdarrmonkey
So do it a column at a time. Even doing 100 column table by hand is ...
18th March 2010, 11:30 AM #16
Except that I didn't take two days to solve this and your initial response was incorrect!
Originally Posted by powdarrmonkey
[EDIT]Yes I know I will sound very grumpy but that's the mood I am in today[/EDIT]
18th March 2010, 12:02 PM #17
Fair point on the autonumber bit - I tend to avoid using these as they're not very relational and do nasty things when you start moving them around...
Originally Posted by apeo
To be honest, I'd have just run a series of
UPDATE table SET field='Default' WHERE field IS NULL;
queries, but here's the equivalent thing in VBA, also picking up the default field value from the table properties where it's available, and confirming each column query before running:
Dim td As TableDef, fl As Field
Dim sq As String, bUseDefault As Boolean
bUseDefault = True ' Setting to false will ignore default value in field properties
For Each td In CurrentDb.TableDefs
If td.Name = "PUT_TABLE_NAME_HERE" Then Exit For
For Each fl In td.Fields
If fl.Type = dbText Then
sq = "UPDATE [" & td.Name & "] SET [" & fl.Name & "]="
If fl.DefaultValue <> "" And bUseDefault Then
sq = sq & fl.DefaultValue
sq = sq & "'" & "PUT_DEFAULT_VALUE_HERE" & "'"
sq = sq & " WHERE [" & fl.Name & "] IS NULL;"
If MsgBox(sq, vbYesNo) = vbYes Then CurrentDb.Execute (sq)
18th March 2010, 01:03 PM #18
True autonumbers can be annoying... guess what students here love using.
Originally Posted by MattMitchell
To be fair i would just use an update query too but your VBA solution is pretty useful.
By stjtech in forum Hardware
Last Post: 15th July 2009, 03:09 PM
By edd in forum Office Software
Last Post: 25th June 2009, 04:45 PM
By TriggerHappyUK in forum General Chat
Last Post: 18th May 2009, 01:41 PM
By Pyroman in forum How do you do....it?
Last Post: 2nd May 2008, 04:19 PM
By rusty155 in forum Windows
Last Post: 8th February 2007, 09:15 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)