check previous data

[COMP]

hi i am trying to come up with a way to check previous records. so when users enter in a quarterm period eg . q1 2009 and then want to add another quarter, is there code where acess will check the previous quarter entered and make sure its not entered in again??

thanks

 

[ajetrumpet]

hi i am trying to come up with a way to check previous records. so when users enter in a quarterm period eg . q1 2009 and then want to add another quarter, is there code where acess will check the previous quarter entered and make sure its not entered in again??

thanks

simply use the DCOUNT() function to check it. make sure the record you have referred to first though, is SAVED first, otherwise it won't be in the table you're looking at. try this:

if dcount("fieldName", "tableName",
   "[fieldName] = '" & me.ControlName & "'") > 0 then

msgbox "Sorry sir....this quarter has already been entered previously.  try again!"

 

[COMP]

Hi,

i tried the code you gave me however it is not working for some reason. this is what i entered in

Private Sub Quarter_Period_AfterUpdate()
If DCount("Quarter_Period", "Claims", "[Quarter_Period] = '" & Me.ControlName & "'") > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If
End Sub

however it does not allow it. it highlights Me.ControlName

Any ideas??

 

[COMP]

I put the code in the after update function in the quarter period box. if that helps??

thanks

 

[COMP]

any ideas??

 

[Scooterbug]

Change the ControlName to the ACTUAL name of the field on your form.

 

[COMP]

Hi there i have now tried that and it still comes up with error message even when i enter in a different quarter. Here is my code:

Private Sub Quarter_Period_AfterUpdate()
If DCount("Quarter_Period", "Claims", "[Quarter_Period] = '" & Me.Quarter_period & "'") > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If
End Sub

 

[Scooterbug]

What is the exact error you are getting?

 

[COMP]

Right following code works, however when i try and enter in a quarter for a sperate project it picks up the quarters from the previous project. Is there a way to reset the code for individual projects??

If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "'") > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If

 

[Scooterbug]

Sounds like you need to requery the results.

me.ControlName.requery

where controlName is the name of whatever control you are using to display the data.

 

[COMP]

Hi Scooterbug, is the following code the right place to put the requery in:

Private Sub Command5_Click()
If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "'") > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If
DoCmd.GoToRecord , , acNewRec
Me.Quarter.Requery
End Sub

 

[DCrake]

If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "'") > 0 Then

The above code does work but it is only checking for the existance of a known quarter, irrespective of the project. What you need to do is to expand the logic to test for the project reference as well. such as:

If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "' And [ProjectId]= " & Me.ProjectId) > 0 Then

Where ProjectId is the actual name of the field in the table and the control on the form.

I would also be tempted to wrap the Nz() function around it thus

If Nz(DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "' And [ProjectId]= " & Me.ProjectId),0) > 0 Then

This will negate any Null errors encountered when no records are found.


David

 

[DCrake]

Following on, I would also suggest a couple of points.

How is the user selecting the quarter? is it freetext or are you using a combobox?

If the former the user could enter gibberish and it would be allowed.

Also could you not generate the quarter in code when the user elects to add a new period. This would be a simple piece of code based on the last quarter used and the year. This way there would be no user intervention therefore no need to validate.

David

 

[COMP]

hi there i have tried entering in the code however it still seems doesnt to work. It still brings up the error message of "quarter used". It still picks them up in other projects here is the code that i put in:

Private Sub Command5_Click()
If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "' And [Project_Reference]= " & Me.Project_Reference) > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If
DoCmd.GoToRecord , , acNewRec
End Sub

What it is is i have created a dropdown list in the table "Test" with the quarters in it, and users can select the relevant one.

"Also could you not generate the quarter in code when the user elects to add a new period. This would be a simple piece of code based on the last quarter used and the year. This way there would be no user intervention therefore no need to validate."

Im not sure what you mean??

 

[COMP]

Following code does not pick up "Quarter", i am not sure why

Private Sub Command5_Click()
If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "' And [ProjectId]= " & Me.ProjectId) > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If
DoCmd.GoToRecord , , acNewRec
End Sub

 

[DCrake]

I would try putting [] around the word Quarter as this is an Access reserved word, it may be trying to attempt something else. You need to be careful about naming conventions and reserved words.

David

 

[COMP]

Hi DCrake,

thanks for that it now recognises it. However it still picks up the quarters frm other projects. The code doesnt reset when you go to another project. here is the code for it:

Private Sub Command5_Click()
If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "' And [Project_Reference]= " & Me.Project_Reference) > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"
End If
DoCmd.GoToRecord , , acNewRec
End Sub

 

[DCrake]

If DCount("Quarter", "Test", "[Quarter] = '" & Me.Quarter & "' And [Project_Reference]= " & Me.Project_Reference) > 0 Then
MsgBox "Sorry sir....this quarter has already been entered previously. try again!"

[COLOR="Red"]You need an Exit Sub here otherwise it will still continue to add a new record[/COLOR]

End If
DoCmd.GoToRecord , , acNewRec

Also I would remove the gender specific reference in your msgbox, females may be offended.

David

 

[COMP]

Hi, tried that however it says i need an end if before the end sub......

 

[COMP]

Hi DCrake, finally works now!! Thank you for your help!!