filter with variable

G

Guest

What changes to the following code so it filters using the set variable.
I keep getting asked to input data for the Employer field.
The first button works so i know the variable is set.
Anyone got any ideas.

Cheers

Module code:

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 ('Comapny B Ltd', 'Company C Ltd')"
End Sub
............................................................................................................
1st Form command button:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

DoCmd.Close

Call Set_A

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
....................................................................................................................
2nd Form command button:

Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

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

If Len(CurrentCompany & "") > 0 Then
stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, , , stLinkCriteria
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
.....................................................................................................
 
G

Guest

Thanks to anyone who has been thinking about this one
but I may have sorted it.
Heres the code if your interested.

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


tim said:
What changes to the following code so it filters using the set variable.
I keep getting asked to input data for the Employer field.
The first button works so i know the variable is set.
Anyone got any ideas.

Cheers

Module code:

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 ('Comapny B Ltd', 'Company C Ltd')"
End Sub
..........................................................................................................
1st Form command button:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

DoCmd.Close

Call Set_A

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
..................................................................................................................
2nd Form command button:

Private Sub Command105_Click()

On Error GoTo Err_Command105_Click

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

If Len(CurrentCompany & "") > 0 Then
stDocName = "frm_Misc_Records"
DoCmd.OpenForm stDocName, , , stLinkCriteria
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
...................................................................................................
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

search command 2
Code Only works first time! 2
Trying to get Current User info only 2
transfer a field using button 1
popup issue if no existing record 1
Open a form and close one 1
Should I change this code? 3
Form button opens blank form 10

Top