Filtering Form for a Report

[David Davis]

Hello,

Ran into a snag while building my database. I have several forms but lets work with two for right now; Form1 and Form2 both have the same fields but use by two different users but both are connected to Report1. How can I get only the data in Form1 to show and vice versa for Form2. Is this possible?

David

[ADezii]

Hello,

Ran into a snag while building my database. I have several forms but lets work with two for right now; Form1 and Form2 both have the same fields but use by two different users but both are connected to Report1. How can I get only the data in Form1 to show and vice versa for Form2. Is this possible?

David

You can actually assign the Recordset of one Form to another, this is probably the easiest Method:

Set Forms!Form2.Recordset = Forms!Form1.Recordset
                     OR
Set Forms!Form1.Recordset = Forms!Form2.Recordset

[David Davis]

You can actually assign the Recordset of one Form to another, this is probably the easiest Method:

Set Forms!Form2.Recordset = Forms!Form1.Recordset
                     OR
Set Forms!Form1.Recordset = Forms!Form2.Recordset

You have to forgive me ADezii on my knowledge but where do I put this formula at exactly.

David

[ADezii]

You have to forgive me ADezii on my knowledge but where do I put this formula at exactly.

David

If you are opening FormB from FormA, then you can place the following code
in the Open() Event of FormB:

Set Forms!FormB.Recordset = Forms!FormA.Recordset

[David Davis]

Dzii,

I see the formula and I havent tried it as of yet but I think I need to give clarity. When you open the form (without your coding) it gives me every record that any person has inputted from their individual form. All forms have the same data fields which are connected to one report the feeds off a master table. So when I am in form1 (for example) I get the everybodys record when I go to report (I push a command button that takes me to my report so I can print my records there) and I only want the records of the users form. Now after reading all this would that be the right coding I need. I really need your help on this one.

David

[David Davis]

ADezii,

Have you came up with anything yet on the last reply.

David

[ADezii]

Dzii,

I see the formula and I havent tried it as of yet but I think I need to give clarity. When you open the form (without your coding) it gives me every record that any person has inputted from their individual form. All forms have the same data fields which are connected to one report the feeds off a master table. So when I am in form1 (for example) I get the everybodys record when I go to report (I push a command button that takes me to my report so I can print my records there) and I only want the records of the users form. Now after reading all this would that be the right coding I need. I really need your help on this one.

David

I only want the records of the users form

I am not exactly sure what you mean by this, please explain in greater detail.

[David Davis]

I am not exactly sure what you mean by this, please explain in greater detail.

ADezii,

I apologize for the confusion and hope this clarifies the previous a bit more.

The user uses form1to input information into Masterdatabase(table). Form1 has a cmd button that when pushed it opens Report1. When Report1 opens it brings up every record in Masterdatabase. I only want the records that the user inputed into form1 to come up on Report1 when the cmd button is pushed.

David

[ADezii]

ADezii,

I apologize for the confusion and hope this clarifies the previous a bit more.

The user uses form1to input information into Masterdatabase(table). Form1 has a cmd button that when pushed it opens Report1. When Report1 opens it brings up every record in Masterdatabase. I only want the records that the user inputed into form1 to come up on Report1 when the cmd button is pushed.

David

1. Is there any indicator that will pinpoint exactly what Records a specific User added?
2. Is there a Primary Key Field in the MasterDatabase Table?
3. If there is a PK Field in the MasterDatabase Table, what is its Data Type?
4. If there is no Primary Key Field in MasterTable, is there an AutoNumber Field or a sequentially numbered Field?

[David Davis]

1. Is there any indicator that will pinpoint exactly what Records a specific User added?
2. Is there a Primary Key Field in the MasterDatabase Table?
3. If there is a PK Field in the MasterDatabase Table, what is its Data Type?
4. If there is no Primary Key Field in MasterTable, is there an AutoNumber Field or a sequentially numbered Field?

ADezii,

- There is a Primary Key Field (Sequential Serial Number (Data Type Number) in the MasterDatabase Table. The information that identifies each user is their name (Data Type Text) entered in each record.

[ADezii]

ADezii,

- There is a Primary Key Field (Sequential Serial Number (Data Type Number) in the MasterDatabase Table. The information that identifies each user is their name (Data Type Text) entered in each record.

Just resubscribing, will return later.

[ADezii]

ADezii,

- There is a Primary Key Field (Sequential Serial Number (Data Type Number) in the MasterDatabase Table. The information that identifies each user is their name (Data Type Text) entered in each record.

This is basically a 2-step process:

1. When the Form is initially opened, obtain the value of the Last Primary Key Entry, let's just assume it is 876.
2. Open Report1 with the Criteria of > 876 on the Primary Key Field.
3. Only the Records that the Current User entered will be reflected in Report1.
4. For an additional User, you would have to Close then Re-open the Form to repeat the process.
5. Is this what you are looking for, and do you need additional help on how to implement this?

[David Davis]

This is basically a 2-step process:

1. When the Form is initially opened, obtain the value of the Last Primary Key Entry, let's just assume it is 876.
2. Open Report1 with the Criteria of > 876 on the Primary Key Field.
3. Only the Records that the Current User entered will be reflected in Report1.
4. For an additional User, you would have to Close then Re-open the Form to repeat the process.
5. Is this what you are looking for, and do you need additional help on how to implement this?

Thanks ADezii,

I do need help on implementing this. I am however confused on just the Criteria of > 876 without having anything unique to the Current User. Wouldnt that just bring up every record from the MasterDataBase Table (from > 876) no matter who the user is?

David

[ADezii]

Thanks ADezii,

I do need help on implementing this. I am however confused on just the Criteria of > 876 without having anything unique to the Current User. Wouldnt that just bring up every record from the MasterDataBase Table (from > 876) no matter who the user is?

David

Assuming the Record Source for Report1 is the same or at least contains the [Name] Fields that exist in the Form, you could restrict Report1's Records to only those for the Current User by something similar to the following (assuming you have [LastName] and [FirstName] Fields, and your Text Box Names are txtLastName and txtFirstName):

DoCmd.OpenReport "Report1", acViewPreview, , "[LastName] = '" & Me![txtLastName] & "'" & _
                 " And [FirstName] = '" & Me![txtFirstName] & "'"

NOTE: This would return 'ALL" Records from the MasterDataBase by the Current User, not the most newly entered ones. This is where the idea of capturing the Last Primary Key Value comes in, but if the above is what you want, that is fine.

[David Davis]

Assuming the Record Source for Report1 is the same or at least contains the [Name] Fields that exist in the Form, you could restrict Report1's Records to only those for the Current User by something similar to the following (assuming you have [LastName] and [FirstName] Fields, and your Text Box Names are txtLastName and txtFirstName):

DoCmd.OpenReport "Report1", acViewPreview, , "[LastName] = '" & Me![txtLastName] & "'" & _
                 " And [FirstName] = '" & Me![txtFirstName] & "'"

NOTE: This would return 'ALL" Records from the MasterDataBase by the Current User, not the most newly entered ones. This is where the idea of capturing the Last Primary Key Value comes in, but if the above is what you want, that is fine.

ADezii,
The coding that you mention is want I want but I also want current input.
What is the process on capturing th Last Primary Key Value for current input for the current date?

David

[ADezii]

ADezii,
The coding that you mention is want I want but I also want current input.
What is the process on capturing th Last Primary Key Value for current input for the current date?

David

1. Place the following code in the General Declarations Section of your Form:
   

   Private lngLastValueEntered As Long

2. Place the following code in your Form's Open() Event. Assuming your Primary Key Field is named [PrimaryKey], the Primary Key value of the last Record entered into the Database will be placed into the lngLastValueEntered Variable. I'm also assuming that your Form is not based on a Query with a Primary Sort on a Field other than [PrimaryKey].
   

   Private Sub Form_Open(Cancel As Integer)
     lngLastValueEntered = DLast("[PrimaryKey]", "MasterDatabase")
   End Sub

3. Place the following code in the Click() Event of a Command Button which will Open Report1. Make your own Field/Table Name substitutions whever necessary:
   

   'Save any Edits in Progress
   If Me.Dirty = True Then Me.Dirty = False
    
   DoCmd.OpenReport "rptEmployees", acViewPreview, , "[LastName] = '" & Me![LastName] & "'" & _
                    " And [FirstName] = '" & Me![FirstName] & "' And [PrimaryKey] > " & _
                    lngLastValueEntered

4. Now, only those Records entered by the Current User, for the specific Session during which the Form was opened, will be displayed in the Report.

[David Davis]

1. Place the following code in the General Declarations Section of your Form:
   

   Private lngLastValueEntered As Long

2. Place the following code in your Form's Open() Event. Assuming your Primary Key Field is named [PrimaryKey], the Primary Key value of the last Record entered into the Database will be placed into the lngLastValueEntered Variable. I'm also assuming that your Form is not based on a Query with a Primary Sort on a Field other than [PrimaryKey].
   

   Private Sub Form_Open(Cancel As Integer)
     lngLastValueEntered = DLast("[PrimaryKey]", "MasterDatabase")
   End Sub

3. Place the following code in the Click() Event of a Command Button which will Open Report1. Make your own Field/Table Name substitutions whever necessary:
   

   'Save any Edits in Progress
   If Me.Dirty = True Then Me.Dirty = False
    
   DoCmd.OpenReport "rptEmployees", acViewPreview, , "[LastName] = '" & Me![LastName] & "'" & _
                    " And [FirstName] = '" & Me![FirstName] & "' And [PrimaryKey] > " & _
                    lngLastValueEntered

4. Now, only those Records entered by the Current User, for the specific Session during which the Form was opened, will be displayed in the Report.

ADzii,

What do you mean "Place the following code in the General Declarations Section of your Form?" I know how to bring the forms properties table up but not sure what the General Declarations Section is?

David

[ADezii]

ADzii,

What do you mean "Place the following code in the General Declarations Section of your Form?" I know how to bring the forms properties table up but not sure what the General Declarations Section is?

David

1. In Form Design View.
2. View ==> Code.
3. Upper Left Hand Corner of Window (Combo Box) ==> Select (General)
4. Upper Right Hand Corner of Window (Combo Box) ==> Select (Declarations)

[David Davis]

1. In Form Design View.
2. View ==> Code.
3. Upper Left Hand Corner of Window (Combo Box) ==> Select (General)
4. Upper Right Hand Corner of Window (Combo Box) ==> Select (Declarations)

Adezii,
I have a run time error of 2501 and points to this as being of error

DoCmd.OpenReport "Report1", acViewPreview, , "[Instructor] = '" & Me![Instructor] & "'" & _
                 " And [InstTitle] = '" & Me![InstTitle] & "' And [CertSerialNum] > " & _
                 lngLastValueEntered

David

[NeoPa]

You may want to post the error message associated with error #2501.

[David Davis]

You may want to post the error message associated with error #2501.

NeoPa,

I am glad you answered NeoPa. I found out it wasnt the previous coding of Adezii but the coding you passed to me previously. Problem: When data is in the report it gives no error but when their is no data then it comes up with run time error 2501 and you can either end process or debug the command button.

Code given to me

Private Sub Report_NoData(Cancel As Integer)
  Cancel = True
End Sub

When Debug is initiated it goes to Private Sub code below and Highlights the DoCmd line

Private Sub PrintLMCert_Click()
 
    DoCmd.OpenReport "ChristineMcLaurinCert", acPreview
 
End Sub

You have any solutions?

Also this only reacts this way on one report the other report works great with your coding.

David

[NeoPa]

Well, I'm happy to be involved then David, but could we start with the error message?

[David Davis]

Well, I'm happy to be involved then David, but could we start with the error message?

Taken care of NeoPa,

I replaced the previous coding with the below coding and it fix the problem.

Private Sub PrintLMCert_Click()
 
On Error GoTo Err_PrintLMCert_Click
    Dim stDocChris As String
    stDocChris = "ChristineMcLaurinCert"
   DoCmd.OpenReport stDocChris, acPreview
 
Exit_PrintLMCert_Click:
    Exit Sub
 
Err_PrintLMCert_Click:
    MsgBox Err.Description
    Resume Exit_PrintLMCert_Click
 
End Sub

But I still need your help with my check box. So I am going to exit from this thread and go back to the one we started a week or so ago.

Thanks David

[NeoPa]

Well, I guess I'll get another thread popping up tomorrow then (It's just after midnight here now).

Let me just draw you attention to the rules about posting code while I'm here. They must always be posted in [ CODE] tags. When posting, look for the button with the # on.