Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sequential Numbering Per Parent Record (2003 SP3)

    I am working on a patient record database where the main data entry form has several subforms to record various aspects of a patient's condition. When any new sub-record is created I want independent record numbering for each specific patient. For example, patient A might receive 5 treatments therefore the numbering is from 200 to 204. Patient B might get 3 treatments so the numbering will be from 200 to 202. Each subform has a hidden autonumber field and I've created a dedicated field to store each patient's subform numbering.

    I've put the code within the command button "Add New Record" and it works okay when there existing records in the subform but when it's the first subform record the error "No Current Record" appears.

    Incidentally I've also set the tab key to cycle on the current record only so that no new records are created simply by navigation.

    I am probably just missing something minor but can some shed someone light on this please:

    ================================================== ========
    Dim strSQL As String
    Dim PagingLastID As String
    Dim intPatientID As Integer
    Dim dbs As dao.Database
    Dim rsData As dao.Recordset

    intPatientID = Me.PatientID

    Set dbs = CurrentDb
    strSQL = "SELECT TOP 1 tblMeds.PatientID, tblMeds.PagingE" _
    & " FROM tblPatient INNER JOIN tblMedsON tblPatient.PatientID = tblMeds.PatientID" _
    & " WHERE (((tblPatient.PatientID) = " & intPatientID & "))" _
    & " ORDER BY tblMeds.PagingE DESC;"

    Set rsData = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    PagingLastID = rsData("PagingE")
    rsData.Close
    Set rsData = Nothing

    DoCmd.GoToRecord , , acNewRec

    If IsNull(PagingLastID) Then
    PagingLastID = 300
    Else
    PagingE = PagingLastID + 1
    End If
    ================================================== ========

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Shouldn't it be :
    If IsNull(PagingLastID) Then
    PagingE = 300
    Else
    PagingE = PagingLastID + 1
    End If
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Thanks. The change looked promising but it didn't work.

    I have also tried the code both on Before and After Insert and Before and After Update and each time the error is "No Current Record" and it breaks on line:

    PagingLastID = rsData("PagingE")

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Try this:

    Dim PagingLastID As Integer
    Dim intPatientID As Integer
    intPatientID = Me.PatientID

    PagingLastID = Nz(DMax("PagingE", "tblMeds", "PatientID=" & intPatientID), 299) + 1
    DoCmd.GoToRecord , , acNewRec
    ...

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Still no success. It's breaking on the add new record line with the message "Type Mismatch".

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    If you really get an error message on the line

    DoCmd.GoToRecord , , acNewRec

    the cause is probably somewhere else, not in this code.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Thanks Hans

    The attached file has six patient records and I've left the subform on the tab control to make it accurate to the real version which has several tabs and sub-forms.

    Clicking the subform add button causes the subform paging field to increment correctly but if I add a new record by typing directly into the record, the "before update" code fails.
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Please see <post:=670,074>post 670,074</post:>. You haven't done anything with that reply.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    You only need code for the command button, not for the Before Update event:

    Private Sub cmdAddRecord_Click()
    On Error GoTo Err_cmdAddRecord_Click
    Dim strSQL As String
    Dim varPagingLastID As Variant
    Dim intPatientID As Integer

    intPatientID = Me.PatientID

    DoCmd.GoToRecord , , acNewRec

    varPagingLastID = DMax("PagingE", "tblMeds", "PatientID = " & intPatientID)
    If IsNull(varPagingLastID) Then
    varPagingLastID = 299
    End If
    Me.PagingE = varPagingLastID + 1

    Exit Sub

    Err_cmdAddRecord_Click:
    MsgBox Err.Description, vbExclamation
    End Sub

    See attached version.
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Yes thanks. The attachment I sent did omit your code. Please accept my apologies.

    When the command button is used it works perfectly.

    However, when a new patient record is created, I can start typing directly onto the Meds suform without using the subform command button and it is at that point when the the subform paging is not being created. This explains why I was trying to get the Before Update event to work but without success.

    Can the subform be setup so that the command button must be used under all circumstances? In other words, the subform has all controls invisible until the command button is used or is their an alternative to the Before Update event that would be better?

    Thank you kindly for your help.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Try this:

    Private Sub cmdAddRecord_Click()
    On Error GoTo ErrHandler

    If IsNull(Me.Parent.PatientID) Then
    MsgBox "Create a new patient before entering medication.", vbExclamation
    Else
    DoCmd.GoToRecord , , acNewRec
    End If
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim strSQL As String
    Dim varPagingLastID As Variant
    Dim intPatientID As Integer

    On Error GoTo ErrHandler

    intPatientID = Me.Parent.PatientID
    varPagingLastID = DMax("PagingE", "tblMeds", "PatientID = " & intPatientID)
    If IsNull(varPagingLastID) Then
    varPagingLastID = 299
    End If
    Me.PagingE = varPagingLastID + 1
    Exit Sub

    ErrHandler:
    If Err = 94 Then
    MsgBox "Create a new patient before entering medication.", vbExclamation
    Cancel = True
    Else
    MsgBox Err & Err.Description, vbExclamation
    End If
    End Sub

    It does allow the user to create a new medication record unless there is no patient record yet. The number will be generated the moment the user enters something.
    See attached version.
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering Per Parent Record (2003 SP3)

    Thanks you Hans, it works perfectly! As always I really appreciate your help - and patience!

    Kind regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •