Data Entry Form to create multiple records in single table

I need to create a database to record daily departmental sales from the cash registers in a shop. I have 2 tables Sales and Depts. Sales = SaleId(autonumber), Date, Reg, Dept, Value. Depts = Dept, Vat

I need to create a form to enter the data. I want the top part of the form to have Date, Reg No.and Total Sales. Then I want all the possible depts listed with a field to enter the sales values. I want to be then able to click a save button which will firstly check that the total input for each dept equals the Total sales and then write a record to the Sales table for each department.

Has anybody any ideas on how I might achieve this?:

I need to be able to later summarise the departmental totals by register.

Sorry Umcle, There are 2 cash registers, So the register number is either 1 or 2
My question wasn't very clear, Is the "register" that you refer to an arbitrary number or is it an actual "cash register"? Today 10:05 PM
My question wasn't very clear, Is the "register" that you refer to an arbitrary number or is it an actual "cash register"? Today 10:05 PM

Thanks for your help, Uncle.

I have now created an unbound form for Date, Reg and Total Reading with a subform in datasheet format based on the tblDepts. I added a field called tempValue to the tblDepts to temporarily hold the dept sales values. (This field will be cleared after the form is closed.) This arrangement gives me the layout I require. However I now need to work out the following:

1. I need to add a control to the main form to sum all the values entered in the subform. This will allow the user to check that the total input to the subform agrees with the total reading on the main form. Any ideas?

2. I then need to create a control that will insert a record in tblSales for each non zero department in the subform (using the Insert Sql statement?) This will use the date and the reg fields from the main form and the dept and tempValue fields from the subform to populate SaleDate, SaleReg, SaleDept and Sale Value field. What sort of code would I need ?

The reason I am using tblDepts as the subform is that this is the only way I can see of automatically listing all the departments so that the user does not have to enter any department details, they appear automatically on the screen. I attach a file showing a copy of my form

I created a query qryDeptTempTot to total the tempValue field, and subsequently created a subform which is now on the main data entry form. This unfortunately does not update dynamically as I input the departmental figures

Thanks for all you help Uncle,
I need to spend some time to work out how your code works.
I fear I will have some more questions

I do not want to add the records to tblSales until I am sure that the data entry is correct and I do not want entries for depts with zero sales.That is why I want to use temporary values in tblDepts.
I have tried to modify the “insert into” code kindly provided by Uncle Gizmo to add the data from my frmDataEntry to tblSales but I get an error:
“syntax error in Insert Into statement”. I attach my database and this is the code:-

Public Sub fInsertRecord(datDate As Date, lngTillRegister As Long, lngDeptsID As Long, dblValue As Double)

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

'Set Reference To:-
'VBE > Tools > Reference
'Microsoft ActiveX Data Objects (chances are anything from 2.1 - 2.8 would be fine.
'There's not a lot of reason not to choose 2.8 though really.)
Dim adoCon As ADODB.Connection
Dim adoCmd As ADODB.Command

Set adoCon = CurrentProject.Connection
Set adoCmd = New ADODB.Command

strSQL1 = "INSERT INTO tblSales(Date, Till_ID, Dept_ID, value) VALUES ("
strSQL2 = ", "
strSQL3 = " )"

'Need to add code not to insert if sales value is zero

strSQL = strSQL1 & SQLDate(datDate) & strSQL2 & lngTillRegister & strSQL2 & lngDeptsID & strSQL2 & dblValue & strSQL3
MsgBox " >>> " & strSQL
'Use a Command Object to issue an SQL statement
With adoCmd
.ActiveConnection = adoCon
.CommandType = adCmdText
.CommandText = strSQL
.Execute ‘ error appears here
End With

adoCon.Close
Set adoCon = Nothing
Set adoCmd = Nothing
End Sub 'fInsertRecord

Has anyone any ideas where I am going astray?

Thanks again Uncle Gizmo, That sorted the problem - I will bear those reserved words in mind in future. My form is completed and I have learnt how to use "Insert Into”. However I will be back to this forum - it is a fantastic resource and I have a lot to learn!
I attach the database so that people can see how this was resolved.

Hi
Very nice project
Uncle Gizmo
I want to ask is it posible to imput in the VBA code of the form to add "time" . Local time from computer. I sow that code inputs "date" , but i need "time". Maybe in diferent cell ,or whit this cell bouth whit "date"

Thanks in advance