Query omit current record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have a subform running a query to find records based on the value
(Surname) in a field on the main form. Both the query and the surname field
are linked to the same table. It works well except that when it requeries
(afterupdate) it also shows the current record (ie the name I just typed into
the Surname field). Is there an easy way to filter out this record?
 
Hi
I have a subform running a query to find records based on the value
(Surname) in a field on the main form. Both the query and the surname field
are linked to the same table. It works well except that when it requeries
(afterupdate) it also shows the current record (ie the name I just typed into
the Surname field). Is there an easy way to filter out this record?
Click to expand...

Do you have a Primary Key in the table (surnames are not, of course,
unique)? If so, just include a criterion on the Primary Key of

<> Forms!yourformname!primarykeycontrolname


John W. Vinson[MVP]
 
Thanks John,
but I must be doing something wrong.
My primary key is called
"code" so in criteria of the code field in the query I put
<>[Forms]![Data]![codebox]
being the control on the main form. But it did not change results of query.
So then (to get a better idea of what is going on) I also put a control on
the subform (which displays query result) to show value for "code". After
requery it shows autonumber alongside the surname in the subform but returns
a number value (creating new record) on the main form??
 
Thanks John,
but I must be doing something wrong.
My primary key is called
"code" so in criteria of the code field in the query I put
<>[Forms]![Data]![codebox]
being the control on the main form. But it did not change results of query.
So then (to get a better idea of what is going on) I also put a control on
the subform (which displays query result) to show value for "code". After
requery it shows autonumber alongside the surname in the subform but returns
a number value (creating new record) on the main form??
Click to expand...

I'm sorry, I'm completely lost here. What is actually in [codebox]? a
number? a lookup field? a combo box? What's the datatype of
Code: 
?

Perhaps also post the SQL of the query.


John W. Vinson[MVP]
 
Sorry if I'm not explaining clearly.
Codebox is a textbox control on the main form (format as number). It's
controlsource is the autonumber primary key (Data.Code) of the underlying
data table.
The mainform is for entry of names, addresses etc. The subform displays
results of query on same data table. The aim is to display records with same
surname from last 60 days.
Here is SQL

SELECT Data.Date, Data.Item, Data.Surname, Data.FirstName, Data.Address,
Data.Suburb, Data.[Post Code], Data.[Identification Number],
Data.Storeperson, Data.Code, Data.Flag
FROM Data
WHERE (((Data.Date)>Date()-60) AND ((Data.Surname)=[Forms]![Data]![Surname]))
ORDER BY Data.Date DESC , Data.FirstName DESC;

I am running a requery macro on after update property of surname control on
main form.

John Vinson said:
Thanks John,
but I must be doing something wrong.
My primary key is called
"code" so in criteria of the code field in the query I put
<>[Forms]![Data]![codebox]
being the control on the main form. But it did not change results of query.
So then (to get a better idea of what is going on) I also put a control on
the subform (which displays query result) to show value for "code". After
requery it shows autonumber alongside the surname in the subform but returns
a number value (creating new record) on the main form??
Click to expand...

I'm sorry, I'm completely lost here. What is actually in [codebox]? a
number? a lookup field? a combo box? What's the datatype of
Code: 
?

Perhaps also post the SQL of the query.


John W. Vinson[MVP]
[/QUOTE]
Click to expand...
 
Sorry if I'm not explaining clearly.
Codebox is a textbox control on the main form (format as number). It's
controlsource is the autonumber primary key (Data.Code) of the underlying
data table.
The mainform is for entry of names, addresses etc. The subform displays
results of query on same data table. The aim is to display records with same
surname from last 60 days.
Click to expand...

Sorry for not replying for so long, this got on the "to do" list and
didn't get "to done"!

Try

SELECT Data.[Date], Data.Item, Data.Surname, Data.FirstName,
Data.Address,
Data.Suburb, Data.[Post Code], Data.[Identification Number],
Data.Storeperson, Data.Code, Data.Flag
FROM Data
WHERE (((Data.[Date])>Date()-60) AND
((Data.Surname)=[Forms]![Data]![Surname]))
AND Data.Code <> [Forms]![Data]!
Code: 
ORDER BY Data.Date DESC , Data.FirstName DESC;

Note the brackets around the reserved word Date - that's actually not
a good choice of fieldname, since Access can and will confuse it with
the Date() function.

John W. Vinson[MVP]
 
Sorry John, still no change in outcome. I suspect your first answer will work
but may be something to do with timing of requery? I shall keep playing
around with it.
Willow

John Vinson said:
Sorry if I'm not explaining clearly.
Codebox is a textbox control on the main form (format as number). It's
controlsource is the autonumber primary key (Data.Code) of the underlying
data table.
The mainform is for entry of names, addresses etc. The subform displays
results of query on same data table. The aim is to display records with same
surname from last 60 days.
Click to expand...

Sorry for not replying for so long, this got on the "to do" list and
didn't get "to done"!

Try

SELECT Data.[Date], Data.Item, Data.Surname, Data.FirstName,
Data.Address,
Data.Suburb, Data.[Post Code], Data.[Identification Number],
Data.Storeperson, Data.Code, Data.Flag
FROM Data
WHERE (((Data.[Date])>Date()-60) AND
((Data.Surname)=[Forms]![Data]![Surname]))
AND Data.Code <> [Forms]![Data]!
Code: 
ORDER BY Data.Date DESC , Data.FirstName DESC;

Note the brackets around the reserved word Date - that's actually not
a good choice of fieldname, since Access can and will confuse it with
the Date() function.

John W. Vinson[MVP]
[/QUOTE]
Click to expand...
 
Sorry John, still no change in outcome. I suspect your first answer will work
but may be something to do with timing of requery? I shall keep playing
around with it.
Click to expand...

If the record on the form has not yet been saved, it shouldn't be
showing at all. If it *has* been saved, this query should exclude it!
I'm perplexed!

Try changing

AND Data.Code <> [Forms]![Data]!
Code: 
to

AND Data.Code <> [Forms]![Data]![CodeBox]

if Codebox is the name of the control bound to the unique Code field.

John W. Vinson[MVP]
 
Have already changed this but to no avail. Interestingly if I show the value
of Code from the uderlying table - on the form it shows as a number, but on
the subform it shows as "autonumber".

Perplexed also!
If the record on the form has not yet been saved, it shouldn't be
showing at all. If it *has* been saved, this query should exclude it!
I'm perplexed!

Try changing

AND Data.Code <> [Forms]![Data]!
Code: 
to

AND Data.Code <> [Forms]![Data]![CodeBox]

if Codebox is the name of the control bound to the unique Code field.

John W. Vinson[MVP]
[/QUOTE]
Click to expand...
 
Have already changed this but to no avail. Interestingly if I show the value
of Code from the uderlying table - on the form it shows as a number, but on
the subform it shows as "autonumber".
Click to expand...

Please post the SQL view of the Form and the Subform's RecordSource
queries. It sounds like you might have both the form and the subform
based on the same table, or that you're improperly using an Autonumber
in both tables as a linking field.

John W. Vinson[MVP]
 
Back
Top