Adding Unbound Data to Table

[SueHopson]

{This thread has been created as an offshoot of Adding Time in Calculated Time Fields returning as Strings}

I have gotten a bit of user feedback, I have a question I would like to ask. If I wanted to set the form fields to unbound and use a button to append the resulting info to a table, how would I write the append expressions in VBA?

MON: Val(Nz(DateDiff('n',[IN-Mon],[OUT-Mon])\60-IIf([LCH-Mon],0.5,0),0))

The goal would now be to have the user confirm the unbound data on the entry screen and append that data to the data_Payroll table as values (rather than the calculated fields they are now currently in the existing query).

Any and all other thoughts would be great...

[twinnyfo]

Sue,

In general, you would have a procedure in the AfterUpdate event of your two (unbound) text boxes. The code would evaluate both values to make sure they fit the requirements you are looking for. If so, then you enable a command button that will save the data in those two text boxes to your table.

Instead of using field names, you would use the names of your text boxes. I also recommend establishing a variable that calculates what you need and save the value of the variable to your table. This allows you to 1) evaluate the results of your code before you write it to your table and 2) doesn't break your table if it doesn't work right. This is just a good practice to get into.

Underfortunately, I don't have the time to scatter something in code for you right now, but we are glad to hepp work through your stumbles (if any).

Hope this hepps!

:-)

[NeoPa]

Hi Sue.

I'm a little unclear on whether the data is calculated in the query - and thus bound - or simply entered in by the user - which might not be.

The solutions would be quite different of course.

[SueHopson]

OK, to clarify (hopefully). Let me know if it isn't.
All of the form fields are currently unbound and all calculations are being done on the form itself as shown in the field name descriptions below.

I wanted to try moving the data entry submit to a VBA code/button to ensure that records weren't being "lost" by validating the only 2 required entries and using error messages if possible.

I have been playing around with trying to learn/understand the code for a while, but regrettably INSERT INTO seems to be a little beyond my skillset in terms of how to define the strSQL. For example, I know As String is for Text Boxes and that there would be no single quotes for numbers (as in the example below), but I have no idea how to work it for multiple fields and field types. Also, I have found examples where every field on the form is defined as a string, and others where it isn't... so yeah, confused.

Dim strSQL As String
   strSQL = "INSERT INTO table(field)VALUES('" & Me.formFieldName & "')"
   DoCmd.RunSQL strSQL

Anyway, here are the unbound fields, form and table names for reference

Form Name: frm_PayrollSubmit
Table to Append Unbound Data to: tbl_PayrollData

Unbound Form Fields
Combo Box - User Selected
REQUIRED cbo_EName, data pulled from tbl_Employees
REQUIRED PPID, data pulled from tbl_PayPeriods

Type Checkbox - User Selected Fields with defaults set to -1, except Fri, Sat, Sun
LCHMon, LCHTue, LCHWed, LCHThu, LCHFri, LCHSat, LCHSun

Type Text Box, Format Short Time - User Entered Fields
INMon, INTue, INWed, INThu, INFri, INSat, INSun
OUTMon, OUTTue, OUTWed, OUTThu, OUTFri, OUTSat, OUTSun

Type Text Box, Format Fixed - Calculated Fields based on the following expression
MON, TUE, WED, THU, FRI, SAT, SUN

=Val(Nz(DateDiff('n',[INMon],[OUTMon])\60-IIf([LCHMon],0.5,0),0))
=Val(Nz(DateDiff('n',[INTue],[OUTTue])\60-IIf([LCHTue],0.5,0),0))

etc

WEEK =([MON]+[TUE]+[WED]+[THU]+[FRI]+[SAT]+[SUN])
REG =IIf([WEEK]>44,44,[WEEK])
OT =IIf([UWEEK]>44,[UWEEK]-44,0)

Type Text Box, Format Currency
VACPD
STATPD

[NeoPa]

Hi Sue.

I may not get to respond today as it's expected to be very busy. You caught me before bed earlier but now I've finished for Wednesday. Next stop Friday after a very busy Thursday I'm afraid :-(

Just time to remove the two earlier posts.

[NeoPa]

Let me see if I can rephrase my question by making a statement of what I think the scenario is and you can tell me where I'm awry if I am.

1. You have a table called [Employee Data Table] with Fields in the form of [IN-Mon], [OUT-Mon] & [LCH-Mon] but for all days of the week.
2. You also have a query (qry_sbfm_DataCalcs1), worked on in the linked thread, that processes that table and returns daily totals (MonTotal, ...) as well as a weekly total (WeekTotal).
3. You use this data on a Form somewhere.
4. You are now considering how to take the bound data from that Form and save it away into a table somewhere.
5. Alternatively, you have a Form with unbound Controls that are yet to be described where the operator enters data - and that data is what you want saved away.
6. Another possible alternative might be that the data is bound as in the first scenario but only gets written away once the operator has done something to confirm acceptance of such data.

As I indicated in my earlier post, which of these alternatives is true makes a big difference to how best to proceed.

[SueHopson]

Leaning towards option 5.

The new form I have created is frm_PayrollSubmit and contains all the unbound fields as shown above.
I want the user to have to click a button to append/save the records on the form to tbl_PayrollData after verifying that both the Employee ID and the Payroll ID are entered (both required fields) and providing the appropriate error messages if they are not.

[NeoPa]

When you say you're leaning towards option #5 do you appreciate that means that none of the data previously discussed, with the formulas to prepare them, would be included in this option? Just the operator typing all the values in except those ID values that identify what the other values pertain to? That doesn't seem to be consistent with some of what you've said already. I may be misunderstanding but something doesn't add up somewhere.

[SueHopson]

OK,

So on my new form are unbound fields for all the entries and calculations. Using Monday, as an example:

InMon - User Entered Short Time field
OutMon - User Entered Short Time field
LchMon - User selectector checkbox
MON - Calculated field, Enable No, performs the calculation on the form using control source

=Val(Nz(DateDiff('n',[INMon],[OUT-Mon])\60-IIf([LCH-Mon],0.5,0),0))

Repeat for the rest of the week.

The WEEK, REG and OT are also calculated on the form using the following in the control source respectively:

=([MON]+[UTUE]+[UWED]+[UTHU]+[UFRI]+[USAT]+[USUN])
=IIf([UWEEK]>44,44,[UWEEK])
=IIf([UWEEK]>44,[UWEEK]-44,0)
 

Maybe I'm making this harder than it has to be, but one user always forgot to select the employee name or the pay period and was creating unmatched records - so I thought I would try this way with a submit button to help reduce her data entry errors...

Does that make things any clearer?

[NeoPa]

Generally speaking then Sue, the advice most database developers would give, based on the principles of Normalisation (See Database Normalisation and Table Structures.) would be to save the basic data, that entered by the operator, and calculate the other values any & every time they're required.

This can be done even with new records in a Query if you like. Query formulas work even within new records of a Query, and thus are available on any Form built on such Queries.

Now, as you've already discovered, it is certainly possible to display a Form; allow data entry; check that the data meets certain basic requirements; allow the operator to trigger a process whereby the data is saved away using SQL and data populated from unbound Controls in the Form.

However, this is very rare because there's rarely any need. Fields in Tables can have quite specific and detailed limits to what data they can hold. The [ValidationRule] Property is not only available for Fields though, Tables have one too where you can specify rules about how the Fields interact. For instance you could say that a record can never be saved with the only one of [InMon] & [OutMon] populated. Either neither or both must be.

Let us know if you still feel you'd prefe to travel the less-trod path of unbound Forms and SQL though. It can be done. I don't recommend it for what I've understood of what you've described so far, but it certainly doesn't hurt to understand the concept. My advice would be to leave it until you're more familiar with easier ways of getting to your destination though. Until you understand well you're more likely to be drawn down the longer/harder route.

[SueHopson]

Got it, and that all makes perfect sense. Honestly, I would prefer to stick with data driven by the query - but this user's lack of attention to detail is making my life difficult to say the least (so many mistakes). So, let's say that I stay with the bound form data we previously worked on and keep the calculations in the query, How would I get the validation rules to function for the fields within the query? And yes, I realize this is a completely different question now... so I appreciate your patience with me. :)

Specifically
Employee ID cannot by null
PayPeriod ID cannot be null

I also like your idea that a record can never be saved with the only one of [InMon] & [OutMon] populated. Either neither or both must be.
Could I take that one step further and set a validation rule that says something like If([DNWMon]=-1,[InMon] & [OutMon] must by empty, [InMon] & [OutMon] must be completed?

[NeoPa]

SueHopson:
this user's lack of attention to detail is making my life difficult to say the least (so many mistakes).

Alternatively :-
This user's lack of attention to detail is making me focus on producing real quality software that doesn't rely on expectations and assumptions. This will stand me in very good stead if I go on to work in more such projects.

Tongue in cheek maybe - a little. You're on a learning curve. I'm very happy to share your frustration at the apparent ineptitude of your users. I'll join you at that any day. However, the term Defensive Programming is a very important one and it refers to designing your projects so that they are immune to the attacks of ID10Ts (Or users if you prefer). Any nudge in that direction is a positive for you - even if it may not seem that way just now ;-)

SueHopson:
How would I get the validation rules to function for the fields within the query?

The Queries don't have ValidationRules as such. These are applied to Tables & Table Fields. However, you can't write records via a Query if the underlying Tables &/or Fields have ValidationRules that are not met.

SueHopson:
Employee ID cannot by null

You don't generally specify that using a ValidationRule. There is a specific Property for Fields caled Required. If that's set to True then Nulls are not allowed in that Field.

SueHopson:
Could I take that one step further and set a validation rule that says something like ...

Absolutely. However you have to make each ValidationRule into a single SQL statement. You can use parentheses but the more conditions you want, and the more complex those conditions are to express, the harder it is to fit them all into an expression. That said if you can express it then it will apply it for you.

One helpful hint would be to put as little in there as you can get away with. If it can be put in one for a Field then do that in preference to doing it in the one for the table.

After all that, and just to complicate matters even further, there is another option, which is to handle all these in the code of your bound Form. There are reasons why one way is better than the other but they depend on the perspective of the developer. The Form_BeforeUpdate() event procedure allows you to take control just prior to the data being written away. It also allows you to cancel the update if the situation demands it.

[SueHopson]

You have given me a lot to think about and I've been doing a lot of research...
I've decided that ahead of Before Update() I'm going to try to code the form to try and make the data entry smoother.

Below is the Private Sub TYPE_Mon_AfterUpdate() I have been developing. But I've hit a snag on one of the required attributes, where Vacation hours requested cannot be greater than 9...

If imontemp.Value > 9.1 Then
MsgBox ("The number of requested hours exceeds the daily max. Please Try Again!")
' How do I get to code to restart the loop at the Input Box stage without having to duplicate the code??

You can see how it fits into the whole code below...

Private Sub TYPE_Mon_AfterUpdate()
If Me.TYPE_Mon = "Full" Then
Me.VACMonHrs = 0
Forms!frm_PayrollSubmit!INMon.SetFocus
    Else
If Me.TYPE_Mon = "Part" Or Me.TYPE_Mon = "DNW" Or Me.TYPE_Mon = "VAC" Then
Dim answer As Integer
answer = MsgBox("Did this employee request Vacation Hours?", vbQuestion + vbYesNo)
        If answer = vbYes Then
            Dim imon As Integer
            imon = InputBox("Enter the number of hours requested (max 9 hours)." & vbNewLine & "For all available hours enter 99", "VACATION HOURS REQUESTED", "Type Here")
            Me.imontemp = imon
                If imontemp.Value = 99 Then
                Me.VACMonMessage = "All available"
                Me.VACMonHrs = 0
                    Else
                Me.VACMonMessage = ""
                    If imontemp.Value > 9 Then
                    MsgBox ("The number of requested hours exceeds the daily max. Please Try Again!")
                    ' How do I get to code to restart the loop at the Input Box stage without having to duplicate the code??
                End If
                Me.VACMonHrs = imon
            End If
            Else
        Me.VACMonReq = 0
        Forms!frm_PayrollSubmit!INMon.SetFocus
  End If
End If
End If
End Sub

As always, open to suggestions if you see something redundant or incorrect or that could just simply be expressed better.

[NeoPa]

SueHopson:
You have given me a lot to think about and I've been doing a lot of research...

Very wise.

SueHopson:
I've decided that ahead of Before Update() I'm going to try to code the form to try and make the data entry smoother.

Very wise.

SueHopson:
How do I get to code to restart the loop at the Input Box stage without having to duplicate the code??

Good question. Using ..._AfterUpdate() you can't.

Value checking for validity is always done in the ..._BeforeUpdate() event procedures. These are available for your data Controls as well as for the Form itself. Form-level checking is roughly equivalent to the Table-level ValidationRule whereas Control-level checking is, again roughly, equivalent to the Field-level ValidationRule.

..._BeforeUpdate() procedures come with a parameter called Cancel which, when found to be set after the event procedure returns, causes the update intended for that object (Control or Form) to be stopped. It doesn't return the object to its previous value; it simply goes back to the point just prior to trying to save it. To return the Control to its unchanged state use :

Call {Control}.Undo()

To return the whole Form to its unchanged state use :

Call Me.Undo()

Care is required here if you want to take control of which Control the cursor should be in. Moving the cursor when there are pending changes in a Control will fire off the update again, which is likely to cancel in the same way. However, there are circumstances where it can make sense. To do so use :

Call {Control}.SetFocus()

[NeoPa]

Style tips.

1. Indenting of code is there to help you, and anyone reading your code. It can do that, but only if used in a logical & consistent way. Items that contain grouping of code together are like bookends. These are left at the original level of indentation whereas the group that they contain are indented. Examples of these are :
   If; Else; End If; ElseIf; Do; Loop; While; Wend; With; End With; etc.
   
   Unfortunately the reverse is also true. Incorrectly, inappropriately or illogically set indents can make code very difficult to read. Far worse even than all code in a single block.
2. I find, personally, that Dim statements are a lot easier to deal with when they are all grouped at the head of your procedure. I know others disagree. For me though, when I'm trying to work at what a line of code does and I see it references a variable I would infinitely prefer to look straight up at the top knowing it should be there, than check every line of code to see if it might be.
   
   I've been through enough in my time to realise just how unnecessarily painful that can be.
3. When writing code in the associated module of a Form or Report - use the Me keyword heavily in your code - but always consistently. Consistency is always a worthy aim as it protects you from confusion and lack of clarity.
   
   Even better, where possible, use the following format :
   

   With Me
       ...
       .VACMonMessage = "All available"
       .VACMonHrs = 0
       ...
   End With