Drop Down form doesn't fill in fields...

[Guest]

I created a parameter query in the form of a drop down box that, once an
option is selected, should display a form with a number of fields pre-filled
from a record chosen by the drop-down selection.

The drop down has a command button with the following code in the Onclick
event:

Private Sub cmdCreateReport_Click()

If IsNull(Me.cboPendingUIRLookUp) Or False Then

MsgBox "Please select a UIR from the drop down list!", vbInformation,
"No Selection"
Me.cboPendingUIRLookUp.SetFocus
Else

DoCmd.OpenForm "frmUIRFollowUp"
DoCmd.Close acForm, "frmOpenUIRLookUp"

End If
End Sub

As it now stands when I click the button I get the form but none of the
selected data is filled in. The fields are blank.

The only thing I can think to do is upload the database and see if there's
anyone willing to download it and take a look. So I've attached a link here...

http://www.box.net/public/static/flkxlzav6f.rar

....where those willing to help are welcome to download it in .rar format.

The form that with the initial drop down box is called frmOpenUIRLookUp (UIR
= Unusual Incident Report, by the way). The form that the drop down is
supposed to feed into is frmOpenUIRLookUp and the query that is supposed to
support that form is qryUIRFollowUpData.

Many, many thanks for the help,
David

 

[Brian Bastl]

Instead of expecting anybody to download your db to help you, perhaps you
can simply post the SQL used to populate your combo. While you're at it,
what is the SQL of your qryUIRFollowUpData?

I suspect that all you need is to use the Where clause of your
DoCmd.OpenForm statement to view the appropriate record. Something like:

DoCmd.OpenForm "frmUIRFollowUp", , , "[UIR]=" & Me.cboPendingUIRLookUp
or
DoCmd.OpenForm "frmUIRFollowUp", , , "[UIR]='" & Me.cboPendingUIRLookUp &
"'"

where UIR is a field in the form's recordsource.

Brian

 

[Guest]

Thanks for the advice.

Here's the code for the drop down menu:

SELECT Table1.AIN, Table1.[HAMM # / Incident #], Table1.[Incident Date],
Table1.[Consumer First Name], Table1.[Consumer Last Name]
FROM Table1
WHERE (((Table1.[UIR Status])="Pending" Or (Table1.[UIR Status])="Pending
via SRU"))
ORDER BY Table1.[Incident Date] DESC;


And here's the code (since you asked) for qryUIRFollowUpData:

SELECT tblUIRFuData2.AIN, tblUIRFuData2.FUN, Table1.Program,
Table1.ProgStreet, Table1.ProgCitySt, Table1.ProgZip, Table1.[Consumer First
Name], Table1.[Consumer Last Name], Table1.[Incident Date],
Table1.IncidentDescription, tblDDDIncidentCodes.[Incident Grade],
tblUIRFuData2.chkNewInfo, tblUIRFuData2.chkInvComp, tblUIRFuData2.chkCommDec,
tblUIRFuData2.chkOAR, tblUIRFuData2.chk30Day, tblUIRFuData2.chk3Mon,
tblUIRFuData2.chk5Mon, tblUIRFuData2.chk7Mon, tblUIRFuData2.chk9Mon,
tblUIRFuData2.chk11Mon, tblUIRFuData2.chkOET, tblUIRFuData2.chkOtherReason,
tblUIRFuData2.optUpgrade, tblUIRFuData2.optCurrStatus, tblUIRFuData2.cboPros,
tblUIRFuData2.cboIntComm1, tblUIRFuData2.txtIntComm1,
tblUIRFuData2.cboIntComm2, tblUIRFuData2.txtIntComm2, tblUIRFuData2.cboDOH,
tblUIRFuData2.cboHSPol, tblUIRFuData2.cboDCA, tblUIRFuData2.cboMedEx,
tblUIRFuData2.cboDYFS, tblUIRFuData2.cboPubAdv, tblUIRFuData2.optClosedConcl,
tblUIRFuData2.chkChgTP, tblUIRFuData2.chkTrng, tblUIRFuData2.chkPersonnel,
tblUIRFuData2.chkReassign, tblUIRFuData2.chkSusp, tblUIRFuData2.chkRemoval,
tblUIRFuData2.chkOtrAct, tblUIRFuData2.chkNoAct, tblUIRFuData2.chkPolicyChg,
tblUIRFuData2.chkChgFac, tblUIRFuData2.chkRefEmpRel, tblUIRFuData2.chkPerOtr,
tblUIRFuData2.mmoCurIncSumm, tblUIRFuData2.mmoNewInfo,
tblUIRFuData2.mmoOtrReason, tblUIRFuData2.mmoUpgrade,
tblUIRFuData2.mmoSummInvConcl, tblUIRFuData2.mmoActTaken,
tblUIRFuData2.mmoOtrRemarks, tblUIRFuData2.txtFuCompBy,
tblUIRFuData2.dtFuDate, Table1.[HAMM # / Incident #], Table1.AddComments,
Table1.[DDD Code]
FROM (tblDDDIncidentCodes INNER JOIN Table1 ON tblDDDIncidentCodes.[DDD
Code] = Table1.[DDD Code]) LEFT JOIN tblUIRFuData2 ON Table1.AIN =
tblUIRFuData2.AIN
WHERE (((tblUIRFuData2.AIN)=[cboPendingUIRLookUp].[Column(1)]));


In the meantime I'll also try the code you recommended below.

Many thanks,

David

Instead of expecting anybody to download your db to help you, perhaps you
can simply post the SQL used to populate your combo. While you're at it,
what is the SQL of your qryUIRFollowUpData?

I suspect that all you need is to use the Where clause of your
DoCmd.OpenForm statement to view the appropriate record. Something like:

DoCmd.OpenForm "frmUIRFollowUp", , , "[UIR]=" & Me.cboPendingUIRLookUp
or
DoCmd.OpenForm "frmUIRFollowUp", , , "[UIR]='" & Me.cboPendingUIRLookUp &
"'"

where UIR is a field in the form's recordsource.

Brian

 

[Guest]

Hi Brian,

I actually figured it out. I tried changing the code as you suggested and
that worked along with a few other tweeks I tried.

Thanks a bunch.
David