Filtering form fields that are bound

[hlebforprimeminister]

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

[puppydogbuddy]

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:

Dim strFilter As String
 
'assuming that SW_ID is numeric data type
strFilter = "[SW_ID] = " & Me![SW_ID]       
 
Me.Filter = strFilter
Me.FilterOn= True

[MikeTheBike]

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

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

[hlebforprimeminister]

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:

Private Sub Form_Open(Cancel As Integer)
Dim strFilter As String
 
'assuming that SW_ID is numeric data type
strFilter = "[SW_ID] = " & Me![SW_ID]
 
Me.Filter = strFilter
Me.FilterOn = True
End Sub
 

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

[hlebforprimeminister]

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:

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FRM_SUPPORT"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 

The first form remains open when I open up FRM_SUPPT.

[puppydogbuddy]

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:

Private Sub Form_Open(Cancel As Integer)
Dim strFilter As String
 
'assuming that SW_ID is numeric data type
strFilter = "[SW_ID] = " & Me![SW_ID]
 
Me.Filter = strFilter
Me.FilterOn = True
End Sub
 

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]

[hlebforprimeminister]

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

[puppydogbuddy]

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.

[MikeTheBike]

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

Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FRM_SUPPORT"
    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

[hlebforprimeminister]

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

[puppydogbuddy]

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.

strFilter = "[SW_ID] = " & Me!txtSwid
 
DoCmd.OpenForm "FRM_SWID" , acViewNormal , strFilter

[hlebforprimeminister]

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

This code is in the form FRM_SOFTWARE

Dim stDocName As String
Dim stLinkCriteria As String
 
 
stDocName = "FRM_SUPPT"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 

I need the data on FRM_SUPPT filtered by SW_ID

So i tried:

Dim stDocName As String
Dim stLinkCriteria As String
Dim strFilter As String   
 
strFilter = "[SW_ID] = " & Me!txtSwid
 
stDocName = "FRM_SUPPT"
DoCmd.OpenForm stDocName, , , stLinkCriteria, strFilter
 
 

Thanks

[hlebforprimeminister]

Oh no, I messed it up.

It works perfectly.

Thank you very much puppydog!

Hleb