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

Filtering form fields that are bound

P: 24
Hi

I want to filter records on a continuous form(FRM_SUPPT) by one of the fields, SW_ID.

FRM_SUPPT has a text box ( bound to SW_ID) and two drop-down lists that are bound also. The three together are joint primary keys. I need to be able to view data and enter it on this form.

Now the default value of the SW_ID textbox is from a textbox on the previous form(FRM_SOFTWARE).

On FRM_SOFTWARE form, when you click on a button it opens up FRM_SUPPT and the default value pops up so i can enter new data.

However it also shows all the records entered in this form with ones with a different value for SW_ID.

I want to be able to enter data while at the same time being able to see all records that have the same value of SW_ID, but I do NOT want to be able to see records with a different value.

Help would be greatly appreciated

Thanks
HlebforPM
Aug 9 '07 #1
Share this Question
Share on Google+
12 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hi

I want to filter records on a continuous form(FRM_SUPPT) by one of the fields, SW_ID.

FRM_SUPPT has a text box ( bound to SW_ID) and two drop-down lists that are bound also. The three together are joint primary keys. I need to be able to view data and enter it on this form.

Now the default value of the SW_ID textbox is from a textbox on the previous form(FRM_SOFTWARE).

On FRM_SOFTWARE form, when you click on a button it opens up FRM_SUPPT and the default value pops up so i can enter new data.

However it also shows all the records entered in this form with ones with a different value for SW_ID.

I want to be able to enter data while at the same time being able to see all records that have the same value of SW_ID, but I do NOT want to be able to see records with a different value.

Help would be greatly appreciated

Thanks
HlebforPM
Try this code in the open event for your form:
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. 'assuming that SW_ID is numeric data type
  4. strFilter = "[SW_ID] = " & Me![SW_ID]       
  5.  
  6. Me.Filter = strFilter
  7. Me.FilterOn= True
Aug 9 '07 #2

Expert 100+
P: 635
Hi

I want to filter records on a continuous form(FRM_SUPPT) by one of the fields, SW_ID.

FRM_SUPPT has a text box ( bound to SW_ID) and two drop-down lists that are bound also. The three together are joint primary keys. I need to be able to view data and enter it on this form.

Now the default value of the SW_ID textbox is from a textbox on the previous form(FRM_SOFTWARE).

On FRM_SOFTWARE form, when you click on a button it opens up FRM_SUPPT and the default value pops up so i can enter new data.

However it also shows all the records entered in this form with ones with a different value for SW_ID.

I want to be able to enter data while at the same time being able to see all records that have the same value of SW_ID, but I do NOT want to be able to see records with a different value.

Help would be greatly appreciated

Thanks
HlebforPM
Hi

How about

Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stLinkCriteria As String
  3.  
  4. stLinkCriteria = "[SW_ID] = " & textboxSW_ID
  5.  
  6. stDocName = "FRM_SUPPT"
  7. DoCmd.OpenForm stDocName, , , stLinkCriteria 
??

Just a thought, but you do not give any clues as to how you are opening the second form!

MTB
Aug 9 '07 #3

P: 24
Thanks for the reply.

I've tried it but it doesn't seem to be working, all the entries with different values of SW_ID still keep popping up.

I'm not sure if I am doing it right. The text box that is bound to SW_ID is called txtSwId so I typed in:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim strFilter As String
  3.  
  4. 'assuming that SW_ID is numeric data type
  5. strFilter = "[SW_ID] = " & Me![SW_ID]
  6.  
  7. Me.Filter = strFilter
  8. Me.FilterOn = True
  9. End Sub
  10.  
I tried various combinations by replacing SW_ID with txtSwId because I wasn't sure whethter they were referring to the text box or not
Aug 9 '07 #4

P: 24
Hi

How about


Code: ( text )
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[SW_ID] = " & textboxSW_ID

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


??

Just a thought, but you do not give any clues as to how you are opening the second form!

MTB
I have a button that opens the second form. I just used the button wizard. The code behind the button that opens it is:

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "FRM_SUPPORT"
  5.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  6.  
The first form remains open when I open up FRM_SUPPT.
Aug 9 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
Thanks for the reply.

I've tried it but it doesn't seem to be working, all the entries with different values of SW_ID still keep popping up.

I'm not sure if I am doing it right. The text box that is bound to SW_ID is called txtSwId so I typed in:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim strFilter As String
  3.  
  4. 'assuming that SW_ID is numeric data type
  5. strFilter = "[SW_ID] = " & Me![SW_ID]
  6.  
  7. Me.Filter = strFilter
  8. Me.FilterOn = True
  9. End Sub
  10.  
I tried various combinations by replacing SW_ID with txtSwId because I wasn't sure whethter they were referring to the text box or not

Change the line below. Replace YourForm with actual name of form that is passing the ID field value, Also, is SW_ID a numeric data type or a text data type?

strFilter = "[SW_ID] = " & Form!YourForm![txtSwId]
Aug 9 '07 #6

P: 24
Change the line below. Replace YourForm with actual name of form that is passing the ID field value, Also, is SW_ID a numeric data type or a text data type?

strFilter = "[SW_ID] = " & Form!YourForm![txtSwId]
Its numeric, an autonumber.

I replaced the line but it said "cannot find field FRM_SOFTWARE" when i tried to open the form
Aug 9 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Its numeric, an autonumber.

I replaced the line but it said "cannot find field FRM_SOFTWARE" when i tried to open the form
FRM_Software has to remain open until the ID is passed. Close FRM_Software on the last line of your open event for the form you opened.
Aug 9 '07 #8

Expert 100+
P: 635
I have a button that opens the second form. I just used the button wizard. The code behind the button that opens it is:


Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "FRM_SUPPORT"
  5.     DoCmd.OpenForm stDocName, , , stLinkCriteria
.
If you had used the "Find specifid record", or whatever the option is in the wizard, it would have generated the code (or similar) that I posted.

??

MTB
Aug 9 '07 #9

P: 24
FRM_Software has to remain open until the ID is passed. Close FRM_Software on the last line of your open event for the form you opened.
Sorry for my unclear posts.

The form does remain open (I need it to). Passing the ID is not the problem, that works fine. I have done that part. It passes it and I can make as many entries as I want with SW_ID being kept the same as it takes the value from the other form fine.

It's a continuous form and as well as allowing me to make entries (which works fine) it shows me all the records that are in place, whereas I just want to be able to the see the ones I put in and any previous ones with the same SW_ID.

It has two combo boxes and the text field with SW_ID in it (which i will hide when its done).

At the moment when i open it it has several entries like this (sorry couldn't do proper diagram):

SW_ID= 1 STF_ID - 1 SUPPT_ID - 1
SW_ID= 1 STF_ID - 1 SUPPT_ID - 2
SW_ID= 2 STF_ID - 2 SUPPT_ID - 1
SW_ID= 3 STF_ID - 1 SUPPT_ID - 1

And if I open it with SW_ID being passed through as "3" it would have all the above plus a line allowing me to put in a STF_ID and SUPPT_ID for SW_ID = 3

It functions fine, but when I have thousands of values for SW_ID, I just want it to show the SW_ID for the particular one I want. So I can see what's already there for the particular SW_ID before i make a entry and not have to thousands of records on the form.

Thanks
Hleb
Aug 9 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
Sorry for my unclear posts.

The form does remain open (I need it to). Passing the ID is not the problem, that works fine. I have done that part. It passes it and I can make as many entries as I want with SW_ID being kept the same as it takes the value from the other form fine.

It's a continuous form and as well as allowing me to make entries (which works fine) it shows me all the records that are in place, whereas I just want to be able to the see the ones I put in and any previous ones with the same SW_ID.

It has two combo boxes and the text field with SW_ID in it (which i will hide when its done).

At the moment when i open it it has several entries like this (sorry couldn't do proper diagram):

SW_ID= 1 STF_ID - 1 SUPPT_ID - 1
SW_ID= 1 STF_ID - 1 SUPPT_ID - 2
SW_ID= 2 STF_ID - 2 SUPPT_ID - 1
SW_ID= 3 STF_ID - 1 SUPPT_ID - 1

And if I open it with SW_ID being passed through as "3" it would have all the above plus a line allowing me to put in a STF_ID and SUPPT_ID for SW_ID = 3

It functions fine, but when I have thousands of values for SW_ID, I just want it to show the SW_ID for the particular one I want. So I can see what's already there for the particular SW_ID before i make a entry and not have to thousands of records on the form.

Thanks
Hleb

So if I understand your example correctly, you want to see only SW_ID's = 3 in this particular case. If that is the case, the filter I gave you should work. Don't understand why it is not working. However, there is more than one way to skin a cat. Another way you could do it is to declare your filter in the line preceding the open form command, and use the OpenForm command to filter the form that is to be opened, as is shown below. I think FRM_SWID was the name of the form you were opening, but I don't remember for sure.

Dim strFilter As String

'assumes that the hidden textbox is on FRM_SUPPT and that filter is used in the OpenForm command.
Expand|Select|Wrap|Line Numbers
  1. strFilter = "[SW_ID] = " & Me!txtSwid
  2.  
  3. DoCmd.OpenForm "FRM_SWID" , acViewNormal , strFilter
Aug 9 '07 #11

P: 24
Hi thanks for the help but i still can't get it to work.

This code is in the form FRM_SOFTWARE

Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stLinkCriteria As String
  3.  
  4.  
  5. stDocName = "FRM_SUPPT"
  6. DoCmd.OpenForm stDocName, , , stLinkCriteria
  7.  
I need the data on FRM_SUPPT filtered by SW_ID

So i tried:

Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stLinkCriteria As String
  3. Dim strFilter As String   
  4.  
  5. strFilter = "[SW_ID] = " & Me!txtSwid
  6.  
  7. stDocName = "FRM_SUPPT"
  8. DoCmd.OpenForm stDocName, , , stLinkCriteria, strFilter
  9.  
  10.  
Thanks
Aug 10 '07 #12

P: 24
Oh no, I messed it up.

It works perfectly.

Thank you very much puppydog!

Hleb
Aug 10 '07 #13

Post your reply

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