Update Default Value

[TJones76]

Hi!

2 challenges:

1.) I've got a similar challenge as thread702-772209 with updating default values within a table.

I want to do this through a form w/ command button to select from a dropdown list of values. Once value is selected, it updates the field's default value w/ the new value and I only want the records from that point onward to reflect the new default.

2.) I've got several tables (identical tables for individual users) with this same field that needs to be updated. What's the easiest way to update all tables as above?


Thank you so much!!
Tiffany

 

[scottian]

this is a bit overkill,
but why not make a new table with only one field which will hold the value you want, then reference this field whenever from wherever

Be ALERT - Your country needs Lerts

 

[TJones76]

Thank you for your reply Scottian.

The problem is that only the manager has the authority to update the default set time that it takes for her employees to do a specific set of actions during the day and she is not database saavy enough to go into design view to change the default value when she needs to. This update only occurs periodically.
So, I've created a separate interface for the manager where she can update certain tables with current information such as adding new reason codes and adding new employee information that populates tables used for dropdown lists.

I need code or directon to create what's in my first post.

 

[RoyVidar]

In the thread referenced, the individual tables where not altered. There was no change in the default value of the table field. It involved using another/extra table to hold the "current" default value(s).

Whenever the form(s) using the data table(s) where used, the extra table containing the current default value was opened, and the value currently in that table added to the form controls default value.

Try it out (there should be enough code samples in that thread to get started), and if problems, post back with current code, errormsg...

BTW - having several identical tables sound's like oncomming headaches. Why not use one table, and separate the values thru some kind of category/status etc? This would probably make reporting and maintenance easier...

Roy-Vidar

 

[TJones76]

Hi RoyVidar...
I've tried the code from the above mentioned thread and I'm getting a Compile Error: Variable not defined. Here's what I have: (my table is called DEFAULT VALUES, and there are several fields w/ defaults, in this case I want it to pull from the "Legacy" field)

Private Sub Form_Open(Cancel As Integer)
Set rst = db.OpenRecordset("DEFAULT VALUES")
Me!WIPS_Set_Number_Per_Hour_Legacy.DefaultValue = rst("Legacy")
End Sub

what am i doing wrong?


-Tjones76

 

[RoyVidar]

Check out the other lines at the bottom of the thread. The two lines you have here, is what I referred to as "' the other code" in my last reply there.

Roy-Vidar

 

[TJones76]

I'm sorry Roy, I'm not following very well I guess.
I need to know exactly what to put in the OnOpen event to reference that default table and pull the right value based on the name of the field ("Legacy") in the table ("DEFAULT VALUES"). Here is what I have so far and i'm getting an error msg saying "variable not defined":

Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
' the other code...
rs.Close
Set rs = Nothing
Set db = Nothing
Set rst = db.OpenRecordset("DEFAULT VALUES")
Me!WIPS_Set_Number_Per_Hour_Legacy.DefaultValue = rst("Legacy")

End Sub

-Tjones76

 

[TJones76]

Now I've taken out the "other code" portion and it's asking me for a parameter value for the field when i open the form: (btw, the field in the form is bound to the main table.. is that the issue? I DO want the field to be bound so that the value shows up in the main table.. hopefully this is possible)

Private Sub Form_Open(Cancel As Integer)

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

Set rst = db.OpenRecordset("DEFAULT VALUES")
Me!WIPS_Set_Number_Per_Hour_Legacy.DefaultValue = rst("Legacy").Value

End Sub


-Tjones76

 

[TJones76]

Disregard my last msg, for some reason there were fields missing out of my query... thus the parameter values.

But I'm still stuck with my problem of updating default values through the extra table.. the code is not inserting the values based on the extra table.

Thanks again..


-Tjones76

 

[RoyVidar]

Hi again!

I'm afraid I don't fully understand. As I see it, the code lines you have, should update the default value of the control. Doesn't it?

This code deal with taking a value from a table field, and placing it in a controls default value property every time you open the form.

Is it also the opposite you're looking for, on the after update of the combo, something like this to update the table field and the other field? (in the referenced thread, this operation was performed in another form, probably by storing the value directly in a bound control...)

[tt]Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Me!WIPS_Set_Number_Per_Hour_Legacy.DefaultValue = Me!cboYourCombo.Value
Set rst = db.OpenRecordset("DEFAULT VALUES")
rst("Legacy").Value = Me!cboYourCombo.Value
rst.close
set rs=nothing
set db=nothing[/tt]

Else, please try to describe what you're after, what errormsg, what doesn't work etc

(try placing a breakpoint in one of the code lines (F9) then go back to the for and open it, you should be thrown to the VBE with that line highlighted, and be able to step tru it line by line (F8), hovering the mouse over the variables should provide values...)

Roy-Vidar