Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Question Unanswered: Create sequential numbering when adding new record in subform

    I have a two tables that looks like this:

    [tblWorksheet]
    Ref_id (PK) autonumber
    Date Date/Time
    Employee_id Integer
    Shift_nr Integer


    [tblWorksheet_details]
    Ref_id (PK) autonumber
    Task_nr Integer
    Start_time Date/Time
    End_time Date/Time
    Activity_nr Integer


    And two forms, bounded together by a linked parent/child field [Ref_id]

    frmMainform:
    txtboxRef_id
    txtboxDate
    cboEmployee_id
    cboShift_nr

    frmSubform:
    txtboxRef_id
    txtboxTask_nr
    txtboxStart_time
    txtboxEnd_time
    cboActivity_nr



    What i need to create is this:

    One form that has two-parts, Mainform and a Subform.
    In the mainform, when entering a New "Worksheet", you have to fill in Date, (the Ref_id is already automatically filled in cause its of datatype Autonumber), then choose Employer from combobox, select Shift from combobox, after you go to next field (this is the end of the mainform) you would jump to first field in the Subform, in this case txtboxTask_nr.
    Now when cursor jumps to this field 'txtboxTask_nr', i would the value 1 to be put into this field and be saved in the table [tblWorksheet]. Right after, user has to fill in the rest of the fields in the row (txtboxStart_time, txtboxEnd_time, cboActivity_nr) this would be saved in 1 row in the table. When cursor is at the end and user toggles to the Taborder, and starts with new row, cursor would jump to txtboxTask_nr again and would automatically get value 2 (task '2') assigned. HOw to do this?

    I also have a cmdbutton in the footer of the subform 'Add record'
    When pushed, it would check how many tasks were currently entered (existing) in the table for specific Employer, on specific Date, bound by the unique Ref_id.
    And then would add counter by 1, meaning if there are two Tasks currently created in the table for (1,2...), then when 'Add record' is clicked, the next row would automatically get value 3 entered into txtboxTask_nr.
    Anybody know how to do this? Im struggling with it for too long now, so advice is much appreciated! Thanks alot, and keep up the good work.



    ------- Mainform --------
    Ref_id
    Employee_id
    Date
    Shift_nr


    ------- Subform ---------

    Task_nr Activity Start_time End_time
    1 Administrative 09:00 10:00
    2 Break 10:00 10:30
    3 Direct Labor 10:30 11:00

    *note Ref_id is also in Subform (but Not Visable)

    I use a query to show (Ref_id, Task_nr, Activity_nr, Start_time, End_time)
    Last edited by Milkymouse; 12-23-08 at 18:02.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I hope my suggestions can help. I have a basic grasp and but generally try to stick to a number of simple solutions rather than going all out. These are possible solutions, if they sound like they'd fit let me know and I or someone else will help you develop them.

    Firstly you want the main form to save after updating cboShift_nr? If thats the case, put an AFTER_UPDATE code within cboShift_nr that simply saves the record.

    -----
    Private Sub cboShift_nr_AfterUpdate()
    On Error GoTo Err_cboShift_nr_AfterUpdate

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Exit_cboShift_nr_AfterUpdate:
    Exit Sub

    Err_cboShift_nr_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cboShift_nr_AfterUpdate

    End Sub
    ----------

    Secondly, you want the values 1 automatically entered into txtboxTask_nr and saved? Would it not be possible to make the default value of txboxTask_nr to simply say 1? Then, as the subform gains focus, the text box should give the default value and you could then run a Form_GotFocus() or a Form_OnLoad() event similar to the above that will save the form and this record.

    Your next step is to complete the record, save it and generate a new row with number 2? or a gradually increasing value? This could be done by using a code that counts the records and adds 1 to this. This can be added to an AfterUpdate event or you could put this into the OnFocus event of txtboxTask_nr which should also generate the 1 value when there are no records...

    ----------

    Private Sub EndTime_AfterUpdate()

    Dim ***DATABASE NAME HERE*** As Database
    Dim rstjobrows As Recordset
    Dim CallRecordCount

    Set *DATABASE NAME HERE* = OpenDatabase("*DATABASE PATH HERE*")
    With *DATABASE NAME HERE*
    Set rstjobrows = .OpenRecordset("tblWorksheet_details")
    Debug.Print "Table-type recordset from tblworksheet_details"

    CallRecordCount = rstjobrows.RecordCount

    rstjobrows.Close

    End With

    Me.txtboxTask_nr = CallRecordCount + 1

    End Sub
    ----------

    Both of these examples are totally untested! I don't know if they will work or not and certainly with the second one you would need to tweak this quite a bit... i.e. you would need to somehow calculate the number of rows with the condition that ref_id = ???

    I kinda lost the plot by the way so sorry if this doesn't help.

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sorry I have just thought carefully about what I have written and the second option WILL NOT work. This code will only count the number of records within an existing table (not a query). If anyone knows how to convert the above so it applies to queries it would help though...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If someone added a new session into your data e.g.:
    Code:
    Task_nr      Activity               Start_time           End_time
    1               Administrative        09:00                 10:00
    2               Break         10:00                10:30    
    3               Direct Labor     10:30                11:00
    becomes:
    Code:
    Task_nr      Activity               Start_time           End_time
    ?        Administrative        09:00                 10:00
    ?        Break         10:00                10:15
    ?        Freestyle Yodelling         10:15         10:30
    ?        Direct Labor     10:30                11:00
    What should the "?"s be?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2008
    Posts
    6
    Hi pootle,

    Code:
    Task_nr      Activity               Start_time           End_time
    ?        Administrative        09:00                 10:00
    ?        Break         10:00                10:15
    ?        Freestyle Yodelling         10:15         10:30
    ?        Direct Labor     10:30                11:00
    What should the "?"s be?[/QUOTE]

    the numbers should be:

    1 Administrative 09:00 10:00
    2 Break 10:00 10:15
    3 Freestyle Yodelling 10:15 10:30
    4 Direct Labor 10:30 11:00

    the querie is sorted by refnr, then by tasknr en then startime.
    so user has to manually create their order of tasks. But it would be better if someone inserts a new task, the endtime of the previous task , would be automatically the starttime of the new task. (but thats a bridge tofar for now )

    Thnx alot christyxo for your reply, i will try your suggestion, when im back at my office.

    i was thinking of using some bit of sql to count the rows

    something like:
    Select refnr, count tasknr from tblWorksheet_details group by refnr

    and then add it by 1 (when the button "Add record" is pressed)

    i dunno yet how to do this, any suggestion of this code is appreciated.

  6. #6
    Join Date
    Dec 2008
    Posts
    6
    Hello out there, does anybody knows how to make this work?

    i'm trying to write this vba code, to do several things:
    1- to check the amount of records (Task_nr) there are for current employer (Ref_Nr)
    2- And set counter to +1
    3- Then write this "number" into the field "Task_nr" in the new created record

    i think its possible and not too complicated if you know more about vba then me : )
    * and then put this code behind a button "Add Record", this button is on a subform

    any suggestions is appreciated, thnks

  7. #7
    Join Date
    Dec 2008
    Posts
    6
    Little Update from me....

    I've tried to force a 'Count records (Where Ref_nr is Ref_Nr in Mainform))' using SQL Query and then do An "Insert INTO" But this is almost impossible (as it seems)! I've looked everywhere but could not find a workable solution/example :P
    The recordCount didnt work for me because i wanted to count only the records in my subform, where Ref_Nr = Mainform.Ref_Nr


    So right now i'll stick to my "Simple Solution" - i've added an extra txtbox in my Subform's footer, a txtbox named "txtCountTaskNr" with code in Controlsource =Count([Task_Nr]), This will count the amount of records in the subform, which is linked to mainform on [Ref_Nr].

    Then i've put some lines in the OnClick() event from my "Add Records" button.

    Code:
    Private Sub cmdAddRecord_Click()
    On Error GoTo Err_cmdAddRecord_Click
            
        'First set the cursor to the field 'Task_Nr' IF the cursor is in Mainform
        Task_Nr.SetFocus
        
        'If the cursor is already in the Subform, then go to next record
        DoCmd.GoToRecord , , acNewRec
        
        'Print Counted Task_Nrs +1 into Task_Nr-field
        Me.Task_Nr = Me.txtCountTask_Nr + 1
        
    Exit_cmdAddRecord_Click:
        Exit Sub
    
    Err_cmdAddRecord_Click:
        MsgBox Err.Description
        Resume Exit_cmdAddRecord_Click
        
    End Sub
    This works pretty well, but is not ideal, for example when somebody delete's Record 2 when there are 3 records (1,2,3)....

    I think i have to write a IF/ELse statement to prevent double Task_Nrs being entered into the field. If any body have suggestions, pls reply
    Last edited by Milkymouse; 02-02-09 at 16:02.

Posting Permissions

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