search breaks on foreign key

[timekeepr9]

Hi,

I have 2 relevant tables, one with a list of students, and one with a list of incidents regarding students. The student's primary key is the incident table's foreign key. I have a form where the user can basically create a query through combo boxes to create a report with the selected info.

Everything works perfectly except for the student name. I have a combo box w/ the bound key being the primary key. In the on click method of the build report button, I build up a WITH sql statement and then open a report w/ the WITH statement. It's basically just a lot of if statements checking if the box is blank or not, and then doing the appropriate statement. When I try to compare the student (primary key) to the foreign key of the incident table; however, it pops up a box saying "Enter parameter value: Points Sheet Table - 1 per date.Foreign Key" which I think means its not finding the foreign key. However, the field is definitly called foreign key and is definitly in that table. If I change foreign key to some other number field it works perfectly.

I tried changing the name of foreign key to something else, but no dice. Anyone have any thoughts why this wouldn't work? Here is part of the block of code:

If Me!student <> "" Then
    whereString = whereString & stringAnd & "[Points Sheet Table - 1 per date].[Foreign Key] = " & Me!student
End If
If (Len(whereString) > 0) Then stringAnd = " AND "
If Me!Notes <> "" Then
    whereString = whereString & stringAnd & "[Points Sheet Table - 1 per date].[notes] LIKE '*" & Me!Notes & "*'"
End If

The notes works fine, by the student fails. It creates the following sql substatement:
[Points Sheet Table - 1 per date].[Foreign Key] = 189

Anyone have any thoughts? Thanks!

 

[p27br]

i think your relationships are wrong : shouldn't this be a Many To Many relationship, a student could be part of many incidents and an incident can be related to many students ?
in the case you describe, one student can be part of only one incident if the student PK is the incident ID.

 

[willir]

timekeepr9

Be ware of using reserved words. Foreign key may one of them. F_Key or FKey may be better.

Another good tip is not include spaces and special characters when nameing fields.

Next...

...its not finding the foreign key ... [Points Sheet Table - 1 per date].[Foreign Key] = 189

Does a record exist for 189? And is this student 189, and is the student in the table [Points Sheet Table - 1 per date]? (Perhaps a better name for this table would be tblPointSheet)

We need a little more meat before we can help.

 

[p27br]

also it's very bad practice to use spaces in column names.

 

[timekeepr9]

Each incident only involves one student. If more than one kid is involved, they each get their own entry. I know its bad to have spaces, but I didn't make the original tables, and its too late to change it now (used in too many places)

There is a student 189, and he does have entries in the table. I tried changing foreign key to something else, but it said the same thing, with the new name. I could try that again, maybe I screwed something up when I tried it.

 

[timekeepr9]

I figured out the problem..Anything you use in the WITH statement has to be in the report since foreign key wasn't in the report, it wouldn't work. So now to figure out if I can have it in the report without it being visible...