Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA duplication
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA duplication
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA duplication
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA duplication
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA duplication
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 "Keith74" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplication | Excel Worksheet Functions | |||
Duplication | Excel Discussion (Misc queries) | |||
Duplication | Excel Discussion (Misc queries) | |||
no duplication please | Excel Programming | |||
Duplication | Excel Programming |