Consulting

Results 1 to 6 of 6

Thread: Solved: VBA set Table Default Field Value

  1. #1
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399

    Solved: VBA set Table Default Field Value

    Now before anyone says it i know this is the wrong way to go about it.


    I need a sure fire way to set on the fly (in an .accdr) the default values for fields in a table. Via VBA

    i can redesign it in the future but this is the way i need to do it now.

    As allways any help is apriciated
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    2,775
    Location
    I think you will need to do this in a tabledef using the DefaultValue property.
    I don't have the code for modifying a Tabledef, only creating one, but You will have to "Open" it first.

  3. #3
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    On your suggestion i have tried to implement the following

    [vba]Sub SetDefaultValue(tablename As String, fieldname As String, fieldvalue As Variant)
    Dim db As Database, tdf As TableDef, fld As Field
    Dim blnReturn As Boolean

    Set db = CurrentDb
    Set tdf = db.TableDefs(tablename)
    Set fld = tdf.Fields(fieldname)

    tdf.Fields(fieldname).DefaultValue = fieldvalue

    db.Close
    Set db = Nothing
    End Sub
    [/vba]
    As the main sub routine that actually does the work and the following code

    [vba]Private Sub SympDur_DblClick(Cancel As Integer)
    'Test code for dynamic user default value settings

    Dim tbl As String, ctrlname As String
    tbl = Me.RecordSource
    ctrlname = Screen.ActiveControl.name
    DoCmd.Close acForm, "frmMain"
    SetDefaultValue tbl, ctrlname, InputBox("Enter your new default value")
    DoCmd.OpenForm "frmMain"
    End Sub[/vba]
    On the control that i want to set the default value for.

    The problem i face now is i get the following error

    "Run Time Error 3422:"
    "cannot modify table structure another user has the table open."

    But as you can see from the code i have closed the forms. and there should be no code with the table open... any help would be appreciated as i need to get this done today if at all possible.

    The only other method i can think of that might produce the same results would be to have a lookup table that the user would be able to edit and each fields default value would be a select from where statement... however this would take a very long time to implement where as if i can get this code to work on a single control here i can get it to work throughout the whole system in a matter of minutes.
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  4. #4
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    I think the reason you're having this problem is because frmMain isn't completely closing. Your sub (SympDur_DblClick) is still active during this whole process, since it's the calling routine. You may be closing the Form, but the Form isn't able to completely Unload because it still has code to run in the background.

    Instead of setting the default vlaue in your table, could you assign a default value to the form's control? This would cause the desired value to be written to the table when the record changes are saved, or when the user moves to another record.

    HTH
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  5. #5
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    Have had issues doing that. It Seems that my system only wants to use form defaults for unbound fields and table defaults for bound fields.

    I actually ended up going with a lookup table. Where by it stores the table name and field and the value. Then when a new patient is created it goes through and puts the value into the applicable fields. This allows the end user to overwrite our defaults in the process too
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    2,775
    Location
    That is a neat solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •