By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Showing only records based on a specific value

P: 9
Hello-
I need a little help. I don't think this is too difficult to do, however I have been away from any access development for some time and can't seem to figure it out. I've tried queries and filtering......I give!

FIELD: Typeproject (Listbox)
VALUES: CSA, TASKERS, CONFERENCES, SIGNATURE

For example I want the form called TASKERS - when it opens - to show only records in which the value for the field "typeproject" has been designated as "TASKERS". This is a form which will be used to search for records for editing, etc.

Any help would be greatly appreciated.

THANKS

SV
Mar 13 '07 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,619
Hello-
I need a little help. I don't think this is too difficult to do, however I have been away from any access development for some time and can't seem to figure it out. I've tried queries and filtering......I give!

FIELD: Typeproject (Listbox)
VALUES: CSA, TASKERS, CONFERENCES, SIGNATURE

For example I want the form called TASKERS - when it opens - to show only records in which the value for the field "typeproject" has been designated as "TASKERS". This is a form which will be used to search for records for editing, etc.

Any help would be greatly appreciated.

THANKS

SV
Assumption:
__1. List Box Name: lstTypeProject
Expand|Select|Wrap|Line Numbers
  1. 'To open Form TASKERS and display only Records with the Project Type selected in the List Box (lstTypeProject:
  2. DoCmd.OpenForm "TASKERS", acNormal, , "[typeproject]='" & Me![lstTypeProject] & "'"
Mar 13 '07 #2

P: 9
Thanks...

I tried what you sent for a code but I am doing something wrong. Here is what I have:

The form name is: frmTaskers_Suspense-Edit/Search
The field Name is: Project List
The field Control Source is: ProjectID

I put the code in the form's property-
Event: On Enter - event procedure:

When I click on the command button to open the form I get the following error message:
Compile Error:
Syntex Error

Private Sub Form_Open(Cancel As Integer) THIS IS HILIGHED IN YELLOW
'To open form and display only records with the project type selected in the list box:
DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "Taskers='" & Me! [Project List]& "'" THIS SHOWS IN RED

I checked HELP but I don't have near enough coding experience to understand what the problem is.

Got any ideas?

Thanks.

SV
Mar 14 '07 #3

ADezii
Expert 5K+
P: 8,619
Thanks...

I tried what you sent for a code but I am doing something wrong. Here is what I have:

The form name is: frmTaskers_Suspense-Edit/Search
The field Name is: Project List
The field Control Source is: ProjectID

I put the code in the form's property-
Event: On Enter - event procedure:

When I click on the command button to open the form I get the following error message:
Compile Error:
Syntex Error

Private Sub Form_Open(Cancel As Integer) THIS IS HILIGHED IN YELLOW
'To open form and display only records with the project type selected in the list box:
DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "Taskers='" & Me! [Project List]& "'" THIS SHOWS IN RED

I checked HELP but I don't have near enough coding experience to understand what the problem is.

Got any ideas?

Thanks.

SV
Try:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]  = '" & Me![Typeproject] & "'"
Mar 14 '07 #4

P: 9
Try:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]  = '" & Me![Typeproject] & "'"

Thanks again---

I tried that and it gave me the following:

DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]='" & Me![Project List] & "'"

Error Message:
Compile Error: Expected: end of statement ((It had [typeproject] hilighted)

then -
Run-time error '2465'
MS Access can't find the field "TypeProject' referred to in your expression.

I changed it to the actual field NAME: Project List

No error messages, but now when I open that form it asks for a parameter for Project List- When I ENTER Taskers it brings up a blank record.

Why is it asking for a paramenter? I want it to just automatically open all records already designated as Taskers when that form is opened. I don't want the operators to have to enter a parameter. Did I confuse you on my question or am I doing something wrong?

Thanks.

SV
Mar 14 '07 #5

ADezii
Expert 5K+
P: 8,619
Thanks again---

I tried that and it gave me the following:

DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]='" & Me![Project List] & "'"

Error Message:
Compile Error: Expected: end of statement ((It had [typeproject] hilighted)

then -
Run-time error '2465'
MS Access can't find the field "TypeProject' referred to in your expression.

I changed it to the actual field NAME: Project List

No error messages, but now when I open that form it asks for a parameter for Project List- When I ENTER Taskers it brings up a blank record.

Why is it asking for a paramenter? I want it to just automatically open all records already designated as Taskers when that form is opened. I don't want the operators to have to enter a parameter. Did I confuse you on my question or am I doing something wrong?

Thanks.

SV
I may have been the one that was confused:
DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List] = 'Taskers'"
Mar 14 '07 #6

P: 9
I may have been the one that was confused:
DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List] = 'Taskers'"
Just can't seem to get this to work. No error messages and stills asks for a parameter and even if I do enter the value "Taskers" it brings up all records.

I have this code in the form's properties
EVENT: On Open - [Event Procedure]

This is what I have and what shows (I copied and pasted):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. 'To open form and display only records with the project type selected in the list box:
  3. DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]='Taskers'"
I checked several times on this info and it is correct:
Field NAME: Project List
Control Source: ProjectID

In the properties of the form:
Filter: [Project List]='Taskers'

Don't have a clue what the problem is.
Mar 14 '07 #7

P: 9
Just can't seem to get this to work. No error messages and stills asks for a parameter and even if I do enter the value "Taskers" it brings up all records.

I have this code in the form's properties
EVENT: On Open - [Event Procedure]

This is what I have and what shows (I copied and pasted):

Private Sub Form_Open(Cancel As Integer)
'To open form and display only records with the project type selected in the list box:
DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]='Taskers'"

I checked several times on this info and it is correct:
Field NAME: Project List
Control Source: ProjectID

In the properties of the form:
Filter: [Project List]='Taskers'

Don't have a clue what the problem is.

Anyone out there got any ideas why this doesn't work? This is the only thing keeping me from finishing the database and they have been waiting for this for a long time and would like to have done - you know how it is - months ago. Thanks.
Mar 15 '07 #8

NeoPa
Expert Mod 15k+
P: 31,347
Does (Example Filtering on a Form.) give you help with your problem?
Mar 15 '07 #9

P: 9
Does (Example Filtering on a Form.) give you help with your problem?
No it does not. I've played around with filtering also. Same results. I do not have enough experience to understand the coding to be able to fix. I have not been able to get this fixed.
Mar 20 '07 #10

NeoPa
Expert Mod 15k+
P: 31,347
Just can't seem to get this to work. No error messages and stills asks for a parameter and even if I do enter the value "Taskers" it brings up all records.

I have this code in the form's properties
EVENT: On Open - [Event Procedure]

This is what I have and what shows (I copied and pasted):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. 'To open form and display only records with the project type selected in the list box:
  3. DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[Project List]='Taskers'"
I checked several times on this info and it is correct:
Field NAME: Project List
Control Source: ProjectID

In the properties of the form:
Filter: [Project List]='Taskers'

Don't have a clue what the problem is.
OK.
Why is your Filter set to :
Expand|Select|Wrap|Line Numbers
  1. [Project List]='Taskers'
I would have thought it should be something like :
Expand|Select|Wrap|Line Numbers
  1. [TypeOfProject]='Taskers'
Mar 20 '07 #11

Denburt
Expert 100+
P: 1,356
Sj you are real close but you would use the control source then the field name.

If I am not mistaken:
DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[YOUR CONTROL Source Here]='Taskers'"

You stated that your Control Source for the Project List field was ProjectID

If this is the case you may be dealing with a number field which would change the code and you would need to look in the ProjectTable for the actual ID numbers.

If the control source projectID is a number (In the originating table on a query or form it may appear as text due to lookup features in Access)

A numbered Example would be:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[ProjectID]=1
  2.  
Or more usefull:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTaskers_Suspense-Edit/Search", acNormal, , "[ProjectID]=" & Me![Project List]
  2.  
Mar 20 '07 #12

NeoPa
Expert Mod 15k+
P: 31,347
OK.
Why is your Filter set to :
Expand|Select|Wrap|Line Numbers
  1. [Project List]='Taskers'
I would have thought it should be something like :
Expand|Select|Wrap|Line Numbers
  1. [TypeOfProject]='Taskers'
Sorry, misread your first post - that should have read :
Expand|Select|Wrap|Line Numbers
  1. [TypeProject]='Taskers'
If this doesn't work for you then perhaps some table MetaData might help to clear up exactly what should match what.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Mar 20 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.