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

 

[lespaul]

This needs to be in your code where you are entering the new data into your table.

Leslie

 

[EBGreen]

Immediately after you update the fields in the table. How do the fields currently get updated?

[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]

I have a macro that imports a text file into the table. Since the PatientID field is a Primary Key only the new patients are added to the table.

 

[EBGreen]

So the patient ID is in the text file? If so, and if they are assumed to be unique(since you use them as the Primary Key), then why do you need to jack around with autonumbering anyway?

[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]

Okay .... long story. The data actually comes from three sources, a unix server (the patient data file which goes into the Patient_join_list) and two lab machines. Each lab machine sends it's data out in a text file. Lab machine #1 allows for complete patient IDs (1234.0) but no Patient names. Lab Machine #2 only allows for short patient IDs (1234) and no patient names. I use the Patient_join_list to fill in the patient names on the reports for Lab Machine #1. That won't work for Lab machine #2 (incomplete IDs). So if I can get access to assign a unique long integer to an ID field in the Patient_join_list I can use that as the patient id on Lab Machine #2 and then use a query to match everything up. The problem with using an auto-number is how it incriments each time I update the Patient_join_list. If I could get a text file with just the new patients it would be easier but that's not an option.

 

[EBGreen]

So lab machine #2 is limited to 4 digits for the patient ID? What happens to the 10,001st patient that needs to be entered into the db?

[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, actually it will hold 9 places ... just nothing to the right of the decimal point.

 

[lespaul]

So how do you get the text files into your database?

I have used a JOIN that takes a 12 character field in one table and joins into a 9 character field in another table by: (Table1 has the longer data)

SELECT * From TABLE1 INNER JOIN Table2 on Left(Table1.FieldName, 9) = Table2.FieldName

Perhaps you could use something similar to join the two ID fields that don't exactly match?

leslie

 

[Klo]

The problem is that say for Lab Machine #2 the patient ID entered into the machine is 1234. That could be 1234.0, 1234.1, 1234.2, 1234.3, or even 1234.4. That's 5 different patients. The Lab tech knows who it is but not access. Lab machine #1 would allow 1234.3 so no problem.

 

[lespaul]

So how do you know which patient it really is?

Leslie

 

[Klo]

Okay .. This is how it worked until I had the problem with auto-number. The Lab tech opens a form called IMX worksheet. (Lab machine #2 is an IMX) It has 5 fields. ID, patient ID, Last Name, First Name and MI. She puts in the Patient Id. The other fields are automatically filled in. She prints that out, takes it to the IMX and uses the ID # instead of the patient ID. When the data comes back it has the ID # and I can use a query to substitute the patient ID and Name etc. All this works fine except for using an auto-number. Thats why I was so disappointed at not getting that function to work.

 

[lespaul]

OK, so the text file you get from LabMachine2 has a field in it that you can use in another query to get the "real" patientID field?

Leslie

 

[Klo]

yes

 

[lespaul]

Ok, what is the code for the macro you use to import the text files?



Leslie

 

[Klo]

Public Function Import_Patient_Names()
On Error GoTo Import_Patient_Names_Err

' Imports patientnames.txt file to the Patient join list table
DoCmd.TransferText acImportDelim, "Patientnames Import Specification", "Patient_join_list", "c:\Cobasmira\Data\Patientnames.txt", False, ""


Import_Patient_Names_Exit:
Exit Function

Import_Patient_Names_Err:
MsgBox Error$
Resume Import_Patient_Names_Exit

End Function

 

[EBGreen]

Try this:

Public Function Import_Patient_Names()
On Error GoTo Import_Patient_Names_Err

    ' Imports patientnames.txt file to the Patient join list table
    DoCmd.TransferText acImportDelim, "Patientnames Import Specification", "Patient_join_list", "c:\Cobasmira\Data\Patientnames.txt", False, ""

setRecordId("Patient_join_list", "ID")

Import_Patient_Names_Exit:
    Exit Function

Import_Patient_Names_Err:
    MsgBox Error$
    Resume Import_Patient_Names_Exit

End Function

[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]