Relationship Help

[froggy6703]

Hello,

Newby here, and new to access too. I am working on building a registration form for an event I am working on. Right now I have everything build and working as expected, but ran into an issue while testing. I have a form created (Registration Form) that asks for basic information: Zip Code, # of Ticket, Member & How Did You Hear About Us. There is a Save button, that just moves to the next record. This then dumps into a table (Registration). I then have a separate table (ZipCodes) that contains all the zip code with 300 miles of the event. Along with the City, State, County & Distance for each zip code. I then have a one to many relationship setup between the Zip Codes field in both tables. This way all this data is fed into the Registration table.

The issue I ran into tonight, is that I entered a random zip code into the form and received the following message... You cannot add or change a record because a related record is required in table 'ZipCodes'. I get why this happening as this random zip code isn't listed in the ZipCodes table. I am wondering though is there a way around this?



My initial thought when building this was that the 300 mile radius should catch everyone that comes to the event. In the off chance that we get a visitor from further away I would still want to capture there zip code. But after the event I could just manually look up the city, state... information.

Any help with this question would be greatly appreciated.

Thank you
Matt

[Edgar]

You have two options:
-- Add the zip code to be able to use it
-- Add a field called OtherZip or UnlistedZip, something like that (this is what is normally done)

And then if you use the second, when you need to display Zip, use a simple rule like
If IsNull(OtherZip) Then
ZipToShow = NormalZip
Else
ZipToShow = OtherZip

Something like that

[Micron]

I see no need for a relationship between codes. Plus if that rule enforces referential integrity, you can't use values that are not in the zip table.
The zip table should simply be a lookup table that your zip code combo is bound to (not to be confused with lookup field). You store the zip autonumber as an fk (foreign field) in the registration table. Your query links reg table zip fk (a long) to zip table pk (primary key) and by pulling in the zip table fields you need, you get state name, abbreviation or whatever else you have in there and need.