Hey all, I have 10 fields of check amounts in a table,
Not all 10 fields will have amounts for every record, some customers will pay with different amounts of checks.

i set all but the first's visible field = to flase on the report

So I need somehow the fields with data in them for the visible=true so it prints on the report. If they dont have data, the field shall remain invisible.

not to familiar with VB with reports, can anyone lend me a hand?
thanks!

If your controls are in the detail section

Detail_Format()
'Simple Version
if not isnull(me.NameOfCheckBox) then me.NameOfCheckBox.visible = true
else
me.NameOfCheckBox.visible = true
end if

'Better Version
Dim ctl As Control, rpt As Report
rpt = Me.Name
For Each ctl In rpt.Controls
If ctl.ControlType = acCheckBox Then
If Not IsNull(ctl) Then
ctl.Visible = True
Else
ctl.Visible = True
End If
End If
Next

It looks as though you have a design problem, if a customer can make several payments then you should have a separate table for payments where one customer could have between one and ten records depending on the number of payments and not ten fields within one record

thank you but that didn't do it for me, i don't know if you understood what i was referring to...

There are 9 fields. Every record will have a different number of those fields with data, some will stay null. The report is bound to a query and it pulls a loan number and the 9 fields. I need just the fields with data in them to be visible on the report. The fields that are null should be invisible.

It looks as though you have a design problem, if a customer can make several payments then you should have a separate table for payments where one customer could have between one and ten records depending on the number of payments and not ten fields within one record


i actually did it as you described, but it led to other problems, 2 users couldnt use the database at the same time, so this is what I came up with

It looks as though you have a design problem, if a customer can make several payments then you should have a separate table for payments where one customer could have between one and ten records depending on the number of payments and not ten fields within one record
Far too perceptive, as usual :p

i actually did it as you described, but it led to other problems, 2 users couldnt use the database at the same time, so this is what I came up with
Why did this cause problems - if you split into FE/BE (or even access the same Db), this should not create difficulties.

it was design issues... i always split into FE/BE and it was the same DB

basically i didnt want the user to enter in a loan number for each check, i just wanted that field to be inputted once, so that was on the main form and i made a subform with check information, and it was mismatching one another.

i have the db zipped here but i need a place to host since I'm at work, its 10 megs

im in the process of trying to host it for u guys

i also thought of another method, maybe easier

theres a check box on the report, maybe when value=-1 it will set the 9 fields to visible=true, any way to do that?

If your subform was mismatching then you didn't have the child/master links or the table relationships set correctly.
Can one customer have more than one loan for which many payments can be made?

yes, 1 customer and make a payment with 1 check for 2 loans, but I just put those in as 2 seperate transactions...

im pretty sure the linkchild and master fields were fine, i never actually checked to see how it didn't function properly... i was told it mis matched so i changed it

DB can be found here... 10 megs
http://stevezaiderman.com/yaber/Payment-2.zip

the report you can use is called rpt_test
I removed all ODBC fields from there

yes, 1 customer and make a payment with 1 check for 2 loans, but I just put those in as 2 seperate transactions...



Then in simple terms you need three tables
one which holds the Customer details
Two which holds the custId and the loan details
Three which holds the LoanId and the payments details.

To enter data at form level you need A main form and two nested subforms

Having a quick look at your Db immediately brings up data structure problems. Rich has set you off on the right track. Re-evaluate how you need to store data as you data structure and therefore forms are making it difficult for you to track data effectively.

Your report rpt_test violated 1NF rules (well the report doesn't, tbl_LoansReceived definitely does)

I suggest you read the resources on normalisation and rejig your data accordingly. You may be limited by your ODBC databases but your Access tables need sorting regardless.

well i thought to just put it into 1 table because all that gets entered in is a loan number and check numbers and check amounts, time and date gets populated in there too..

so there would be almost the same type of data in the tables..
the way i had it before was date, time loan number in 1 table
and date, time, loan, amounts and numbers in the other, and set a relationshop with the loan numbers