Search results

Date1 = Nz(DMax("[DateAttended]", "Dates of Distribution", "[ClientId]='" & Forms!HomePage!NavigationSubform.Form!ClientID & "' And [Shampoo]= True")) This is the syntax that worked. Finally!!! Thanks so much for your help Jim

Private Sub Ctl_HouseholdMembers_GotFocus() Dim Date1 As Date Date1 = Nz(DMax("[DateAttended]", "Dates of Distribution", "[ClientId]='" & Forms!HomePage!NavigationSubform.Form!ClientID & "'")) MsgBox "Entitled to Shampoo" End Sub The above statement will return a date in variable Date1...

Private Sub Ctl_HouseholdMembers_GotFocus() If (DateDiff("d", Nz(DMax("[DateAttended]", "Dates of Distribution", ("[Shampoo] = True" And "[ClientId] ='"&forms!HomePage!NavigationSubform.form!ClientId&"'")), Date) >= 28) Then MsgBox "Entitled to Shampoo" End If End Sub For some reason it does...

Private Sub Ctl_HouseholdMembers_GotFocus() If (DateDiff("d", Nz(DMax("[DateAttended]", "Dates of Distribution", "[Shampoo] = 'YES'")), Date) >= 28) Then MsgBox "Entitled to Shampoo" End If End Sub This is the code I am trying to implement. I call up a client, then enter the number of family...

Thanks Paul, I'll study up and give it a try. Thank you so much for your patience and help. Jim At this point I owe you at least 5 dinners :)

Paul, Thanks for the reply. Yes I do have a client id field in the visits table. I guess once I have the dmax date I can subtract it from today and see if it is 28 days (4 weeks) or 84 days of toothbrushes (7 times 12 weeks). I'm not sure how to code this solution. Could you please show me...

I have a table which I store visits by clients to our food pantry. The table contains the following fields" Date of visit date field #members in family number field Received shampoo yes/no field Received toothbrush yes/no field Shampoo rule is the client is entitled to shampoo every 4...

Thank you so much I will try this immediately. I really appreciate helping me with this issue because was I really stuck Jim

SELECT Count([Client Information].County) AS Transylvania, Sum([Client Information].[#HouseholdMembers]) AS [Transylvania#] FROM [Distinctfamiliesinmonth/year] INNER JOIN [Client Information] ON [Distinctfamiliesinmonth/year].Clientid=[Client Information].ClientID HAVING ((([Client...

I created a query with two colums; one column is the count the number of records and the other column sums another field based upon a certain criteria being met. This information is printed in a report.(actually a subreport of the main report) If no records in my database meet the defined...

Bob, I moved the code to the "on exit" event and it works great now. I believe that having the code at "before update" event, Access was attempting to update a record whose ClientID violated the unique requirement, hence the error message from Access letting me know that the key is a duplicate...

Bob, I tried your code change and it solved most of my problem. When I enter the ClientID (which is a duplicate) and tab to the LastName field: I correctly get my error message AND the cursor now stays in the ClientID field correctly, BUT I get this message on the screen after I click ok on my...

I created VB event on the ClientID field when its focus is lost, to check for a possible duplicate id being entered. When I tab from the ClientID to the next field (lastName) the msgbox appears to warn the user that they just entered a duplicate ClientID. I want to cursor to go back to the...

I was able to make it work properly, thanks to you. I created an event when the ClientID lost focus; passed the data from the navigation subform to the VB code you supplied; created a MsgBox to alert the user of the dup key; it worked like a charm. I would have never thought of the solution...

Paul, Thank you for all your help. I'll try not to be nuisance to you. Best Regards, Jim

Could you please explain how to do this. I'm not clear what to do. Thanks so much. Jim

I have a NEW Client form that the user enters basic data i.e. name and address. This table called "ClientInformation" uses a 6 character key called CLIEDTID and is unique for that table.(ClientID is the 1st 4 characters of last name and first 2 char of first name. ) As part of the NEW...

apr pillai This is what I did: Opened form,clicked on ClientID Field to select,press alt+F11 to display the VBA module. At this point after I did the alt+f11 a screen appeared called "microsoft Visual Basic for Application" It was blank, so I clicked on the insert icon and was the presented...

I am a new user to access 2010. I encountered a puzzling problem in my form. I designed the following form in which the top portion of the form, and is the clients basic information. For example, it contains: First Name Last Name Address City State, zip The above information is stored in a...

I can't thank you enough for your help and encouragement.