Primary Key Function

[pewilson]

I am trying to utilize a function to create a primary key value of D1000 or D-1000 each time an entry is made into my customer table through a form. Can someone provide a example in how a function like this is to be created? And under which event this would take place?


Thanks in advance.

Paul

 

[Aivars]

Hi, Paul!

I think your question isn't correct. You can't create primary key for each value (you can create it only for table's field). But to check for duplicate value in table is possibly. Here is one of ways how to do it (Event Before Update):

private sub txtMyTextBox_BeforeUpdate(cancel as integer)
dim rst as recordset
dim strSQL as string
dim strValue as string
dim i as byte

strValue = me.txtMyTextBox
i=instr(1,strValue,"-",vbTextCompare)
if i>0 then
strValue=left(strValue,i-1) & mid(strValue,i+1)
endif
strSQL="Select MyTable.MyField From MyTable "
strSQL=strSQL & "Where MyField='" & strValue & "'"
if i>0 then
strSQL=strSQL & " Or MyField='" & me.txtMyTextBox & "'"
end if
strSQL=strSQL & ";"
set rst=currentdb.openrecordset(strSQL)
if not rst.eof then
msgbox "' " & me.txtMyTextBox & iif(i>0," or " & strValue,"&quot & " already exist!"
me.txtMyTextBox.undo
cancel=true
end if
rst.close
set rst=nothing
end sub

Aivars

 

[pewilson]

Aviars,
Thanks you for responding, but I left out one other element in the equation. The purpose of the whole thing was for it to increment by 1 on each new record inputted through the form. Will the example you provide still give me the same result?

Thank you.

Paul

 

[Aivars]

Are all data like Dxxxx or D-xxxx in the table's field?
Aivars

 

[RobJDB]

Does either of you (or anyone else) know how to create an autonumber primary key field for a new table in ADO?

Thanks,
Rob