Can't update data in query..

  • Thread starter HLCruz via AccessMonster.com
  • Start date
H

HLCruz via AccessMonster.com

I have a database that uses a table with Zip codes and City,State,County
information. I would like to use this data in my various forms so that my
users will only need to enter a zip code and the city, state, county will
populate on the form. I have been trying to do this by including that table
in my queries for the forms, however, I have just run into a situation where
doing so makes my query unable to update the data on the form. Obvioulsy
this is useless since the forms are set up to make data entry easier. Is
there another way to accomplish this same result? Or, is there a way to
manipulate the settings on my query? Here is some basic information from my
example:

Thanks in advance to anyone who can help!

SELECT tblCamperInformation.CamperID, tblCamperInformation.FolderID,
tblCamperInformation.LastName, tblCamperInformation.FirstName,
tblCamperInformation.Gender, tblCamperInformation.Birthdate,
tblCamperInformation.Race, tblCamperInformation.[Parent/Guardian],
tblCamperInformation.Address1, tblCamperInformation.Address2,
tblCamperInformation.Zip, tblCamperInformation.HomePhone,
tblCamperInformation.MotherWork, tblCamperInformation.FatherWork,
tblCamperInformation.EmergencyContact, tblCamperInformation.Notes,
tblCamperInformation.ShiningStar, tblCamperInformation.ReturnedMail,
tblCamperInformation.InelligibleForCamp, [FirstName] & " " & [LastName] AS
FullName, tblCamperRegistration.RegistrationID, tblCamperRegistration.
CampYear, tblCamperRegistration.HorseCamp, tblCamperRegistration.Deposit,
tblCamperRegistration.PillBox, tblCamperRegistration.ReturningCamper,
tblCamperRegistration.HealthPlan, tblCamperRegistration.Neurologist,
tblCamperRegistration.[Info/HlthHistForm], tblCamperRegistration.ReleaseForm,
tblCamperRegistration.CodeOfConduct, tblCamperRegistration.PacketSent,
tblCamperRegistration.PacketSentDate, tblCamperRegistration.Notes,
tblCamperRegistration.TShirtSize, tblCamperRegistration.FinancialAsst,
tblCamperRegistration.HouseholdIncome, tblCamperRegistration.PaymentPlan,
tblCamperRegistration.RequestedInfo, tblCamperRegistration.FollowedUp,
tblCamperRegistration.ReferredBy, tblCamperRegistration.[1stTimeCamper]
FROM (tblCamperInformation LEFT JOIN tblCamperRegistration ON
tblCamperInformation.CamperID = tblCamperRegistration.CamperID) INNER JOIN
EFM_ZIPS ON tblCamperInformation.Zip = EFM_ZIPS.ZIP
ORDER BY tblCamperInformation.LastName, tblCamperInformation.FirstName;
 
G

Guest

hi,

When you base the fields value directly on a table, you are bound to run
into problems. This looks like a case where the table can't be updated. I
would suggest you use a hidden field on your form based on something like the
following:

controlsource for [citypaster] field:
DLookUp("city","tablename","zipcode= [forms]![formname]![zipcode]")
You can also have a field that looks up the state:
controlsource for [statepaster] field:
=DLookUp("state","tablename","zipcode= [forms]![formname]![zipcode]")

You can add code to the [zipcode] field AfterUpdate Event, which populates
the [city] and [state] fields in your form:

With CodeContextObject
..city = [forms]![formname]![citypaster]
..state = [forms]![formname]![statepaster]
end with

There is probably countless ways you can achieve what you want, but this is
just one of them.


Hope this helps!

HLCruz via AccessMonster.com said:
I have a database that uses a table with Zip codes and City,State,County
information. I would like to use this data in my various forms so that my
users will only need to enter a zip code and the city, state, county will
populate on the form. I have been trying to do this by including that table
in my queries for the forms, however, I have just run into a situation where
doing so makes my query unable to update the data on the form. Obvioulsy
this is useless since the forms are set up to make data entry easier. Is
there another way to accomplish this same result? Or, is there a way to
manipulate the settings on my query? Here is some basic information from my
example:

Thanks in advance to anyone who can help!

SELECT tblCamperInformation.CamperID, tblCamperInformation.FolderID,
tblCamperInformation.LastName, tblCamperInformation.FirstName,
tblCamperInformation.Gender, tblCamperInformation.Birthdate,
tblCamperInformation.Race, tblCamperInformation.[Parent/Guardian],
tblCamperInformation.Address1, tblCamperInformation.Address2,
tblCamperInformation.Zip, tblCamperInformation.HomePhone,
tblCamperInformation.MotherWork, tblCamperInformation.FatherWork,
tblCamperInformation.EmergencyContact, tblCamperInformation.Notes,
tblCamperInformation.ShiningStar, tblCamperInformation.ReturnedMail,
tblCamperInformation.InelligibleForCamp, [FirstName] & " " & [LastName] AS
FullName, tblCamperRegistration.RegistrationID, tblCamperRegistration.
CampYear, tblCamperRegistration.HorseCamp, tblCamperRegistration.Deposit,
tblCamperRegistration.PillBox, tblCamperRegistration.ReturningCamper,
tblCamperRegistration.HealthPlan, tblCamperRegistration.Neurologist,
tblCamperRegistration.[Info/HlthHistForm], tblCamperRegistration.ReleaseForm,
tblCamperRegistration.CodeOfConduct, tblCamperRegistration.PacketSent,
tblCamperRegistration.PacketSentDate, tblCamperRegistration.Notes,
tblCamperRegistration.TShirtSize, tblCamperRegistration.FinancialAsst,
tblCamperRegistration.HouseholdIncome, tblCamperRegistration.PaymentPlan,
tblCamperRegistration.RequestedInfo, tblCamperRegistration.FollowedUp,
tblCamperRegistration.ReferredBy, tblCamperRegistration.[1stTimeCamper]
FROM (tblCamperInformation LEFT JOIN tblCamperRegistration ON
tblCamperInformation.CamperID = tblCamperRegistration.CamperID) INNER JOIN
EFM_ZIPS ON tblCamperInformation.Zip = EFM_ZIPS.ZIP
ORDER BY tblCamperInformation.LastName, tblCamperInformation.FirstName;
Click to expand...
 
Top