default value macro

[tjrobert82]

I am trying to create a macro that will automatically change the default value for a field in a table (from 2 to 3, for instance). Ideally, it would take this default value from a form, but otherwise a simple input box would be fine. SetValue only works for forms and reports, I think, so is there a way to get Access to do what I want?

 

[pbaldy]

Don't know a macro solution, but this will set the default at the table level:

  Dim tDef             As DAO.TableDef
  Dim db               As DAO.Database

  Set db = CurrentDb
  Set tDef = db.TableDefs("TableName")

  tDef.Fields("FieldName").DefaultValue = "test default"

  Set tDef = Nothing
  Set db = Nothing

 

[tjrobert82]

follow-up

Thanks for the help, Paul! I think I have it very close to working, there's just one little snag left. I have the code run as a button in a form which looks at the table with the changing default value (Holy prepositions, Batman!). Anyway, I added another line right before the update tDef code:

DoCmd.Close acForm, "MyOpenForm", acSaveYes

but it still says, "Cannot modify table structure. Another user has the table open."

I figured the other user was the form, so I closed it before running the update. But it still gives me that message. I'm the only one using this database right now, so it's not a networking thing. Maybe because I'm running the code out of that form, it still considers it open even though I closed it. I don't know. Any suggestions?

 

[tjrobert82]

following the follow up

Guess I jumped the gun a little bit in asking more questions. I made a switchboard that opens from the form and runs the code that closes the form. This seems to be working as I've been able to update my default table values.