Company Name

  • Thread starter Thread starter Pdehner
  • Start date Start date
P

Pdehner

I am beginning to start a database which can be used in
various company divisions. I want the division name to
display on all forms and reports without having to change
them all when this is used in another division.
I created a table called company information, there will
numerous other tables, orders, inventory etc... I want to
be able to call the company information for each form,
query or report. How should I go about doing this??
Any help is appreciated. Thought I should ask before I
got too far.

Thanks
 
One way is an unbound text box on each form with it's
control source filled using a DLookup() function to your
company info table.

You could do something similar in code at each Form's OnOpen
event to add the division name to each Form's Caption
property. Or I should say concatenate it to whatever you've
put in the caption already.

Or create a template Form with the name in a label and then
copy it and use as the basis for each new form you create.
 
Use the following function to test if the Division name has already been
entered. If not, the function returns a zero-length string, in which case,
you use an InputBox to get the Division name from the user. Then call the
function again, only this time, supplying the Division name as a parameter.

Whenevr you want to use the Division name, for example, to specify the
form's Caption property, use it like so:
Me.Caption = GetSetDivision()

If this approach doesn't suit, let me know, because I can give you a class
that gets a truckload of information from the server. Often administrators
store a Division name (or something that you can use as one), which can be
retrieved from the server.

Public Function GetSetProp(vProperty As Variant, _
Optional vValue As Variant = "") As Variant
Dim prp As Property
Dim db As Database

On Error Resume Next

If Len(Trim(vProperty)) = 0 Then GoTo Proc_Exit

Set db = CurrentDb

'Attempt to get or set the property's value.
'If it doesn't exist, an error 3270 "Property not found" will occur.
If vValue = "" Then
GetSetProp = db.Containers(1)(3).Properties(vProperty)
Else
db.Containers(1)(3).Properties(vProperty) = vValue
End If

If Err <> 0 Then
'The property doesn't exist.
On Error GoTo Proc_Err

If vValue = "" Then
'Since we're not setting the property, return nothing.
GetSetProp = ""
GoTo Proc_Exit
Else
'The property doesn't exist, create it.
Set prp = db.CreateProperty(vProperty, dbText, vValue)
'Debug.Print db.CreateProperty(vProperty, dbText, vValue).Name

'Append it to the collection
db.Containers(1)(3).Properties.Append prp

'Now read the property
GetSetProp = db.Containers(1)(3).Properties(vProperty)
End If
End If

Proc_Exit:
'Clean up
On Error Resume Next
Set prp = Nothing
Set db = Nothing
Exit Function

Proc_Err:
DoCmd.Beep
MsgBox "Error " & Err.Number & vbCrLf & vbCrLf & _
Err.Description, vbOKOnly + vbExclamation, _
"Could not get/set property"
Resume Proc_Exit
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
I don't understand how I can use dlookup() when there is
only one record in the table and it doesn't relate to the
other tables,

That's what DLookup() does. DLookup() is ideal for getting
the contents of a single field out of a table that is
unrelated to the main stream of data.
and I really don't want to add fields and update them with
Click to expand...
division information.

You said you had a table of company info. I assumed that
would include divisional info. My mistake, sorry.

At the risk of stating the obvious, if you want the division
name to display on forms and so on you've got to get it from
somewhere; a table, the user, a server(as Graham suggested)
whatever.

--
Nick Coe (UK)
www.alphacos.co.uk

---

message
I don't want to use a template as when I create reports it
Click to expand...
will be about 70 reports that will have to be changed when
it goes to a different division.
I don't understand how I can use dlookup() when there is
Click to expand...
only one record in the table and it doesn't relate to the
other tables, and I really don't want to add fields and
update them with division information. Any Suggestions?
 
Back
Top