.Open command unable to pull data from current table

[Shenlung]

Hello, geniuses of Access.

I am trying to teach myself Access VBA coding to rework a tracker database that has been destroyed over the past year. The database consists of a single table containing some 2200 records with the following columns: LastName, FirstName, MI, Ssno, MOB, Med Status, Med Date Modified, Med Date Due, Dent Status, Dent Date Modified, Dent Date Due, Memo, Updated By, Last4SSno, IDprimary key.

The code I'm trying to run is running on a form that has the fields: txtSSno, txtRecordType, txtLocation, txtName, txtMemo.

The objective of the form is to set the location and record type fields to where ever the record is located and which record type(M for medical, D for dental) is being scanned, then lookup the social in the table, match it to the social being scanned, and then update the record in the table with the proper information. I have spent almost a week solid now google searching and reading everything I could get my hands on to find the answers, but I'm stumped. Here is the code:

Option Compare Database

Private Sub Form_Activate()
  Me.txtSSno = Nothing
  Me.txtName = Nothing
  Me.TxtMemo = Nothing
  Me.txtSSno.SetFocus
End Sub

Private Sub txtSSno_AfterUpdate()
Dim rsTemp As ADODB.Recordset
  
  On Error Resume Next
  Err.Clear
  Set rsTemp = New ADODB.Recordset
  rsTemp.Open "SELECT * from MedicalRecords.table WHERE Ssno = " & Me.txtSSno & "", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
  If Err.Number <> 0 Then
 '   MsgBox "Social not on file.  Click on Menu, then Add a new record, and return here.", vbInformation, "Missing Number!"
    MsgBox "Error" & Err
    Exit Sub
  End If
  rsTemp.MoveFirst
  Me.txtName = rsTemp.Fields("lastname") & ", " & rsTemp.Fields("firstname") & "; [" & rsTemp.Fields("Ssno") & "] "
  rsTemp.Close
  Set rsTemp = Nothing


    DoCmd.SetWarnings False
    If Me.txtLocation = "IN" And Me.txtRecordType = "M" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET MedStatus='IN', MedOut='', MedDue='', Memo='" & Me.TxtMemo & "', WHERE MedicalRecords.Ssno='" & Me.txtSSno & "'"
    ElseIf Me.txtLocation = "IN" And Me.txtRecordType = "D" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET DenStatus='IN',DenOut='',DenDue='',Memo='" & Me.TxtMemo & "', WHERE MedicalRecords.Ssno='" & Me.txtSSno & "'"
    ElseIf Me.txtLocation = "OUT" And Me.txtRecordType = "M" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET MedStatus='OUT',MedOut= #" & Now() & "#,MedDue=#" & DateAdd("d", 14, Now()) & "#,Memo='" & Me.TxtMemo & "', WHERE MedicalRecords.Ssno='" & Me.txtSSno & "'"
    ElseIf Me.txtLocation = "OUT" And Me.txtRecordType = "D" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET DenStatus='OUT',DenOut= #" & Now() & "#,DenDue=#" & DateAdd("d", 14, Now()) & "#,Memo='" & Me.TxtMemo & "', WHERE MedicalRecords.Ssno='" & Me.txtSSno & "'"
    ElseIf Me.txtLocation <> "IN" And Me.txtLocation <> "OUT" And Me.txtRecordType = "M" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET MedStatus='" & Me.txtLocation & "', MedOut= '" & Now() & "', MedDue='" & DateAdd("d", 14, Now()) & "', Memo='" & Me.TxtMemo & "', WHERE Ssno='" & Me.txtSSno & "'"
        MsgBox ("Error" & Err)
    ElseIf Me.txtLocation <> "IN" And Me.txtLocation <> "OUT" And Me.txtRecordType = "D" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET DenStatus='" & Me.txtLocation & "',DenOut= #" & Now() & "#,DenDue=#" & DateAdd("d", 14, Now()) & "#,Memo='" & Me.TxtMemo & "', WHERE MedicalRecords.Ssno='" & Me.txtSSno & "'"
    End If
    DoCmd.SetWarnings True
    Me.txtSSno = ""
    Me.txtName = ""
    Me.TxtMemo = ""
    Me.txtSSno.SetFocus


Err_txtConfirm_Click:
    MsgBox Err.Description
    
    
End Sub

I also want to remove the ID field from the table and make the Ssno the primary key, since no two people should have the same social security number.

Take it with a grain of salt that the code is not going to be elegant, I'm learning and any explanation of WHY you chose to present a solution will be very much appreciated.

Thanks in advance,
Shen

 

[MarkK]

You haven't defined a problem and you haven't asked a question. What fails? How does it fail? Where does it fail? What is 'scan a social?'
Cheers,
Mark

 

[Shenlung]

Holy hell, you're right!

...I need coffee.

The problem appears to lie in the rstemp.Open "Select..." line of code. It gives me an error -2147217913. I have no idea what that error means, nor do i know exactly where the problem sits in that line of code. I use a handheld scanner to scan a barcode on the medical record that inputs the 9 digit social security number into whatever the cursor is in. It requires no special software or anything, it will put the social into anything that accepts text exactly as if it was being typed. The code is written to make it easier to do an inventory on the current record inventory, as well as make signing records in and out a lot easier. The question is : where the heck is the problem? The code looks fine to me, but I wouldn't know if it looked wrong, and I have no screw around test area to attach because the copy I'm using has over 2200 records with socials in it...

If I think of anything else to clarify, or manage to narrow down the location of the error, I'll post again.

 

[MarkK]

What is the name of the table? If it is just 'MedicalRecords' then amend as follows and see what you get...

"SELECT * FROM MedicalRecords WHERE Ssno = " & Me.txtSSno

You don't need to append '.table'.

 

[Shenlung]

Sitting like this, it still gives me that 2147217913 Error. I stepped through it, and the error is definitely coming from the rstemp line... is the (adOpenDynamic, adLockOptimistic, adCmdText) stuff possibly an issue?

Set rsTemp = New ADODB.Recordset
  rsTemp.Open "SELECT * FROM MedicalRecords WHERE Ssno = " & Me.txtSSno & "", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
  If Err.Number <> 0 Then

Also, yes, the name of the table is MedicalRecords. I've verified the names of all the variables/textboxes/table/form at least 5 times each, not that it has helped. I also noticed that the form has the txtSSno as plain text, and the table has Ssno as text... I'm figuring those are the same thing, but figure it's worth mentioning.

 

[boblarson]

First up -

1. I would NOT use SSNo as a primary key. Keep the ID. Because

2. You may find in the not so distant future that even storing SSNos in an Access database (normally non-encrypted and of course easy to make copies of and take away, even for the most novice of users), is going to be against the law.

3. It already is against the law to store them in an Access database when used in conjunction with Healthcare Information (HIPAA).

4. Not everyone HAS a SSNo. Really, its true.

Stick with the current ID and I would say you ought to be making plans to REMOVE the SSNos from the database.

 

[VilaRestal]

Can I just ask (confirm):
Is this an Access Data Project (.adp file) connected to an SQL Server database?
If it's a normal access database (.mdb or .accdb file) then an ADODB recordset won't work, you'd want a DAO recordset.
If it is an ADP then the first parameter
CurrentProject.Connection
should be
CurrentProject.AccessConnection

 

[Shenlung]

Hi Bob,

This is a special case scenario, as this is a medical records tracker for the National Guard units of the state I am in. To reply in sequence:

1) See points 2-4
2) Database is encrypted and stored in a controlled sector of the server; which itself requires a CAC access card to get into. Odds of theft are as close to nil as physically possible.
3) HIPAA does not pertain (though all users still are required to hold a current HIPAA certification due to handling the actual records) because the database contains no healthcare info, it only keeps track of what record is where, when it is due back, when it left, and who signed it out.
4) To get your record into this system, you must have a SSN, as every soldier will be naturalized after graduating basic training/AIT prior to shipping to their duty station.

The SSN's are the filing system for the records, every record has the social on the jacket and the last 4 digits are what they are filed by. If these rules change, the government will be shelling out enough money to get new records made that they can splurge the $2000 to have a new filing database created in place of this patch job I'm doing. The previous iteration of this thing required scanning 4 different barcodes for every record and some complex forms to input records into the database; after it broke and the guy wanted an additional $2000 to come and fix it, they started manipulating to table manually, which lead to me coming in and cutting all the garbage out of it and trying to focus on the little form I now have.

After the shenanigans that have occurred recently with soldiers and their socials, I understand the thought processes, but it is not a fear here, as the records cannot be manipulated unless you are on the network and logged in with your CAC card, and all usage is monitored and recorded.

...I'd be more concerned about the fact that every piece of paperwork soldiers are handed has to have their full social on it, tbqh.

 

[Shenlung]

It is a .mdb file.

Edit:
here is the line that works:

  rsTemp.Open "SELECT * FROM MedicalRecords WHERE Ssno = ' & Me.txtSSno & '", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

Thanks for the help everyone, I'll be back lol

 

[Shenlung]

Currentproject.Connection worked fine, the problem lied in my use of " versus '. Thank you though