I'm trying to use a textbox on a datasheet to open a specific related report, but...

[ShovelinFishHeads]

I'm trying to use a textbox on a datasheet to open a specific related report, but...

I'm not getting the report for the specific record.

Background: I made a datasheet that shows several fields from a query that has a large number of fields. The first two columns of the datasheet show PK numbers. PK numbers in the first column repeat while PKs in the second column don't.

I want to give the user the ability to click on any textbox for PKs in either of these 2 columns and open a report associated with that PK in that specific record (not just a report associated with the first record in the column which is what I have happening now with my one line of code (that I have mastered!)).

Any guidance is greatly appreciated! Thanks

 

[pbaldy]

You haven't shown any code, so let's start here:

http://www.baldyweb.com/wherecondition.htm

 

[missinglinq]

...PK numbers in the first column repeat...

How, exactly, do these Primary Keys repeat?

Linq ;0)>

 

[ShovelinFishHeads]

Pbaldy:

here is the code that I am currently using:

Private Sub AssignmentID_Click()

DoCmd.OpenReport "Assignment Report", acViewReport, , "AssignmentID = " & AssignmentID

End Sub

This code works wonderfully for showing me the report I want opened but it always opens the report showing info related to the first record in the recordset of the datasheet.

I need to make the code bring up the report and filter it based on the related PK value that the textbox is showing for that specific record. For example, if I have 100 records displayed in the datasheet, I should get the report showing the PK for the record associated with PK 100 if I indeed click on the textbox that reads "100". Not a report for some other PK number of some other record in the record set.

So I guess the question is: how do a pass the value of the PK number in a given textbox to the filter function in my code?

missinglinq:

The query driving the datasheet is made up of two queries that in turn read two "main" tables. One of those tables has a significant number of other tables related to it. But to answer your question basically, there is a one to many relationship between the "base" tables. So, if there are two or more records associated with the PK number in column one, there will be two or more instrances of that PK number in the first column and two or more PK numbers on the "many" side of that relationship in the second column. PK numbers in the second column don't repeat and are incremental.

If this does not answer your question fully, please let me know and I will provide added info.

Thanks, guys

 

[pbaldy]

If your code is in the detail, it should work to pull up the record with that PK. If it's on a main form, it will pull whatever record in the subform has focus, by default the first. With a subform, I typically use a button or the double click event of a textbox. Either way, I'd probably disambiguate it with Me:

DoCmd.OpenReport "Assignment Report", acViewReport, , "AssignmentID = " & Me.AssignmentID

 

[ShovelinFishHeads]

Ok so if I use Record Selectors to switch the focus to one record in the datasheet, I can then click on my PK textbox and get the report to open and filter correctly by using:

DoCmd.OpenReport "Assignment Report", acViewReport, , "AssignmentID = " & Me.AssignmentID

that works good.

Thanks for all the help pbaldy

 

[pbaldy]

No problem.

 

[ShovelinFishHeads]

Oh one last question: will the


DoCmd.OpenReport "Assignment Report", acViewReport, , "AssignmentID = " & Me.AssignmentID

method work for any text box? Or are there qualifications like it has to be indexed or be a Primary Key?

 

[Minty]

It will work for any control, but you need to allow for different datatypes - so for an number you have the correct syntax.

If it's a text string you need to surround (escape) the criteria in single quotes e.g.

"FirstName = [COLOR="Red"]'[/COLOR]" & Me.txtFirstName & "[COLOR="red"]'[/COLOR]"

If it's a date then the date needs surounding with # e.g.

"StartDate >= #" & Me.txtDateFrom & "#"

Dates also need to be of the mm/dd/yyyy format to work correctly.
See here for further assistance http://allenbrowne.com/ser-36.html