Hi, i have a query in this Sub and need to print the result to a report. everything works fine, except that there will be several popup windows prompt for waveform11, freqMin, freqMax, and freqRate.
it didn't prompt for title and id which are the 1st 2 fields in the select statement though.
is there a way to turn off the popup?
thank you for help
Sub RunReport()
Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report
Dim strSql As String
'set the title
title = "Title for the Report"
' initialise position variables
lngLeft = 0
lngTop = 0
'Create the report
Set rpt = CreateReport
' set properties of the Report
strSql = "select rv.radio_variant_title_tx as title, rv.radio_variant_id_cd as id " + "from radio_variant rv where rv.radio_variant_id_cd = 0"
With rpt
.Width = 18500
.RecordSource = strSql
.Caption = title
End With
' Create Label Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Title", 0, 0)
lblNew.FontBold = True
lblNew.FontSize = 12
lblNew.SizeToFit
strSql = "select rv.radio_variant_title_tx as title, rv.radio_variant_id_cd as id, " + _
"w.waveform_title_tx as waveform11, " + _
"frl.freq_min as freqMin, " + _
"frl.freq_max as freqMax, " + _
"br.bandwidth_rate_title_tx as freqRate " + _
"from radio_variant as rv, variant_waveform_link as vwl, waveform as w, freq_range_link as frl, bandwidth_rate as br " + _
"where rv.radio_variant_id_cd = vwl.[Radio Variant] and " + _
"vwl.[Waveform] = 27 and " + _
"vwl.[Waveform] = w.waveform_id_cd and " + _
"frl.parent_id_cd = w.waveform_id_cd and " + _
"frl.[Frequency Rate] = br.bandwidth_rate_id_cd"
'Set rs = CurrentDb.OpenRecordset(strSql, Type:=dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset(strSql)
' Create corresponding label and text box controls for each field.
For Each fld In rs.Fields
' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acDetail, , fld.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit
' Create new label control and size to fit data.
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
' Increment top value for next control
lngLeft = lngLeft + txtNew.Width + 25
'lngTop = lngTop + txtNew.Height + 10
Next
lngLeft = 0
lngTop = lngTop + txtNew.Height + 10
Dim id As String
Dim recordTitle As String
Dim waveform As String
Dim freqMin As String
Dim freqMax As String
Dim freqRate As String
'Debug.Print strSql
While Not rs.EOF
recordTitle = rs.Fields("title")
id = rs.Fields("id")
waveform = rs.Fields("waveform11")
freqMin = rs.Fields("freqMin")
freqMax = rs.Fields("freqMax")
freqRate = rs.Fields("freqRate")
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, , recordTitle, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngLeft = lngLeft + txtNew.Width + 25
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, , id, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngLeft = lngLeft + txtNew.Width + 25
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, , waveform, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngLeft = lngLeft + txtNew.Width + 25
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, , freqMin, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngLeft = lngLeft + txtNew.Width + 25
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, , freqMax, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngLeft = lngLeft + txtNew.Width + 25
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, , freqRate, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngTop = lngTop + txtNew.Height + 10
lngLeft = 0
rs.MoveNext
Wend
' Create datestamp in Footer
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageFooter, , Now(), 0, 0)
' Create page numbering on footer
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
txtNew.SizeToFit
' Open new report.
DoCmd.OpenReport rpt.Name, acViewPreview
'reset all objects
rs.Close
Set rs = Nothing
Set rpt = Nothing
Set db = Nothing
End Sub