Adding Yes/No using VBA

[AKMonkeyboy]

I found some great code for adding a Yes/No field to a table, but need some help modifying the code to allow for a loop/dowhile. I have alot of fields to add and don't want to have to type it all in. This is the code without the loop (I know how to do that), but with my modifications allowing for variable field names (all my field names are the same except for the last character which will increase by 1 each time).

Private Sub btn18_Click()
Dim intField As Integer
Dim strFieldUpdate As String
Dim strFieldAdd As String

    intField = 1
    strFieldAdd = "ALTER TABLE tblPhysicianMod ADD COLUMN fraStatement1a" & intField & " YesNo;"
    strFieldUpdate = "tjTab.Fields!fraStatement1a" & intField
    
'Add Delete Column to tblLabs
    Set tjDb = CurrentDb
    Set tjTab = tjDb.TableDefs!tblPhysicianMod
    
    tjDb.Execute strFieldAdd
    
    Set tjFld = strFieldUpdate
    
    Set tjPropFormat = tjFld.CreateProperty("Format", dbText, "Yes/No")
    tjFld.Properties.Append tjPropFormat
    
    Set tjPropDisplay = tjFld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
    tjFld.Properties.Append tjPropDisplay
    
    tjDb.Close
    
    Set tjPropDisplay = Nothing
    Set tjPropFormat = Nothing
    Set tjFld = Nothing
    Set tjTab = Nothing
    Set tjDb = Nothing
End Sub

Every time I run the code I get a message:
Compile error: Object required.

I'm being told the problem is the line that reads:

Set tjFld = strFieldUpdate

Any thoughts on what I might be doing wrong?

Thanks,
Adam

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[TheAceMan1]

How are ya AKMonkeyboy . . .

Declare the object & type 1st:

[blue]   Dim intField As Integer
   Dim strFieldUpdate As String
   Dim strFieldAdd As String
   [purple][b]Dim tjFld as Field[/b][/purple][/blue]

See Ya! . . . . . .

 

[AKMonkeyboy]

Howdy AceMan1 - long time no write. And your life was going so well

I implemented the change you suggested - only now I'm told I have a type mismatch on:

Set tjFld = strFieldUpdate

???

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[TheAceMan1]

AKMonkeyboy . . .

You [blue]set an object to the type its was declared with[/blue]. A Field type is not a String Type! So you should have:

[blue]   Set tjFld = tjTab.Fields(fraStatement1a" & intField)[/blue]

I see you have other objects your setting to nothing that need to be declared properly! . . .

See Ya! . . . . . .

 

[AKMonkeyboy]

That's what I was researching, but couldn't quite get what I needed to get over the hump!

I bet there's a lot of things I'm doing that need corrected... In all my travels (not many) I've never actually met anyone that knows what they're doing w/ Access - so I learn my own habits and run with them (I'd be completely lost w/o Tek-tips).

Anyway, thanks so much for your help. Wish there was something I could do for you in return.

Guess I'll just try and "pay it forward".

Monkey

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[AKMonkeyboy]

For anyone who stumbles on this post - here's the final code. The result is the addition of 69 properly formatted Yes/No fields in a table. Thanks again AceMan

Private Sub btn18_Click()
Dim intField As Integer
Dim strFieldAdd As String
Dim tjFld As Field

'Set starting field suffix
    intField = 1
  
    Do While intField < 70
   
'Set strFieldAdd
    strFieldAdd = "ALTER TABLE tblPhysicianMod ADD COLUMN fraStatement1a" & intField & " YesNo;"
    
    Set tjDb = CurrentDb
'Set table to add field
    Set tjTab = tjDb.TableDefs!tblPhysicianMod
'Add new field
    tjDb.Execute strFieldAdd
'Set field to modify
    Set tjFld = tjTab.Fields("fraStatement1a" & intField)
'Set new field properties
    Set tjPropFormat = tjFld.CreateProperty("Format", dbText, "Yes/No")
    tjFld.Properties.Append tjPropFormat
    
    Set tjPropDisplay = tjFld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
    tjFld.Properties.Append tjPropDisplay
    
    tjDb.Close
    
    Set tjPropDisplay = Nothing
    Set tjPropFormat = Nothing
    Set tjFld = Nothing
    Set tjTab = Nothing
    Set tjDb = Nothing
    
'Increase field suffix by 1
    intField = intField + 1
'Repeat
    Loop
    
End Sub

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[PHV]

addition of 69 properly formatted Yes/No fields in a table
Have a look here:

http://r937.com/relational.html