Use Value on form control as default value in other tables

[matt beamish]

Can anyone explain to me how I might use a value selected by a user on a database opening menu (which remains open), as a default value for records created programmatically in other tables?

I wonder if I need to write a function to repeat the value - but I cannot see how to use a form value outside of the form's own code. Some of my existing code inserts values into tables using SQL converted into VBA and I do not really want to start fiddling with that - I would rather for now use the default value of the table for the field.

My variable which will change depending upon which set of records a user is working on is a string "FullAccession".

When a user creates records in a table called tblGroups, I need the string "FullAccession" to be the default value in the tblGroups.FullAccession field. A unique integer in the tblGroups is "GroupNo". "GroupNo" and "FullAccession" are joined in a unique index for tblGroups. There is a separate PK autonumber.

thanks

 

[TimW]

Hi
I am not entirely sure what you are trying to achieve. But could you use a global variable? Also you could copy the form object into a module to copy attributes. You could set the default value of the second from depending on the values in the first form....

I hope this helps..if not..if you could re-explain your problem please. It may clarify your requirements.

T

 

[matt beamish]

Thanks for reply - I think you understand what I am after.

The user opens the database and on menu picks a group of records to work on from a combo. This sets "FullAccession"
When in that session records are subsequently created in various tables (including eg tblGroups) I want the value "FullAccession" entered into a field in those tables.

So from your answer:
1. How do I copy the form object into a module - what would the code for this be?

When I have tried this, I get "invalid outside procedure" messages, and the VBA break points to the form object being the culprit.

 

[marlan]

Hi,

For getting values from other forms use the Forms!FormName for loaded forms, or AllForms!formName for non loaded forms.

But - as for using this value as a default, I would have the form Load event sign this value as a control default value, not a DB field.

Changing DB field default value will affect all users until the next time it is changed. It is not good practice to change it for UI reasons - That is what Form Load and form controls are for - user interface.

All the best,

Marlan

 

[matt beamish]

Thanks for your reply. Can I ask you to be more precise so I can understand better?

For getting values from other forms use the Forms!FormName for loaded forms, or AllForms!formName for non loaded forms.

I understand and use this method for controlling control values from queries although I have never used non loaded forms. For loaded forms the value is used. How is a current value established for a non loaded form?

But - as for using this value as a default, I would have the form Load event sign this value as a control default value, not a DB field.

Do you mean the loading of my menu form (where the value is chosen) or a target form where the value needs to be input? If the former, what is my code to "sign this value as a control default value"

I have written this little bit of code for when a user clicks a button to progress to the next menu, having confirmed the identifier "SiteAccession"

Private Sub Command3_Click()
Dim FullAccession As String
FullAccession = Me.SiteAccession
Debug.Print FullAccession
DoCmd.OpenForm "F_OpenMenu2"
End Sub

thanks again for your help

 

[marlan]

I have written this little bit of code for when a user clicks a button to progress to the next menu, having confirmed the identifier "SiteAccession"

Private Sub Command3_Click()
Dim FullAccession As String
FullAccession = Me.SiteAccession
Debug.Print FullAccession
DoCmd.OpenForm "F_OpenMenu2"
End Sub

FullAccession is a local variable, F_OpenMenu2 has no access to it. Make it a global variable:

Public FullAccession As String' Better to have this variable in a global module, not on the forms module.

Private Sub Command3_Click()
FullAccession = Me.SiteAccession
Debug.Print FullAccession
DoCmd.OpenForm "F_OpenMenu2"
End Sub

Then have F_OpenMenu2 on_Load event assign this value as your control default value:

Private Sub Form_Load()
'ModuleNmae.FullAccession is a global variable, assigned a value by the menu Form
...
me.ControlNmae.DefaultValue = ModuleNmae.FullAccession 
...
End Sub

NOTE: if me.ControlNmae is bound to a DB field, the value (or default value) may overcome Control Default value. In that case you can Consider assigning ModuleNmae.FullAccession to me.ControlNmae.Value.

Hope I am of any help

 

[matt beamish]

thanks very much - this has given me some good ideas to work with and has improved my understanding

 

[marlan]

I skipped it? this link could help you: http://www.fmsinc.com/tpapers/primer/