Default Values 1

[BMcMakin]

How do I use a control in a form to set/change the default value in a table?

 

[ZOR]

I just did a search on the forum for Tabledef. If you look at the thread titled (Default Values 0 to "") there may be info on what you are searching for (hope it is) Maybe you should say what defaults you are trying to set and someone might help a bit further. Regards

 

[BMcMakin]

Sorry if my question was unclear. Here is my situation. I have built a database that my company uses for billings and quotes. Each person using it has an individual copy of the database on their laptops. Everybody uses different variables like labor rates, tax rates, ect. I wanted to add a form for them so they could set their default rates. Right now I have the default values defined in the table. Is their away to change these defaults in an easy user form without having to take them into the table design window?

 

[TheAceMan1]

How are ya BMcMakin . . . . .

Yes it can be done.

List the table/field names of interest in the following format:

[blue]TableName.FieldName[/blue]

Now . . . there's an [blue]operational consideration[/blue] you have to take into account:

[blue]In order to directly edit properties of a table, the executing code must have sole access . . .[/blue]

This means any forms, queries and reports accessing the table/s of interest, [blue]will have to be closed![/blue] When the Defaults Form is finally closed, [purple]what form to open in its place has to be decided upon[/purple], otherwise the [blue]database window may be exposed bare[/blue].

[purple]Supply some input on your intent with the above.[/purple]

In the meantime, construct an unbound form with labels for the table names (they can be descriptive as long as I know what table they define) and unbound textboxes for the fields.

See Ya! . . . . . .

 

[BMcMakin]

The database is menu driven with a main menu at the start. I can easily direct them to a "define default value" form that could be open without having anything else open.

One possible problem with this is there are some equations where the database is on a network and two or three users could be on the same database at once, preventing sole access. Is there a check or something for this that would stop the operation and tell the user that he is not the only one in the database at the time?

There are multiple forms where I need to change default values, but the most complicated is listed below.

Table: "Change Order Sub - CE Details"

Change Order Sub - CE Details.Electrician Labor Rate
Change Order Sub - CE Details.Foreman Labor Rate
Change Order Sub - CE Details.General Foreman Labor Rate
Change Order Sub - CE Details.Superintendent Labor Rate
Change Order Sub - CE Details.Project Manager Labor Rate
Change Order Sub - CE Details.Project Engineer Labor Rate
Change Order Sub - CE Details.Premium Time Labor Rate

I don't understand what you mean about constucting an unbound form. I mean...I can do it, but am I supposed to somehow send it to you?

This is just one of several "Default Values" that I would like the user to be able to define without having to know how to navigate in Access.

Thanks alot for your help.

 

[TheAceMan1]

You said:

[blue] Each person using it has an individual copy of the database on their laptops. [purple]Everybody uses different variables[/purple] like labor rates, tax rates, ect.[/blue]

Then in your last post:

[blue]One possible problem with this is there are some equations where the database is on a network and [purple]two or three users could be on the same database at once[/purple], preventing sole access.[/blue]

Your requirement of [blue]Independent Defaults[/blue] makes the networking issue invalidate changing [blue]Table Defaults[/blue]. Since the tables on a network are common, independency is lost. Also be aware: if the database on the network is not a split database then you can't set independent defaults without having an equivalent table for each user. So what I may/maynot be able to depends on the following question:

Is the database on the network a split database (that is, tables are in a seperate database; forms/queries/reports are in another)?

See Ya! . . . . . .

 

[BMcMakin]

Multiple users are etremely rare but does happen. The database is NOT split. The database file is put on the network in the very rare occassions when two people are working on the same project. In this case, the database is not customized to have different default values for each user.

Hope this answers your question.

 

[PHV]

The basic idea:
CurrentDB.TableDefs("CE Details").Fields("Electrician Labor Rate").DefaultValue = Me![relevant textbox].Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya PHV! . . . . .

[blue]TableDefs[/blue] are the only way I know to change properties of [blue]existing[/blue] tables. So I'm with ya there . . . . .

[blue]BMcMakins[/blue] has thrown a curve with the network and the fact the [purple]database is not split![/purple] Can't set defaults for tables on the network (common to all). Can't even say, use the forms [blue]Tag Property[/blue] to save defaults (common form), so another method has to be resolved.

See Ya! . . . . . .

 

[PHV]

How are you [blue]TheAceMan1[/blue] !

Each person using it has an individual copy of the database on their laptops

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

BMcMakin . . . . . Sorry to get back so late . . .

Thanks to a specific prompt from [blue]PHV[/blue], we'll take care of the notebooks and worry about the network later. [purple]Backup the database so you can come back to square one if necessary.[/purple]

[blue]I don't understand what you mean about constucting an [purple]unbound form[/purple].[/blue]

An [blue]unbound form[/blue] is a form with no [blue]RecordSource[/blue] (displays no records). When you make the form, in the [blue]Forms Window[/blue] just click [purple]New[/purple].

You can design the form as you please. I only suggest you group the TextBoxes under some descriptive Labels [blue]easily identified by the users.[/blue] Discriptions for the [blue]TextBoxes[/blue] can be the same. These are of course [blue]Unbound TextBoxes[/blue] (just drag them from the toolbar), and you don't have to give them any specific name as there being identified by the [purple]Tag Property[/purple].

[blue]Important!:[/blue]

[blue]For each textbox that holds a default value, in the [purple]TagProperty[/purple] your going to enter the table & field name in the following format:

[purple][TableName].[FieldName][/purple][/blue]

You can group select those that are common and set it once for all in the group. [purple]Be careful of spelling, we don't want to have problems due to typo's![/purple]

In the meantime I've setup a simulation and currently testing the code, I'll get back to you as soon as i'm done. This may not be until tomorrow. its getting late here in NY. If you have any questions or need help, just postback . . .

See Ya! . . . . . .

 

[BMcMakin]

Thanks Aceman1....I will await your next post.

 

[TheAceMan1]

BMcMakin . . . . .

In testing operations I find your going to need two [purple]Command Buttons[/purple]:

[ol]
[li][purple]View Current Defaults[/purple]: clears the form then displays the current default settings. The same occurs on opening the form.[/li]
[li][purple]Save Defaults[/purple]: save the current view to their respective defaults. Empty textboxes on the form will null that particular default.[/li]
[[/ol]
Both buttons will have [blue]confirmation pop-ups[/blue].

See Ya! . . . . . .

 

[TheAceMan1]

OK BMcMakin . . . . .

[purple]BackUp the database so you can come back to square one if you have to.[/purple]

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

In the code module for the new form, copy/paste the following code:

[blue]Public Sub DefaultsHandler(Sav As Boolean)
   Dim db As DAO.Database, ctl As Control
   Dim tdf As DAO.TableDef, fld As Field, Prp As Property
   Dim tblName As String, fldName As String
   Dim Idx As Integer, Typ As Integer
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   
   DL = vbNewLine & vbNewLine

   If Sav Then
      Msg = "Are you sure you want to Save Defaults?" & DL & _
            "Saving will overwrite current settings!" & DL & _
            "Click 'Yes' to save defaults." & DL & _
            "Click 'No' to abort"
      Style = vbQuestion + vbYesNo
      Title = "Save Defaults Confirmation . . ."
      
      If MsgBox(Msg, Style, Title) = vbNo Then Exit Sub
   End If
   
   Set db = CurrentDb()
   
   For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox And Len((ctl.Tag) & "") > 0 Then
         DoCmd.Hourglass True
         Idx = InStr(1, ctl.Tag, ".")
         tblName = Left(ctl.Tag, Idx - 1)
         fldName = Right(ctl.Tag, Len(ctl.Tag) - Idx)
         Set fld = db.TableDefs(tblName).Fields(fldName)
         Set Prp = fld.Properties("DefaultValue")
         Typ = fld.Type
         
         If Sav Then [green]'Save Defaults[/green]
            If Len(Trim(ctl) & "") > 0 Then
               If Typ = dbText Then [green]'Text[/green]
                  Prp = """" & ctl & """"
               ElseIf Typ = dbDate Then [green]'Date/Time[/green]
                  Prp = "#" & ctl & "#"
               Else
                  Prp = Val(ctl) [green]'Numeric[/green]
               End If
            Else
               Prp = "" [green]'No Data[/green]
            End If
         Else [green]'Load Defaults[/green]
            If (Typ = dbText Or Typ = dbDate) And Len(Trim(Prp) & "") > 0 Then
               ctl = Mid(Prp, 2, Len(Prp) - 2) [green]'strip " & #[/green]
            Else
               ctl = Prp
            End If
         End If
         
         DoCmd.Hourglass False
      End If
   Next
   
   Set fld = Nothing
   Set db = Nothing

End Sub[/blue]

Next, in the [purple]Load Event[/purple] of the form and the [purple]Click Event[/purple] of the button [purple]View Current Defaults[/purple], copy/paste the following:

[blue]   Call DefaultsHandler(False)[/blue]

Next in the Click Event of the button [purple]Save Defaults[/purple], copy/paste the following"

[blue]   Call DefaultsHandler(True)[/blue]

[blue]Note:The Current Defaults are loaded when the form opens.[/blue]

Thats it! . . . . give it a whirl and let me know . . . .

See Ya! . . . . . .

 

[BMcMakin]

The code looks great! But I don't have the DAO 3.6 Object Library in my references toolbar. I have the Microsoft Access 9.0 Object Library.

Sorry to be a pain.

 

[TheAceMan1]

BMcMakin . . . . .

[blue]Really! . . . . .[/blue]What version Access?

If the [blue]reference listing[/blue] shows any [purple]Microsoft DAO 3.x Object Library[/purple] use that.

If no DAO 3.x, search the hard dirve and/or installation CD using [blue]DAO*.*[/blue] If you find it, copy to [blue]Drv:\Program Files\Common Files\Microsoft Shared\DAO[/blue] folder.

See Ya! . . . . . .

 

[BMcMakin]

I thought I had replied to this a long time ago but I just checked it again to see my message did not get posted.

I just wanted to thank you for your help. IT WORKS PERFECTLY!

Thanks,

Bob.

 

[TheAceMan1]

[blue]Great![/blue]

[purple]You take care! . . . . . Ya Hear![/purple]

See Ya! . . . . . .