query not working

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

I have a query to prevent duplicate entries which is called from a form. The
criteria are set to forms!myForm!myControl. When I open the query and supply
the criteria, it works but when it is opened from the form there are no
records displayed.
 
Yes. There are 4 controls on the form. 2 will have data and at least 1 of
the other 2 will have data. The form contains the same data as I supplied to
the query to open it independantly.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
Is there a value in the myControl control on that form?
Click to expand...
 
Is the myControl control in a subform on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Walter said:
Yes. There are 4 controls on the form. 2 will have data and at least 1
of
the other 2 will have data. The form contains the same data as I supplied
to
the query to open it independantly.
Click to expand...
 
What type of data are in the myControl control -- number? string/text? date?
 
I am calling this query from a DLookup function for record validation to
prevent duplicate records.
 
Why bother with a query when DLookup is all that is needed.

If not isnull(DLookup("[YourField]", "YourTable", "YourField = Me!Info")
then

msgbox Me!info & "exists, guess again"

End If
 
Then I'm guessing that you're not delimiting the value from myControl with '
characters, which is needed for string values. This is example of how you'd
do it in VBA code, if that is what you're using:

Dim strValue As String
strValue = DLookup("FieldBeingLookedUp", _
"TableQueryOfData", _
"FieldBeingSearched='" & Me.myControl.Value & "'")
 
This brings up another question I have. Can DLookup accept multiple criteria?
The criteria for the query is:
fldA = ctlA and fldB = ctlB and fldC = ctlC and fldD = ctlD
OR fldA = ctlA and fldB = ctlB and fldC = ctlC and fldD IsNull
OR fldA = ctlA and fldB = ctlB and fldC = IsNull and fldD = fldD
--
Thanks for your help!
Walter


Mike Painter said:
Why bother with a query when DLookup is all that is needed.

If not isnull(DLookup("[YourField]", "YourTable", "YourField = Me!Info")
then

msgbox Me!info & "exists, guess again"

End If



I am calling this query from a DLookup function for record validation
to prevent duplicate records.
Click to expand...
Click to expand...
 
All of the criteria are included in the query itself. The value being looked
up is integer, all criteria are text. I have:
intMyVariable = NZ(DLookup("TableID", "qryTableLookup"),0)
 
Please post the code that you're running; let's see what it contains. Likely
we will be able to see the problem then.
 
The SQL is:
SELECT tblTires.TireID, tblTires.Brand, tblTires.Model, tblTires.[DOT#],
tblTires.SerialNumber
FROM tblTires
WHERE (((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber])) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber) Is Null)) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND ((tblTires.[DOT#])
Is Null) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber]));

The code in the form is:
Private Sub DuplicateEntry()
Dim intTire As Integer
Dim varBrand, varModel, varDot, varSN
Dim Msg, Style, Title, Response, MyString

intTire = Nz(DLookup("TireID", "qryDuplicateTire"), 0)
varBrand = DLookup("Brand", "qryDuplicateTire")
varModel = DLookup("Model", "qryDuplicateTire")
varDot = DLookup("[DOT#]", "qryDuplicateTire")
varSN = DLookup("SerialNumber", "qryDuplicateTire")
Msg = _
"Tire " & intTire & vbCrLf & _
"" & varBrand & """ "" & varModel & """ & vbCrLf & _
"DOT Number "" & varDOT & """ & " " & "Serial Number "" & varSN
& """ & vbCrLf & _
"has already been entered. Click OK to modify information " &
vbCrLf & _
"or CANCEL to abort entry."
Style = vbOKCancel
Title = "Duplicate Entry!"

If intTire <> 0 Then
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.
MyString = "OK" ' Perform some action.
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.ctlDOT_.SetFocus

Else ' User chose No.
MyString = "Cancel" ' Perform some action.
Me.cboBrand.Undo
Me.cboModel.Undo
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.cboBrand.SetFocus

End If
This is called from ctlSerialNumber BeforeUpdate and on Exit in case of no
entry in this control.
BTW: I have another DLookup on another query that was working fine a couple
days ago that now is giving me an "Object doesn't contain the automation
object TireID" error. The TireID is in the query.
 
I don't see anything obvious, so let's try an experiment.

Make a copy of your database. In the copy, open your frmEnterTires form, and
move the code from the ctlSerialNumber_BeforeUpdate event procedure to the
ctlSerialNumber_AfterUpdate event procedure. Then go to the DuplicateEntry
subroutine and comment out the .Undo lines. Save and close the form.

Then open the form in forms view, and select information in the controls
that should cause your query to have a record. Does it show a record? While
the form is open, go to the saved query qryDuplicateTire and run it -- does
it show a record?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Walter said:
The SQL is:
SELECT tblTires.TireID, tblTires.Brand, tblTires.Model, tblTires.[DOT#],
tblTires.SerialNumber
FROM tblTires
WHERE (((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber])) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber) Is Null)) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])
Is Null) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber]));

The code in the form is:
Private Sub DuplicateEntry()
Dim intTire As Integer
Dim varBrand, varModel, varDot, varSN
Dim Msg, Style, Title, Response, MyString

intTire = Nz(DLookup("TireID", "qryDuplicateTire"), 0)
varBrand = DLookup("Brand", "qryDuplicateTire")
varModel = DLookup("Model", "qryDuplicateTire")
varDot = DLookup("[DOT#]", "qryDuplicateTire")
varSN = DLookup("SerialNumber", "qryDuplicateTire")
Msg = _
"Tire " & intTire & vbCrLf & _
"" & varBrand & """ "" & varModel & """ & vbCrLf & _
"DOT Number "" & varDOT & """ & " " & "Serial Number "" & varSN
& """ & vbCrLf & _
"has already been entered. Click OK to modify information " &
vbCrLf & _
"or CANCEL to abort entry."
Style = vbOKCancel
Title = "Duplicate Entry!"

If intTire <> 0 Then
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.
MyString = "OK" ' Perform some action.
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.ctlDOT_.SetFocus

Else ' User chose No.
MyString = "Cancel" ' Perform some action.
Me.cboBrand.Undo
Me.cboModel.Undo
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.cboBrand.SetFocus

End If
This is called from ctlSerialNumber BeforeUpdate and on Exit in case of no
entry in this control.
BTW: I have another DLookup on another query that was working fine a
couple
days ago that now is giving me an "Object doesn't contain the automation
object TireID" error. The TireID is in the query.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
Please post the code that you're running; let's see what it contains.
Likely
we will be able to see the problem then.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
I copied the db, called the routine from the AfterUpdate event, commented out
the .Undo lines, and put a code break in. I then opened the form and put
information in that I know was duplicate. When the code broke, I stepped
through to see the values of the variables. They showed no record. I then
opened the query with the form still open and it displayed a blank record. I
closed the query and the form then reopened the query and supplied the same
information for each parameter prompt. The query then showed 2 records since
it saved the new record when I closed the form.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
I don't see anything obvious, so let's try an experiment.

Make a copy of your database. In the copy, open your frmEnterTires form, and
move the code from the ctlSerialNumber_BeforeUpdate event procedure to the
ctlSerialNumber_AfterUpdate event procedure. Then go to the DuplicateEntry
subroutine and comment out the .Undo lines. Save and close the form.

Then open the form in forms view, and select information in the controls
that should cause your query to have a record. Does it show a record? While
the form is open, go to the saved query qryDuplicateTire and run it -- does
it show a record?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Walter said:
The SQL is:
SELECT tblTires.TireID, tblTires.Brand, tblTires.Model, tblTires.[DOT#],
tblTires.SerialNumber
FROM tblTires
WHERE (((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber])) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber) Is Null)) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])
Is Null) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber]));

The code in the form is:
Private Sub DuplicateEntry()
Dim intTire As Integer
Dim varBrand, varModel, varDot, varSN
Dim Msg, Style, Title, Response, MyString

intTire = Nz(DLookup("TireID", "qryDuplicateTire"), 0)
varBrand = DLookup("Brand", "qryDuplicateTire")
varModel = DLookup("Model", "qryDuplicateTire")
varDot = DLookup("[DOT#]", "qryDuplicateTire")
varSN = DLookup("SerialNumber", "qryDuplicateTire")
Msg = _
"Tire " & intTire & vbCrLf & _
"" & varBrand & """ "" & varModel & """ & vbCrLf & _
"DOT Number "" & varDOT & """ & " " & "Serial Number "" & varSN
& """ & vbCrLf & _
"has already been entered. Click OK to modify information " &
vbCrLf & _
"or CANCEL to abort entry."
Style = vbOKCancel
Title = "Duplicate Entry!"

If intTire <> 0 Then
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.
MyString = "OK" ' Perform some action.
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.ctlDOT_.SetFocus

Else ' User chose No.
MyString = "Cancel" ' Perform some action.
Me.cboBrand.Undo
Me.cboModel.Undo
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.cboBrand.SetFocus

End If
This is called from ctlSerialNumber BeforeUpdate and on Exit in case of no
entry in this control.
BTW: I have another DLookup on another query that was working fine a
couple
days ago that now is giving me an "Object doesn't contain the automation
object TireID" error. The TireID is in the query.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
Please post the code that you're running; let's see what it contains.
Likely
we will be able to see the problem then.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


All of the criteria are included in the query itself. The value being
looked
up is integer, all criteria are text. I have:
intMyVariable = NZ(DLookup("TableID", "qryTableLookup"),0)
--
Thanks for your help!
Walter


:

Then I'm guessing that you're not delimiting the value from myControl
with '
characters, which is needed for string values. This is example of how
you'd
do it in VBA code, if that is what you're using:

Dim strValue As String
strValue = DLookup("FieldBeingLookedUp", _
"TableQueryOfData", _
"FieldBeingSearched='" & Me.myControl.Value & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


All 4 are text.
--
Thanks for your help!
Walter


:

What type of data are in the myControl control -- number?
string/text?
date?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


No. It is a stand alone form.
--
Thanks for your help!
Walter


:

Is the myControl control in a subform on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Yes. There are 4 controls on the form. 2 will have data and
at
least
1
of
the other 2 will have data. The form contains the same data
as I
supplied
to
the query to open it independantly.
--
Thanks for your help!
Walter


:

Is there a value in the myControl control on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a query to prevent duplicate entries which is called
from a
form.
The
criteria are set to forms!myForm!myControl. When I open
the
query
and
supply
the criteria, it works but when it is opened from the form
there
are
no
records displayed.
Click to expand...
Click to expand...
Click to expand...
 
I figured it out. It hit me last night that there may be an issue with the
field name "DOT#". I renamed it to "DOT", updated all my references and the
query now works as it should. I now have another issue. The .Undo lines are
not removing the control values thereby allowing a duplicate record to be
entered.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
I don't see anything obvious, so let's try an experiment.

Make a copy of your database. In the copy, open your frmEnterTires form, and
move the code from the ctlSerialNumber_BeforeUpdate event procedure to the
ctlSerialNumber_AfterUpdate event procedure. Then go to the DuplicateEntry
subroutine and comment out the .Undo lines. Save and close the form.

Then open the form in forms view, and select information in the controls
that should cause your query to have a record. Does it show a record? While
the form is open, go to the saved query qryDuplicateTire and run it -- does
it show a record?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Walter said:
The SQL is:
SELECT tblTires.TireID, tblTires.Brand, tblTires.Model, tblTires.[DOT#],
tblTires.SerialNumber
FROM tblTires
WHERE (((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber])) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber) Is Null)) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])
Is Null) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber]));

The code in the form is:
Private Sub DuplicateEntry()
Dim intTire As Integer
Dim varBrand, varModel, varDot, varSN
Dim Msg, Style, Title, Response, MyString

intTire = Nz(DLookup("TireID", "qryDuplicateTire"), 0)
varBrand = DLookup("Brand", "qryDuplicateTire")
varModel = DLookup("Model", "qryDuplicateTire")
varDot = DLookup("[DOT#]", "qryDuplicateTire")
varSN = DLookup("SerialNumber", "qryDuplicateTire")
Msg = _
"Tire " & intTire & vbCrLf & _
"" & varBrand & """ "" & varModel & """ & vbCrLf & _
"DOT Number "" & varDOT & """ & " " & "Serial Number "" & varSN
& """ & vbCrLf & _
"has already been entered. Click OK to modify information " &
vbCrLf & _
"or CANCEL to abort entry."
Style = vbOKCancel
Title = "Duplicate Entry!"

If intTire <> 0 Then
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.
MyString = "OK" ' Perform some action.
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.ctlDOT_.SetFocus

Else ' User chose No.
MyString = "Cancel" ' Perform some action.
Me.cboBrand.Undo
Me.cboModel.Undo
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.cboBrand.SetFocus

End If
This is called from ctlSerialNumber BeforeUpdate and on Exit in case of no
entry in this control.
BTW: I have another DLookup on another query that was working fine a
couple
days ago that now is giving me an "Object doesn't contain the automation
object TireID" error. The TireID is in the query.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
Please post the code that you're running; let's see what it contains.
Likely
we will be able to see the problem then.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


All of the criteria are included in the query itself. The value being
looked
up is integer, all criteria are text. I have:
intMyVariable = NZ(DLookup("TableID", "qryTableLookup"),0)
--
Thanks for your help!
Walter


:

Then I'm guessing that you're not delimiting the value from myControl
with '
characters, which is needed for string values. This is example of how
you'd
do it in VBA code, if that is what you're using:

Dim strValue As String
strValue = DLookup("FieldBeingLookedUp", _
"TableQueryOfData", _
"FieldBeingSearched='" & Me.myControl.Value & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


All 4 are text.
--
Thanks for your help!
Walter


:

What type of data are in the myControl control -- number?
string/text?
date?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


No. It is a stand alone form.
--
Thanks for your help!
Walter


:

Is the myControl control in a subform on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Yes. There are 4 controls on the form. 2 will have data and
at
least
1
of
the other 2 will have data. The form contains the same data
as I
supplied
to
the query to open it independantly.
--
Thanks for your help!
Walter


:

Is there a value in the myControl control on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a query to prevent duplicate entries which is called
from a
form.
The
criteria are set to forms!myForm!myControl. When I open
the
query
and
supply
the criteria, it works but when it is opened from the form
there
are
no
records displayed.
Click to expand...
Click to expand...
Click to expand...
 
The event on which you run the code will impact how to "undo" the values
entered.

For all the things that you're doing here, it may be best to use the form's
BeforeUpdate event to do the duplicate test. That way, you can cancel the
update (thereby not saving the new duplicate record), and you can use
Me.Undo in place of all the other .Undo steps.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Walter said:
I figured it out. It hit me last night that there may be an issue with the
field name "DOT#". I renamed it to "DOT", updated all my references and
the
query now works as it should. I now have another issue. The .Undo lines
are
not removing the control values thereby allowing a duplicate record to be
entered.
--
Thanks for your help!
Walter


Ken Snell (MVP) said:
I don't see anything obvious, so let's try an experiment.

Make a copy of your database. In the copy, open your frmEnterTires form,
and
move the code from the ctlSerialNumber_BeforeUpdate event procedure to
the
ctlSerialNumber_AfterUpdate event procedure. Then go to the
DuplicateEntry
subroutine and comment out the .Undo lines. Save and close the form.

Then open the form in forms view, and select information in the controls
that should cause your query to have a record. Does it show a record?
While
the form is open, go to the saved query qryDuplicateTire and run it --
does
it show a record?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Walter said:
The SQL is:
SELECT tblTires.TireID, tblTires.Brand, tblTires.Model,
tblTires.[DOT#],
tblTires.SerialNumber
FROM tblTires
WHERE (((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber])) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])=[forms]![frmEnterTires]![ctlDOT_]) AND
((tblTires.SerialNumber) Is Null)) OR
(((tblTires.Brand)=[forms]![frmEnterTires]![cboBrand]) AND
((tblTires.Model)=[forms]![frmEnterTires]![cboModel]) AND
((tblTires.[DOT#])
Is Null) AND
((tblTires.SerialNumber)=[forms]![frmEnterTires]![ctlSerialNumber]));

The code in the form is:
Private Sub DuplicateEntry()
Dim intTire As Integer
Dim varBrand, varModel, varDot, varSN
Dim Msg, Style, Title, Response, MyString

intTire = Nz(DLookup("TireID", "qryDuplicateTire"), 0)
varBrand = DLookup("Brand", "qryDuplicateTire")
varModel = DLookup("Model", "qryDuplicateTire")
varDot = DLookup("[DOT#]", "qryDuplicateTire")
varSN = DLookup("SerialNumber", "qryDuplicateTire")
Msg = _
"Tire " & intTire & vbCrLf & _
"" & varBrand & """ "" & varModel & """ & vbCrLf & _
"DOT Number "" & varDOT & """ & " " & "Serial Number "" &
varSN
& """ & vbCrLf & _
"has already been entered. Click OK to modify information "
&
vbCrLf & _
"or CANCEL to abort entry."
Style = vbOKCancel
Title = "Duplicate Entry!"

If intTire <> 0 Then
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.
MyString = "OK" ' Perform some action.
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.ctlDOT_.SetFocus

Else ' User chose No.
MyString = "Cancel" ' Perform some action.
Me.cboBrand.Undo
Me.cboModel.Undo
Me.ctlDOT_.Undo
Me.ctlSerialNumber.Undo
Me.cboBrand.SetFocus

End If
This is called from ctlSerialNumber BeforeUpdate and on Exit in case of
no
entry in this control.
BTW: I have another DLookup on another query that was working fine a
couple
days ago that now is giving me an "Object doesn't contain the
automation
object TireID" error. The TireID is in the query.
--
Thanks for your help!
Walter


:

Please post the code that you're running; let's see what it contains.
Likely
we will be able to see the problem then.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


All of the criteria are included in the query itself. The value
being
looked
up is integer, all criteria are text. I have:
intMyVariable = NZ(DLookup("TableID", "qryTableLookup"),0)
--
Thanks for your help!
Walter


:

Then I'm guessing that you're not delimiting the value from
myControl
with '
characters, which is needed for string values. This is example of
how
you'd
do it in VBA code, if that is what you're using:

Dim strValue As String
strValue = DLookup("FieldBeingLookedUp", _
"TableQueryOfData", _
"FieldBeingSearched='" & Me.myControl.Value & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


All 4 are text.
--
Thanks for your help!
Walter


:

What type of data are in the myControl control -- number?
string/text?
date?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


No. It is a stand alone form.
--
Thanks for your help!
Walter


:

Is the myControl control in a subform on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Yes. There are 4 controls on the form. 2 will have data
and
at
least
1
of
the other 2 will have data. The form contains the same
data
as I
supplied
to
the query to open it independantly.
--
Thanks for your help!
Walter


:

Is there a value in the myControl control on that form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a query to prevent duplicate entries which is
called
from a
form.
The
criteria are set to forms!myForm!myControl. When I open
the
query
and
supply
the criteria, it works but when it is opened from the
form
there
are
no
records displayed.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top