Update Table after Record Update with Form

[speciman_A]

Unfortunately I can't post it because it contains sensitive data, but I can try to elaborate on the structure and everything. I will also attempt to strip it down to the basic elements so that I can post it. Unfortunately I have had zero success narrowing down the problem. Just for reference, here is the current script I am trying to run:

Option Compare Database


Option Explicit
Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstring As String
Set db = CurrentDb
SQLstring = "SELECT * FROM Employee_Data WHERE EMP_ID = " & Me.EMP_ID
'Debug.Print SQLstring
Set rs = db.OpenRecordset(SQLstring, dbOpenDynaset)
With rs
.Edit
!LastUpdate = Now()
!LastUpdateID = ReturnUserName
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Again, it produces Error 3061, with

SELECT * FROM Employee_Data WHERE EMP_ID = ????????

showing up in the intermediate window. I have no issues when I test with other fields, only my primary key (EMP_ID), which is a replication ID. I have checked multiple times for spelling errors.

Basically, I have the Employee_Data table with the basic information for each employee, and really basic form for editing the data (for now, as I am just trying to nail down the code at this point). There are no combo boxes or anything, just basic controls. Let me know how I can elaborate while I work on creating a sandbox database for problem specific testing.

Again, thank you so much for the help.

V/R

Josh

[speciman_A]

I can use the SSN field for now, but I am concerned that in the off chance the same SSN shows up in the DB, two records will be updated instead of just the one just modified.

V/R

Josh

[pbaldy]

I've never used a replication ID; I assume some sort of number appears in the EMP_ID field?

[speciman_A]

A replication ID looks like the following:

{662A4973-11C2-42C9-9AED-437F22B7A846}

Just a thought, but is there anyway the code might be confused if the replication ID is linked to the EMP_ID table in another table?

V/R

Josh

[ssanfu]

In post #8 you said:

Ok, so it still won't let me use the replication ID,..<snip>

Why are you using a replication ID as the PK? This is not recommended


If you really are using a replication type field, you need to read this:
Michka is well regarded as one of, if not the, leading expert on Access replication.
http://www.trigeminal.com/usenet/usenet011.asp?1033


From: http://office.microsoft.com/en-001/a...010341783.aspx
AutoNumber sub header
The Replication ID field size is used for AutoNumber fields that are used as replication IDs in a database replica. Do not use this value unless you are working in or implementing the design of a replicated database.

[speciman_A]

ssanfu,

I am using replication ID because I wanted a very unique number (not the usual 1, 2, 3 etc. that is normally assigned with an autonumber). However, it appears that I do not have any idea what a replication ID is intended for...so I went through the painful process of deleting all of my relationships and remade my pk using a long integer autonumber. Of course, that made my script work fine. I would still like to somehow format the autonumber to randomly generate a number, so back to the books (and Google) I go. Thanks for the help.

V/R

Josh

[ssanfu]

I would still like to somehow format the autonumber to randomly generate a number, so

That is called a "custom autonumber".
You would have to write the code to generate the random number for your PK field. Personally, I think that is too much work. I use autonumbers for linking - no user sees the number anyway.


One example of custom autonumber is at Paul's site: http://www.baldyweb.com/CustomAutonumber.htm


Google: "custom autonumbers"

[speciman_A]

Not to unleash a monster, but I was hoping to use an autonumber that is just as or more unique than a SSN because I am hoping to somehow set up my backend on a SharePoint site. I would keep sensitve information on the frontend, but reference all the linked information explicitly through a random autonumber on the backend so that if the site is hacked the information is non-attributable to the employee. This may make no sense at all (both my SharePoint and Access skills are in their infancy), but it was just a concept I was digging into. Anyway, I will keep playing with it (but feel free to let me know if my aforementioned "plan" is completely crazy haha). Thank you so much for the help.

V/R

Josh

[pbaldy]

What difference does it make if it's

{662A4973-11C2-42C9-9AED-437F22B7A846}

vs

12345

as long as it's unique? You can use an autonumber as the primary key and something else as the employee number if you don't want it attributable to the employee.

[speciman_A]

Paul,

Good point. I ended up just plugging the following into the Default Value line in the EMP_ID field properties, using Double instead of Autonumber. Seems to work fine, so I'm going to go with that until it presents a problem. This is PLENTY unique for a 4500 employee database (I think).

Int((999999999999999-999999999+1)*Rnd()+999999999)

V/R

Josh

[pbaldy]

I'm glad it works for you.