applying an automatic filter to a form

[tazibc]

Pls pls help anyone this is killing me:-

I have created a login form using the code below:-

Private Sub Command5_Click()
Username.SetFocus
If Username = "user1" And Password = "user1" Then
MsgBox "Access Granted"
DoCmd.Close
DoCmd.OpenForm "Crystal Compliance Database"
Else
MsgBox "Please Enter Correct Username or Password"
End If
End Sub

so the login form will only ever open the form "Crystal Compliance Database". The problem is that I have a feild in the form called "service centre / satellite" this lists the location of a depo i.e a depo could be in Basingstoke. Is there anyway that when the user logs in this will open the form "crystal compliance database" and shows data on the form for depo basingstoke.

help much appreciated

 

[HarleyQuinn]

Yep, the fourth parameter of the OpenForm method (WhereCondition) allows you to pass a valid WHERE statement to the form you're opening.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[tazibc]

Thanks Harley:-

but could you tell me what the code would look like I am new to access. I mean where would it fit into this code:-

Private Sub Command5_Click()
Username.SetFocus
If Username = "user1" And Password = "user1" Then
MsgBox "Access Granted"
DoCmd.Close
DoCmd.OpenForm "Crystal Compliance Database"
Else
MsgBox "Please Enter Correct Username or Password"
End If
End Sub


using my feild "Service centre/ Satellite" to = basingstoke on the form.

pls pls help

 

[HarleyQuinn]

Is the Crystal Compliance Database form bound to a table or is it just your one Service centre/ Satellite field that's bound to a table?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[tazibc]

The whole form is bound to a table

 

[HarleyQuinn]

That helps, something like:

DoCmd.OpenForm "Crystal Compliance Database",,,"WHERE [YourField] = 'Your Criteria for a Basingstoke Record'"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[PHV]

Harley, FYI, don't put the WHERE keyword in the 4th parameter ...

 

[HarleyQuinn]

Yep, of course.

Cheers PHV. It's been a long day mate.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[tazibc]

so guys would my code look like this:-

Private Sub Command5_Click()
Username.SetFocus
If Username = "user1" And Password = "user1" Then
MsgBox "Access Granted"
DoCmd.Close
DoCmd.OpenForm "Crystal Compliance Database",,,"WHERE [YourField] = ' Basingstoke'"
Else
MsgBox "Please Enter Correct Username or Password"
End If
End Sub

 

[HarleyQuinn]

Take the WHERE keyword out (as PHV mentioned, sorry that was my mistake) and replace [YourField] with the name of the field in your table you want to query and it should be good to go.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[tazibc]

does my feild have to be in the square brackets ?? for example:-

",,," [Service Centre / Satellite] = ' Basingstoke'

 

[tazibc]

Hi Harley you've been a complete star and thanks for your help but when I add the filter to the query:-

Private Sub Command5_Click()
Username.SetFocus
If Username = "user1" And Password = "user1" Then
MsgBox "Access Granted"
DoCmd.Close
DoCmd.OpenForm "Crystal Compliance Database", , , "[Service Centre / Satellite] = ' Basingstoke'"
Else
MsgBox "Please Enter Correct Username or Password"
End If
End Sub

its brings back no records in my form for basingstoke but i know there are 30 odd records for basingstoke am I doing something wrong.......

 

[tazibc]

Thanks Harley & PHV your both Stars in my book I got it to work thanks so much........

 

[HarleyQuinn]

Glad to help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com