Afternoon.
My report is bound to a table. However, in a heading I want to use a date that is held in a seperate table. I have used the Expression Builder from the data property of the text box I wish to use to display this date. It gives me the format =tblIni!DateFrom as my expression and I assume (that word again) that Access knows how to refer to the table.
However, when the report is run I get a messagebox asking me to enter a value. The box is titled "Enter paramater value:" and the inside of the box just says "tblIni". I assume it's asking for a field?
Any clues what I'm doing wrong? Thanks.
Full Version: Report Bound To Table A. How To Read Data From Table B?
How about on the OnLoad Event of the report you can DLookUp("DateFrom","tblIni") and assign it to your date field.
Me.ReportDateField = NZ(DLookUp("DateFrom","tblIni"), Date())
Me.ReportDateField = NZ(DLookUp("DateFrom","tblIni"), Date())
Hi Mike,
Could you provide a little background on the 2 tables? For example, the table with the date that you want displayed, how are you determining which date? Do you have a relationship between the 2 tables and the date that you want displayed is indicating that the records on the report are all for that date? If so, you do not need to do all that. If you could answer the above, I can provide you with an accurate answer on doing this.
Could you provide a little background on the 2 tables? For example, the table with the date that you want displayed, how are you determining which date? Do you have a relationship between the 2 tables and the date that you want displayed is indicating that the records on the report are all for that date? If so, you do not need to do all that. If you could answer the above, I can provide you with an accurate answer on doing this.
2 very quick replies, thanks.
OK, the main table being looped through is a linked table, stored in a remote SQL server. I can change nothing, just read it. My looping process examines a field for text. [1] There may or may not be text in this field. I am trying to note the date of the first occurance of text.
The first time the loop says "yes, there is text" I have it copy the date from another field. I tried storing this date in a global variable, but couldn't access it from a report. A link I googled said globals can only be accessed from VBA not reports. So I have a table I use to store settings, I call it tblIni. I made a field in there and store this date.
The report is bound to the table that stores the results of the 30,000 loopings. The report heading is to include the date of the first instance so I want to retrieve it from this second table.
This is my loop and how I retrieve the date. The inner If loop just makes sure it's only done once.
[1] It examines the field for words which it puts into a seperate table used for counting occurances and associations. This works fine.
Thanks.
Mike.
OK, the main table being looped through is a linked table, stored in a remote SQL server. I can change nothing, just read it. My looping process examines a field for text. [1] There may or may not be text in this field. I am trying to note the date of the first occurance of text.
The first time the loop says "yes, there is text" I have it copy the date from another field. I tried storing this date in a global variable, but couldn't access it from a report. A link I googled said globals can only be accessed from VBA not reports. So I have a table I use to store settings, I call it tblIni. I made a field in there and store this date.
The report is bound to the table that stores the results of the 30,000 loopings. The report heading is to include the date of the first instance so I want to retrieve it from this second table.
This is my loop and how I retrieve the date. The inner If loop just makes sure it's only done once.
CODE
'Loop through all records in rstIn. Look for text in the tag field and split it into
'a storage table.
Do Until rstIn.EOF
'Show a counter to the user on the button
'The loop is to reduce flickering when redrawing the caption.
If intRefreshRate = 100 Then
lblPercentCounter.Caption = Int((i / intNumberOfRecords) * 100) & " %"
Me.Repaint
intRefreshRate = 0
End If
intRefreshRate = intRefreshRate + 1
'If no text in the tag field then loop around.
If IsNull(rstIn![Tag]) Or rstIn![Tag] = "" Then
rstIn.MoveNext
'If there IS text in the tag field call the function then loop
Else
'For the first record found with tags, record it's date for use on the report.
If booTagFound Then
rstIni.Edit
rstIni![DateFrom] = DateValue(rstIn![dtPictureTaken])
rstIni.Update
booTagFound = False
End If
Call funFindWords(rstIn![Tag])
rstIn.MoveNext
End If
'Increase the users counter
i = i + 1
Loop
'a storage table.
Do Until rstIn.EOF
'Show a counter to the user on the button
'The loop is to reduce flickering when redrawing the caption.
If intRefreshRate = 100 Then
lblPercentCounter.Caption = Int((i / intNumberOfRecords) * 100) & " %"
Me.Repaint
intRefreshRate = 0
End If
intRefreshRate = intRefreshRate + 1
'If no text in the tag field then loop around.
If IsNull(rstIn![Tag]) Or rstIn![Tag] = "" Then
rstIn.MoveNext
'If there IS text in the tag field call the function then loop
Else
'For the first record found with tags, record it's date for use on the report.
If booTagFound Then
rstIni.Edit
rstIni![DateFrom] = DateValue(rstIn![dtPictureTaken])
rstIni.Update
booTagFound = False
End If
Call funFindWords(rstIn![Tag])
rstIn.MoveNext
End If
'Increase the users counter
i = i + 1
Loop
[1] It examines the field for words which it puts into a seperate table used for counting occurances and associations. This works fine.
Thanks.
Mike.
Arnelgp, it has no on-load event. It has an on-open and I shall experiment with your suggestion. Thanks.
Mike.
Mike.
Me.boxDateRange = Nz(DLookup("DateFrom", "tblIni"), Date)
Me.boxDateRange.Value = Nz(DLookup("DateFrom", "tblIni"), Date)
Me.boxDateRange.Text = Nz(DLookup("DateFrom", "tblIni"), Date)
"You can't assign a value to this object" Oh well...
Hi Mike,
You can pass the value from a function to an expression in your report. You would write a function something like below.
Then in your report, you would set the controlsource to =GetSQLDate()
I'm sure that your code is way different than what I posted but all you have to do is assign the variable that is getting the value you want to the function name like above. HTH.
You can pass the value from a function to an expression in your report. You would write a function something like below.
CODE
Public Function GetSQLDate()
Dim dt As Date
dt = DMax("YourDate", "YourTable")
GetSQLDate = dt
End Function
Dim dt As Date
dt = DMax("YourDate", "YourTable")
GetSQLDate = dt
End Function
Then in your report, you would set the controlsource to =GetSQLDate()
I'm sure that your code is way different than what I posted but all you have to do is assign the variable that is getting the value you want to the function name like above. HTH.
QUOTE (accesshawaii @ Mar 26 2012, 03:32 PM) 
Hi Mike,
You can pass the value from a function to an expression in your report. You would write a function something like below.
Then in your report, you would set the controlsource to =GetSQLDate()
I'm sure that your code is way different than what I posted but all you have to do is assign the variable that is getting the value you want to the function name like above. HTH.
You can pass the value from a function to an expression in your report. You would write a function something like below.
CODE
Public Function GetSQLDate()
Dim dt As Date
dt = DMax("YourDate", "YourTable")
GetSQLDate = dt
End Function
Dim dt As Date
dt = DMax("YourDate", "YourTable")
GetSQLDate = dt
End Function
Then in your report, you would set the controlsource to =GetSQLDate()
I'm sure that your code is way different than what I posted but all you have to do is assign the variable that is getting the value you want to the function name like above. HTH.
Hello Wisconsin.
I think I get this...
The function is called with no paramaters.
It gets the date from the "DateFrom" field in my tblIni table, sets it to the return paramater and is sends it to the textbox... I assume this function is written in the form's code, not the reports. Lets try it.
CODE
Public Function funPassDateToReport()
Dim dt As Date
dt = DMax("FromDate", "tblIni")
GetSQLDate = dt
End Function
Dim dt As Date
dt = DMax("FromDate", "tblIni")
GetSQLDate = dt
End Function
This opens a messagebox as before asking for a paramater. Anything I type in gives me ?Name on the report. I'll keep going...
Programatically, I always find it easier to set the caption for a label than I do the value of a textbox. So, my reccomendation would be to replace the textbox with a label control and use something like this in the report's on_open event:
Also gives you the advantage of being able to closely control the format of how you want the date displayed.
DFirst may be a better choice than DLookup. Try experimenting with both.
CODE
Private Sub Report_Open(Cancel As Integer)
Dim dtVal As Date
dtVal = DLookup("[DateFrom]", "[tblIni]")
Me.Label2.Caption = Format(dtVal, "mm/dd/yyyy")
End Sub
Dim dtVal As Date
dtVal = DLookup("[DateFrom]", "[tblIni]")
Me.Label2.Caption = Format(dtVal, "mm/dd/yyyy")
End Sub
Also gives you the advantage of being able to closely control the format of how you want the date displayed.
DFirst may be a better choice than DLookup. Try experimenting with both.
The function would be placed in a public module not in the form or the report. Just go to modules, create a new module and place your code in there with the syntax that I provided where you assign the function name to the variable value and that will do the trick.
Good morning.
Putting it in a module no doubt helped, but so did finally noticing that I had a field name the wrong way round!!! DateFrom or FromDate, which was it meant to be? I'm sure I must be slightly dxyslexic. I now have a report showing the date of the first instance. Thanks for all your help. Again.
The answer is DateFrom is correct in my case.
Mike.
Putting it in a module no doubt helped, but so did finally noticing that I had a field name the wrong way round!!! DateFrom or FromDate, which was it meant to be? I'm sure I must be slightly dxyslexic. I now have a report showing the date of the first instance. Thanks for all your help. Again.
The answer is DateFrom is correct in my case.
Mike.
Glad you got it figured out. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.