many fields on one form

  • Thread starter Thread starter Paul Foster
  • Start date Start date
P

Paul Foster

I'm trying to track time losses on different lines, and
have a table with fields for: record_id (autonumber);
date; shift; line - lookup to lines table (6); category
of loss - look up to categories table (12); and time
lost.
I'd like to have a form for each shift for supervisors to
fill in, but am having a hard time putting it together.
I want it to have the 6 lines across the top, with the 12
different categories down the side, giving 72 boxes to be
filled in with time lost.
If I've got the information, I could easily display it
using a crosstab query, but whats the best way to design
a form for inputting the data? I obviously want only one
record per line/category per shift.
Any help would be greatly appreciated.
Thanks in advance
Paul Foster
Bournemouth UK
 
One approach would be to set up an unbound form just like
your paper form.

I would create unbound controls for the shift and date
(don't use the reserved word 'date' for the field in the
table).

Add the labels for the line across the form and the
categories down the side. Create 72 unbound controls for
the time lost.

Create a button ('ADD'or 'ENTER DATA') to run code to add
the records.

(See this site: Cycle through controls with common names
at
http://www.mvps.org/access/forms/frm0003.htm)

Think of the controls as an array; if the line table has
the entries (primary key) from 1 to 6 and the categories
(primary key) are 1 to 12, the controls for the time lost
could be named Loss11, Loss12, Loss13, ... Loss612.

The cmdButton code would open an record set, loop thru the
controls creating 72 records and close the recordset.

The loop would be two loops - an outer loop (x= 1 to 6)
for lines and an inner loop (y = 1 to 12) for categories.

The code to create the records would :

Check to make sure the date control is valid
Check to make sure the shift is not null
Open the recordset

For x = 1 to 6 'the line
For y = 1 to 12 'the category
If Not IsNull(Me("Loss" & x & y).Value) ' optional
With rst 'recordset
.AddNew
.dteDate = Me.ShiftDate
.shift = Me.Shift
.Line = x ' the outer loop index
.Category = y ' the inner loop
.Loss = Me("Loss" & x & y).Value 'the time lost
.update
End With
End If 'optional

Close the recordset
Set rst = nothing

Do the loops again setting the controls to null to clear
them.

You can flesh out the rest of the code.....

(Don't forget a 'close form' button)

HTH

Steve
 
I missed the start of this but I have done a few things that require "Things
down" and "things across"
students down and dates across, dates down and rooms across, etc.

I ended up with a bound form to a table that was a flat file.
This allowed the form to be filled in just like they were used to.
The afterupdate for the fields (named Room, SomeDateName, d1...d31) in the
flat file updated the real table that maintained the relational data.
This allows unlimited scrolling etc.
The addition of a subform linked to the "cell" on the main form allowed
filling in other information.
 
Thanks for your response.
Your code is a little bit advanced for me - I've tried
something similar but opening a form bound to the
underlying table instead - it works just the same, but
the code is more longwinded. Your opinion would be
welcome. I dont understand recordsets yet.

Private Sub Command115_Click()
Dim counterc As Integer
Dim counterl As Integer
DoCmd.OpenForm "tblKPIfrm", acViewNormal, acAdd
For counterc = 1 To 12
For counterl = 1 To 6
If Forms("frmSHIFTMASTER")("frmKPI")("Loss" & counterc &
counterl) > 0 Then
DoCmd.GoToRecord acDataForm, "tblKPIfrm", acNewRec
Forms![tblKPIfrm]![SHIFT] = Forms![frmSHIFTMASTER]![SHIFT]
Forms![tblKPIfrm]![DATE] = Forms![frmSHIFTMASTER]![DATE]
Forms![tblKPIfrm]![Line] = counterl
Forms![tblKPIfrm]![Category] = counterc
Forms![tblKPIfrm]![Loss] = Forms("frmSHIFTMASTER")
("frmKPI")("Loss" & counterc & counterl)
Else
End If
Next
Next
DoCmd.Close acForm, "tblKPIfrm", acSaveYes
End Sub

BTW, my date control is called DATE, it seems to work
alright. Should I change it?

Paul Foster
Bournemouth UK
 
Paul,

Glad to be of some help.
BTW, my date control is called DATE, it seems to work
alright. Should I change it?
Click to expand...

If you have a field in a table, acontrol on a form and a
function all named Date, how do you tell them apart? More
important, how does Access??

If they were named dteDate, txtDate and Date, and you knew
that a naming convention was in use, you could figure out
which was a field (dte = date type), which was a control
(txt= text box) and which wsa the function (Date).

This is how I name objects. I also use tbl, frm,
qry...same idea. Additionally, I append a PK (primary key)
and FK (foreign key).

Look at this excelent site for info on naming conventions:

http://mvps.org/access/general/gen0012.htm

I'm a little confused by this statement:
If Forms("frmSHIFTMASTER")("frmKPI")("Loss" & counterc &
counterl) > 0 Then
Click to expand...

Is "frmSHIFTMASTER" the main form?
Is "frmKPI" a sub form?


but other than that, your code is basically (no pun
intended) the same as mine.


Using a record set allows you to control when and how
records are added. Once you start using them, they are
pretty easy.

Run down to the used book store (or thrift store) and get
a book on Access 97 or higher. That is where I get mine.
Try the examples on record sets.. it gets easier <grin>.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


-----Original Message-----
Thanks for your response.
Your code is a little bit advanced for me - I've tried
something similar but opening a form bound to the
underlying table instead - it works just the same, but
the code is more longwinded. Your opinion would be
welcome. I dont understand recordsets yet.

Private Sub Command115_Click()
Dim counterc As Integer
Dim counterl As Integer
DoCmd.OpenForm "tblKPIfrm", acViewNormal, acAdd
For counterc = 1 To 12
For counterl = 1 To 6
If Forms("frmSHIFTMASTER")("frmKPI")("Loss" & counterc &
counterl) > 0 Then
DoCmd.GoToRecord acDataForm, "tblKPIfrm", acNewRec
Forms![tblKPIfrm]![SHIFT] = Forms![frmSHIFTMASTER]![SHIFT]
Forms![tblKPIfrm]![DATE] = Forms![frmSHIFTMASTER]![DATE]
Forms![tblKPIfrm]![Line] = counterl
Forms![tblKPIfrm]![Category] = counterc
Forms![tblKPIfrm]![Loss] = Forms("frmSHIFTMASTER")
("frmKPI")("Loss" & counterc & counterl)
Else
End If
Next
Next
DoCmd.Close acForm, "tblKPIfrm", acSaveYes
End Sub

BTW, my date control is called DATE, it seems to work
alright. Should I change it?

Paul Foster
Bournemouth UK
-----Original Message-----
One approach would be to set up an unbound form just like
your paper form.

I would create unbound controls for the shift and date
(don't use the reserved word 'date' for the field in the
table).

Add the labels for the line across the form and the
categories down the side. Create 72 unbound controls for
the time lost.

Create a button ('ADD'or 'ENTER DATA') to run code to add
the records.

(See this site: Cycle through controls with common names
at
http://www.mvps.org/access/forms/frm0003.htm)

Think of the controls as an array; if the line table has
the entries (primary key) from 1 to 6 and the categories
(primary key) are 1 to 12, the controls for the time lost
could be named Loss11, Loss12, Loss13, ... Loss612.

The cmdButton code would open an record set, loop thru the
controls creating 72 records and close the recordset.

The loop would be two loops - an outer loop (x= 1 to 6)
for lines and an inner loop (y = 1 to 12) for categories.

The code to create the records would :

Check to make sure the date control is valid
Check to make sure the shift is not null
Open the recordset

For x = 1 to 6 'the line
For y = 1 to 12 'the category
If Not IsNull(Me("Loss" & x & y).Value) ' optional
With rst 'recordset
.AddNew
.dteDate = Me.ShiftDate
.shift = Me.Shift
.Line = x ' the outer loop index
.Category = y ' the inner loop
.Loss = Me("Loss" & x & y).Value 'the time lost
.update
End With
End If 'optional

Close the recordset
Set rst = nothing

Do the loops again setting the controls to null to clear
them.

You can flesh out the rest of the code.....

(Don't forget a 'close form' button)

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


.
Click to expand...
.
Click to expand...
 
SteveS said:
Paul,

Glad to be of some help.
Click to expand...
Yes. Date is a reserved word. Access allows it (I DON'T think they should)
but sooner or later it will cause a problem and it will be hard to trace
because it always worked before.
Give it a meaningful name StartDate, LastDate, WhatIsADate?
If you have a field in a table, acontrol on a form and a
function all named Date, how do you tell them apart? More
important, how does Access??

If they were named dteDate, txtDate and Date, and you knew
that a naming convention was in use, you could figure out
which was a field (dte = date type), which was a control
(txt= text box) and which wsa the function (Date).

This is how I name objects. I also use tbl, frm,
qry...same idea. Additionally, I append a PK (primary key)
and FK (foreign key).

Look at this excelent site for info on naming conventions:

http://mvps.org/access/general/gen0012.htm

I'm a little confused by this statement:
If Forms("frmSHIFTMASTER")("frmKPI")("Loss" & counterc &
counterl) > 0 Then
Click to expand...

Is "frmSHIFTMASTER" the main form?
Is "frmKPI" a sub form?


but other than that, your code is basically (no pun
intended) the same as mine.


Using a record set allows you to control when and how
records are added. Once you start using them, they are
pretty easy.

Run down to the used book store (or thrift store) and get
a book on Access 97 or higher. That is where I get mine.
Try the examples on record sets.. it gets easier <grin>.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


-----Original Message-----
Thanks for your response.
Your code is a little bit advanced for me - I've tried
something similar but opening a form bound to the
underlying table instead - it works just the same, but
the code is more longwinded. Your opinion would be
welcome. I dont understand recordsets yet.

Private Sub Command115_Click()
Dim counterc As Integer
Dim counterl As Integer
DoCmd.OpenForm "tblKPIfrm", acViewNormal, acAdd
For counterc = 1 To 12
For counterl = 1 To 6
If Forms("frmSHIFTMASTER")("frmKPI")("Loss" & counterc &
counterl) > 0 Then
DoCmd.GoToRecord acDataForm, "tblKPIfrm", acNewRec
Forms![tblKPIfrm]![SHIFT] = Forms![frmSHIFTMASTER]![SHIFT]
Forms![tblKPIfrm]![DATE] = Forms![frmSHIFTMASTER]![DATE]
Forms![tblKPIfrm]![Line] = counterl
Forms![tblKPIfrm]![Category] = counterc
Forms![tblKPIfrm]![Loss] = Forms("frmSHIFTMASTER")
("frmKPI")("Loss" & counterc & counterl)
Else
End If
Next
Next
DoCmd.Close acForm, "tblKPIfrm", acSaveYes
End Sub

BTW, my date control is called DATE, it seems to work
alright. Should I change it?

Paul Foster
Bournemouth UK
-----Original Message-----
One approach would be to set up an unbound form just like
your paper form.

I would create unbound controls for the shift and date
(don't use the reserved word 'date' for the field in the
table).

Add the labels for the line across the form and the
categories down the side. Create 72 unbound controls for
the time lost.

Create a button ('ADD'or 'ENTER DATA') to run code to add
the records.

(See this site: Cycle through controls with common names
at
http://www.mvps.org/access/forms/frm0003.htm)

Think of the controls as an array; if the line table has
the entries (primary key) from 1 to 6 and the categories
(primary key) are 1 to 12, the controls for the time lost
could be named Loss11, Loss12, Loss13, ... Loss612.

The cmdButton code would open an record set, loop thru the
controls creating 72 records and close the recordset.

The loop would be two loops - an outer loop (x= 1 to 6)
for lines and an inner loop (y = 1 to 12) for categories.

The code to create the records would :

Check to make sure the date control is valid
Check to make sure the shift is not null
Open the recordset

For x = 1 to 6 'the line
For y = 1 to 12 'the category
If Not IsNull(Me("Loss" & x & y).Value) ' optional
With rst 'recordset
.AddNew
.dteDate = Me.ShiftDate
.shift = Me.Shift
.Line = x ' the outer loop index
.Category = y ' the inner loop
.Loss = Me("Loss" & x & y).Value 'the time lost
.update
End With
End If 'optional

Close the recordset
Set rst = nothing

Do the loops again setting the controls to null to clear
them.

You can flesh out the rest of the code.....

(Don't forget a 'close form' button)

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


-----Original Message-----
I'm trying to track time losses on different lines, and
have a table with fields for: record_id (autonumber);
date; shift; line - lookup to lines table (6); category
of loss - look up to categories table (12); and time
lost.
I'd like to have a form for each shift for supervisors to
fill in, but am having a hard time putting it together.
I want it to have the 6 lines across the top, with the 12
different categories down the side, giving 72 boxes to be
filled in with time lost.
If I've got the information, I could easily display it
using a crosstab query, but whats the best way to design
a form for inputting the data? I obviously want only one
record per line/category per shift.
Any help would be greatly appreciated.
Thanks in advance
Paul Foster
Bournemouth UK
.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Steve, Mike,

Thanks to you both for your help and advice. Im changing
the names of everything in my database now.

Paul Foster
Bournemouth UK
-----Original Message-----

SteveS said:
Paul,

Glad to be of some help.
Click to expand...
Yes. Date is a reserved word. Access allows it (I DON'T think they should)
but sooner or later it will cause a problem and it will be hard to trace
because it always worked before.
Give it a meaningful name StartDate, LastDate, WhatIsADate?
If you have a field in a table, acontrol on a form and a
function all named Date, how do you tell them apart? More
important, how does Access??

If they were named dteDate, txtDate and Date, and you knew
that a naming convention was in use, you could figure out
which was a field (dte = date type), which was a control
(txt= text box) and which wsa the function (Date).

This is how I name objects. I also use tbl, frm,
qry...same idea. Additionally, I append a PK (primary key)
and FK (foreign key).

Look at this excelent site for info on naming conventions:

http://mvps.org/access/general/gen0012.htm

I'm a little confused by this statement:


Is "frmSHIFTMASTER" the main form?
Is "frmKPI" a sub form?


but other than that, your code is basically (no pun
intended) the same as mine.


Using a record set allows you to control when and how
records are added. Once you start using them, they are
pretty easy.

Run down to the used book store (or thrift store) and get
a book on Access 97 or higher. That is where I get mine.
Try the examples on record sets.. it gets easier
Click to expand...
HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


-----Original Message-----
Thanks for your response.
Your code is a little bit advanced for me - I've tried
something similar but opening a form bound to the
underlying table instead - it works just the same, but
the code is more longwinded. Your opinion would be
welcome. I dont understand recordsets yet.

Private Sub Command115_Click()
Dim counterc As Integer
Dim counterl As Integer
DoCmd.OpenForm "tblKPIfrm", acViewNormal, acAdd
For counterc = 1 To 12
For counterl = 1 To 6
If Forms("frmSHIFTMASTER")("frmKPI")("Loss" & counterc &
counterl) > 0 Then
DoCmd.GoToRecord acDataForm, "tblKPIfrm", acNewRec
Forms![tblKPIfrm]![SHIFT] = Forms![frmSHIFTMASTER]! [SHIFT]
Forms![tblKPIfrm]![DATE] = Forms![frmSHIFTMASTER]! [DATE]
Forms![tblKPIfrm]![Line] = counterl
Forms![tblKPIfrm]![Category] = counterc
Forms![tblKPIfrm]![Loss] = Forms("frmSHIFTMASTER")
("frmKPI")("Loss" & counterc & counterl)
Else
End If
Next
Next
DoCmd.Close acForm, "tblKPIfrm", acSaveYes
End Sub

BTW, my date control is called DATE, it seems to work
alright. Should I change it?

Paul Foster
Bournemouth UK
-----Original Message-----
One approach would be to set up an unbound form just
like
your paper form.

I would create unbound controls for the shift and date
(don't use the reserved word 'date' for the field in the
table).

Add the labels for the line across the form and the
categories down the side. Create 72 unbound controls for
the time lost.

Create a button ('ADD'or 'ENTER DATA') to run code to
add
the records.

(See this site: Cycle through controls with common names
at
http://www.mvps.org/access/forms/frm0003.htm)

Think of the controls as an array; if the line table has
the entries (primary key) from 1 to 6 and the categories
(primary key) are 1 to 12, the controls for the time
lost
could be named Loss11, Loss12, Loss13, ... Loss612.

The cmdButton code would open an record set, loop thru
the
controls creating 72 records and close the recordset.

The loop would be two loops - an outer loop (x= 1 to 6)
for lines and an inner loop (y = 1 to 12) for
categories.

The code to create the records would :

Check to make sure the date control is valid
Check to make sure the shift is not null
Open the recordset

For x = 1 to 6 'the line
For y = 1 to 12 'the category
If Not IsNull(Me("Loss" & x & y).Value) ' optional
With rst 'recordset
.AddNew
.dteDate = Me.ShiftDate
.shift = Me.Shift
.Line = x ' the outer loop index
.Category = y ' the inner loop
.Loss = Me("Loss" & x & y).Value 'the time lost
.update
End With
End If 'optional

Close the recordset
Set rst = nothing

Do the loops again setting the controls to null to clear
them.

You can flesh out the rest of the code.....

(Don't forget a 'close form' button)

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


-----Original Message-----
I'm trying to track time losses on different lines, and
have a table with fields for: record_id (autonumber);
date; shift; line - lookup to lines table (6); category
of loss - look up to categories table (12); and time
lost.
I'd like to have a form for each shift for supervisors
to
fill in, but am having a hard time putting it
together.
I want it to have the 6 lines across the top, with the
12
different categories down the side, giving 72 boxes to
be
filled in with time lost.
If I've got the information, I could easily display it
using a crosstab query, but whats the best way to
design
a form for inputting the data? I obviously want only
one
record per line/category per shift.
Any help would be greatly appreciated.
Thanks in advance
Paul Foster
Bournemouth UK
.

.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top