Need help with MS Access

[sattien]

I am new to programming and don't know VBA so I need some help from here. Here are my questions (sorry if they sound silly):

1. Let's say I have a form with an ID field that's a primary key but not auto-numbered. Now I want to insert some code into the "AfterUpdate" event of that field so that it checks to see if the value already exists after user input, and pop up a custom warning. How should I code it? I know Access will check that after you move on to another record, but I want it to check right after the update of the ID field.

2. How do I reset a running AutoNumber without deleting and recreating that AutoNumber field?

3. How do I program an AutoNumber so that it runs in the right format (like alpha numeric, fixed digits, etc)? I would appreciate if someone has a link on this because it's probably gonna be a long post.

All of the above questions are M$ Access-specific. I know it's slow and sucks but I don't have any other choice. Thanks. :)

[sattien]

Anyone? :(

[Stu]

1. Let's say I have a form with an ID field that's a primary key but not auto-numbered. Now I want to insert some code into the "AfterUpdate" event of that field so that it checks to see if the value already exists after user input, and pop up a custom warning. How should I code it? I know Access will check that after you move on to another record, but I want it to check right after the update of the ID field.

AfterUpdate is a bad place to put the code, you are checking for duplicates on the primary key "after" the data has been accepted--if it was accepted, no duplicate exists! So, you want the BeforeUpdate Event (either on the form or on the column/field). Assuming the column/field in question is of Number type, is named col1, and is in a table named Table1, here is the code:




Private Sub col1_BeforeUpdate(Cancel As Integer)
Dim db As Database
Dim rst As Recordset
Dim strSQL As String

' Build SQL...
strSQL = "SELECT col1 FROM Table1 where col1 = " + col1.Text

' Define the database to use...
Set db = CodeDb

' Open a record set...
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' A record already exists with this primary key

' Print your error message here

' Kill the insert or update...
DoCmd.CancelEvent

End If

' Close the record set...
rst.Close

' Free resources...
Set rst = Nothing
Set db = Nothing


End Sub



2. How do I reset a running AutoNumber without deleting and recreating that AutoNumber field?

You don't. The whole point of an AutoNumber is to guarantee that every number assigned to that column is unique--if you could reset it that would defeat its purpose.

3. How do I program an AutoNumber so that it runs in the right format (like alpha numeric, fixed digits, etc)? I would appreciate if someone has a link on this because it's probably gonna be a long post.

Assuming you mean leading zeros and/or positionally fixed characters (like commas and spaces), open the table in Design View, Select the column, and on the General tab in the Format field type in zeros for digits and characters for characters. This may sound a little confusing, but try it and you'll see what I mean.

[sattien]

Thanks Stu, I have the code in a TXT file and will insert it into the BeforeUpdate event when I get on that computer. I don't know the proper command to insert an error message but it shouldn't be difficult to figure that out. :)

Thanks again. You saved my day. Is it okay if I have further questions in the future? :)