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 --------
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
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
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
Me.txtboxTask_nr = CallRecordCount + 1
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.
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...
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
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.
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
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.
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
'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
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