The simplest way to solve this would be to mock up a query into the LEAKS
FOUND table.
In the Criteria row, enter anything under the Address, Street, Street1,
and
R_Found fields.
Then switch to SQL View (View menu), and look at the WHERE clause. That's
an
example of what you are trying to create.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Allen,
Now I am getting the following:
Run time error '3075':
Syntax error (missing operator) in query expression
Maybe I should tell you what I'm trying to do and you will be able to
help
me. I have the following code. When the user enters a new address
where
there is a leak, Access checks to see if any duplicates of the new
street
address exists. If it already exists, a pop up box show up saying that
the
street address already exists and takes the user to the existing data.
It worked great, the only thing was, that only took care of a part of
what
I'm trying to do. The next part is in a couple of months a new leak at
that
same address could happen, which the cause of is different. The way I
had
it
set up, the user wan't able to input that new leak, because it was at
an
existing address. So, I figured that I want access to pull up
addresses
that
already exist, only where the [R_FOUND] field is null. If the
[R_FOUND]
field is not null, I don't want the pop up box to show that the data
already
exits.
Hence, me adding the [R_FOUND] field for to the search in the DLookup
in
my
coding.
Your help is really appreciate with this. I am new to VBA.
I thank you for your time.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varADDRESS As Variant
If Me.NewRecord Then
varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '"
&
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] = '"
&
Me.STREET1 _
& "' and [R_FOUND]= " & Format(Me.R_FOUND,
"\#mm\/dd\/yyyy\#"))
If Not IsNull(varADDRESS) Then
If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to
that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS &
"'"
bolCheckDuplicate = True
End If
End If
End If
Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate
End Sub
:
If R_Found is a Date type field in your table, try:
& "' and [R_FOUND] = " & Format(Me.R_FOUND, "\#mm\/dd\/yyyy\#")
Allen,
varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] =
'"
&
Me.STREET1 _
& "' and [R_FOUND] = '" & Me.R_FOUND _
& "'")
These lines are highlighted in yellow, when I run the code. I have
been
reading archived post on
http://groups.google.com and I think it has
something to do with the fact that [R_FOUND] is a text box, which I
converted
into a combobox and when clicked there is a pop up calendar with
date
fields.
As you probably can tell, I'm new to VBA.
Any help is appreciated.
Thanks for your time.
:
Which line generates the error?
Since you are using the single quote as the delimiter in this line,
for
example:
Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS & "'"
it will fail on any address that contains an apostrophy.
Try using double quotes, which have to be doubled up when embedded
in
the
string:
Me.Recordset.FindFirst "[ADDRESS] = """ & varADDRESS & """"
I have the following code. I get a the following code when I run
it:
Runtime error '3464':
Data type mismatch in criteria expression.
When I choose help, there is not information on this error, or
what
I'm
doing wrong. Can someone please help me out?
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varADDRESS As Variant
If Me.NewRecord Then
varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND",
"[ADDRESS] =
'"
&
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and
[STREET1] =
'"
&
Me.STREET1 _
& "' and [R_FOUND] = '" & Me.R_FOUND _
& "'")
If Not IsNull(varADDRESS) Then
If MsgBox("This record already exists." & _
"Do you want to cancel these changes and
go
to
that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS] = '" &
varADDRESS &
"'"
bolCheckDuplicate = True
