We help IT Professionals succeed at work.

populate patient number and sc number automatically when form opens

JOHN_STIBBARD
on
393 Views
Last Modified: 2013-11-28
Hello experts,

I have a form containing a subform which based on a temporary table (Temp Data Input1).  When the form is opened, I need the Patient ID numbers (from Patient Demographics table)and SC ID  numbers (from Patients Waiting table) to be automatically populated.  I have code that will populate the Patient ID and SC ID numbers for the first row in the subform, and have tried unsuccessfully to write code to populate the others.

Could someone please advise where the code is incorrect?

Thanks

John
Private Sub Form_Open(Cancel As Integer)
 
    Dim db As Database
    Dim StrSCIDNo As String
    Dim strRightNo As String
    Dim Rs As DAO.Recordset, strSQL As String
    
    If IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![Patient ID]) Or Forms![F_TempDataInput1]![Temp data input 1 subform]![Patient ID] = 0 Then
        StrSCIDNo = DMax("[Patient ID]", "patient demographics") + 1
        strRightNo = Right(StrArtNo, 5)
        Forms![F_TempDataInput1]![Temp data input 1 subform]![Patient ID] = StrSCIDNo & strRightNo
    End If
    If IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![SC ID]) Or Forms![F_TempDataInput1]![Temp data input 1 subform]![SC ID] = 0 Then
        StrSCIDNo1 = DMax("[SC ID]", "Patients Waiting") + 1
        strRightNo1 = Right(StrArtNo, 5)
        Forms![F_TempDataInput1]![Temp data input 1 subform]![SC ID] = StrSCIDNo1 & strRightNo1
    End If
    
    strSQL = "SELECT * FROM [Temp Data Input 1]"
    Set Rs = CurrentDb.OpenRecordset(strSQL)
 
    If Not Rs.BOF Then
        Rs.MoveLast
        Rs.MoveFirst
        Rs.MoveNext
        Do Until Rs.EOF
            StrSCIDNo = DMax("[Patient ID]", "Temp Data Input 1") + 1
            strRightNo = Right(StrArtNo, 5)
            Forms![F_TempDataInput1]![Temp data input 1 subform]![Patient ID] = StrSCIDNo & strRightNo
        Rs.MoveNext
        Loop
   End If
 
End Sub

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
I'm afraid that you are confusing two unrelated ideas here.
You are reading through a recordset, and setting a value in (the same record in) your form each time you move to a new record in the recordset. But nothing links the recordset to the form.  They are separate objects. Moving through the recordset has no effect on where you are in a form.  You just stay on the same record.

I'm not clear from your description whether there are already multiple records in the subform or whether the subform is supposed to be a data entry form.

Author

Commented:
Hello peter57r,
Thanks for the prompt reply.
The subform will hold multiple entries based on the "TempInputData1" table which is generated from excel spreadsheets.  When spreadsheets are received, they are loaded into the temp table, and patient id and SC ID numbers are assigned to them.  Once the operator is sure that everything is in order, the tamporary data is copied onto the 'live data' and the temporary table deleted.
What I am trying to do is to populate the patient id and sc id numbers from the appropriate tables, and then autofill the remainder of the entries on the subform with the appropriate sequential numbers.
John

Author

Commented:
Hello peter57r
This may explain it better.
When the form opens, the next Patient ID and SC ID numbers are populated.  What I need is for all the nunbers on the list to be filled in sequentially.

Patient ID      UR Number  Surname      Given Names     SC ID
13014      110211         WOOD                      SOPHIA               14987
      099082         ALEGARIZ      RENE      
      030406         BUNNESFORD      FERDINAND      
      192889         PEACOCK      LOUIS      
      200146         WILMOTT      FANNY      
      179072         BARKER                      RONALD      

John

Author

Commented:
Sorry,
This should look better.
Patient ID      UR Number  Surname      Given Names     SC ID
13014           110211     WOOD         SOPHIA          14987
                099082     ALEGARIZ     RENE      
                030406     BUNNESFORD   FERDINAND      
                192889     PEACOCK      LOUIS      
                200146     WILMOTT      FANNY      
                179072     BARKER       RONALD      

Open in new window

Author

Commented:
Could anyone else help me with this please? I have increased the points to 200.
CERTIFIED EXPERT

Commented:
Replace the recordset loop with this:

On Error Resume Next
me.dirty = false  'save first record
Do
If Nz(Me.[patient id], 0) = 0 Then
   me.[patient id] = DMax("[Patient ID]", "Temp Data Input 1") + 1
   me.[sc id] =  DMax("[SC ID]", "Temp Data Input 1") + 1
   Me.Dirty = False   ' save the record
 
Else
' do nothing
End If
  DoCmd.GoToRecord , , acNext
If Me.NewRecord = True Then Exit Do
Loop Until Err <> 0

 

Author

Commented:
Hello peter57r,
New day here - explains the delay in reply.
I have tried the code suggested, but no numbers populate the fields.
Should the code be behind the subform rather than the form? I tried "Forms![F_TempDataInput1]![Temp data input 1 subform]![patient id] = DMax("[Patient ID]", "temp data input 1") + 1" instead of the "Me.xxx" statement, but still no luck.
Any thoughts?
Cheers
John
CERTIFIED EXPERT

Commented:
Sorry - yes - I lost track that it was a subform.
I'll need to look at the code again.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks peter57r,
The code as written didn't work exactly as I intended, and I amended it to the following.  It is working fine now.  Thanks for pointing me in the right direction - points are yours.
Cheers
John
Private Sub Form_Open(Cancel As Integer)
 
    Dim db As Database
    Dim StrSCIDNo As String
    Dim strRightNo As String
    
    If IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![patient id]) Then
        StrSCIDNo = DMax("[Patient ID]", "patient demographics") + 1
        strRightNo = Right(StrArtNo, 5)
        Forms![F_TempDataInput1]![Temp data input 1 subform]![patient id] = StrSCIDNo & strRightNo
    ElseIf Not IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![patient id]) Then
        DoCmd.GoToRecord , , acNext
    End If
    If IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![SC ID]) Then
        StrSCIDNo1 = DMax("[SC ID]", "Surgery Connect Patients Waiting") + 1
        strRightNo1 = Right(StrArtNo, 5)
        Forms![F_TempDataInput1]![Temp data input 1 subform]![SC ID] = StrSCIDNo1 & strRightNo1
    End If
    
    Me.Temp_data_input_1_subform.SetFocus
    On Error Resume Next
    Forms![F_TempDataInput1]![Temp data input 1 subform].Dirty = False  'save first record
 
    Do
        DoCmd.GoToRecord , , acNext
        If IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![patient id]) Then
            Forms![F_TempDataInput1]![Temp data input 1 subform]![patient id] = DMax("[Patient ID]", "Temp Data Input 1") + 1
            Forms![F_TempDataInput1]![Temp data input 1 subform]![SC ID] = DMax("[SC ID]", "Temp Data Input 1") + 1
            Forms![F_TempDataInput1]![Temp data input 1 subform].Dirty = False   ' save the record
        Else
            'do nothing
        End If
    
    Loop Until IsNull(Forms![F_TempDataInput1]![Temp data input 1 subform]![Facility Name])
 
End Sub

Open in new window

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.