Hi Tom,
I'm still having trouble with this. In your reply you have " &
me.myCasenumberControlName". My control is a text box called Case Number.
I've entered it as "myCaseNumber", "my [Case Number]", Case_Number and [Case
Number]. None are working. I get a compile error message - Expected: End of
Statement.
Please help.

"Tom van Stiphout" wrote:

> On Sat, 22 May 2010 22:32:01 -0700, mbparks
> <(E-Mail Removed)> wrote:
>
> I'm assuming you meant "move to the record with the correct
> casenumber". This is accomplished with the bookmark technique:
> with me.recordsetclone
> .findfirst "[case number]=" & me.myCasenumberControlName
> if not .nomatch then
> me.bookmark = .bookmark
> end if
> end with
>
> -Tom.
> Microsoft Access MVP
>
>
> >In the current form, move to the record with the correct case number.
> >
> >"Tom van Stiphout" wrote:
> >
> >> On Sat, 22 May 2010 18:23:01 -0700, mbparks
> >> <(E-Mail Removed)> wrote:
> >>
> >> I'm not sure I follow you. You write: "If it is blank I would like the
> >> existing record to open". Open how? Do you mean "open another form
> >> with the correct casenumber preselected", or do you mean "in the
> >> current form, move to the record with the correct casenumber"? Or?
> >>
> >> It is nearly impossible and not recommended to use ONE control both
> >> for lookups as for data-entry.
> >>
> >> -Tom.
> >> Microsoft Access MVP
> >>
> >>
> >> >My table contains a field labled "Case Number" and another field labled "Date
> >> >Completed". I have created an input form and included code to check for
> >> >duplicate case numbers. A msgbox appears with a warning. I would like the
> >> >code to check for a duplicate case number and then check the "Date Completed"
> >> > field to see if it is blank. If it is blank I would like the existing
> >> >record to open. If the date completed field is not blank I would like to
> >> >continue entering data in the form to create a new record.
> >> >The code I am currently using is:
> >> >
> >> >Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
> >> >If DCount("*", "Copy of DIV 3 ICT Database", "[Case Number] = '" & Me![Case
> >> >Number] & "'") > 0 Then
> >> >MsgBox "This item already exists in the table."
> >> >Cancel = True
> >> >Me.Undo
> >> >End If
> >> >End Sub
> >> >
> >> >How can I incorporate the changes?
> >> >Any help is greatly appreciated.

This code should do what you want

Also as a note for future reference don’t use spaces in any table name
form name field in a table anything as it creates problems when
programming use camel case like this CopyOfDIV3ICTDatabase

You forgot to put [] around Copy of DIV 3 ICT Database because of the
spaces in your code

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
If isnull(Dlookup("[Case Number]","[Copy of DIV 3 ICT Database]",
"[Case Number] = '" & [Case
Number].value)) Then
DoCmd.GoToRecord , , acNewRec
else
If isnull(Dlookup("[Date Completed]","[Copy of DIV 3 ICT Database]",
"[Case Number] = '" & [Case
Number].value)) Then
Me.Filter = "id = 2"
Me.FilterOn = True
Me.Requery
else
Cancel = True
Me.Undo
End If
end if
End Sub

Hope this helps

Regards
Kelvan

I'm still having a problem. I copied your code but still having problems.
The code window opens and the first line of code is highlighted.
Please help.

"Lord Kelvan" wrote:

> This code should do what you want
>
> Also as a note for future reference don’t use spaces in any table name
> form name field in a table anything as it creates problems when
> programming use camel case like this CopyOfDIV3ICTDatabase
>
> You forgot to put [] around Copy of DIV 3 ICT Database because of the
> spaces in your code
>
> Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
> If isnull(Dlookup("[Case Number]","[Copy of DIV 3 ICT Database]",
> "[Case Number] = '" & [Case
> Number].value)) Then
> DoCmd.GoToRecord , , acNewRec
> else
> If isnull(Dlookup("[Date Completed]","[Copy of DIV 3 ICT Database]",
> "[Case Number] = '" & [Case
> Number].value)) Then
> Me.Filter = "id = 2"
> Me.FilterOn = True
> Me.Requery
> else
> Cancel = True
> Me.Undo
> End If
> end if
> End Sub
>
> Hope this helps
>
> Regards
> Kelvan
> .
>

Assuming what Lord Kelvan gave as a sub is what you are looking for, he
simply forgot to add a closing ' at the end of the DLookUp criteria. Try

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Handler

If IsNull(DLookup("[Case Number]", "[Copy of DIV 3 ICT Database]", _
"[Case Number] = '" & Me.[Case Number] & "'")) Then
DoCmd.GoToRecord , , acNewRec
Else
If IsNull(DLookup("[Date Completed]", "[Copy of DIV 3 ICT
Database]", _
"[Case Number] = '" & Me.[Case Number] & "'")) Then
Me.Filter = "id = 2"
Me.FilterOn = True
Me.Requery
Else
Cancel = True
Me.Undo
End If
End If

Error_Handler_Exit:
On Error Resume Next
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: Case_Number_BeforeUpdate" & vbCrLf
& "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub

Also, I strongly encourage you to use proper code indenting and most
importantly error handling.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"mbparks" wrote:

> I'm still having a problem. I copied your code but still having problems.
> The code window opens and the first line of code is highlighted.
> Please help.
>
> "Lord Kelvan" wrote:
>
> > This code should do what you want
> >
> > Also as a note for future reference don’t use spaces in any table name
> > form name field in a table anything as it creates problems when
> > programming use camel case like this CopyOfDIV3ICTDatabase
> >
> > You forgot to put [] around Copy of DIV 3 ICT Database because of the
> > spaces in your code
> >
> > Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
> > If isnull(Dlookup("[Case Number]","[Copy of DIV 3 ICT Database]",
> > "[Case Number] = '" & [Case
> > Number].value)) Then
> > DoCmd.GoToRecord , , acNewRec
> > else
> > If isnull(Dlookup("[Date Completed]","[Copy of DIV 3 ICT Database]",
> > "[Case Number] = '" & [Case
> > Number].value)) Then
> > Me.Filter = "id = 2"
> > Me.FilterOn = True
> > Me.Requery
> > else
> > Cancel = True
> > Me.Undo
> > End If
> > end if
> > End Sub
> >
> > Hope this helps
> >
> > Regards
> > Kelvan
> > .
> >