Auto-number Woes

[Klo]

I have a table called PatientNames that contain Patient ID, LastName, Firstname, and MI. The primary key is Patient ID. This table is updated by importing a text file that has all the Patient Names and associated IDs (old and new). Since the Patient ID is the primary key only the new patients are added and I get the usual info box saying 31678 names could not be added. That is not a problem. The problem is that I now need to add a unique number for each patient to the table. Auto-number works at first, adding 1-31678. The next time I update the table auto-number starts with 92,000 (or something close) for the new patients and the next time will go to 128,000. It recounts all the records including the ones with the same primary key and goes from there. Is there any way to get it to start at 1 and just add 1 to the last number in the ID field for new Patients?
Thanks, Mike

 

[EBGreen]

Check the FAQs. Here is one that is applicable:
faq181-158

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Klo]

Ok, this must be wrong. What did I do?
Function setRecordID("Patient_join_list" As String, "ID" As String)

 

[EBGreen]

Is that all the code that you added?

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Klo]

No .... I pasted all of it from the faq. It's just that access says there's an error on that line so I must have the syntax wrong. I've been working with WinBatch for the last couple of months. It's close to the same but different. It's hard to make the transition in the thought process.

 

[EBGreen]

That line wasn't part of the code in the FAQ. Where did you add it?

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[lespaul]

I don't think you should have the " around your variable names:


Function setRecordID(Patient_join_list As String, ID As String)

Leslie

 

[Klo]

Function setRecordID(strObject As String, strField As String)
Dim myWS As DAO.Workspace
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
On Error GoTo err_setRecordID

1) strObject could be a local, linked table, query, or an SQL statement.
2) StrField is the name of the field you want to autonumbered

I guess I'm not understanding the whole thing.

 

[EBGreen]

If you are using that line as the function declaration of the function from the FAQ, then I think you will have problems. The function is written for the first object passed in to be an object not a string.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Klo]

So knowing that the table is named Patient_join_list and the field is names ID, how would I write it using the faq?

 

[EBGreen]

First, let me state that I was mistaken. The first (and second for that matter) parameter is a string not an object. Try calling the function thusly:

setRecordID("Patient_join_list", "ID")

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Klo]

Still no luck. All my other functions start something like thisublic Function Run_Patientname_update()So I don't understand how the faq is written as far as the function name. I'm still lost. What is the purpose of the setRecordID?

 

[lespaul]

are you trying to create your own function or just trying to use the one in the FAQ?

If you just want to use it, paste it into a module and call it like Tom showed you.





Leslie

 

[Klo]

This is how I have it set up:

Function setRecordID("Patient_join_list", "ID")

Dim myWS As DAO.Workspace
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
On Error GoTo err_setRecordID

Set myWS = DBEngine(0)
Set mydb = CurrentDb
Set myRS = mydb.OpenRecordset("Patient_join_list", dbOpenDynaset)

myWS.BeginTrans
' Do bulk changes to Recordset.
With myRS
.MoveFirst
Do While Not .EOF
.Edit
.Fields(ID) = .AbsolutePosition + 1
.Update
.MoveNext
Loop
End With
' If all updates done successfully, commit the transaction.
myWS.CommitTrans
Exit Function

err_setRecordID:
Select Case Err.Number
Case 3061
'Error in the function arguments
MsgBox "Error in your Object name or your Field name"
Case Else
'If any error occur while making the updates
'all of the changes will be rolled back (not saved).
MsgBox "Start the function again, updating problems"
myWS.Rollback
End Select

Exit Function

End Function



When I try to run it access says I have a syntex error.

 

[Klo]

I'm sorry ... It says I have a syntex error on this line:
Function setRecordID("Patient_join_list", "ID")

 

[EBGreen]

Change this:
Function setRecordID("Patient_join_list", "ID")
to:
Function setRecordID(strObject As String, strField As String)

Then in the rest of your code, whenecer you do something that would add records to Patient_join_list, immediately execute this line:
setRecordID("Patient_join_list", "ID")

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Klo]

Sorry guys, but I just can't get it to work. I've never worked with DAO before. Thanks all for your help. I'll have to find another way around it.
Cheers, Mike

 

[EBGreen]

Sorry we couldn't help, but if you post the error you are getting I'm sure someone can figure it out eventually.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[lespaul]

There are only two things to do to get this function to work:

1. copy the function from the FAQ directly into a module.

2. within your code you enter:
setRecordId("Patient_join_list", "ID")

and you get an error message?

What is the error?

Now, if you did not follow the two steps above, what did you do?




Leslie

 

[Klo]

I believe it is step 2 that I don't understand. Where in the code do I enter: setRecordId("Patient_join_list", "ID")?