Form doesn't show query results

[Tony Williams]

I have a form whose control source is a query. The query compares two tables,
without any fields being linked in a relationship, and looks for records in
both tables where data in certain fields is duplicated. When I run the query
from design view it returns results. I have a command button on a form that
should run the query and then populate another form with the results. I know
the query is running becuse the hourglass is there for a while but when the
form opens there are no records showing.
Here is the sql for the query

SELECT VDATAPNC.txtVRM, VDATAPNC.txtVIN, VDATAPNC.txtstatus,
VDATAPNC.txtimportdate, VDATAPNC.txtowner, VDATAPNC.txtmake, VDATAPNC.txtmodel
FROM VDATAPNC, Semita
WHERE (((VDATAPNC.txtVRM)=[semita].[txtvrm]) AND ((VDATAPNC.txtstatus)="01")
AND ((VDATAPNC.txtimportdate)=[semita].[txtimportdate])) OR
(((VDATAPNC.txtVIN)=[semita].[txtvin]) AND ((VDATAPNC.txtstatus)="01") AND
((VDATAPNC.txtimportdate)=[semita].[txtimportdate]));


And here is the code behind my commnad button

Private Sub cmdopendups_Click()
On Error GoTo Err_cmdopendups_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmduplicates"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdopendups_Click:
Exit Sub

Err_cmdopendups_Click:
MsgBox Err.Description
Resume Exit_cmdopendups_Click

End Sub

Can anyone see anything I'm doing wrong?
Thanks
Tony

 

[Linq Adams via AccessMonster.com]

You say

"I have a command button on a form that should run the query and then
populate another form with the results"

Is the actual RecordSource for "frmduplicates" the Query you posted?

 

[Tony Williams]

Yes Linq it is.Tony

 

[tina]

a form doesn't have a control source, it has a RecordSource property.
assuming that's what you meant, that the form's RecordSource is set to the
query, and since you say that the query returns records when you open it
directly, then i'd say there's some problem in your form properties.

if the query is not updateable - easy to find out by opening it directly and
attempting to add or edit a record; if you can't, the recordset returned by
the query is not updateable - then check the form's DataEntry property - it
should be set to No. while you're at it, also check for code in the form's
Open or Load event that tries to move to a new record - though such code
would cause a runtime error, unless there is error handling code that
ignores that particular error.

another thing to check: is the form being opened from another form, or from
a toolbar button or menu button - in other words, is the form being opened
programmatically, rather than just be clicking on it in the database window?
if so, then check the code that opens the form to see if 1) the records are
being filtered, because the applied filter may be returning no records, or
2) if the data mode is being set to "add only", which is the same as setting
the form's DataEntry property to Yes.

hth

 

[Rob Parker]

Hi Tony,

I assume that the Record Source for frmDuplicates is the query that you
posted. If not, then set it to be so (either via the name of the saved
query, or by entering the SQL as the Record Source).

The obvious problem is that you are opening frmDuplicates with a Where
parameter (stLinkCriteria) in the DoCmd.OpenForm line, and that variable has
nothing assigned to it; therefore, your form will not display any records.
You also say that your command button "should run the query and then
populate another form with the results"; if frmDuplicates has the query as
its Record Source, then your command button does not need to explicitly run
the query (and, indeed, it is not doing so), nor take any action to
"populate the form with the results"; all it need do is open the form.

HTH,

Rob

 

[Tony Williams]

Hi tina and Rob. Got rid of the WHERE parameter and works just fine now.
Thanks to you both for your suggestions
Tony