create a unique text string

[Jen0dorf]

Hi

I created a simple form with a text box for the user to type in a text string.



I need to compare that text string to the text string in a table and for it to show if it's been used before.

so I have a field in 39999 records called Mlink. This Mlink field is not unique and is in the format of LL1234

I want to type this into a text box and then compare that to the Mlink field in my table and for the box to turn green, show a message OK if the entry has not been used before.

Sounds simple huh?

cheers

Ian

[pbaldy]

In the after update event of the textbox, use a DCount() to see if the value exists, and change the color appropriately. Probably want code in the current event too, to catch when the record changes.

[Jen0dorf]

Hi

just got back from holiday with grandson - exhausted where does a 4 year old get that much energy?

Anyway took a look at dCount() and google it as well as youtubed and I seem to be hitting a crossroads with no signposts. Some links take me into visual basic, others into mysql and web applications and others into pages and pages of code that I don't understand.

I gather that I run DCOunt with criteria that it matches my textbox and if a zero total is the result then the value does not exist and I can then display a message box or anyhting I want.

The theory is great but would really appreciate a link to a tutorial on how to implement this on a local database

thanks

Ian

[orange]

Why do you care about the format of the random string of 6 chars?
If the format has meaning, please describe what it is.

In general
User types a string into txtBoxA eg XYRRED

So to see if it already exists, you could

if DCount("mlink","tablename", "mlink ='" & txtboxA &"'") = 0 then
    this random string does not exist
else
    this random string already exists
end if

[Jen0dorf]

Hi

The format has no meaning at all.

When this database was created all family graves were created and linked using a field designated MLink ( Memorial Link)

However in the 15 or so years it has taken to photograph and record each grave several volunteers have been involved. With one main transcriber into the final tables.

Problem is that no logical sequence was used ( o I can't see onee) so a1 is followed by a2 but next month they might start at nn1 followed by nn2

So when adding new unrelated records is it important to ensure that the proposed mlink has not already been used, Hence my wish to have a method of checking.

This project is I suppose a "bandaid job" as there was never a concrete nomenclature in place.

There will be very few new records as the graveyard is on;y excepting existing family memebers.

thanks for the assist

Ian