how to build if code

[solty89]

Hey Guys,
I have a form where im fillind data into boxes and then i have "add record" button which is sending the data to a table.The code is like this:
Private Sub addrecord_Click()

CurrentDb.Execute "INSERT INTO tablename(a1,a2,a3,a4,a5,a6)" & _
"VALUES('" & b1 & "','" & b2 & "','" & b3 & "','" & b4 & "','" & b5 & "','" & b6 & "')"

End Sub

My quation is: How can i say if the values in B1 and B2 already exist in the table into a1 and a2, to do not add new record?
ans also
How ot make a button, if the data in B1 and B2 already exist in A1 and A2, to find it and then to show the rest data in b3, b4, b5 and b6 to be able for edit?

Gr

 

[jdraw]

Research primary key.
Then adjust your table accordingly.
Good luck.

 

[solty89]

Research primary key.
Then adjust your table accordingly.
Good luck.

already have primery key and this is B1, but my question have nothing with this

 

[Mihail]

Depending of your needs there are more solutions here.

First, by using indexes with UNIQUE property set to TRUE.
This is what JDRAW suggest but using other words.

Second, by using DCount function in order to count the records where a certain value appear in a certain field. If this function return 0 (zero) the new value will not be a duplicate.

3th, by using a parametric query and see IF or IF NOT return records.

and more...

 

[solty89]

Depending of your needs there are more solutions here.

First, by using indexes with UNIQUE property set to TRUE.
This is what JDRAW suggest but using other words.

Second, by using DCount function in order to count the records where a certain value appear in a certain field. If this function return 0 (zero) the new value will not be a duplicate.

3th, by using a parametric query and see IF or IF NOT return records.

and more...

Okey, thanks,
1st way with indexing not posible for my situation, so can you help me with any other way with the VBA code, so when i hit the ADD button to return a message that the values alreadi exist in a1 and a2

 

[Mihail]

Okey, thanks,
1st way with indexing not posible for my situation, so can you help me with any other way with the VBA code, so when i hit the ADD button to return a message that the values alreadi exist in a1 and a2

Maybe I can. But I need your DB in order to develop the code.

 

[solty89]

Maybe I can. But I need your DB in order to develop the code.

I'll be thanksful if u help me.do i need to send u jyst the vba code or all the database

 

[Mihail]

Entire DB (saved in 2003 or in 2007 version of Access).
And, of course, some guidance in order to quickly find the problem (what form, what control, what module etc.

 

[solty89]

Entire DB (saved in 2003 or in 2007 version of Access).
And, of course, some guidance in order to quickly find the problem (what form, what control, what module etc.

thank u man,
so becouse i had no idea of vba im doing exactly the same as this guide http://www.youtube.com/watch?v=Ri2Y9-16AEo
so...
1.the form is "fourniturenform" and the code is for this form
so... u see i have "add record" button.That what i want is the combination of "lila + code" to do not repeat in the table.
In the youtube guide the boy is solving the problem like he sad "if he hit add and there is nothing in "ID"field to add new record ,becouse his "ID" field is autonumber and primkey,in "id" field exist just to update the record."
What i want is "when i hit "Add record" buttnon to check if "lila + code" already exist as a record in the table, and if so to update the record, if not to add new record" )


pS: can u give me e-mail to send u tha DB

 

[Mihail]

Upload your DB here, on the forum. This way will be more others (and more skilled that me) that can take a look to your DB and help you.
Convert your DB for Access 2003 (better) or 2007, ZIP it and upload.

And, next time, be a little bit more precise because this

What i want is "when i hit "Add record" buttnon to check if "lila + code" already exist as a record in the table, and if so to update the record, if not to add new record"

is a new (and different) requirement (question) than in your first post - and with a different solution.

 

[solty89]

Upload your DB here, on the forum. This way will be more others (and more skilled that me) that can take a look to your DB and help you.
Convert your DB for Access 2003 (better) or 2007, ZIP it and upload.

And, next time, be a little bit more precise because this

is a new (and different) requirement (question) than in your first post - and with a different solution.

Hey Mihail,
at the moment im working on MO 2013 and its telling be that it cant save the DB as v 2003 becouse of the new feutures im my DB.Also with zip is over 2mb and i cant upload it here

 

[pr2-eugin]

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

With regards to the "new features", try removing those features from the tables. Example calculated fields, multivalued fields, attachments, lookups etc. and try again. If not, we are at loss..

 

[Mihail]

Even you can't convert you should upload it. Someone will be able to open it.
Follow Paul's instructions.
Many time if compact the DB is enough.

 

[Mihail]

Take a look to the EXECUTE method.
First, EXECUTE an UPDATE query then
use the RecordsAffected to see if this method has update a record.
If NOT then
run an APPEND query

I'm pretty sure that this should be your approach (based on your last requirement)

 

[solty89]

Take a look to the EXECUTE method.
First, EXECUTE an UPDATE query then
use the RecordsAffected to see if this method has update a record.
If NOT then
run an APPEND query

I'm pretty sure that this should be your approach (based on your last requirement)

View attachment Lundia Database v1.0n.zip this is the db