VB code to perform custom query of MS access table and display alert

[ghadley_00]

I have a MS access database which has entries (each of which has a name
field) and uses a form to make new entries. I would like to put
together code that will run when a new entry's name is entered that
does the following:

1) query the database to see if that name is already entered
2) if the name is already present, popup a msgbox telling the user the
name is already in the system, and in some way list the results of the
query (it is possible to have more than 1 record in the table to have
the same name)

I think I can do part 1 by running VB code that runs as afterupdate
event code, but I'm having trouble finding out how to connect to the
access database and perform the query.

Any help would be greatly appreciated.

George Hadley
gh********@yaho o.com

[Tom van Stiphout]

On 26 Dec 2005 12:32:31 -0800, gh********@yaho o.com wrote:

A column name of "name" is discouraged, because it is a reserved word.
Use it at your peril. Below I'm assuming you renamed it to
CustomerName, that the name of your control is txtCustomerName , and
the name of the table in question tblYourTable.

A crude way of doing what you want is to place the following code in
the CustomerName_Af terUpdate event:
dim lngCount as Long
lngCount = DCount("[CustomerName]","tblYourTable ","[CustomerName]='" &
txtCustomerName & "'")
if lngCount > 0 then
Msgbox "There are already " & lngCount & " customers by that name in
the database.", vbExclamation
else
Msgbox "Cool - New name.",vbExclam ation
end if

A more elegant approach requires the use of a recordset, concatenating
the records with the same name. That's a bit more advanced. See this
link for details:
http://www.mvps.org/access/modules/mdl0004.htm

-Tom.

I have a MS access database which has entries (each of which has a name
field) and uses a form to make new entries. I would like to put
together code that will run when a new entry's name is entered that
does the following:

1) query the database to see if that name is already entered
2) if the name is already present, popup a msgbox telling the user the
name is already in the system, and in some way list the results of the
query (it is possible to have more than 1 record in the table to have
the same name)

I think I can do part 1 by running VB code that runs as afterupdate
event code, but I'm having trouble finding out how to connect to the
access database and perform the query.

Any help would be greatly appreciated.

George Hadley
gh********@yah oo.com

[Jana]

George:

Actually, I would use the BeforeUpdate event, rather than the
AfterUpdate event. Otherwise, you'll ALWAYS have an entry that matches
the one you just input because the record has already been added. :)

Jana

[Tom van Stiphout]

On 27 Dec 2005 08:47:06 -0800, "Jana" <Ba********@gma il.com> wrote:

I was assuming the form would have more fields than a single
CustomerName field. If that's correct, then CustomerName_Af terUpdate
is the best event to put this code, unless you want to whistle back
the user and deny the right to add a duplicate (which is not what the
OP indicated).
If there are several more fields in addition to CustomerName, just
filling out that field does NOT create the record in the table.

-Tom.

George:

Actually, I would use the BeforeUpdate event, rather than the
AfterUpdate event. Otherwise, you'll ALWAYS have an entry that matches
the one you just input because the record has already been added. :)

Jana

[ghadley_00]

Thank you for all the replies. The code appears to work properly as an
AfterUpdate event. The table does in fact have more than the
CustomerName field.

Is it possible to modify this code to take into account information
from another field?

In other words, do a count of customer name where a particular criteria
exists? e.g. [AcType=4]

Thanks,

George

gh********@yaho o.com