Validate 2 Fields - DLookup Perhaps...

J

Jani

I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
 
K

Klatuu

--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If
 
J

Jani

Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



Klatuu said:
--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

Jani said:
I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
 
K

Klatuu

This
[Location] = '300"
should be coming out as
[Location] = '300'

If [Location] is a text field, the code should be:
(quotes expanded for readibility)
"# And [Location] = ' " & Me.txtLocation & " ' "))

If it is a numeric field, it should be:
"# And [Location] = " & Me.txtLocation))
--
Dave Hargis, Microsoft Access MVP


Jani said:
Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



Klatuu said:
--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

Jani said:
I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
Click to expand...
 
J

Jani

Still no luck. Location is definitely a text field as is Date1 (because of
mainframe). Is that the issue?

Klatuu said:
This
[Location] = '300"
should be coming out as
[Location] = '300'

If [Location] is a text field, the code should be:
(quotes expanded for readibility)
"# And [Location] = ' " & Me.txtLocation & " ' "))

If it is a numeric field, it should be:
"# And [Location] = " & Me.txtLocation))
--
Dave Hargis, Microsoft Access MVP


Jani said:
Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



Klatuu said:
--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

:

I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

It is mosty definitely an issue. You will need to fomat the date to match
the format in which it is stored. The # should be replaced with a single
quote. It is used only for dates.
--
Dave Hargis, Microsoft Access MVP


Jani said:
Still no luck. Location is definitely a text field as is Date1 (because of
mainframe). Is that the issue?

Klatuu said:
This
[Location] = '300"
should be coming out as
[Location] = '300'

If [Location] is a text field, the code should be:
(quotes expanded for readibility)
"# And [Location] = ' " & Me.txtLocation & " ' "))

If it is a numeric field, it should be:
"# And [Location] = " & Me.txtLocation))
--
Dave Hargis, Microsoft Access MVP


Jani said:
Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



:


--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

:

I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
Click to expand...
 
J

Jani

Yeah!!! almost there... now get the message that it's a duplicate but when I
click OK have a Runtime 2108 - you must save the field before you execute the
GoToControl action, the GOTOControl method, or the SetFocus method. The
SetFocus line is highlighted. I'm so glad you 'guys' put up with us novices!

Klatuu said:
It is mosty definitely an issue. You will need to fomat the date to match
the format in which it is stored. The # should be replaced with a single
quote. It is used only for dates.
--
Dave Hargis, Microsoft Access MVP


Jani said:
Still no luck. Location is definitely a text field as is Date1 (because of
mainframe). Is that the issue?

Klatuu said:
This
[Location] = '300"
should be coming out as
[Location] = '300'

If [Location] is a text field, the code should be:
(quotes expanded for readibility)
"# And [Location] = ' " & Me.txtLocation & " ' "))

If it is a numeric field, it should be:
"# And [Location] = " & Me.txtLocation))
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



:


--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

:

I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

I thought the Undo would take care of that, but I guess not. See it it runs
successfully without the setfocus line. (comment it out).
--
Dave Hargis, Microsoft Access MVP


Jani said:
Yeah!!! almost there... now get the message that it's a duplicate but when I
click OK have a Runtime 2108 - you must save the field before you execute the
GoToControl action, the GOTOControl method, or the SetFocus method. The
SetFocus line is highlighted. I'm so glad you 'guys' put up with us novices!

Klatuu said:
It is mosty definitely an issue. You will need to fomat the date to match
the format in which it is stored. The # should be replaced with a single
quote. It is used only for dates.
--
Dave Hargis, Microsoft Access MVP


Jani said:
Still no luck. Location is definitely a text field as is Date1 (because of
mainframe). Is that the issue?

:

This
[Location] = '300"
should be coming out as
[Location] = '300'

If [Location] is a text field, the code should be:
(quotes expanded for readibility)
"# And [Location] = ' " & Me.txtLocation & " ' "))

If it is a numeric field, it should be:
"# And [Location] = " & Me.txtLocation))
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



:


--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

:

I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
Click to expand...
 
J

Jani

Perfect. Thanks so much for your patience and have a wonderful holiday season.

Klatuu said:
I thought the Undo would take care of that, but I guess not. See it it runs
successfully without the setfocus line. (comment it out).
--
Dave Hargis, Microsoft Access MVP


Jani said:
Yeah!!! almost there... now get the message that it's a duplicate but when I
click OK have a Runtime 2108 - you must save the field before you execute the
GoToControl action, the GOTOControl method, or the SetFocus method. The
SetFocus line is highlighted. I'm so glad you 'guys' put up with us novices!

Klatuu said:
It is mosty definitely an issue. You will need to fomat the date to match
the format in which it is stored. The # should be replaced with a single
quote. It is used only for dates.
--
Dave Hargis, Microsoft Access MVP


:

Still no luck. Location is definitely a text field as is Date1 (because of
mainframe). Is that the issue?

:

This
[Location] = '300"
should be coming out as
[Location] = '300'

If [Location] is a text field, the code should be:
(quotes expanded for readibility)
"# And [Location] = ' " & Me.txtLocation & " ' "))

If it is a numeric field, it should be:
"# And [Location] = " & Me.txtLocation))
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the quick reply Klatuu... but I'm getting an error. I've changed
my text boxes to be named as you had in the code. The error is a Run Time
3075 - Syntax error in date in query expression '[Date1]= #200711# And
[Location] = '300". The code I entered is below. What did I do wrong? The If
phrase is highlighted.

Private Sub txtDate1_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" & Me.txtDate1
& "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SetFocus
End If

End Sub



:


--
Dave Hargis, Microsoft Access MVP

Use the Before Update event of the form.

If Not IsNUll(DLookup("[Date1]", "dbo_uRMData", "[Date1] = #" &
Me.txtDate1 & "# And [Location] = '" & Me.txtLocation & "'")) Then
MsgBox "This Date and Location Are Already in the Table"
Me.Undo
Cancel = True
Me.txtDate1.SefFocus
End If

:

I found a couple similar examples but have given up trying to get them to
work. The 1st 2 fields on a form (Date1 and Location) need to be validated to
ensure that the data is not already in the database. If the information for a
date and location are already in the table (dbo_uRMData) when one moves off
the location field, I want a message to be displayed that the data is already
entered in the dbo_uRMData table, the form cleared, and the Date1 code to
have focus. Your help, AS ALWAYS, is so much appreciated. Jani
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set Focus Assistance Needed Urgently 1
Validate If Then 3
Help with simple code getting frostrated 4
DLookup refresh help needed. 2
Validation of Unique Fields Already Used & Query Help 2
dlookup formula in Form 5
Access Access DLookup Function – more than 1 criteria 0
Form not displaying value set by code... 2

Top