Combo question

H

Hilary Ostrov

I have a form, myInvoice, on which the user will typically begin by
selecting a SiteID from cboSiteID. After he's selected, a number of
fields on the form will be populated in accordance with
cboSiteID_AfterUpdate. It works fine.

Sometimes, though, the user does not know (or has not been given) the
SiteID, and rather than scrolling through 300+ entries in cboSiteID
(which is bound to SiteID but also displays [Site Name] ), he can
search via cboSiteName which becomes temporarily visible. After the
user selects the Site Name, and ...

Private Sub cboSiteName_AfterUpdate()
Me.cboSiteID = Me.cboSiteName.Column(1)

End Sub

... regardless of whether I use Me.cboSiteID.SetFocus above, SiteID
displays correctly in cboSiteID and the following fields also display,
as expected:

[Site Name]
[Site Address]
[City]
[Billing Contact]
[Site Contact]

However cboSiteID_AfterUpdate does not fire/work (sorry, I don't know
the correct terminology) to display:

BillTo
InCareOf
BillAddress
BillCity
txtOtherSiteAddresses

until (and unless) SiteID from cboSiteID is reselected and updated.
I'm sure there must be a way around this - and possibly/probably the
obstacle lies somewhere in the source query for cboSiteID, because -
with the exception of txtOtherSiteAddresses (unbound) - the others are
IIF concatenations. The SQL is as follows:

==
SELECT Jobsites.SiteID, Jobsites.[Site Name], IIf([Owner] Like
"Strata",[Site Name] & " " & "Strata" & " " & [SiteID],[Site Name] &
" " & [Owner]) AS BillTo, IIf([mName] Is Not Null,"c/o " & [mName])
AS IncareOf, IIf([Managed By] Is Not Null,[mAddress],[Site Address])
AS BillAddress, IIf([Managed By] Is Not Null,[CityID] & ", " &
[Prov_State] & " " & [mPSTL CDE],[City] & ", " & "BC" & " " &
[PostalCode]) AS BillCity, Jobsites.[Site Address], Jobsites.City,
Jobsites.[Other Site Addresses], Jobsites.[Billing Contact],
Jobsites.[Site Contact]
FROM (Jobsites LEFT JOIN ManagementCompany ON Jobsites.[Managed By] =
ManagementCompany.ManageCodeID) LEFT JOIN qryCities ON
ManagementCompany.mCITY = qryCities.CityID
ORDER BY Jobsites.SiteID;
==

But, I'm a relative newbie and I haven't a clue how I might correct
the problem:( Btw, I apologize for the apparent duplications of data
that will be written to the Invoice table via this form. I know that
some data looks like really should appear only in rptInvoice - but I
thought very carefully before including them, and I do have reasons
for each one.

While I know far more now than when I started this project, my
programming knowledge/skills are still *very* limited!

Thanks for any guidance/assistance on this.
hro
 
V

Van T. Dinh

That's true. When you assign a Value for cboSiteID by code, the
cboSiteID_AfterUpdate Event doesn't occur. However, after you assign the
value, you can execute the cboSiteID_AfterUpdate Event code by calling the
Sub with:

Call cboSiteID_AfterUpdate

--
HTH
Van T. Dinh
MVP (Access)



Hilary Ostrov said:
I have a form, myInvoice, on which the user will typically begin by
selecting a SiteID from cboSiteID. After he's selected, a number of
fields on the form will be populated in accordance with
cboSiteID_AfterUpdate. It works fine.

Sometimes, though, the user does not know (or has not been given) the
SiteID, and rather than scrolling through 300+ entries in cboSiteID
(which is bound to SiteID but also displays [Site Name] ), he can
search via cboSiteName which becomes temporarily visible. After the
user selects the Site Name, and ...

Private Sub cboSiteName_AfterUpdate()
Me.cboSiteID = Me.cboSiteName.Column(1)

End Sub

... regardless of whether I use Me.cboSiteID.SetFocus above, SiteID
displays correctly in cboSiteID and the following fields also display,
as expected:

[Site Name]
[Site Address]
[City]
[Billing Contact]
[Site Contact]

However cboSiteID_AfterUpdate does not fire/work (sorry, I don't know
the correct terminology) to display:

BillTo
InCareOf
BillAddress
BillCity
txtOtherSiteAddresses

until (and unless) SiteID from cboSiteID is reselected and updated.
I'm sure there must be a way around this - and possibly/probably the
obstacle lies somewhere in the source query for cboSiteID, because -
with the exception of txtOtherSiteAddresses (unbound) - the others are
IIF concatenations. The SQL is as follows:

==
SELECT Jobsites.SiteID, Jobsites.[Site Name], IIf([Owner] Like
"Strata",[Site Name] & " " & "Strata" & " " & [SiteID],[Site Name] &
" " & [Owner]) AS BillTo, IIf([mName] Is Not Null,"c/o " & [mName])
AS IncareOf, IIf([Managed By] Is Not Null,[mAddress],[Site Address])
AS BillAddress, IIf([Managed By] Is Not Null,[CityID] & ", " &
[Prov_State] & " " & [mPSTL CDE],[City] & ", " & "BC" & " " &
[PostalCode]) AS BillCity, Jobsites.[Site Address], Jobsites.City,
Jobsites.[Other Site Addresses], Jobsites.[Billing Contact],
Jobsites.[Site Contact]
FROM (Jobsites LEFT JOIN ManagementCompany ON Jobsites.[Managed By] =
ManagementCompany.ManageCodeID) LEFT JOIN qryCities ON
ManagementCompany.mCITY = qryCities.CityID
ORDER BY Jobsites.SiteID;
==

But, I'm a relative newbie and I haven't a clue how I might correct
the problem:( Btw, I apologize for the apparent duplications of data
that will be written to the Invoice table via this form. I know that
some data looks like really should appear only in rptInvoice - but I
thought very carefully before including them, and I do have reasons
for each one.

While I know far more now than when I started this project, my
programming knowledge/skills are still *very* limited!

Thanks for any guidance/assistance on this.
hro
Click to expand...
 
H

Hilary Ostrov

That's true. When you assign a Value for cboSiteID by code, the
cboSiteID_AfterUpdate Event doesn't occur. However, after you assign the
value, you can execute the cboSiteID_AfterUpdate Event code by calling the
Sub with:

Call cboSiteID_AfterUpdate
Click to expand...

Wow! Thank you so much, Van! That works perfectly!
hro
 
Top