Search issue

[karanvemuri]

HI guys,



I have let access chose my primary key for my db, however When i search a record using the date field on the original form, Access creates a new record in the DB ? can this be rectified?

[pdebaets]

How are you doing the search?

[karanvemuri]

have created a 2ndform to display the search results with the Datefield as the parameter

[pdebaets]

I think the search may be returning no match. Check for this condition before displaying the 2nd form. Also verify that you are comparing similar date formats.

[karanvemuri]

the search is returning match,but access is allocating a new ID and creatin a record for the searched criteria

[pdebaets]

Please post the code that does the search, and opens the 2nd form.

[karanvemuri]

Dim Yesterday As Date
Yesterday = Date - 1

DoCmd.OpenForm "DetailForm", , , "Date = #" & Yesterday & "#"

[pdebaets]

"Date" is an Access internal function that returns the current date. It is likely that the criteria you are specifying (Where Date (current date) = Yesterday) will always return False. You should not name your table fields or form controls with this name. Try renaming your "Date" field/control to "DetailDate". Then change the OpenForm to

DoCmd.OpenForm "DetailForm", , , "DetailDate = #" & Yesterday & "#"

[pdebaets]

One more thing to check is your DetailForm DataEntry property. It should be set to "No".

[karanvemuri]

i renamed the Date field to Detail date..the search is returning match,but still access is allocating a new ID and creatin a record for the searched criteria....

[hitesh_asrani_j]

Even I am waiting for a reply on this issue....
Whenever I search with a field, get a result in a new form which is correct... but when i have to create a new record i click on the "new record" button and even the search criteria is saved in the DB :S REally want to clear this off as it is creating undesired space in the DB

[pdebaets]

How can you tell that the search is returning a match?

[karanvemuri]

I can find the search results in the subform which i have created

[pdebaets]

What is the search criteria or filter that you've specified to get the desired results in your subform?

[pdebaets]

Oh, this just occurred to me... Try this:

DoCmd.OpenForm "DetailForm", , , "format(DetailDate,'yyyymmdd') = format(#" & Yesterday & "#,'yyyymmdd')"