How to write code to update data to a table

[Vicky]

I have a form which has a field "Version" I would like when I add a new
record in the form, to have a macro which looks to see if that record is
already in a table (tblVersion) and if it is then prompt me that it already
exists, if not, have it update the table with the new data. I believe I need
an append query, and I understand that I need an IF function, but not sure
how to write the code to run the query If needed. Any suggestions for me?
If you have a site that could be helpful to teach me, that would be great.

Vicky

 

[Tom van Stiphout]

On Thu, 13 Nov 2008 15:21:02 -0800, Vicky

Say you have a form with a textbox named txtVersion bound to a table
named myTable and a text field named Version, and you go to a new
record and then enter a version and then tab or click out of that
field. The Version_BeforeUpdate event fires. Write:
If Me.NewRecord Then
If Not IsNull(DLookup("Version", "myTable", "Version='" &
Me.txtVersion & "'")) Then
MsgBox "Aaarrcchhh, this one already exists", vbCritical
Cancel = True
End If
End If

So we're using DLookup to find the value. Note how we wrap the value
in txtVersion with single quotes as is required for textual lookups.
Then we test if the return is Null, and if not, the version already
exists. Then we set Cancel=True so the user cannot leave the control
without specifying a better value.

Personally I would comment out the first and last lines of my code and
perform the test also on existing records.

-Tom.
Microsoft Access MVP

 

[Vicky]

THank you. I am not sure if I am doing something wrong but the second line
(if Not IsNull) is not running - compiler error. I changed the mytable to
the table name I want to search.

Also, maybe I wasnt clear, or leaving out details when I explained the
issue - not becuase I am trying to make it difficult for someone to help,
just because this is new to me. What I was trying to say was the form that
has "version" is tied into a table but that is not the table that I need to
update. I need to update a related table. I think I need to include an
append query. Not sure how to approach. Any help is greatly appreciated.