Validate 2 Fields - DLookup Perhaps...

  • Thread starter Thread starter Jani
  • Start date Start date
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
 
--
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
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top