Subform if statment for field values

[dickohead]

Hey Guys,

I have a form that contains a subform. Let's use the example of a video store;
We have a record for each person who hires videos containing personal details, and a subform containing all the videos they have hired and whether or not they've been returned.

That all works great!

How do I get the master form to display a label if ANY of the hired videos are overdue?

So we have:
Video 1 - Returned
Video 2 - On Loan
Video 3 - On Loan
Video 4 - Overdue - $5

Can I somehow push the entires into an array each time they are loaded up? So when i go to the next record, the array is filled with new values, keeping in mind that someone may only have 1 video, while others could have hundreds!!!

I have a really bad "for" loop example that *might* help clear up what i'm trying to achieve.

For $i in VideoReturnStatus[] (
if [ReturnStatus] = "Overdue" then
[SomeLabel] = "OVERDUE"
else if VideoReturnStatus[] = "On Loan" then
[SomeLabel] = "On Loan"
else
[SomeLabel] = ""
end if
)

Does that make sense? So if any of the videos are overdue it displays OVERDUE, if any of them are on loan, and none are overdue it displays On Loan, and if their videos are all returned, display nothing.

I have had bugger all success trying to talk to the subform data so far. I'd also like to be able to have a summary total of how much they owe in overdue video fines.

Can someone help me out with this?

I'm not scared of using VBA - just unsure what to use so far.

Thanks for reading the above! I hope you can help.

 

[boblarson]

Maybe this will help you

http://www.access-programmers.co.uk/forums/showthread.php?t=127981

 

[dickohead]

That's a good reference site, which I quickly bookmarked!

But how do I reference multiple rows at a time, when the number of rows WILL vary from record to record?

 

[boblarson]

You have to iterate through the recordset

 

[dickohead]

Forgive my ignorance, does that mean check them one by one?

 

[boblarson]

Maybe. But, you could create a query that uses the criteria of the ID field and where the value is "overdue" and then use DCount to see if there are any records and if so then display the label.

 

[dickohead]

Sorry to be a pain...

I have got this in a query:

[Alert], [ID Number] and the following Expression.
=Payment Status: DCount([IDNumber],"qryVideosSubForm",[Alert]="Overdue")

[Alert] is my field that contains the Overdue value, and [IDNumber] is my reference to the main table for each record.

I get ye olde #Name? error when I apply the above query to the control source field.

When I run the query stand-alone, it returns me with an odd set of data, it seems to be counting the total number of occurences of OverDue for the ENTIRE data set, rather than summarising them for each [IDNumber]...

I hope that makes some sense?

 

[boblarson]

I didn't mean to put the DCount in the query. I meant to use the query in the DCount. In other words the control source for the text box in your main form you would use:

=IIf(DCount([IDNumber],"qryVideosSubForm",[Alert]="Overdue")>0,"Overdue","")

 

[dickohead]

Alright...

Starting to get fed up with Access... woo-sa, woo-sa.

I've written a query that gives me only the ID number's of the customers who have overdue videos. IE:

Customer 123 has 3 overdues, customer 4 has 1 overdue, customer 7 has 8 overdues.

I'd get:
123
4
7

How do I now get a text-box/label to display Overdue if the ID number in my new query matches that of the current record?

I have tried EVERY possible method of referencing the stupid fields, but all I ever get is #name? in the text field.

Surely getting access to reference fields for the current record can't be this hard?

Stuff it - i'm going home!

 

[gemma-the-husky]

assuming you have

1. a customer table
2. a hires table linked to the customers
3. information in the hires table that will identify overdues

then
1. do a query from the hires table to identify the detailed overdues with eg date hired/date due/overdue period/overdue fee/customer

2. copy this to another query - using the same filter that identified the overdues - add the customers table and include name\address\member no etc - now turn this into a totals query by customer (or a unique values query)- which should give you just the customers with overdues

3. now one way is
a) have a main form based on query 2, with a subform set to continuous form based on query 1
you will only get the overdue customers shown, and as you step through the
customers, the overdue hires appropriate to each customer will immediately be shown

very little programming required, if any

 

[dickohead]

I have my primary customer table, with a one-to-many relationship on my hires table.

Subform Query
I then have a query that evaluates the dates to decide if the video is overdue yet or not ("DateField<Date()") and displays OVERDUE in the Alert field if it is in fact overdue - so it's not in the table, it's only in my query. The subform is then built off this query. All the correct data is coming through in my subform, which is good.

OverDue Query
I have then created another query that joins my tables together and shows me only the ones that have OVERDUE in my alert field. This is then grouped by IDNumber so that if user 123 has 4 videos overdue, user 123 only shows up once.

Main Form
I have the created a text box that has my Overdue Query set as it's control source.

So when I run my overdue query I get:
User Status
123 OverDue
456 OverDue
789 OverDue

But if I assign this query as my control source - I get the #NAME? error appearing in my text box.

How would I reference this correctly? This is the issue I've had the whole time with this mongrel.

I don't really care HOW I get the main form to display if any of their videos are overdue, i just need it to be there!

So - to summarise what I WANT:

Main Form
Name
Address
Contact Details
***Overdue status box (visible if any of the videos are overdue)
***Total overdue payment owing (summary of their videos that are overdue and have money owing)

Sub Form
Videos on hire
Date Hired
Status (overdue, on loan, returned)
Overdue payment owing

***These are thge things I want to display, but as yet have had no success getting to work. I can create queries that give me these things just fine, but when trying to assign a single field to show me the value of the query - I constantly get #NAME?

Slowly getting frustrated.