Repost: Please help! Or just tell me if not possible...

[Tandy]

I am trying to make a seemingly simple form, but am having no luck. My table
( PayType/Rate) has 3 fields (PayGroup, PayType and PayRate). The way it is
setup is that every PayGroup has three PayTypes (Weekday, Weekend and
Holiday) with different PayRates. So my table looks something like this:
PayGroup PayType PayRate
1 Weekday $10
1 Weekend $11
1 Holiday $12
2 Weekday $15
2 Weekend $16
2 Holiday $17

Right now when I make tabular data entry form, it shows only one line to
enter PayGroup, PayType and PayRate. It would be really nice for my user (who
has never used Access) if the form listed three lines, one having PayGroup
and PayRate blank, but PayType saying "Weekday", the next with "Weekend" and
the last iwth "Holiday". It would also be nice if I create a dialog box to
prompt my user for the PayGroup number and fill it in on the three lines, so
all my user would have to fill in is the new PayRates. This what I am
thinking:

PayGroup PayType PayRate
3 Weekday ___
3 Weekend ___
3 Holiday ___

I would really appreciate any help anyone could give!

 

[KARL DEWEY]

I think you need to change you table structure --
PayGroup Weekday Weekend Holiday
1 10.00 15.30 21.50
2 7.45 9.25 14.97

 

[tina]

putting data (weekday, weekend, holiday = pay rate categories) in field
names violates standard relational design rules. i try never to say never,
so it's possible there could be a sound business reason to break that rule
in a very rare instance - but, IMHO, it would take a thorough analysis of
the business process and the database to make that decision.

hth

 

[tina]

hi Tandy. i came up with a fairly simple data entry solution that meets the
requirements you outlined, using the same tables that i built the other day
to figure out a solution to your separate query post. i don't think i can
clearly explain the steps to set up the data entry form in a post, though -
at least not without writing a book! if you want to post your email address,
i can send the demo db to you so you can examine the setup for yourself.

if you post your email address, be sure to "disguise" it so you don't get
spammed to death, something like
myemailREMOVEALLTHECAPITALSaddressatsomethingMORECAPITALSdotcom

the demo db is in Access 2000. i can convert to A97 or A2002-2003, if
necessary.

hth

 

[SteveS]

I am trying to make a seemingly simple form, but am having no luck. My table
( PayType/Rate) has 3 fields (PayGroup, PayType and PayRate). The way it is
setup is that every PayGroup has three PayTypes (Weekday, Weekend and
Holiday) with different PayRates. So my table looks something like this:
PayGroup PayType PayRate
1 Weekday $10
1 Weekend $11
1 Holiday $12
2 Weekday $15
2 Weekend $16
2 Holiday $17

Right now when I make tabular data entry form, it shows only one line to
enter PayGroup, PayType and PayRate. It would be really nice for my user (who
has never used Access) if the form listed three lines, one having PayGroup
and PayRate blank, but PayType saying "Weekday", the next with "Weekend" and
the last iwth "Holiday". It would also be nice if I create a dialog box to
prompt my user for the PayGroup number and fill it in on the three lines, so
all my user would have to fill in is the new PayRates. This what I am
thinking:

PayGroup PayType PayRate
3 Weekday ___
3 Weekend ___
3 Holiday ___

I would really appreciate any help anyone could give!

Have you thought about using an unbound form? You could have a combo box to
pick the Paygroup, three labels and three text boxes for the PayRate.

PayGroup PayType PayRate
Weekday $ ___
cbo Weekend $ ___
Holiday $ ___


Add Save/ Cancel buttons for control. The code behind the button could ask
if you reall want to save the new data, then insert three rows. All you have
to do is select a PayGroup (combo box), enter three amounts and click one
button!

Code for Save button

'*** begin code ***
Private Sub Command2_Click()
Dim response As Integer
Dim rst As DAO.Recordset
Dim i As Integer
Dim msg As String

If IsNull(Me.cboPayGroup) Then
MsgBox "Please select a group"
Me.cboPayGroup.SetFocus
Me.cboPayGroup.Dropdown
Exit Sub
ElseIf IsNull(Me.PR1) Then
MsgBox "Please enter a rate for Weekday"
Me.PR1.SetFocus
Exit Sub
ElseIf IsNull(Me.PR2) Then
MsgBox "Please enter a rate for Weekend"
Me.PR2.SetFocus
Exit Sub
ElseIf IsNull(Me.PR3) Then
MsgBox "Please enter a rate for Holiday"
Me.PR3.SetFocus
Exit Sub
End If

msg = "Are you sure you want to save the new data?"

response = MsgBox(msg, vbYesNo + vbQuestion + vbDefaultButton2)
If response = vbYes Then
'change tblPay to the name of your table
Set rst = CurrentDb.OpenRecordset("tblPay")
With rst
'add three records
For i = 1 To 3
.AddNew 'add new record
!PayGroup = Me.cboPayGroup
Select Case i
Case 1
!PayType = "WeekDay"
Case 2
!PayType = "Weekend"
Case 3
!PayType = "Holiday"
End Select
!payRate = Me("PR" & i).Value
.Update 'save it
Next
.Close 'recordset
End With

Set rst = Nothing 'destroy reference

'clear entries
Me.cboPayGroup = Null
Me.PR1 = Null
Me.PR2 = Null
Me.PR3 = Null

End If
End Sub
'*** end code ***

Remember to change the field names and table name...

HTH

 

[Tandy]

Tina,

Here is the message I wrote to you under my List Box thread:

" Hi! I would love to see your database solution, for this post and the
one on 7/15. I'm sorry, I only saw your first post on 7/15, so that's why I
never replied. Anyway, I am going to to give you a different e-mail than the
one I use for this discussion group (I don't really care if this one ends up
getting any junk). If it is not a hassle just RE me to tell me you e-mailed
it. Thank you so much for your help. You have no idea how much I appreciate
you taking the time to really understand my problem and solve it! The e-mail
address is tandy underscore e at hotmail dot com. I hope that's how you hide
the e-mail address! "

Thanks again,
Tandy