Changeable Default

  • Thread starter Thread starter melinda.chase
  • Start date Start date
M

melinda.chase

Hello,
I have a database that is designed to hold all of the correspondance
information for any given project. What I'd like to do, is have the
users type in job specific information in a form when they start the
correspondance database for a project. Then, the job info will
automatically enter itself on the RE: line of the letters and faxes.
How can I set up a form field to pull from a table other than the
source table. I'd rather not have to use subforms. I tried the
following VB in the OnOpen command.

Me.ProjInfo1 = Tables.ProjectInfo.ProjInfo1

But it gets hung up on Tables. Any ideas?
Thanks!
Melinda
 
If I am understanding the question correctly, what you want to do is have the
vaule of Me.ProjInfo1 set to a value in the field ProjInfo1 of table
ProjectInfo.
If this is correct, then you can use the Before Update event of the contorl
the user enters the project information in to do that. What I don't know is
what field in the table you will need to match the entered information with.
For example purposes, we will use ProjInfo1. You can change that to be
whatever it needs to be:

Dim varProjInfo as Variant

varProjInfo = DLookup("[ProjInfo1]","ProjectInfo","[ProjInfo1] = '" & _
Me.ControlUserTypedInto & "'"
If IsNull(varProjInfo) Then
MsgBox "Value Not Found"
Cancel = True
Else
Me.ProjInfo1 = varProjInfo
End If
 
Yes, I want Me.ProjInfo1 set to the value in ProjInfo1 of Table Project
Info.
But, I want it to be there when the user opens the form. I don't want
them to have to type in any job related information. Each project is
going to have its own correspondance database, so there is no reason to
be looking up the project info. So, what I want is to have the fields
acting like something is typed in the DefaultValue box of their
properties, but have it pull from the table instead. This way users
don't have to mess with Default Values when they copy this db between
projects.
I hope that clears things up.
Melinda
 
My first observation is that you really shouldn't have to have separate
databases for each project. This is inadequate design. It increases the
work necessary to maintain all those versions. You post is obvious evidence
of this.
It would make more sense to have a field in your tables that identifies the
project, and filters the data based on the project the user is working in.
Your opening form should allow them to select a proect, then the rest is easy.
However, the solution for this would be in the Open event of the form to
select the default value based on the name of the current database. You can
find that with:

strCurrDatabase = CurrentDB.Name
strCurrDtabase = Right(strCurrDatabase,len(strCurrDatabase) - instrrev _
(strCurrDatabase,"\"))

Now you have just the database name. Using this, you can set your default
value.

Hope it works for you.
But, please consider my suggestion. You will save yourself a whole lot of
work everytime you have to make a change or debug a problem.
 
I had considered putting a back end on the network that stored all of
our correspondance and a front end that had a filter to show the
correspondence for each project. My only concern is that we handle
literally 10 to 20 projects at any given time. Allowing users to
choose a project from an opening form could get cumbersome. Also, once
a copy of the db is in a project's folder, I wouldn't be updating that
particular copy anymore. Our projects have a tendency to last only a
few months.
Perhaps I will do this though. Then, when users add a new form, I can
have a combo box to select what project they are working on and have it
fill in the relevant project info.
Thanks for your thoughts and push in the right direction!
Melinda
 
Back
Top