VBA duplication

[Guest]

Hi, I have an excel form which is currently used to insert records into sql
table via the click of a macro button. The only problem is that it currently
allows duplicates and is causing a few problems. How can I stop duplicates on
Client Name being inserted into the sql table to make my form more efficient?
Any ideas would be appreciated?

 

[Keith74]

Run a query on the table prior to the insert to see if the name
already exists then use that result to determine if to run the insert

i.e.


SELECT ClientName FROM MyTable WHERE ClientName = 'Joe Bloggs'

<code to run query>

If myRecordsSet.BOF = True and myRecordsSet.EOF = True then
<INSERT sql statement>
end if

hth

Keith

 

[Guest]

I have tried this and i am still having problems. The ClientName is in the
excel worksheet, and the database is keeping a record of the ClientName's by
the use of the macro within the excel document. Within the access database
the column that the ClientName's are inserted to is called "PortfolioName". I
have tried to write the vba code but it doesn't seem to understand any of the
sql code even though i have followed your advice. Any help would be very
helpful

Thanks

 

[Keith74]

Hi

The code i posted was more of a general principle thing as i don't
know exactly how you're set up. To make it a little more prescriptive
the select should be something like

mySQL = "SELECT PortfolioName FROM <tablename where PortfolioName is
found> WHERE PortfolioName = ''" & <your textboxname>.value & "'"

What we're doing is running a simple select to determine is the name
in your textbox already exists on the database. The results of the
query are checked using

If myRecordsSet.BOF = True and myRecordsSet.EOF = True then

The "myRecordSet" is whatever you have Dimmed you recordset object as,
and the "myRecordsSet.BOF = True and myRecordsSet.EOF" checks to see
if the recordset is empty, i.e that name doesn't exist on the table.

If you're still having trouble pm me.

Keith

 

[Guest]

Thanks, im slowly getting there i think. Its now accepting the code but it is
still updating the record set. I need it to bring a message box up and not to
update the record set if the mySQL slect finds a match. Im new to visual
basic so any help is appreciated, thanks

Paul