Populating Form Using ComboBox Selection

[RunTime91]

Hello ~



The form I am trying to populate has 44 unbound textboxes.

The idea is very straight forward, the user selects a PK value from a combobox and the rest of the form populates.

I am currently have a query as the Form Record Source and the After Update event of the combobox.

I have tried a few different ways using Requery - nothing

I have tried using several versions of the DoCmd Instruction - nothing

I have tried to tie this into a RecordSet - nothing

I would like to avoid using 44 DLookups - which is why I am here...

I'm not sure what code to post with this one as I have left off using a Requery in the After Update of the ComboBox.

The query is as follows:

SELECT (44 Fields) 
FROM TblData 
WHERE TblData.RefNum (the PK) = [Forms].[FrmUnited].[CmboRef].[Text] (which of course houses only the PK's from TblData

If I can provide any additional information please just ask...

Thank You...

[RayMilhon]

since the textboxes are unbound you must assign a value before they will display. Based on how you're doing this you would need 44 Assignments to assign each field to a textbox. You're best bet is to make the souce for the form as the query. Assign each textbox to one of the fields in the query using the control property. Then in the combo box for your selection in the Onchange event use the requery statement that'll refresh all of the controls on the form based on your combobox.

[RunTime91]

Ray ~ Thank you...

No question you're spot-on...

But one question I do have is, I'm confused when you say: "Assign each textbox to one of the fields in the query using the control property."... When I think of using the control property I think of the Control Source property... in the properties list. What it sounds like you are saying here is use the control property in the query; which, I am unfamiliar...I'm sure I'm just not interpreting this correctly but can you repost with some clarification on that part of your answer - Thanks Ray

[Micron]

the control property in question would be the Control Source for a text box or combo, which would bind the control to a field. Changing data in that control will alter the data in the underlying query (if it is updatable) or table.

The requery method applies to the form. The event for this probably should be After Update (as you mentioned). On Change will fire upon each keypress if the user can type directly into the control - likely not what you want. It will fire after the After Update event, so you might be OK if no one can type in the control, but I wouldn't use it for processing a selection change. Based on your post, I have to wonder if you've bound the combos or just the textboxes. Sometimes combos aren't bound even though they have a row source (that which populates the list) since inadvertent new selections from the list will alter the underlying data. I point that out because you seem to be populating a lot of controls based on the selection, and you might not want to be altering an entire record by accident.

[RayMilhon]

Micron is correct use the combo boxes After update event. The last time I used this I used on change because I was filtering a list box as the user typed in search criteria.

[RunTime91]

Ray & Micron ~ Can't thank you guys enough

I have shifted gears here due to there were more issues awaiting me once this issue was solved...

I recreated the form to incorporate a subform, relinked all the textboxes and got the subform to populate perfectly using the combobox selection.

But, when I added the query for the main form, I received back a RT Error 3022 -

"The changes you requested to the table were not successful because they would create duplicate values in the index...blah, blah, blah."

So now my question is, how do I convince my form that I just want to view a record - not add a record??

As for the code, I'll post both queries and then what I have in the AfterUpdate event of the Combobox

Here is the query for the main form

SELECT tblData.Reipient, tblData.ReipientFaxNum, tblData.RequestDt, tblData.RequestPriority, tblData.DMIS, tblData.FacilityName, tblData.R_ProviderNm, tblData.R_ProviderNPI, tblData.RefNum, tblData.UIN, tblData.SponsorSSN, tblData.PatientID, tblData.PatientNm, tblData.PatientDOB, tblData.PatientGender, tblData.PatientAddress, tblData.PatientCity, tblData.PatientState, tblData.PatientZip, tblData.PatientPhone, tblData.EnrolledDMIS, tblData.EnrolledFacility, tblData.EnrolledRegion, tblData.SHCP, tblData.PrimaryProvisionalDiagnosis, tblData.ReasonForRequest, tblData.HealthNetReviewerNm, tblData.S_ProviderSpecialty, tblData.S_ProviderNm, tblData.S_ProviderPhone, tblData.S_ProviderNPI, tblData.S_GroupNm, tblData.S_GroupAddress, tblData.S_GroupCity, tblData.S_GroupState, tblData.S_GroupZip, tblData.S_GroupNPI, tblData.S_FacilityName, tblData.S_FacilitySpecialty, tblData.S_FacilityAddress, tblData.S_FacilityCity, tblData.S_FacilityState, tblData.S_FacilityZip, tblData.S_FacilityNPI
FROM tblData
WHERE (((tblData.RefNum)=[Forms].[FrmOverSeas].[CmboRef].[Text]));

Here is the query for the subform

SELECT tblServiceLines.RefNum, tblServiceLines.ServiceDecision, tblServiceLines.ServiceType, tblServiceLines.PlaceOfService, tblServiceLines.ServiceCodeRange1, tblServiceLines.ServiceCodeRange2, tblServiceLines.ServiceQty, tblServiceLines.AuthorizedStartDate, tblServiceLines.AuthorizedEndDate, tblServiceLines.ServiceDescription
FROM tblServiceLines RIGHT JOIN tblData ON tblServiceLines.RefNum = tblData.RefNum
WHERE (((tblServiceLines.RefNum)=[Forms].[FrmOverSeas].[CmboRef].[Text]));

And finally here is the code stored in the AfterUpdate Event of the ComboBox

Private Sub CmboRef_AfterUpdate()
Dim Frm1 As Form
Set Frm1 = Forms("FrmOverSeas")
'FrmOverSeas.Requery
Frm1.Requery
tblServiceLines.Requery
End Sub

A couple additional notes - TblServiceLines in the above query is actually the SubForm. For whatever reason the SubForm adopted the table name and any attempt to change it (at least by me) to FrmServiceLines renders the form useless

Also, for some reason I had to Dim the main form requery but the SubForm requery (TblServiceLines.Requery) works perfectly with setting it as a variable

For all intents and purposes, I am brand new to subforms (can you tell?)

Anyway, the code errors @ Frm1.Requery, but commenting all of the main form code and the subform displays all the service lines associated with the Ref# perfectly.

Sorry for the book - Thanks in advance for any help... I feel as though I'm close -