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?:

TippMan said:

I want the top part of the form to have Date, Reg No.

Click to expand...

The value highlighted in red, do you mean the “register number”? If you do mean register number then I can’t understand why you want it in the top level form? Please could you explain.

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

My question wasn't very clear, Is the "register" that you refer to an arbitrary number or is it an actual "cash 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

At now I understand! I was thinking out of a large store with “departments”, like “menswear” “shoes” “ladies’ undies” a bit like “are you being served” you are in fact referring to the “department keys” you find on a till.

It’s quite a common requirement to have a table automatically filled with a set of values. This can be accomplished by writing code, a function, to add this data to a table, and then display it in a sub-form.

I would suggest on your top level form, the form hosting the sub-form that displays this data, you have a button to perform this task. The operator fills in the date, the total value and any other information in the fields provided; these can either be bound to an underlying table to save the data or unbound.

The information would then be passed into a function which would use an INSERT SQL statement to insert the information into the table.

I would also recommend having a separate table sometimes referred to as a “look up table” to hold the departments.

You currently show two fields for “department” Depts = Dept, Vat – I would suggest an additional field, “department ID” = DeptID....

Hence you would have: Depts = DeptID, Dept, Vat

Probably best written like this: tblDepts = DeptID, DeptDesc, DeptVat

Your sales table would look good like this: tblSales = SaleId(autonumber), SaleDate, SaleReg, SaleDept, SaleValue

There are good reasons for naming your objects like this, for more information search for “naming convention”.

If I understand your question correctly then you will require one row in the sub-form for each “department” if this is the case then I would recommend looping through the department “look up table” and adding a row for each department found there. This means that in the future if you added an extra department then your code would still run correctly.

I suspect from the way you have written your question that you would like to see the department name listed as text in the data generated, however you would be better off using the department ID and not the actual department description. The department description can be shown by using various methods, usually a combo box set to show the text instead of the department ID number.

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 ?

TippMan said:

with a subform in datasheet format based on the tblDepts.

Click to expand...

The sub-form should be based on the tblSales surely?

The code routine I mentioned would automatically populate this sub-form with a row for each department, and then the user would fill in the value of the takings for each department.

After revisiting your last post, I can see the merit in your suggestion. However it does add a level of complexity to the code, which I think is unnecessary.

To reiterate my view of what is needed, on the main form you enter such details as:

The till (register) number: in this case there are two registers let’s call them till
number one and till number two.

The date, I would imagine the default would be “today”

Total sales: this is the total takings taken by a particular till, (register).

A sub-form which would display the sales information. (After being populated as described below)

Once you have entered the above information then you would press a command button which would fill the sub-form with a set of records related to the sales for each department plus an empty field “SaleValue” in which you would enter the value of sales for that particular department.

I understand the reason for you wanting to enter the values in separately against the department table, as you want some way of checking that the sum of the values adds up to the total value however this can still be done with the records shown in this sub-form.

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

This is Sorta what I was thinking (See Attached File)

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?

TippMan said:

"INSERT INTO tblSales(Date, Till_ID, Dept_ID, value)

Click to expand...

Date is a reserved word, change BOTH the Field name and the SQL string to something like: sDate

Value is a reserved word, change BOTH the Field name and the SQL string to something like: sValue

In the Function: 'fGetDepartments

Change This:

To This:

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