constant variable

[Guest]

I have a main page Form with command buttons to show either Company A details
or to show both Company B & C details.
(just used event procedure and ran a filter macro for the Company name)
This event procedure loads a general details form with the filtered results
Showing ie it shows just Company A details. There are several command
buttons on this form and on every other form so that I can navigate to the
other forms that show different details.
When I select a button I want the loaded form to show the relevant Company x
details for the current record.

Ie. I select Company A details, a General Form loads showing the details of
the first person in that company. Lets say I navigate through the records to
record 5 and this pertains to a ‘John Doe’s details. Now if I select a button
to show me his address details then that’s what I want to see, not record 1
address.
So I think I need a way to set a common variable when I select one of the
‘Company’ buttons and then use the variable name in a macro filter ?
I dont want 2 db's because of queries that need to list people from all
companies
Please can anyone help

Thanks

 

[Guest]

Looks like this is too hard for you to solve.
So lets try this.
DoCmd.RunMacro "SetGlobalVar"

now if the global variable is called WhichCompany and there are
3 values it can be set to ie CompanyOne , Two & Three what would be the code
for the module and how would you code a command button to set the value of
the variable ? Also how would you pass the variable to a filter macro?

 

[Dirk Goldgar]

Looks like this is too hard for you to solve.

Look like you're way too impatient. Answers to newsgroup questions come
from volunteers who have jobs and "real lives" -- though sometimes you'd
wonder -- so it may be a while before the person who has both the time
and the knowledge to answer your question happens to see it.

So lets try this.
DoCmd.RunMacro "SetGlobalVar"

now if the global variable is called WhichCompany and there are
3 values it can be set to ie CompanyOne , Two & Three what would be
the code for the module and how would you code a command button to
set the value of the variable ? Also how would you pass the variable
to a filter macro?

Usually this sort of filtering is done by the code for the command
button that opens the form. It filters the form by using the
WhereCondition argument of the DoCmd.OpenForm method:

DoCmd.OpenForm "MyForm",
WhereCondition:="Company=" & _
Chr(34) & WhichCompany & Chr(34)

The above assumes that Company is a text field; for a numeric company
ID, drop the Chr(34)s.

On the other hand, if you want to do this in the queries by reference to
a global variable, you'll have to write a function (or user-defined
property) to return the value of that variable. For example,

--------- in a standard module --------------

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

--------- in a query -----------
SELECT * FROM SomeTable
WHERE Company = WhichCompany()

 

[Guest]

Thanks for replying Dirk,

appologies for being impatient.
I understand some of your reply but cannot
see how the variable has been set.
I can assign a filter to the initial command button
but its through a macro - i cannot work out how
to use two filters based on one field,

ie employer="'Company2 of 'Company3'" does not work
so used macro filter
:- [Employer]="Company2" or [Employer]="Company3".

I know DoCmd.OpenForm stDocName, , , stLinkCriteria is the general format
but unsure what code sets the variable ? and what replaces the
stLinkCriteria ?
To set the variable is it WhichCompany = "'Company2' or ' Company3'" -
(again is this correct format for a double query ?)

Thanks

Tim

 

[Douglas J Steele]

Just as you needed to repeat [Employer] = in the macro filter, so too must
you in setting stLinkCriteria:

stLinkCriteria = "[Employer] = 'Company2' Or [Employer] = 'Company3'"

although you could also use

stLinkCriteria = "[Employer] IN ('Company2', 'Company3')"

If you want to be able to type multiple fields in WhichCompany, you could
use WhichCompany = "'Company2', ' Company3'" and change what Dirk gave you
to

DoCmd.OpenForm "MyForm",
WhereCondition:="Company IN (" & _
WhichCompany & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying Dirk,

appologies for being impatient.
I understand some of your reply but cannot
see how the variable has been set.
I can assign a filter to the initial command button
but its through a macro - i cannot work out how
to use two filters based on one field,

ie employer="'Company2 of 'Company3'" does not work
so used macro filter
:- [Employer]="Company2" or [Employer]="Company3".

I know DoCmd.OpenForm stDocName, , , stLinkCriteria is the general format
but unsure what code sets the variable ? and what replaces the
stLinkCriteria ?
To set the variable is it WhichCompany = "'Company2' or ' Company3'" -
(again is this correct format for a double query ?)

Thanks

Tim

Look like you're way too impatient. Answers to newsgroup questions come
from volunteers who have jobs and "real lives" -- though sometimes you'd
wonder -- so it may be a while before the person who has both the time
and the knowledge to answer your question happens to see it.


Usually this sort of filtering is done by the code for the command
button that opens the form. It filters the form by using the
WhereCondition argument of the DoCmd.OpenForm method:

DoCmd.OpenForm "MyForm",
WhereCondition:="Company=" & _
Chr(34) & WhichCompany & Chr(34)

The above assumes that Company is a text field; for a numeric company
ID, drop the Chr(34)s.

On the other hand, if you want to do this in the queries by reference to
a global variable, you'll have to write a function (or user-defined
property) to return the value of that variable. For example,

--------- in a standard module --------------

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

--------- in a query -----------
SELECT * FROM SomeTable
WHERE Company = WhichCompany()


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Thanks Douglas,

Worked for the first form. Now that I have chosen which company
records i want to view and the other form has opened I now have several
command buttons that load up other forms (they show things like next of kin
details, bank details etc).
If I create a module named mod_WhichCompany
with the following code:-
Option Compare Database

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function
.................
What code would I insert into the first button to set the variable.
And in the 'sub' forms can i just replace the stlinkCriteria with the
variable name

Cheers

Tim

Just as you needed to repeat [Employer] = in the macro filter, so too must
you in setting stLinkCriteria:

stLinkCriteria = "[Employer] = 'Company2' Or [Employer] = 'Company3'"

although you could also use

stLinkCriteria = "[Employer] IN ('Company2', 'Company3')"

If you want to be able to type multiple fields in WhichCompany, you could
use WhichCompany = "'Company2', ' Company3'" and change what Dirk gave you
to

DoCmd.OpenForm "MyForm",
WhereCondition:="Company IN (" & _
WhichCompany & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying Dirk,

appologies for being impatient.
I understand some of your reply but cannot
see how the variable has been set.
I can assign a filter to the initial command button
but its through a macro - i cannot work out how
to use two filters based on one field,

ie employer="'Company2 of 'Company3'" does not work
so used macro filter
:- [Employer]="Company2" or [Employer]="Company3".

I know DoCmd.OpenForm stDocName, , , stLinkCriteria is the general format
but unsure what code sets the variable ? and what replaces the
stLinkCriteria ?
To set the variable is it WhichCompany = "'Company2' or ' Company3'" -
(again is this correct format for a double query ?)

Thanks

Tim

Looks like this is too hard for you to solve.

Look like you're way too impatient. Answers to newsgroup questions come
from volunteers who have jobs and "real lives" -- though sometimes you'd
wonder -- so it may be a while before the person who has both the time
and the knowledge to answer your question happens to see it.

So lets try this.
DoCmd.RunMacro "SetGlobalVar"

now if the global variable is called WhichCompany and there are
3 values it can be set to ie CompanyOne , Two & Three what would be
the code for the module and how would you code a command button to
set the value of the variable ? Also how would you pass the variable
to a filter macro?

Usually this sort of filtering is done by the code for the command
button that opens the form. It filters the form by using the
WhereCondition argument of the DoCmd.OpenForm method:

DoCmd.OpenForm "MyForm",
WhereCondition:="Company=" & _
Chr(34) & WhichCompany & Chr(34)

The above assumes that Company is a text field; for a numeric company
ID, drop the Chr(34)s.

On the other hand, if you want to do this in the queries by reference to
a global variable, you'll have to write a function (or user-defined
property) to return the value of that variable. For example,

--------- in a standard module --------------

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

--------- in a query -----------
SELECT * FROM SomeTable
WHERE Company = WhichCompany()


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Douglas,

Worked for the first form. Now that I have chosen which company
records i want to view and the other form has opened I now have
several command buttons that load up other forms (they show things
like next of kin details, bank details etc).
If I create a module named mod_WhichCompany
with the following code:-
Option Compare Database

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function
................
What code would I insert into the first button to set the variable.

CurrentCompany = "A" ' for example

And in the 'sub' forms can i just replace the stlinkCriteria with the
variable name

No, you have to refer to the function WhichCompany(). Like this:

DoCmd.OpenForm "MyForm",
WhereCondition:="Company=" & _
Chr(34) & WhichCompany & Chr(34)

Or else, a form's recordsource query could refer to the WhichCompany()
function in its WHERE clause, as I showed in my first post.

 

[Guest]

Thanks Dirk,

I cannot get the code to work. It prompts for a manual input of the company
name.
(The field for the company name is 'Employer').
Should the module run automatically when the database is loaded ?
Is there a way to check that the variable has been set ?
The company names are in the format Company One Ltd, ....Two Ltd etc,
would this have any relevance ?

code for 'second button'

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

stDocName = "frm_Other_Records"
DoCmd.OpenForm stDocName, WhereCondition:="Employer=" & Chr(34) &
WhichCompany & Chr(34)

DoCmd.GoToRecord , , acGoTo, recnum

DoCmd.Close acForm, "frm_Employee_Form", acSaveNo


is this correct - i'm not sure what the character code does ?

Cheers

Tim

 

[Douglas J Steele]

To check whether the variable has been set, you can check its length.

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany) > 0 Then
stDocName = "frm_Other_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No company provided"
End If

You haven't declared WhichCompany or recnum in that code snippet, nor have
you assigned them values. Iif there's a chance that the variable is a
variant, which can hold Null values, you're best off appending a zero-length
string to the variable, to handle the possibility that the value is Null:

If Len(WhichCompany & "") > 0 Then

or

If Len(WhichCompany & vbNullString) > 0 Then


Chr(34) is another way of specifying ". If you wanted to represent a double
quote in VBA and didn't want to use the Chr function, you'd have to use
"""". It is possible to use a single quote rather than a double quote:

DoCmd.OpenForm stDocName, _
WhereCondition:="Employer='" & WhichCompany & "'"

However, you'll run into problems if the company name includes an
apostrophe, such as O'Toole's.

 

[Guest]

Thanks for the help Douglas, however I need some more.
I understand the code that checks the string length and
either opens another form or displays the error message.
I dont understand the declared bit. Do you mean I should
use the Dim statement ? I thought the first command button
code and module would take care of things.

Thanks

Tim

 

[Douglas J Steele]

I was simply commenting that I couldn't see your declarations for those two
variables.

If you've declared them in an event for the first command button, that
declaration is unlikely to apply to any other events.

http://msdn2.microsoft.com/en-us/library/1t0wsc67.aspx explains about Scope
of Variables. (While it's for VB, it applies equally to VBA)

 

[Guest]

thanks for replying Douglas,

i have the follwing code for a command button on my main front
page.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String
Dim CurrentCompany As String

stLinkCriteria = "[Employer] IN ('Company Two Ltd', 'Company Three Ltd')"
CurrentCompany = "[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

stDocName = "frm_Employee_Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command2_Click:
Exit Sub


Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

; I have another button on a secondary form

Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany & "") > 0 Then
stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No Company Name"
End If


Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub

;and a module (mod_WhatCompany)

Option Compare Database

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

;thats all. I've tried to follow everones post but cannot
get the setting of any variable to work.
I am probably missing a chunk of code or something.
Also whats the underscore _ thats before the wherecondition for ?

thanks for the link, i shall have a look.

Cheers T

 

[Douglas J. Steele]

You're setting CurrentCompany to

"[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

(which isn't correct: the double quote atd Company Three Ltd needs to be a
single quote)

That means that you don't want to set WhereCondition as:

WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)

You're going to end up with WhereCondition begin equal to:

Employer=""[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

All you want is:

WhereCondition = WhichCompany()


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

thanks for replying Douglas,

i have the follwing code for a command button on my main front
page.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String
Dim CurrentCompany As String

stLinkCriteria = "[Employer] IN ('Company Two Ltd', 'Company Three
Ltd')"
CurrentCompany = "[Employer] IN ('Company Two Ltd', 'Company Three
Ltd'')"

stDocName = "frm_Employee_Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command2_Click:
Exit Sub


Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

; I have another button on a secondary form

Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany & "") > 0 Then
stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No Company Name"
End If


Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub

;and a module (mod_WhatCompany)

Option Compare Database

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

;thats all. I've tried to follow everones post but cannot
get the setting of any variable to work.
I am probably missing a chunk of code or something.
Also whats the underscore _ thats before the wherecondition for ?

thanks for the link, i shall have a look.

Cheers T

 

[Guest]

Hi Douglas,

thanks for the help. I changed the double quote
and the WhereCondition, but getting the
'No Company Name' error, so looks like the
variable has not been set and how to set it
is my problem. Any ideas on how to do this ?

Thanks

Tim

You're setting CurrentCompany to

"[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

(which isn't correct: the double quote atd Company Three Ltd needs to be a
single quote)

That means that you don't want to set WhereCondition as:

WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)

You're going to end up with WhereCondition begin equal to:

Employer=""[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

All you want is:

WhereCondition = WhichCompany()


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

thanks for replying Douglas,

i have the follwing code for a command button on my main front
page.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String
Dim CurrentCompany As String

stLinkCriteria = "[Employer] IN ('Company Two Ltd', 'Company Three
Ltd')"
CurrentCompany = "[Employer] IN ('Company Two Ltd', 'Company Three
Ltd'')"

stDocName = "frm_Employee_Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command2_Click:
Exit Sub


Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

; I have another button on a secondary form

Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany & "") > 0 Then
stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No Company Name"
End If


Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub

;and a module (mod_WhatCompany)

Option Compare Database

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

;thats all. I've tried to follow everones post but cannot
get the setting of any variable to work.
I am probably missing a chunk of code or something.
Also whats the underscore _ thats before the wherecondition for ?

thanks for the link, i shall have a look.

Cheers T

I was simply commenting that I couldn't see your declarations for those
two
variables.

If you've declared them in an event for the first command button, that
declaration is unlikely to apply to any other events.

http://msdn2.microsoft.com/en-us/library/1t0wsc67.aspx explains about
Scope
of Variables. (While it's for VB, it applies equally to VBA)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the help Douglas, however I need some more.
I understand the code that checks the string length and
either opens another form or displays the error message.
I dont understand the declared bit. Do you mean I should
use the Dim statement ? I thought the first command button
code and module would take care of things.

Thanks

Tim

:

To check whether the variable has been set, you can check its length.

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany) > 0 Then
stDocName = "frm_Other_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany &
Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No company provided"
End If

You haven't declared WhichCompany or recnum in that code snippet, nor
have
you assigned them values. Iif there's a chance that the variable is a
variant, which can hold Null values, you're best off appending a
zero-length
string to the variable, to handle the possibility that the value is
Null:

If Len(WhichCompany & "") > 0 Then

or

If Len(WhichCompany & vbNullString) > 0 Then


Chr(34) is another way of specifying ". If you wanted to represent a
double
quote in VBA and didn't want to use the Chr function, you'd have to
use
"""". It is possible to use a single quote rather than a double
quote:

DoCmd.OpenForm stDocName, _
WhereCondition:="Employer='" & WhichCompany & "'"

However, you'll run into problems if the company name includes an
apostrophe, such as O'Toole's.

 

[Guest]

I think i've sorted it Douglas os thanks for your help.
This is the code I used for the module & buttons.

Option Compare Database

Global CurrentCompany As String
Option Explicit
Public Sub Set_A()
CurrentCompany = "[Employer] IN ('Company A Ltd')"
End Sub
Public Sub Set_BC()
CurrentCompany = "[Employer] IN ('Company B Ltd', 'Company C Ltd')"
End Sub
………………………………………………………………….
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

DoCmd.Close

Call Set_A ; (for other button Call Set_BC)

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = CurrentCompany

stDocName = "frm_Employee_Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub
………………………………………………………………….
Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = CurrentCompany
recnum = Me.CurrentRecord
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo

stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acGoTo, recnum

Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub

Hi Douglas,

thanks for the help. I changed the double quote
and the WhereCondition, but getting the
'No Company Name' error, so looks like the
variable has not been set and how to set it
is my problem. Any ideas on how to do this ?

Thanks

Tim

You're setting CurrentCompany to

"[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

(which isn't correct: the double quote atd Company Three Ltd needs to be a
single quote)

That means that you don't want to set WhereCondition as:

WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)

You're going to end up with WhereCondition begin equal to:

Employer=""[Employer] IN ('Company Two Ltd', 'Company Three Ltd'')"

All you want is:

WhereCondition = WhichCompany()


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

thanks for replying Douglas,

i have the follwing code for a command button on my main front
page.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String
Dim CurrentCompany As String

stLinkCriteria = "[Employer] IN ('Company Two Ltd', 'Company Three
Ltd')"
CurrentCompany = "[Employer] IN ('Company Two Ltd', 'Company Three
Ltd'')"

stDocName = "frm_Employee_Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command2_Click:
Exit Sub


Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

; I have another button on a secondary form

Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany & "") > 0 Then
stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany & Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No Company Name"
End If


Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub

;and a module (mod_WhatCompany)

Option Compare Database

Public CurrentCompany As String

Public Function WhichCompany() As String
WhichCompany = CurrentCompany
End Function

;thats all. I've tried to follow everones post but cannot
get the setting of any variable to work.
I am probably missing a chunk of code or something.
Also whats the underscore _ thats before the wherecondition for ?

thanks for the link, i shall have a look.

Cheers T


:

I was simply commenting that I couldn't see your declarations for those
two
variables.

If you've declared them in an event for the first command button, that
declaration is unlikely to apply to any other events.

http://msdn2.microsoft.com/en-us/library/1t0wsc67.aspx explains about
Scope
of Variables. (While it's for VB, it applies equally to VBA)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the help Douglas, however I need some more.
I understand the code that checks the string length and
either opens another form or displays the error message.
I dont understand the declared bit. Do you mean I should
use the Dim statement ? I thought the first command button
code and module would take care of things.

Thanks

Tim

:

To check whether the variable has been set, you can check its length.

Dim stDocName As String
Dim stLinkCriteria As String
recnum = Me.CurrentRecord

If Len(WhichCompany) > 0 Then
stDocName = "frm_Other_Records"
DoCmd.OpenForm stDocName, _
WhereCondition:="Employer=" & Chr(34) & WhichCompany &
Chr(34)
DoCmd.GoToRecord , , acGoTo, recnum
DoCmd.Close acForm, "frm_Employee_Form", acSaveNo
Else
MsgBox "No company provided"
End If

You haven't declared WhichCompany or recnum in that code snippet, nor
have
you assigned them values. Iif there's a chance that the variable is a
variant, which can hold Null values, you're best off appending a
zero-length
string to the variable, to handle the possibility that the value is
Null:

If Len(WhichCompany & "") > 0 Then

or

If Len(WhichCompany & vbNullString) > 0 Then


Chr(34) is another way of specifying ". If you wanted to represent a
double
quote in VBA and didn't want to use the Chr function, you'd have to
use
"""". It is possible to use a single quote rather than a double
quote:

DoCmd.OpenForm stDocName, _
WhereCondition:="Employer='" & WhichCompany & "'"

However, you'll run into problems if the company name includes an
apostrophe, such as O'Toole's.