Find record code

[moneypennie21]

HI, I have a form that pulls info from a table. I want users to be able to search for existing records before entering a new one so if it exists they an update the existing rather than create a new record. I created a find record button and used the built in functions on create button to make it find records. it works OK and brings up the 'FIND and Replace' search box.

However I have a couple of issues with it,


1. it doesn't always find the record for some reason -(on some occasions if the users exit the form and then go back in and try again it works but not always - users set search parameters to 'whole document' and 'any part of field')
2, I would like it to give an error message if users try and enter a value in a specific field that already exists - the field is set to numeric

I have seen some mention of how you can use Dcount to do this but I am not sure of how to write the code to make it work can anyone help me?

the table the form is based on is called Table1 and the field I want to search is called System_Number

Thanks

J

[isladogs]

You could test if the record exists using something like:

If DCount("*","TableName", "FieldName ='some value'")>0 Then

However if your search routine is done properly that shouldn't be necessary.
Allen Bowne has several search forms n his website you could look at.
For example, http://allenbrowne.com/ser-62.html

[moneypennie21]

Hi Ridders52, the link you have provided looks like it is aimed at someone with a bit more access knowledge than me! I do like the idea of creating a search form like this and I have done it in previous databases I have built - not as complex as this one though but this code might help me make it better. I might have a look at this at some point I would need to add a link to open the Data entry form at the specified record and also if no record is found open the form to add a new record which I think I know how to do! in the meantime if there was a simpler way of resolving the issue with the find record command that would be useful as creating the search form will take me some time and I need to resolve this fairly quickly for the users.

[isladogs]

Allen Browne always explains the code but you can largely use it as is in your own databases without understanding it all
He also has a couple of other examples you could look at or do a forum / site search.

You will need to upload your form to get detailed advice on how to fix it.
I'm sure someone will assist.
In the meantime did you try using the DCount code I provided?

[Micron]

on some occasions if the users exit the form and then go back in and try again it works but not always

Possibly they are searching a table whose records have not been updated. Closing the form and re-opening it would cause it to "see" the new record(s). Possible; maybe not highly probable that this is the case. Also possible that a record isn't found based on their input because there isn't one?

You could also research using a recordsetclone along with FindFirst and NoMatch properties if you don't need a full blown search form
https://docs.microsoft.com/en-us/off...recordsetclone

or add a textbox with a filter-as-you-type feature. As the user types a value (using the OnChange Event), the form records filter out
There's probably a lot of other approaches you could take as well. It is also possible that your process is a bit flawed. If it's an indexed field and should not contain dupes, then maybe it shouldn't be user defined?

[moneypennie21]

I have tried putting in the d count but I can't get it to work. where you have 'some value' I have tried replacing with me.system_number to ask it to compare to the field but this doesn't work, what am i doing wrong?

Thanks

J

[isladogs]

If you are trying to get a value from a control, you need slightly different syntax
For a number field

If DCount("*","TableName", "FieldName = " & Me.ControlName)>0 Then

If it's a text field, use text delimiters

If DCount("*","TableName", "FieldName = '" & Me.ControlName & "'")>0 Then

[moneypennie21]

HI Macron, unfortunately the system number is the only unique identifier that we have for each client that is required to be called and the agent enters it as part of the information to create the record, but that client may be called multiple times due to no answer or call -back requested (outcome can be updated). Yesterday evening I manually removed any duplicates, made the customer number indexed and no duplicates allowed and everything seems to be working ok so far today even the find record is working. so I will see how this progresses.

Thanks

J