Having a form pop up

[djcambas]

Hi there I am trying to help a coworker by letting her see when contracts are about to expire with a pop up form saying something like "Hey this contract is going to expire in X months time. Would you like to review it now?" I have been trying to work on it off and on for about 2 weeks now but I am not all that familiar with VB or Access that much and was wondering if someone knew a quick script for this?



I'm at a bit of a dead end on it.

Here is what I have so far

'On Load of the switchboard check Jobs table for any uncompleted jobs

Dim Store As Integer

'Count of uncomplete jobs that are past the Expected Completion Date
Store = DCount("[Contractor]", "[tblcontract]", "[End Date] <=Now() AND [Complete] =0")

'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If Store = 0 Then
            Exit Sub
                Else
                    If MsgBox("There are " & Store & " uncompleted jobs" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
                    DoCmd.Minimize
                    DoCmd.OpenForm "Form", acNormal
                Else
            Exit Sub
        End If
    End If
End Sub

However with this, I keep getting errors in the Dcount function.

Any help would be greatly appreciated.

Thanks for your time

[rpeare]

DCount("[Contractor]", "[tblcontract]", "[End Date] <= #" & date() & "# AND [Complete] = 0")

I don't know if that'll do what you want but it's the correct (or should be) dcount syntax

[djcambas]

I will try this to see if I encounter any errors and will reply back when I know. Thanks for the suggestion

[djcambas]

Okay I am no longer getting the syntax error I was receiving before Thanks! However I am receiving a new error.



I checked under my table tab in access and the one I am trying to use is indeed called "contract". I'm not sure why I am getting this error unless the table name is actually called something different and I'm not looking in the right spot to find the appropriate name.

[rpeare]

is your table named tblcontract or contract if the table is named 'contract' your d function has to use that name exactly

[djcambas]

Oh wow I feel silly xD. Thanks for that. That seems to fix my problem now. I just have to mess with the code a bit to get it to function 100% properly. Thanks so much for your help. If I have any other questions concerning this problem would you mind if I pm'd you about it? I don't want to keep bogging down the Form Sub-Forum.

[rpeare]

Chances are whatever you're going to ask is going to help someone sometime so it's better to post here but you can PM if you like.

[djcambas]

Okay well now that I have done some things to the base code I have it compiling together and working as intended. Except one last thing. My form is should be pulling up the contracts that are about to expire. It tells me that I have, for instance, 19 about to expire but it pulls up all contracts listed on the table instead of just the 19 in question.

So I guess I'm asking how can I edit my current form to pull up only the soon to expire contracts rather than all of them? If you need more information on the problem (probably sounds pretty vague) let me know. As I said I am really new to access and am not 100% sure what is and isn't relevant information.

[rpeare]

you'll have to alter the record source of your table if they select YES, or apply a filter, personally I prefer altering the record source it's just easier to me. In other words, in the ON CLICK event of your yes button you'd open the form change the record source then requery your results. Maybe the easiest way is to create a new form, base it on a query of 'about to expire' contracts only so in essence it would be identical to your 'show all' form except you're limiting the data source and open that second form if they respond 'yes' to the prompt.

[djcambas]

Thanks for the help and giving me a direction to go in! I'll be sure to look into those suggestions and I'll reply back to the topic when I make some more progress on it. Thank you again for all of the help.