473,545 Members | 2,091 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Adding Unbound Data to Table

47 New Member
{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?
Expand|Select|Wrap|Line Numbers
  1. 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...
Mar 10 '21 #1
14 4913
3,653 Recognized Expert Moderator Specialist

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.

Underfortunatel y, 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!

Mar 10 '21 #2
32,563 Recognized Expert Moderator MVP
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.
Mar 10 '21 #3
47 New Member
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.    strSQL = "INSERT INTO table(field)VALUES('" & Me.formFieldName & "')"
  3.    DoCmd.RunSQL strSQL
Anyway, here are the unbound fields, form and table names for reference

Form Name: frm_PayrollSubm it
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

Type Text Box, Format Short Time - User Entered Fields
INMon, INTue, INWed, INThu, INFri, INSat, INSun

Type Text Box, Format Fixed - Calculated Fields based on the following expression
Expand|Select|Wrap|Line Numbers
  1. =Val(Nz(DateDiff('n',[INMon],[OUTMon])\60-IIf([LCHMon],0.5,0),0))
  2. =Val(Nz(DateDiff('n',[INTue],[OUTTue])\60-IIf([LCHTue],0.5,0),0))

REG =IIf([WEEK]>44,44,[WEEK])
OT =IIf([UWEEK]>44,[UWEEK]-44,0)

Type Text Box, Format Currency
Mar 10 '21 #4
32,563 Recognized Expert Moderator MVP
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.
Mar 11 '21 #5
32,563 Recognized Expert Moderator MVP
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_DataC alcs1), 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.
Mar 11 '21 #6
47 New Member
Leaning towards option 5.

The new form I have created is frm_PayrollSubm it 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.
Mar 11 '21 #7
32,563 Recognized Expert Moderator MVP
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 misunderstandin g but something doesn't add up somewhere.
Mar 11 '21 #8
47 New Member

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
Expand|Select|Wrap|Line Numbers
  1. =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:
Expand|Select|Wrap|Line Numbers
  1. =([MON]+[UTUE]+[UWED]+[UTHU]+[UFRI]+[USAT]+[USUN])
  2. =IIf([UWEEK]>44,44,[UWEEK])
  3. =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?
Mar 12 '21 #9
32,563 Recognized Expert Moderator MVP
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.
Mar 12 '21 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: srinik | last post by:
Guy’s, Adding Index to Table may reduce performance of Some Select? Thanks in advance, Srini
by: Bill Stock | last post by:
The few times in the past that I've loaded unbound data, I've tended to cheat and use temp tables (not really unbound) or use code for small datasets. I'm currently involved in a project that has numerous tables in the 200 column range, with several thousand rows of data. A consulting review prior to my involvement stressed the wasted space...
by: Luis Esteban Valencia | last post by:
Hello I wrote a program with code behind in C# to add row into table dynamically and the program worked very well in .Net Framework 1.1. When I run this program in .Net Framework 2.0 beta version, the program is not working as in version 1.1. So what is the problem? Microsoft declared that the version 2.0 is fully backward support, but...
by: Brian Mitchell | last post by:
Ok, I know this is an elementary question but I have a data grid that is bound to a data table and I can't seem to find a way to match the selected row in the grid with it's respective row in the underlying data table. If the rows in the grid are in the same order as the rows in the table then I can use the Datagrid.CurrentRowIndex to return...
by: John | last post by:
Hi When using Table Adapter Configuration Wizard if 'Use SQL Statements' is selected as Command Type, the data table's name in dataset is retained and only its data adapter's select statements are replaced. If however 'Create new stored procedures' is selected as Command Type, the data table name in replaced by the name of the newly created...
by: DH | last post by:
I have an untyped dataset with a table. I am trying to programmatically add a row to this table. This was working in VS 2003 / .net 1.1 I am receiving an error "Object reference not set to an instance of an object." Here is the code that worked in VS 2003 / .net 1.1: row = dtLines.NewRow()
by: JMO | last post by:
I can import a csv file with no problem. I can also add columns to the datagrid upon import. I want to be able to start importing at the 3rd row. This will pick up the headers necessary for the datagrid. Once I can get to that point I need some way to be able to add new data only to the new columns that were added. Here is some of my...
by: blitzenn | last post by:
I am attempting to use triggers to add some simple auditing to my database structure (SQL Server 2005). I am running an ASP with this so I have many databases (dozens) with identical structure. My problem is that as we create new databases and tables, we have never paid attention to column ordering. Who cares right? Well triggers seem to care...
by: sathyashrayan | last post by:
Dear group, A mysql wrapper class, which just uses the CRUD operations for large scale php development. Is the same advisable for the mysql stored procedure? Following is a link I got from search http://www.phpclasses.org/browse/package/1615.html. All it does is do the ADD/EDIT/DELETE/View in stored procedure file. Any more links possible?...
by: Mel | last post by:
I want to display custom data in some sort of control on a web page. By custom data I mean it's not in a database yet it just resides in a List Box on a different tab . I just want to display the data and allow the user to edit a couple values. What control can I use that will allow me to manually fill it with whatever data I want and allow...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.