TxtBox Control Source not In form Record Source

[Qwert0000]

Howdy,

I have a DB with 3 Main tables all linked by a SSN field. Table 1 contains "contact info" records on everyone. Table 2 has a 1 to 1 relationship with table 1 containg records only on "students". Table 3 has a 1 to many relationship containing "attendance data" on all of the students.

What I am tring to do is assign a locked textbox that shows the accumalated sum of training days for a record on my training data form. I have a sum caculated in a querry, but if I add that information to my form query the record doesnt show up in my form until I add at least 1 attendance record for that student.

I current add a blank record in the attendance table for that SSN when a new student is added, but that seems kind of ugly to me.

I would like to use an IIF statement or some vb code that basicly checks my grouped by SSN attendance query for a Days Completed record on the current SSN and returns that value to the form. IF no record is found place 0 in the txt box.

The other problem I seem to have is along the same lines. I cant seem to figure out how to call for an item from another table to be place in a report/Form.

I have created a table with information for headers and such that occasionally changes. Instead of hard codeing a label in the form/report. I would like to use this table to populate header info. I point to the Table.Field for the control source but get #name in my form. This table has 1 record with a several fields for different header information.

Thanks for any help you can give this newb!

 

[Eupher]

You could try using the Nz() function in the field that calculates the training days, that way if there is no data, it will insert a zero (or any other value you specify).

You could try DLookup() for your lable fields.

Ken S.

 

[Qwert0000]

DLookup did the trick for the lables. Just what I was looking for Thank you so much.

Still working on the NZ Function.

 

[Qwert0000]

I used a NZ(Dlookup()) on the form to pull a sum total off a querry. It is working fine but seems slow. The rest of the form load quick the result for this text box comes in a sec or two later.

Is there a way to use a NZ function in side my record source query? I played with it for a bit but couldnt seem to make it work. Query would pull no records.

 

[Eupher]

Qwert0000,
Yes, you can use it as part of an expression in your query. For instance, in a field block in the query grid, if you wanted to pull all training days and substitute a zero for all records that have a null value in that field, you would put:

Expr1: Nz(

.[Field], 0)

...where Expr1 is what you wish to name the expression field, and

.[Field] is the table and field you want to evaluate.

HTH...

Ken S.

 

[Qwert0000]

That did the trick. I had to change the Join type (still dont understand the different types), but I got it doing what I wanted. Thanks again.

Eric