Assigning a value to primary key

[Judy]

I have a form where the user enters a preassigned number. I want VBA to automatically assign a value to the primary key field that consists of the number entered by the user + 20 at the end. Example: user enters 12345 in txtNum field, VBA assigns 1234520 in txtNumID field. I have written the following pseudocode to demonstrate what I would like to see. This is attached to the txtNum data entry field on the form(?).

After update txtNum
Check to see if txtNum exists
If no
txtNumID = txtNum + 20
If yes
AlertBox = "Number already exists
search number field for
existing record"
Set Focus.txtNum
DoCmd.GoToRecord , , acNewRec
End if

Any help is appreciated! I'm new to Access VBA and so far this site has been a tremendous help! Thanks in advance.

 

[Jon Holmes]

Judy,

The result of your statement txtNum + 20 will yield 12365. There are two options.
1. Convert the field to a text field, then use txtNumID = txtNum & "20"
2. Leave as a number txtNumID = txtNum * 100 + 20

Jon

 

[Judy]

Thanks Jon, I will use your first suggestion of txtNum & "20" to concactenate the numbers. I'm not sure how to write the rest of the code.

Thanks!

 

[D-Fresh]

Put the following code in the beforeupdate event of the txtNum field.

dim MyRecs as recordset
dim MyDB as database
dim MySQL as string

MySQL = "SELECT * FROM

WHERE txtNumID=" & int(me("txtNum") & "20")

set MyDB = currentdb
set MyRecs = MyDB.openrecordset(MySQL)

if not MyRecs.eof then
'The record exists.
msgbox("Sorry, the record already exists")
cancel=true 'This will prevent the record from updating.

else
me("txtNum")=int(me("txtNum") & "20")
end if


This should do it for ya.. Hope that helps...

Doug

 

[Judy]

Yes, thank you, that helped tremendously. I only hope one day I'll be able to post answers instead of questions!!