unwanted "enter parameter value" popup when OpenReport is called

[hung_ko]

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

[hung_ko]

i forgot to mention that it seems the popup show after
DoCmd.OpenReport rpt.Name, acViewPreview is called


i really need to turn the popups off, thanks

[weekend00]

the popup is from the report, not from the code. actually, from the query (data source) of the report, so please check the data source of the report

[hung_ko]

Hi, Weekend.
could you be more specific?
all i have is a select query and i had gotten rid of all the alias for fields and tables
but it will still prompt for all fields that i selected
it DIDN'T prompt for radio_variant_title_tx though

any ideas? thank you
strSql = "select radio_family.radio_family_title_tx, " + _
"radio_variant.radio_variant_title_tx, " + _
"waveform.waveform_title_tx, " + _
"freq_range_link.freq_min, " + _
"freq_range_link.freq_max, " + _
"bandwidth_rate.bandwidth_rate_title_tx, " + _
"freq_modulation.freq_modulation_title_tx, " + _
"comsec_device.comsec_device_title_tx, " + _
"comsec_device.internal_ind, service.service_title_tx " + _
"from radio_family, radio_variant, variant_waveform_link, " + _
"waveform, freq_range_link, bandwidth_rate, freq_modulation_link, " + _
"freq_modulation, variant_comsec_link, comsec_device, " + _
"variant_platform_link, platform , service " + _
"where radio_variant.radio_variant_id_cd = variant_waveform_link.[Radio Variant] " + _
"and radio_family.radio_family_id_cd = radio_variant.[Radio Family] " + _
"and variant_waveform_link.[Waveform] = waveform.waveform_id_cd " + _
"and freq_range_link.parent_id_cd = waveform.waveform_id_cd " + _
"and freq_range_link.[Frequency Rate] = bandwidth_rate.bandwidth_rate_id_cd " + _
"and freq_modulation_link.parent_id_cd = waveform.waveform_id_cd " + _
"and freq_modulation_link.[Frequency Modulation] = freq_modulation.freq_modulation_id_cd " + _
"and variant_comsec_link.[Radio Variant] = radio_variant.radio_variant_id_cd " + _
"and variant_comsec_link.[Comsec Device] = comsec_device.comsec_device_id_cd " + _
"and variant_platform_link.[Radio Variant] = radio_variant.radio_variant_id_cd " + _
"and variant_platform_link.[Platform] = platform.platform_id_cd " + _
"and platform.[Service] = service.service_id_cd "

[weekend00]

the easy way to create a query is using Access Designer ( query in design view), you can inner join all the tables easily. run the query to confirm the syntax is correct.

after finish the design, go to SQL view, copy and paste the code to you VBA editor,