option group/radio button

[Aria]

Hi Tom,
OK, thanks. Maybe I didn't do it right or I'm missing something. Since it
worked in the test, I put it in my production copy and now have a problem
that I discovered when I created a report. So, I looked at the query in
datasheet view and see that the test field is storing the values for my
option group. Is this supposed to happen? My option group is to identify
phone types. I have a field to account for that in my table. It's empty. The
query for my report uses the values in the phone type field hence no records
pulled for the report. Well, there were 3 records but then I did something
dumb...fast forward to 3 a.m.; issue unresolved. So, can you assist?

Here's the SQL:
UPDATE tblPhones SET tblPhones.Test = 0
WHERE (((tblPhones.Test) Is Null));

 

[Tom Wickerath]

Hi Aria,

So, I looked at the query in
datasheet view and see that the test field is storing the values for my
option group. Is this supposed to happen?

Yes. The Option Group is a bound control. It is bound to the field named Test.

My option group is to identify phone types. I have a field to account for
that in my table. It's empty.

Is this field numeric or text? The reason I ask is that the Option Group can
only be used to store a numeric value. It appears as if you will possibly
need to add a new lookup table, related 1:M (one-to-many) to your Test field
(perhaps rename Test to a more suitable name, such as fkPhoneType, where fk
stands for "foreign key"). You'll need a PhoneTypes tables that has a
corresponding record for each possible numeric value. If that's the case,
then you might want to consider just replacing the option group with the
three buttons with a simple combo box instead.

The query for my report uses the values in the phone type field hence
no records pulled for the report.

If you have a PhoneType table that is a parent (lookup) table, have you
created a relationship (Tools | Relationships) with enforced referential
integrity (RI)? It sounds to me like you might have a situation where you are
using an Inner Join in your query, which means that records will only be
returned if there are matching values in both tables. If you've been able to
add unmatched values to the child table, then you have an RI violation, which
would have been prevented if a relationship included the Enforce Referential
Integrity option checked. In other words, if you have a PhoneType lookup
table, does it have a corresponding record with 0 as a value?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Aria]

Hi Tom,

Is this field numeric or text?

Yes, it's numeric.

If that's the case, then you might want to consider just replacing the option group >with the three buttons with a simple combo box instead.

I relunctantly agree. That may be the easiest thing for me to do. I created
2 queries actually, one for campus phones and one for district cell phones. I
didn't want it to pull cell phone data for the campus phone report and vice
versa. I don't have a Phone Type table but for all of the tables that I do
have, referential integrity has been enforced. You're right about the inner
joins. I used the junction tblPhonesEmps because more than one staff person
can be assigned to the same location phone.

The SQL for Campus Phones is:
SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID, tblEmployees.LastName,
tblEmployees.FirstName, tblPhones.PhoneNumber, tblPhones.PhoneDescription,
tblPhones.PhoneType
FROM tblPhones INNER JOIN (tblEmployees INNER JOIN tblPhonesEmps ON
tblEmployees.EmpID = tblPhonesEmps.EmpID) ON tblPhones.PhoneID =
tblPhonesEmps.PhoneID
WHERE (((tblPhones.PhoneType)=2))
ORDER BY tblEmployees.LastName, tblEmployees.FirstName;

I'll change it to a phone type combo. Thank you very much for your time and
assistance. I appreciate it.