Record filtering with Option goup

[adamstuff]

Hi all,
I've got a form with an Option group and simple selection:

Select Case fraEEPicks.Value
    Case 0
        MsgBox "Please select a reporting form."
    Case 1
        DoCmd.OpenForm "Claims Analyst"
    Case 2
        DoCmd.OpenForm "Provider File Analyst"
    Case 3
        DoCmd.OpenForm "Repricing Analyst"
    Case 4
        DoCmd.OpenForm "CCU Analyst"
    Case 5
        DoCmd.OpenForm "Credit Analyst Form"
    Case 6
        DoCmd.OpenForm "Provider File Analyst"
    Case 7
        DoCmd.OpenForm "Repricing Analyst"
    Case 8
        DoCmd.OpenForm "Adjustment Analyst Form"
    Case 9
        MsgBox "Please select a reporting form."
End Select

What I want to do is:
1) Determine if the user has created a record today.
a)If no, continue the selection and go to next form
b)If yes, process the filter
2) Look at record and see if certain fields, depending on selection, have any data.
a) If no, continue the selection and go to next form
b) If yes, pop message, hide selection, return user to selection form.
OR
Perhaps it would be better to run total filter on record before opening form and hide unavailable choices immediately?

I think this would be the way to select the record. Table1 contains data entered by user. Table2 is user data (source).

SELECT Table1.Name, Table1.Date
FROM Table1
WHERE (((Table1.Name)=[Forms]![Main]![UserName]) AND ((Table1.Date)=Now()));

and this wold be the way to check if fields have been used. All fields are numeric and I thought that adding the fields and looking for sum of 0 would be the way to see if record had or didn't have (dirty) fileds

SELECT Table2.Supervisor, Sum([Table1]![IMPd]+[Table1]![IMDup]+[Table1]![OnlPd]+[Table1]![OnlDup]+[Table1]![PHSPd]+[Table1]![PHSDup]) AS Expr1
FROM Table2 LEFT JOIN Table1 ON Table2.EmpID = Table1.EmpID
GROUP BY Table2.Supervisor
ORDER BY Table2.Supervisor;

I don't know how to put this or even if I can, together into the Option using nested IF's. If someone could help me here with one selection, it's obvious that the others would be near identical.
TIA



Heisenberg was probably right.

 

[stix4t2]

Hard to answer with such limited info. I try to look at things by level. Without discussing the code, what is the overall objective and who are the players? Then at the next level down, what is the purpose of the form and how are you trying to acheive it?

The total query with making choice unavailable would probably be better for the user, and eliminate verification. Also note you want to use the Date() function so not to use the time info supplied by Now().

 

[adamstuff]

Hi stix4t2,
The overall objective is to allow any one user to put data into only one record per day. The option group is in the form that employees see after they log into the switchboard. The only other things on the form are a Back button, which takes them back to the switchboard and a SUBMIT button that takes them to the form they have selected with the option group. Each selected form fills only those fields unique to its function. No form has the same fields. This is a case of over micro-management.
The users are not allowed to enter more data on the same form (data) on the same day, but are allowed to enter different data on other forms that have not put data into them.
I.E. One record filled by several forms per user per day, and the user can't change (or see) what he's already put in.

HTH

Heisenberg was probably right.